Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

optimizer cost model tips

Oracle Tips by Burleson Consulting


Starting with Oracleyou have the ability to view the estimated CPU, TEMP and I/O costs for every SQL execution plan step.  Oracle Corporation has noted that typical OLTP databases are becomingly increasingly CPU-bound and has provided the ability for the DBA to make the optimizer consider the CPU costs associated with each SQL execution step. 

Also, make sure to read these important notes on the no_cpu_costing hint.

As a review, the CBO gathers information from many sources, and he has the lofty goal of using DBA-provided metadata to always make the "best" execution plan decision:
 

Oracle uses data from many sources to make an execution plan


Oracle 10g has recognized this trend toward considering CPU consumption by providing you with the ability to choose CPU-and-I/O based or exclusively I/O-based costing during SQL optimization (the 10g default is CPU-costing). 

In Oracle10g and beyond, system stats are gathered by default, and in Oraclethe DBA must manually execute the dbms_stat.gather_system_stats package to get CBO statistics.

alter session set "_optimizer_cost_model"=io; 

alter session set "_optimizer_cost_model"=cpu; 

The dbms_stats.gather_system_stats procedure measures important timings within the database and adjusts the optimizers propensity to choose indexes vs. full-scans, in a similar fashion to my script that measures I/O times to set the optimizer_index_cost_adj parameter.  See  dbms_stats.gather_system_stats and SQL optimizer behavior for details.

You can use this parameter to choose the best optimizer costing model for your particular database, based on your own I/O and CPU load:

  • CPU_COST - The CPU cost of the operation as estimated by the cost-based SQL optimizer based on a secret algorithm. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans.
     

  • IO_COST Oracle estimates the I/O cost of the SQL based upon its knowledge of the settings for db_file_multiblock_read_count, the tablespace blocksize and the presence of indexes.  Oracle does NOT use data buffer statistics because Oracle cannot have any a-priori knowledge of whether a desired data block is already cached in the RAM data buffers.

Your choice of relative weighting for these factors depends upon the existing state of your database.  Databases using 32-bit technology (and the corresponding 1.7 gig limit on SGA RAM size) tend to have databases that are I/O-bound with the top timed events being spent performing disk reads:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~                                           % Total
Event                                     Waits    Time (s) Ela Time
---------------------------------- ------------ ----------- --------
db file sequential read                   xxxx       xxxx      30
db file scattered read                    xxxx       xxxx      40

Once 64-bit became popular, Oracle SGA sizes increased, more frequently-referenced data was cached, and databases became increasingly CPU-bound.  Also, solid-state disk (RAM SAN) has removed disk I/O as a source of waits:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~                                           % Total
Event                                     Waits    Time (s) Ela Time
---------------------------------- ------------ ----------- --------
CPU time                                  xxxx       xxxx      55.76
db file sequential read                   xxxx       xxxx      27.55

The gathered statistics are captured via the dbms_stats package (in 9.2 and above) and CPU statistics are captured automatically in 10g and stored in the sys.aux_stats$ view.

  • single block disk read time (in milliseconds)
  • multiblock disk read-time (in milliseconds)
  • cpu speed in MHz
  • average db_file_multiblock_read_count in number of blocks

As we have noted, in database where CPU is the top timed event may benefit from changing their SQL optimizer to consider the CPU costs associated with each execution plan.  Your cpu_count parameter is also important, and I have details here.

Warning about the optimizer "cost" display:  It's important to note that the cost figures that are displayed in an execution plan are not described in any Oracle documentation, and it's been demonstrated that the plan with the lowest "cost" number is not always the plan chosen by the optimizer.  Further, the "cost" figures do not always indicate the "best:" execution plan for a query, given the divergent optimizer goals of first_rows (optimizer for response time) and all_rows (optimize for minimizing computing resources).

Steve Bombach notes that setting _optimizer_cost_model can result in dramatic performance improvements (3x faster):

"We experimented with the following other parameters so far:

db_writer_processes (set to 4 in combination with cpu_count = 4)
fast_start_parallel_rollback = high
_optimizer_cost_model = cpu


Although the last parameter is undocumented (_optimizer_cost_model) it has brought the greatest performance improvement so far:

dropping >2 min execution time down to 40 seconds"

Remember, using CPU costing may not be good for databases that are I/O-bound.  Also, note that changing to CPU-based optimizer costing will change the predicate evaluation order of your query (See MOSC bulletin 276877.1).

Turning on CPU costing

The default for the optimizer cost model is choose, meaning that the presence of CBO statistics will influence whether or not CPU costs are considered.  According to the documentation, CPU costs are considered when you collect SQL optimizer schema statistics with the dbms_stat.gather_system_stats package (the default behavior in Oracle10g), and CPU costs will be considered in all of your SQL optimization.

Important Note:  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats.  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

To ensure that you are using CPU costing:

  • In Oracle use dbms_stats.gather_system_stats to collect system statistics

  • Set the undocumented parm _optimizer_cost_model=cpu

Turning off CPU costing

As we noted, I/O-bound databases (especially 32-bit databases) may want to use I/O-based SQL costing.  The default optimizer costing in Oracle 10g is cpu, and you can change to io costing by using these techniques:

  • Make sure that optimizer_index_cost_adj is set to the default value (Oraclebug 2820066)

  • Add a "no_cpu_costing" hint in your SQL

  • alter session set "_optimizer_cost_model"=io; 

  • Set init.ora hidden parameter _optimizer_cost_model=io

In sum, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in un-patched Oracle versions less than 10.1.0.2.

Next, lets see how we can change from CPU-based to I/O-based SQL optimization when the processing characteristics of our database change on a regular basis.

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

 Note:  In Oracle 10g and beyond, you can achieve a similar result to reducing the value of optimizer_index_cost_adj by analyzing your workload statistics (dbms_stats.gather_system_stats).  Also note that utilizing CPU costing (_optimizer_cost_model) may effect the efficiency of plans with lower values for optimizer_index_cost_adj.

 

Bi-modal system configuration

It is not uncommon for databases to be bi-modal, operating OLTP during the day (CPU-intensive) and doing aggregations and rollups (I/O-intensive) at night.  I describe this technique in detail in my book Oracle Tuning: The Definitive Reference, but the idea is simple.

You can capture CPU and I/O statistics using dbms_stats and then swap-them in as your processing mode changes.  Most shops do this with the dbms_scheduler (dbms_job) package so that the statistics are swapped at the proper time.

Oracle MOSC has detailed script listings in Note 149560.1 Collect and Display System Statistics (CPU and IO) for CBO usage:

/* e.g. activate the DAY statistics each day at 7:00 am */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate

+ 1');

END;

 

/* e.g. activate the NIGHT statistics each day at 9:00 pm */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate

+ 1');

END;

 

*** ********************************************************

*** Initialize the OLTP System Statistics for the CBO

*** ********************************************************

 

1. Delete any existing system statistics from dictionary:

   

   SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;

   PL/SQL procedure successfully completed.

 

2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:

 

   SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(-  

   >         stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');

 

   PL/SQL procedure successfully completed.

 

3. All system statistics are now visible in the data dictionary table:

 

   SQL> select * from sys.aux_stats$;

 

   SQL> select * from aux_stats$;

 

   SNAME                PNAME                   PVAL1 PVAL2

   -------------------- ------------------ ---------- --------------

   SYSSTATS_INFO        STATUS                        COMPLETED

   SYSSTATS_INFO        DSTART                        08-09-2001 16:40

   SYSSTATS_INFO        DSTOP                         08-09-2001 16:42

   SYSSTATS_INFO        FLAGS                       0

   SYSSTATS_MAIN        SREADTIM                7.581

   SYSSTATS_MAIN        MREADTIM               56.842

   SYSSTATS_MAIN        CPUSPEED                  117

   SYSSTATS_MAIN        MBRC                        9

 

   where 

   => sreadtim : wait time to read single block, in milliseconds

   => mreadtim : wait time to read a multiblock, in milliseconds

   => cpuspeed : cycles per second, in millions 

 

*** ********************************************************

*** CPU_COST and IO_COST in PLAN_TABLE table

*** ********************************************************

 

   SQL> explain plan for select * from oltp.test where c='AAAHxGAABAAAJS1AEZ';

   Explained.

 

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

 

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

   ------------------ -------------------- ------------ ---------- -

   SELECT STATEMENT                                          10500          1

   INDEX              UNIQUE SCAN          SYS_C002218       10500          1

 

   SQL> truncate table plan_table;

 

   SQL> explain plan for select * from oltp.test;

   Explained.

 

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

 

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

   ------------------ -------------------- ------------ ---------- ----------

   SELECT STATEMENT                                        2677480         27

   INDEX              FAST FULL SCAN       SYS_C002218     2677480         27
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

   

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.