Hungry DBA .com for DBAs who like food
System Statistics in 10g
 
Where as in 9i you needed to manually gather system statistics in order to enable CPU Costing; in 10g CPU Costing is enabled by default.
 
For this reason the poor performance which was experienced due to missing system statistics when migrating Oracle 8i to 9i is not so apparent when migrating to 10g.
 
One of our other articles Essential System Statistics describes the benefits and problems which can be encountered when using system statistics; however in 10g there is one major question which needs to be considered:
 
Do we need to gather system statistics in 10g?
 
The surprising answer is perhaps no.
 
As explained by Jonathan Lewis in his book "Cost-Based Oracle Fundamentals"; In 10g if you have not gathered system statistics Oracle makes use of three other statistics from the aux_stats$ table:
 

PNAME

PVAL1 

Description

CPUSPEEDNW

1605.509

speed in millions of operations per second

IOSEEKTIM

10

disk seek time in milliseconds 

IOTFRSPEED

4069

disk transfer time in bytes per millisecond

 
Oracle will use the above statistics, the db_block_size, and the db_file_multiblock_read_count to calculate values for sreadtim, mreadtim, and MBRC using the below formula:
 
  • MBRC is set to the actual value of db_file_multiblock_read_count.
  • sreadtim is set to ioseektim + db_block_size / iotrfrspeed.
  • mreadtim is set to ioseektim + db_file_multiblock_read_count * db_block_size / iotrfspeed

 

Therefore we are back to the problem of the settings of DB_FILE_MULTIBLOCK_READ_COUNT affecting explain plans.  There is however an answer and the following explains why it could be worth not manually gathering system statistics in 10g. 

If you ensure the parameter DB_FILE_MULTIBLOCK_READ_COUNT is not set and leave Oracle to set this to its default values; Oracle will not use this parameter in order to calculate the MBRC and will actually make a good effort of optimizing explain plans without the need to manually gather system statistics. 

If DB_FILE_MULTIBLOCK_READ_COUNT is not set Oracle will use the value of the hidden parameter "_db_file_optimizer_read_count" for the MBRC. 

However when you explicitly set db_file_multiblock_read_count to anything other than default (even if setting this to the same value which Oracle sets it to by default) this will then over-ride the hidden parameter so that db_file_multiblock_read_count is used for the MBRC. Of course this assumes that no system stats are manually gathered. If system stats are gathered then the parameter settings are not relevant and Oracle will use what ever the system stats figures are set to. 

For example, with DB_FILE_MULTIBLOCK_READ_COUNT removed from the init.ora file and no manual gathering of system statistics; Oracle shows the following parameter values: 

NAME

VALUE

_db_file_exec_read_count

128 

_db_file_optimizer_read_count

8

db_file_multiblock_read_count

128

 

Oracle appears to have set the default value of DB_FILE_MULTIBLOCK_READ_COUNT to 128

I try an explain plan of:

select * from audit_data;

SELECT STATEMENT,

GOAL = ALL_ROWS Cost=431394 Cardinality=31420512 Bytes=7195297248

TABLE ACCESS FULL

Object owner=AUDIT Object name=AUDIT_DATA Cost=431394

Cardinality=31420512 Bytes=7195297248

Just to test the difference I then set db_file_multiblock_read_count to 128 (same as what appears in the default):

alter session set db_file_multiblock_read_count = 128

The explain plan then changes:

select * from audit_data;

SELECT STATEMENT,

GOAL = ALL_ROWS Cost=278886 Cardinality=31420512 Bytes=7195297248

TABLE ACCESS FULL Object owner=AUDIT Object name=AUDIT_DATA Cost=278886

Cardinality=31420512 Bytes=7195297248

So explicitly setting db_file_multiblock_read_count has caused the cost for the full table scan to have dropped; thus encouraging the optimizer to perform more full scans.  This proves that Oracle was not using DB_FILE_MULTIBLOCK_READ_COUNT as the MBRC by default but only then did so when the parameter was explicitly set.

So in summary; in 10g it is worth considering to not gather system statistics but instead ensure the parameter db_file_multiblock_read_count is not set.  Oracle should then calculate optimal explain plans based on appropriate derived system statistic values.

However, it is important to test performance levels with the above scenario and also with system statistics gathered.  Based on this testing you should then decide which method provides the best performance for your environment.