Undocumented parms for using CPU-based SQL optimization Oracle Tips by Burleson Consulting

 

Many Oracle professionals are under the mistaken impression that their optimizer is calculating optimal execution plans based on CVPU, when in reality it is being computed based on I/O costs.

 

Back in the 1990’s when 32-bit technology limited the size of the data buffer caches most Oracle databases were constrained by physical I/O (“db file sequential reads” and “db file scattered reads”) and Oracle SQL for OLTP databases (the first_rows optimizer mode) was built to make optimal execution plan decisions based on minimizing disk reads.

 

Today the advent of 64-bit technology and cheaper RAM resources that shifted this bottleneck and many instances are now CPU-bound.  Oracle has recognized this shift and implemented CPU-based costing in Oracle 10g, but it is tricky.

 

For Oracle 9i and 10g database the CBO has been set to create execution plans based on CPU costs. (See Metalink Note 153761.1 for details on collecting system statistics and the process to turn on CPU costing. Note that according to Bug 2820066, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in Oracle versions less than 10.1.0.2.

 

However, CPU costing is not used unless the undocumented parameter "_optimizer_cost_model" is set to "cpu" for example: alter session set "_optimizer_cost_model"=cpu; This parameter defaults to CHOOSE. 

The Ion tool is the easiest way to analyze STATSPACK disk I/O data in Oracle and Ion allows you to spot hidden I/O trends.