ORA FAQ Performance Tuning series--oracle 9 with Oracle 8 CPU

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

Author ' s Name:jonathan Lewis
Author ' s email:jonathan@jlcomp.demon.co.uk
Date written:15th DEC 2002

Oracle version (s): 9
What is the difference between cpu_costing in Oracle 9 and the previous costing of Oracle 7 and 8?
What is the difference between Oracle 9 and previous Oracle 7 and 8 cpu_costing?


Oracle 9 introduces a more subtle, and thorough costing mechansim. It's a great improvement on the Oracle 7/8 version, but I the change-over are going to being a bit like the Change-over From rule-based to cost-based. If you don ' t understand how to do it works your may some strange events.
Oracle 9 introduces a more elaborate and comprehensive cost mechanism. This is a major improvement on Oracle version 7/8, but I think this change is similar to a shift from rule-based to cost based. If you don't understand how it works, you'll see strange events.

Can enable cpu_costing simply by collecting system_statistics for a appropriate period of time with the Dbms_stats PA Ckage. This is records in the table sys.aux_stats$ values for:
You can activate the cpu_costing by using the Dbms_stats package to collect system statistics for an appropriate period of time.

CPU speed (MHz) assumed assumed CPU speed in MHz
Single blocks read time in milliseconds (ms)
Multiblock read time in milliseconds multi-block reads (MS)
Typical achieved multiblock read. Generally reached by the multi-block read

These figures are used to produce three main effects.
These figures can yield three main effects:

Instead of Oracle Assuming that single block reads are just as expensive as Multiblock reads, Oracle knows the relative SP Eed. This is roughly equivalent to setting the parameter Optimizer_index_cost_adj according to the DB file xxxx read average WA It times-it'll encourage Oracle to use indexed access paths instead of Tablescans because Oracle now understands Tablescans are more expensive than it used to.
Oracle does not fake order block reading as expensive as multiple reads, it knows the relative speed. This is roughly the same as the average wait time setting parameter Optimizer_index_cost_adj based on the database file xxxx read--because Oracle now thinks table scans are more expensive than originally understood, this encourages Oracle to use index access paths instead of table scans.

Secondly, Oracle would use the ' achieved ' average Multiblock read count to calculate the number of read requests required T o 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 which is inappropriate, the 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.
Second, Oracle will use the "reached" (achieved) average number of blocks to compute the number of read requests needed to scan a table, instead of using a db_file_multiblock_read_count adjustment value. Because many people use db_file_multiblock_read_count values that are not appropriate, Oracle may increase the cost of table scans (and index fast full scans) and thus reduce the likelihood of table scans (and index fast full scans). Note-The recorded value is used for calculation, and Oracle tries to use the value in Init.ora when the scan is actually performed.

Finally, Oracle knows that [e.g.) To_char (date_col) = ' character value ' costs a lot more CPU than Number_col = Number_valu E, so it could change its choice of index to use a less selective index if the consequential cost of using that index. ER on CPU usage. (Also, Oracle would re-arrange the order of non-access predicates to minimise CPU costs, but the difference in performance From isn't likely to being visible in most cases).
Finally, Oracle knows (for example) that the CPU cost of the TO_CHAR (date column) = ' character value ' is more than the numeric column = value, so Oracle may modify the selection of the index by using a sequential read price with a smaller selectivity index (consequential Cost) is lower than the CPU usage, this index is selected. (In addition, Oracle will rearrange the NON-ACCESS predicate order to reduce CPU costs, but in most cases it will not be aware of performance differences.) ）

Overall-it ' s definitely a good thing. In practice, you may have a much stronger bias towards indexed access paths which the May impact performance.
Anyway--it's certainly a good thing. In fact, you can find a stronger preference for index access paths, which will affect performance.

Further reading:oracle 9.2 Performance Tuning Guide and Reference
Further reading: Oracle 9.2 Performance Tuning guide and reference

--------------------------------------------------------------------------------
The translator only retains the copyright in translation from http://www.jlcomp.demon.co.uk/faq/