Event 10053 and the Cost Based Optimizer - Secrets of the Oracle Database - page 74

When changing DB_FILE_MULTIBLOCK_READ_COUNT at session level, this is reflected as the
undocumented parameter _DB_FILE_OPTIMIZER_READ_COUNT in the subsection on altered values.
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_db_file_optimizer_read_count = 64
The excerpt below illustrates the effect of adjusting OPTIMIZER_INDEX_COST_ADJ at state-
ment level with the hint OPT_PARAM on the third subsection.
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
optimizer_index_cost_adj = 30
A small subset of these parameters, pertaining to the SELECT statement used as an example,
may be retrieved with the following query:
SQL> SELECT name FROM V$SQL_OPTIMIZER_ENV WHERE sql_id='2ck90xfmsza4u';
System Statistics
On the system where the case study was performed, workload statistics had been set in the data
dictionary with the following anonymous PL/SQL block (file set_system_stats.sql ):
SQL> BEGIN
dbms_stats.set_system_stats('sreadtim', 4);
dbms_stats.set_system_stats('mreadtim', 10 );
dbms_stats.set_system_stats('cpuspeed', 839);
dbms_stats.set_system_stats('mbrc', 14);
dbms_stats.set_system_stats('maxthr', 8 * 1048576);
END;
/
This is reflected in the 10053 trace as reproduced here:
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using WORKLOAD Stats
CPUSPEED: 839 millions instructions/sec
SREADTIM: 4 milliseconds
MREADTIM: 10 milliseconds
MBRC: 14.000000 blocks
MAXTHR: 8388608 bytes/sec
SLAVETHR: -1 bytes/sec
These system statistics were derived by averaging the results of several system statistics
gatherings with DBMS_STATS.GATHER_SYSTEM_STATS . The value of CPUSPEED depends on the
hardware used and fluctuates with the apportionment of CPU time to the DBMS. I have included