Tag Archives: optimizer

Use of optimizer index cost adj Parameter in Oracle

Use of optimizer_index_cost_adj Parameter in Oracle

Optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 10,000 and a default value of 100.

General Setting for OLTP environment:

Alter system Set optimizer_index_caching=80 scope=both;
alter system set optimizer_index_cost_adj=25 scope=both;

OPTIMIZER_INDEX_COST_ADJ is used for choosing the access path selection to be more or less index over a full table scan. As value is making below 100 then it will go to choose index path instead of full table scan.
For OLTP systems, setting parameter to a smaller value (around 25) may result in performance gains as SQL statements change from large-table full-table scans to index range scans.

Note: Value 100 means optimizer has equal rights to choose from multiple path or index path.

A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event.

For Better Result
In order to determine these wait events during a specific time period to perhaps better reflect typical loads during these times, one could also simply run a Statspack or an AWR report and look at the wait event section of the report.

Find optimal value from v$system_event with following formula: (it better to get from AWR or Statspack report)

Optimizer_index_cost_adj = (Avg waits DB file Sequential read/Avg waits DB file scattered read) * 100

alter session set optimizer_index_cost_adj = 50;

Note: For example, a setting of 50 makes the index access path look half as expensive as normal.

Following query give parameter value according to current waits:

- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits)*100 c3,
b.total_waits /(a.total_waits + b.total_waits)*100 c4,
(b.average_wait / a.average_wait)*100 c5
v$system_event a,
v$system_event b
a.event = 'db file scattered read'
b.event = 'db file sequential read';

C1      C2     C3  C4  C5
------- ------ --- --- ---
13,824  5,072  13  86  36   

We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential pre fetch (see db_file_multiblock_read_count):

select value from sys.v_$parameter where name = 'db_file_multiblock_read_count';

alter session set db_file_multiblock_read_count = 32768;

Another query to find the optimal value

set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999
select a.snap_id "Snap",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
dba_hist_system_event a,
dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read';


Adaptive feature caused performance in Oracle 12c

In Oracle 12c, Adaptive feature is released with 12.1 release of Oracle Database.
optimizer_adaptive_features is the parameter in 12.1 released control the behavior of optimizer with one parameter optimizer adaptive features

alter system set optimizer_adaptive_features=false scope=both;
alter system set optimizer_adaptive_features=true scope=both;

In release 12.2, Oracle depreciated the optimizer_adaptive_features parameter into two parameters Optimizer_Adaptive_plans and optimizer_Adaptive_statistics.



Adaptive feature in optimizer is used for making the run time discussion about choose of execution plan and take discussion of Stats. This feature try to generate more optimal plan for query.

Adaptive feature in oracle 12c caused the performance issue in many platform. It increase the overhead of system to choose better plan during execution of Query which some time cause performance issue.

optimizer_adaptive_features default is True has a huge negative impact on the performance of database.


We got following sql statement from statspack report having lot of execution in database which caused the performance degradation in Oracle Database.
On checking on net, it is related with optimizer_adaptive_features (i.e adaptive features introduced in Oracle 12c)

SELECT executions, end_of_fetch_count
    ,elapsed_time / px_servers elapsed_time
    ,cpu_time / px_servers cpu_time
    ,buffer_gets / executions buffer_gets
    ( SELECT
          SUM (executions) AS executions,SUM (
              WHEN px_servers_executions > 0
              THEN px_servers_executions
              ELSE executions
          ) AS px_servers 
          ,SUM (end_of_fetch_count) AS end_of_fetch_count
          ,SUM (elapsed_time) AS elapsed_time
          ,SUM (cpu_time) AS cpu_time
          ,SUM (buffer_gets) AS buffer_gets
          executions > 0
          AND sql_id = : 1
          AND parsing_schema_name = : 2

optimizer_adaptive_features feature is used for making the life of dba easy. So, that optimizer can choose the better plan and stats at run time but in few cases it have very negative impact of database. so you need to disable it by following parameters

alter system set optimizer_adaptive_features=false scope=both;

we can set this hidden parameter:

alter system set "_optimizer_dsdir_usage_control"=0 scope=both;

Dynamic Sampling is also caused performance issue, So, we can also disable with following parameter or you follow link for more detail: Dynamic Sampling

alter system set optimizer_dynamic_sampling=0 scope=both;

Extended statistics Performance issue Oracle 12c

Extended statistics Performance Issue in Oracle 12c

Extended Stats in Oracle 12c caused performance issue with following SQL executing multiple times. Find number of execution in Statspack report or AWR Report consuming lot of CPU:
select default$ from col$ where rowid=:1

On Checking,query is related with extended stats in 12c causing performance issue. In 12c, extended stats is created itself as optimizer needed.
Oracle 12c version create extended statistics automatically, it’s fixed in next 12.2 version. In oracle 11g, you have option to create extended statistics( column Group) manually.
you can follow link for manually created:
In some cases, when we upgrade the database from oracle 11g to 12c.
We faced performance issue due to extended stats (column group ), So we manually disable it and drop the extended stats in database. Steps as following in blog:
On checking Statspack report of Oracle Database following query is consuming CPU.
select default$ from col$ where rowid=:1
Statspack Report:

SQL ordered by CPU DB/Inst: IC/ic Snaps: 8812-8823
-> Total DB CPU (s): 2,637
-> Captured SQL accounts for 15.8% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time(s)  Executions  Exec(s)  %Total  Time(s)  Buffer Gets  Hash Value
-------  ----------  -------  ------  -------  -----------  ----------
75.39    2,610,891   0.00     2.9     79.09    5,221,732    3360804353
select default$ from col$ where rowid=:1


1. For stop using extended stats in Oracle 12c. You can disable the hidden parameter.
alter system set "_optimizer_enable_extended_stats"=FALSE scope=both;
2. You can also change cursor sharing parameter to “FORCE” if your application is using hard parsing(but not recommended)
alter session set cursor_sharing='force' scope=both;
3. You can drop the already created extended stats by following process:

Check the extended stats extension for owner and table:
SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP' and owner ='SCOTT'
If you find the column group or extension present on table, you can take backup of stats and drop the extension (column group) from database by following package:
Note: copy paste extension column of above query to following command:
Drop Command:

exec dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension => '("ENTITY_NO","ACCT_TYPE","DOC_TYPE")');

In my case, By deleting the extended stats will fixed the performance issue in Oracle 12c.
Scripts for drop all the table and schema extended stats

spool E:\stats.txt
SET LONG 50000;
set termout off
set verify off
set longchunksize 200000
set feedback off
set echo off
SELECT 'exec dbms_stats.drop_extended_stats(ownname =>'''||owner ||''',tabname =>'''||table_name||''',extension => '''|| extension||''');' FROM dba_stat_extensions WHERE owner ='SCOTT';

For more detail, please refer following link:

Dynamic sampling performance issue

Dynamic sampling performance issue on 11g

Dynamic sampling is used by optimizer to fetch the low cost execution plan.
Default value is 2.


NAME                       TYPE     VALUE
-------------------------- -------- -------
optimizer_dynamic_sampling integer  2

Its has different level, if we want to disable use of dynamic_sampling =0
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed
1. at least 1 unanalyzed table in the query;
2. unanalyzed table is in joined condition
3. this unanalyzed table has no indexes;
4. this unanalyzed table has more blocks than the number of blocks(i.e 32) that would be used for dynamic sampling of this table.
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. T
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

Multiple execution of query found in SP or AWR Report:
Dynamic sampling is used in the database, it caused the performance issue in the database by consuming CPU at runtime gather stats. Muliple run caused the performance issue.

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO

For disable it, we need to change the parameter value to zero.
Alter system set optimizer_dynamic_sampling=0 scope=both

Note: Optimizer will have behavior to decide the dynamic sampling will be used or what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables and partitioned table.
Where as we expect parallel statements to be more resource intensive, so the additional overhead at compile time is worth it to ensure we can be best execution plan.

Plan hash value: 3061943406
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop|
| 0 |SELECT STATEMENT | | 1 | 563| 106 (0)| 00:00:02 | | |
| 1 |PARTITION RANGE SINGLE | | 1 | 563| 106 (0)| 00:00:02 | 262 | 262 |
|* 2 |TABLE ACCESS BY LOCAL INDEX ROWID | TRAN | 1 |563|106(0)|00:00:02|262|262|
|* 3 |INDEX RANGE SCAN | 1TRAN_PRIMARY_INDEX | 7 | |69(0)|00:00:01|262|262|
- dynamic sampling used for this statement (level=7)

In 12c
dynamic statistics used: dynamic sampling (level=AUTO)

Problem: (Bug 11841491 : POOR PERFORMANCE WITH FIX 7452863)
optimizer_dynamic_sampling is set to 2, and table has statistics.
But dynamic sampling is automatically involved by optimizer in few cases.

To fix this issue “Oracle cover this in bug 7452863”

ALTER SESSION SET "_fix_control"='7452863:0';
Note: you can disable it by zero parameter as above do.
Alter system set optimizer_dynamic_sampling=0 scope=both;

Note:Dynamic statistics has been enhanced in Oracle 12c, but may cause longer parse time during parsing. If you face any issue of slowness then first trace the SQL statements and check if they used dynamic sampling then try to use above bug fix parameter. May your performance issue is fixed. Thanks!