Performance Tuning

About system statistics, db_file_multiblock_read_count, optimizer_index_cost_adj and optimizer_index_caching

User Rating:  / 3
PoorBest 
Parent Category: Articles
Created on Sunday, 26 September 2010 22:28
Last Updated on Wednesday, 08 May 2013 17:22
Published on Sunday, 26 September 2010 22:28
Written by Guy Lambregts
Hits: 8633

About system statistics, db_file_multiblock_read_count, optimizer_index_cost_adj and optimizer_index_caching



What does the Oracle Documentation mentions about the initialization parameter db_file_multiblock_read_count



DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Not mentionned here is that there is a relationship between the cost of a full table scan, system statistics and db_file_multiblock_read_count. Any new Oracle 10G database has by default the no workload statistics. With the dbms stats procedure we can gather workload statistics. If system statistics are not gathered either are removed then the cost of the full table scan is derived from the value for db_file_multiblock_read_count.

Thus

1. Without system stats and if we increase the value for db_file_multiblock_read_count the cost for the FTS decreases

2. Without system stats and if we decrease the value for db_file_multiblock_read_count the cost for the FTS increases


SQL> begin
2  dbms_stats.delete_system_stats;
3  end;
4  /


SQL> select * from sys.aux_stats$;

SYSSTATS_INFO               STATUS
COMPLETED

SYSSTATS_INFO               DSTART
09-25-2010 17:23

SYSSTATS_INFO               DSTOP
09-25-2010 17:23

SYSSTATS_INFO               FLAGS                        0


SYSSTATS_MAIN               CPUSPEEDNW             266.513


SYSSTATS_MAIN               IOSEEKTIM                   10


SYSSTATS_MAIN               IOTFRSPEED                4096


SYSSTATS_MAIN               SREADTIM


SYSSTATS_MAIN               MREADTIM


SYSSTATS_MAIN               CPUSPEED


SYSSTATS_MAIN               MBRC


SYSSTATS_MAIN               MAXTHR


SYSSTATS_MAIN               SLAVETHR




SQL> alter session set db_file_multiblock_read_count=16;        

Session altered.

SQL> set autotrace traceonly explain;

SQL> select /*+ FULL */ * from pm.t_exchangerate_er;

Execution Plan
---------------------------------------------------------------------------------------------------------------
Plan hash value: 2138012165

---------------------------------------------------------------------------------------------------------------

| Id  |     Operation      | Name              | Rows  | Bytes |  Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 |  SELECT STATEMENT  |                   |  1701K|    64M|  2826    (5)| 00:00:34 |

|   1 |  TABLE ACCESS FULL | T_EXCHANGERATE_ER |  1701K|    64M|  2826    (5)| 00:00:34 |

---------------------------------------------------------------------------------------------------------------



SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select /*+ FULL */ * from pm.t_exchangerate_er;

Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165

---------------------------------------------------------------------------------------------------------------

| Id  |     Operation      | Name              | Rows  | Bytes |  Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 |  SELECT STATEMENT  |                   |  1701K|    64M|  2506    (6)| 00:00:31 |

|   1 |  TABLE ACCESS FULL | T_EXCHANGERATE_ER |  1701K|    64M|  2506    (6)| 00:00:31 |

---------------------------------------------------------------------------------------------------------------

We gather system statistics

 

SQL> begin
2  dbms_stats.gather_system_stats('start');
3  end;
4  /

PL/SQL procedure successfully completed.

 

We wait for an hour or two, we assure normal database processing is going on. After this period of representative database processing we stop the gather system stats.

 

SQL> begin
2  dbms_stats.gather_system_stats('stop');
3  end;
4  /

PL/SQL procedure successfully completed.

 

We query the view aux_stats$ and notice there are entries for SREADTIM, MREADTIM, MBRC.


SQL> select * from sys.aux_stats$;

SNAME                   PNAME                   PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO               STATUS
COMPLETED

SYSSTATS_INFO               DSTART
09-26-2010 18:51

SYSSTATS_INFO               DSTOP
09-26-2010 20:50

SYSSTATS_INFO               FLAGS                      1


SYSSTATS_MAIN               CPUSPEEDNW           266.513


SYSSTATS_MAIN               IOSEEKTIM                 10


SYSSTATS_MAIN               IOTFRSPEED              4096


SYSSTATS_MAIN               SREADTIM           15398.365


SYSSTATS_MAIN               MREADTIM           26972.559


SYSSTATS_MAIN               CPUSPEED                 828


SYSSTATS_MAIN               MBRC                      66


SYSSTATS_MAIN               MAXTHR              31055872


SYSSTATS_MAIN               SLAVETHR                8192



13 rows selected.

 

 

We notice that no matter what the value for db_file_multiblock_read_count is the cost of the ful table scan does not differ anymore. The cost is derived from sys.aux$_stats.MBRC.


SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> set autotrace traceonly explain;
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;

Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165

------------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name              | Rows  | Bytes |  Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                   |  1701K|    64M|   327    (0)| 01:23:56 |

|   1 |  TABLE ACCESS FULL | T_EXCHANGERATE_ER |  1701K|    64M|   327    (0)| 01:23:56 |

------------------------------------------------------------------------------------------------------------


SQL> set autotrace off;
SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL> set autotrace traceonly explain;
SQL> select /*+ FULL */ * from pm.t_exchangerate_er;

Execution Plan
----------------------------------------------------------
Plan hash value: 2138012165

-------------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name              | Rows  | Bytes |  Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                   |  1701K|    64M|   327    (0)| 01:23:56 |

|   1 |  TABLE ACCESS FULL | T_EXCHANGERATE_ER |  1701K|    64M|   327    (0)| 01:23:56 |

-------------------------------------------------------------------------------------------------------------

 

So once we gathered system statistics we can benefit from raising the value for  db_file_multiblock_read_count to the maximum what the operating system allows, WITHOUT
increasing the amount of full table scans ( multiblock read ). So if the optimizer decided to go for a full table scan then the maximum amount of blocks are retrieved with 1 single disk IO read.


Note, if the value for db_file_multiblock_read_count has not been set in the server parameter file or init file then the value is derived from the hidden parameter _db_file_exec_read_count

 

SQL> select KSPPSTVL,KSPPSTDVL from
2  x$ksppi i,x$ksppcv v
3  where v.indx = i.indx
4  and v.inst_id = i.inst_id
5  and i.ksppinm = '_db_file_exec_read_count';

KSPPSTVL
--------------------------------------------------------------------------------
KSPPSTDVL
--------------------------------------------------------------------------------
49
49


SQL>
SQL> show parameter db_file_multiblock_read_count

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count         integer     49

 

So we have learnt that db_file_multiblock_read_count can influence the cost of full table scans.
The higher the value the lower the cost for a full table scan when there are no workload statistics.
The lower the value the higher the cost for a full table scan when there are no workload statistics.
Oracle 8.1 has introduced 2 powerful CBO parameters : optimizer_index_cost_adj and optimizer_index_caching. These parameter should NOT anymore be used in Oracle 10G

The lower the value for optimizer_index_cost_adj the lower the cost for index access
Optimizer_index_caching is used to "tell" the CBO that a percentage of the index blocks are already in the buffer cache.

Consider using dbms_stats.gather_system_stats in stead of using optimizer_index_cost_adj and optimizer_index_caching.

Valuable documentation