What is The Difference Between CPU_COSTING IN ORACLE 9 AND THE OLD COSTING ORACLE 8? ORACLE 9 What changes with CPU_COSTING in Oracle 8?
Author's name: Jonathan Lewisauthor's Email: Jonathan@jlcomp.demon.co.ukdate Written: 15th Dec 2002
Oracle Version (S): 9WHAT IS The Difference Between CPU_COSTING IN ORACLE 9 AND THE PREVIOUS COSTING METHODS OF ORACLE 7 and 8? Oracle 9 changes in CPU_COSTING in Oracle 7 and 8?
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 it works You May Some Strange Events.Orcle 9 Introduces a fine and comprehensive cost mechanism. This is a major improvement to Oracle 7/8, but I think this change is similar from rule-based transformation to the cost. If you don't understand how it works, you will see the strange event.
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:. You can collect system statistics for an appropriate period of time with dbms_stats package, in order to activate CPU_COSTING.
Assumed CPU Speed In MHZ Assumption CPU Speed (MHz) Single Block Read Time In MilliseConds Single Read Time IN MilliseConds Multi-Scribed (MS) Typical Achieved Multiblock Read. Generally reached multiple readings
These numbers are used to produce Three Main Effects. These numbers can generate three main utilities:
. 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 Unders That tablescans Are More Expensive THAN IT Used to think. Oracle does not assume that single read is as expensive, it knows relative speed. This is approximately the same as the average wait time set by the database file xxxx. Since Oracle now thinks that the table scan is more expensive than the original understanding, this will encourage Oracle to use the index access path instead of the table scan. 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 SecondUally Running A TableScan. Oracle will use the "Achieved) average multiple read number to calculate the number of read requests required to scan a table, not the adjustment value of a DB_FILE_MULTIBLOCK_READ_COUNT. Since many people use the db_file_multiblock_read_count value is not suitable, this causes Oracle to increase the price of table scans (and index fast full scans), and thus reduce the possibility of table scans (and index fast full scans). Note - The value of the record is used to calculate, and Oracle will try to use the value in Init.ORA when it is actually scanned.
Finally, Oracle knows that (eg) 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 To Minimise CPU COSTS, But The Difference In Performance from this is not likely to be visible in nuSt Case). Finally, oracle knows (for example) To_char (date) = 'character value' CPU cost is larger than the numeric column = value, so Oracle may modify the selection of indexes, if a continuous read price of indexes with smaller selectivity is lower than CPU This index will be selected. (In addition, Oracle will rearrange the predicate order of non-Access to reduce CPU expense, but in most cases the difference is different.) Overall - It's definitely a good thing.. In Practice, You May See A Much Stronger Bias Whatards Indexed Access Paths Which May Impact Performance. In short - this is definitely a good thing. In fact, you can find a stronger preference for the index access path, which will affect performance.
Further Reading: Oracle 9.2 Performance Tuning Guide and Reference further reading: Oracle 9.2 Performance Adjustment Guide and Reference
-------------------------------------------------- ----------------------------- This article is translated from http://www.jlcomp.demon.co.uk/faq/ Only reserved the translation copyright
