The Oracle (tm) Users' Co-Operative FAQ

What is the difference between cpu_costing in Oracle 9 and the old costing of Oracle 8 ?

Author's name: Jonathan Lewis

Author's Email:

Date written: 15th Dec 2002

Oracle version(s): 9

What is the difference between cpu_costing in Oracle 9 and the previous costing methods of Oracle 7 and 8 ?

Back to index of questions

Oracle 9 introduces a more subtle, and thorough costing mechansim. It's a great improvement on the Oracle 7/8 version, but I think the change-over is going to be a bit like the change-over from rule-based to cost-based. If you don't understand how it works you may see some strange events.

You can enable cpu_costing simply by collecting system_statistics for an appropriate period of time with the dbms_stats package. This records in the table sys.aux_stats$ values for:

	assumed CPU speed in MHz
	single block read time in milliseconds
	multiblock read time in milliseconds
	typical achieved multiblock read.

These figures are used to produce three main effects.

Instead of Oracle assuming that single block reads are just as expensive as multiblock reads, Oracle knows the relative speed. This is roughly equivalent to setting the parameter optimizer_index_cost_adj according to the db file xxxx read average wait times - it will encourage Oracle to use indexed access paths instead of tablescans because Oracle now understands that tablescans are more expensive than it used to think.

Secondly, Oracle will use the 'achieved' average multiblock read count to calculate the number of read requests required to scan a table, instead of using an adjusted value of db_file_multiblock_read_count. Since many people use a value of db_file_multiblock_read_count that is inappropriate, the result of this is that Oracle is likely to increase the cost of, and therefore decrease the probability of, doing tablescans (and index fast full scans). Note - the recorded value is used in the calculations, but Oracle tries to use the init.ora value when actually running a tablescan.

Finally, Oracle knows that (e.g.) to_char(date_col) = 'character value' costs a lot more CPU than number_col = number_value, so it may change its choice of index to use a less selective index if the consequential cost of using that index is lower on CPU usage. (Also, Oracle will re-arrange the order of non-access predicates to minimise CPU costs, but the difference in performance from this is not likely to be visible in most cases).

Overall - it's definitely a good thing. In practice, you may see a much stronger bias towards indexed access paths which may impact performance.

Further reading: Oracle 9.2 Performance Tuning Guide and Reference

Back to top

Back to index of questions