ITPub博客

首页 > 数据库 > Oracle > Oracle SQL optimizer cost model

Oracle SQL optimizer cost model

原创 Oracle 作者:mfm088 时间:2006-09-28 14:52:47 0 删除 编辑

Starting with Oracle9i you 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.

Ref:http://www.dba-oracle.com/oracle_tips_optimizer_cost_model.htm

[@more@]

Oracle SQL optimizer cost model
Don Burleson

Starting with Oracle9i you 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.

Oracle 10g has recognized this trend toward CPU-based optimization by providing you with the ability to choose CPU-based or I/O-based costing during SQL optimization (the 10g default is CPU-costing). In Oracle10g, system stats are gathered by default, and in Oracle9i the DBA must manually execute the dbms_stat.gather_system_stats package to get CBO statistics.


Got Scripts?

Mike Ault, one of the world's most widely-read Oracle experts, has finally consented to release his complete collection of more than 450 Oracle scripts.

Mike has priced his collection of 465 scripts at $39.95, less than a dime per script. You can download them immediately at this link:

http://www.rampant-books.com/download_adv_mon_tuning.htm

alter session set "_optimizer_cost_model"=choose; -- default value

alter session set "_optimizer_cost_model"=io;

alter session set "_optimizer_cost_model"=cpu;

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_stat$ view.

  • single block disk read time (in micro-seconds)

  • cpu speed in mhz

    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.

    But it gets tricky because of Bug 2820066 where CPU cost is computed whenever optimizer_index_cost_adj is set to a non-default value. Unless you have applied the 9.2.0.6 server patch set, your Oracle9i database may be generating CPU statistics, regardless of your CBO stats collection method.

    To ensure that you are using CPU costing:

    • In Oracle9i use dbms_stats.gather_system_stats to collect 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 (Oracle9i bug 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

    Notes on Bug 2820066:

    CPU cost is computed when optimizer_index_cost_adj is set to a non-default value.

    • Range of versions believed to be affected: Versions < 10.1.0.2

    • Platforms affected: Generic (all / most platforms affected)

    • This issue is fixed in 9.2.0.6 (Server Patch Set) and 10.1.0.2

    Bug description: If optimizer_index_cost_adj is set to a non-default value CPU costs are calculated regardless of the optimizer cost model used. If you have optimizer_index_cost_adj set and you are not using the optimizer CPU cost model, but explain plan shows that for queries not using domain indexes CPU costs are being calculated, you are probably hitting this bug.

    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, let’s 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 WISE tool is the easiest way to analyze disk I/O data in Oracle and WISE allows you to spot hidden I/O trends.

    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 Metalink 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

  • 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:

http://www.dba-oracle.com/oracle_tips_cpu_count_wrong.htm

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 MetaLink bulletin 276877.1).


Need Oracle Tuning Support?
Burleson Consulting now offers a one-day performance review for your Oracle database. Working with top experts who tune hundreds of databases each year, you can get fast expert tuning advice to hypercharge your Oracle database.

We also provide expert upgrades to Oracle9i and Oracle10g, and our DBAs can quickly show you how to implement the important new features of new Oracle releases.

Call now for remote Oracle support.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/330796/viewspace-867871/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于EXEC DBMS_STATS
请登录后发表评论 登录
全部评论

注册时间:2012-01-14

  • 博文量
    98
  • 访问量
    1025027