Automatic Evolution of SQL Plan Baselines

In the section Evolving SQL Plan Baselines of TOP (pages 299-300) I explain what an evolution is and how to manually do it. What I do not cover is the automatic evolution. In fact, I wrote only the following text about it: “In addition to the manual evolution just explained, automatic evolution of SQL plan baselines is supported with the Tuning Pack. The idea is simply that an automated task periodically checks whether nonaccepted SQL plan baselines should be evolved.”

Since the automatic evolution is not as obvious as one might think, in this post I would like to share some information about it. First of all, let me explain when and how an automatic evolution works…

During the maintenance window, the SQL Tuning Advisor works on the SQL statements that had a significant impact on the system. When possible, it provides some recommendations to improve their response time. If it notices that a nonaccepted SQL plan baseline leads to better performance than an accepted one, it recommends a SQL profile that says nothing else than to accept the SQL plan baseline. Obviously, if that SQL profile is accepted (which is, per default, not the case) the SQL plan baseline is accepted as well. Therefore, the SQL plan baselines are automatically accepted only if the SQL profiles generated by the SQL Tuning Advisor are also automatically accepted.

Now that we have seen the theory, let me show you an example:

  • First of all, let the database engine capture a SQL plan baseline. This is performed by setting the initialization parameter optimizer_capture_sql_plan_baselines to TRUE and by executing the same SQL statement twice.
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82

SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
  • As a result, we have a SQL plan baseline with the following content (basically, the SQL text, some information about the status and an execution plan).
SQL> SELECT *
  2  FROM table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_492bdb47e8861a89',
  4               format     => 'basic'
  5             ));

-----------------------------------------------------------------------
SQL handle: SYS_SQL_492bdb47e8861a89
SQL text: SELECT count(pad) FROM t WHERE n = 42
-----------------------------------------------------------------------

-----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a893fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
-----------------------------------------------------------------------

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------
  • One of the essential things to notice in the previous output is the full table scan used to get data from the table T. In that particular case, this is not optimal (the table T contains 1,000,000 rows and only 82 of them fulfill the restriction n=42). To improve the response time of this query, let’s create an index based on the column referenced in the WHERE clause.
SQL> CREATE INDEX i ON t (n);
  • With that index in place, the execution plan associated with the SQL plan baseline is no longer the best one. However, the query optimizer is not allowed to choose another execution plan (provided that the initialization parameter optimizer_use_sql_plan_baselines is set to TRUE, which is the default). The only thing that the query optimizer can do is to add that information to the SQL plan baseline. To reproduce that behavior it is enough to execute the SQL statement once.
SQL> SELECT count(pad) FROM t WHERE n = 42;

COUNT(PAD)
----------
        82
  • Now, let’s display the content of the SQL plan baseline. Notice that there are two execution plans. The first one, which is based on a full table scan, is the execution plan currently in use. The second one, which is based on an index range scan, is not accepted and, therefore, it cannot be chosen by the query optimizer. To use it, an evolution is necessary.
SQL> SELECT *
  2  FROM table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_492bdb47e8861a89',
  4               format     => 'basic'
  5             ));

----------------------------------------------------------------------
SQL handle: SYS_SQL_492bdb47e8861a89
SQL text: SELECT count(pad) FROM t WHERE n = 42
----------------------------------------------------------------------

----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a893fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
----------------------------------------------------------------------

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

----------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_e8861a8959340d78
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
----------------------------------------------------------------------

Plan hash value: 3694077449

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | I    |
---------------------------------------------
  • To make sure that the SQL Tuning Advisor works on the SQL statement used during this test, let’s execute it 50 times. In addition, an AWR snapshot is also taken.
SQL> DECLARE
  2    l_count PLS_INTEGER;
  3  BEGIN
  4    FOR i IN 1..50
  5    LOOP
  6      SELECT count(pad) INTO l_count
  7      FROM t
  8      WHERE n = 42;
  9    END LOOP;
 10    dbms_workload_repository.create_snapshot;
 11  END;
 12  /
  • Instead of waiting for the next automatic execution of the SQL Tuning Advisor, let’s start it manually. Notice that with the parameter execution_params it is specified to automatically accept SQL profiles. To change the value of this parameter for the automatic execution of the SQL Tuning Advisor, the procedure set_tuning_task_parameter in the package dbms_sqltune is available.
SQL> DECLARE
  2    l_args dbms_advisor.argList;
  3  BEGIN
  4    l_args := dbms_advisor.argList('ACCEPT_SQL_PROFILES', 'TRUE');
  5    dbms_sqltune.execute_tuning_task(
  6      task_name        => 'SYS_AUTO_SQL_TUNING_TASK',
  7      execution_params => l_args
  8    );
  9  END;
 10  /
  • After the execution of the SQL Tuning Advisor both execution plans of the SQL plan baseline are accepted.
SQL> SELECT plan_name, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE sql_handle = 'SYS_SQL_492bdb47e8861a89';

PLAN_NAME                      ACC
------------------------------ ---
SYS_SQL_PLAN_e8861a893fdbb376  YES
SYS_SQL_PLAN_e8861a8959340d78  YES

In summary, with the right configuration in place the database engine automatically evolves the SQL plan baselines that are likely to reduce the load on the system. This is for sure a good thing. The only real problem (or, better, restriction) that I see in this area, it is that not only you need the Enterprise Edition but also the Tuning Pack. In fact, the former is needed for using SQL plan baselines and the latter for the SQL Tuning Advisor.

11 Comments

  1. Sriram's Gravatar Sriram
    January 5, 2009    

    Thanks for this detailed clarifiaction and this one example of yours has cleared out the differece between stored outlines, sql profiles and SPM.

    So to sum it up we can say that the automatic feature (SPM) is something like an evolving stored outline with the help of creaing sql profiles created and accepted automa
    tically.

    In case of manual stuff it is left to our discretion whether to accept the sql profile or not and instead the difference between 10g and 11g is instead of invoking DBMS_SQLTUNE
    DBMS_SQLTUNE , You use the DBMS_SPM for Manual load and evolution.

    Thanks

  2. January 27, 2009    

    Hi Christian Antognini,

    One question,
    after we accepted the SQL profile and the linked SQL Plan baseline,
    also reference your book “TOP” utilization of a SQL profile(page 267),
    Step C and D: will step D generates an execution plan(do a hard parse)? or get plan from the SQL plan baseline directly and skip step D?

    If so, it will not strictly follow the below process flow:

    (page 292) Figure 7-9. Main steps carried out during the selection of a SQL plan baseline.
    the query optimizer generates an execution plan without the support of SQL plan baselines.

    You book TOP is really a good book, make CBO hardest part easy to understand.
    I’ll email some other SQL profile questions to you later.

    Thanks for your good work,
    Charlie (Yi)
    Victoria, BC Canada

  3. January 29, 2009    

    I could not reproduce the result. dbms_sqltune.execute_tuning_task dose not work for me; how to troubleshoot it? dbms_spm.evolve_sql_plan_baseline works for me.
    Oracle 11.1.0.7 on RH ES 4 Linux.

    SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

    SQL_TEXT
    ——————————————————————————–
    SQL_HANDLE PLAN_NAME ENA ACC
    —————————— —————————— — —
    SELECT count(pad) FROM t WHERE n = 42
    SYS_SQL_492bdb47e8861a89 SYS_SQL_PLAN_e8861a893fdbb376 YES YES

    SELECT count(pad) FROM t WHERE n = 42
    SYS_SQL_492bdb47e8861a89 SYS_SQL_PLAN_e8861a8959340d78 YES NO

    This works for me:

    set long 32000

    var report clob;
    exec :report := dbms_spm.evolve_sql_plan_baseline(sql_handle=>’SYS_SQL_492bdb47e8861a89′,verify => ‘yes’,commit => ‘YES’);

    print report

  4. February 2, 2009    

    Hi Charlie

    > will step D generates an execution plan(do a hard parse)?
    > or get plan from the SQL plan baseline directly and skip step D?

    The hard parse is executed as usual. In fact, in the DD, only hints are stored (not execution plans).

    > I could not reproduce the result. dbms_sqltune.execute_tuning_task
    > dose not work for me; how to troubleshoot it?

    Mhmm… do you get an error? In other words, please, describe what do you see.

    Cheers,
    Chris

  5. February 13, 2009    

    > In fact, in the DD, only hints are stored (not execution plans)
    Is DD mean in the SQL Profile?

    I’ll try your Evolution of SQL Plan Baselines example again and paste the result soon,
    on my 11.1.0.6 on Windows and pre-production 64bit 11.1.0.7 on Linux.

    Thanks,
    Charlie

  6. February 16, 2009    

    Hi Charlie

    > Is DD mean in the SQL Profile?

    I do not understand that question. Did the acronym (DD = data dictionary) confuse you?

    Cheers,
    Chris

  7. February 19, 2009    

    Aha, DD = data dictionary, you created a new word ^_^.

    OK. When involve with binding variable. ACS (Adaptive cursor sharing) and SPM(SQL Plan Management), seems it dose not work, see last line, accepted = ‘NO’.

    set pagesize 0
    
    drop table t purge;
    
    CREATE TABLE t (
    id NUMBER,
    n  number,
    pad VARCHAR2(4000),
    CONSTRAINT t_pk PRIMARY KEY (id)
    );
    
    INSERT INTO t
    SELECT rownum AS id, mod(rownum,1000) n, rpad('*',500,'*') AS pad
    FROM dual
    CONNECT BY level < = 10000;
    
    commit;
    
    exec dbms_stats.gather_table_stats(user,'T');
    
    REM remove all baselines created in the last 15 minutes
    /*
    DECLARE
      ret PLS_INTEGER;
    BEGIN
      FOR c IN (SELECT DISTINCT sql_handle
                FROM dba_sql_plan_baselines
                WHERE creator = USER --'SH'
                --AND created > systimestamp - to_dsinterval('0 00:15:00')
                )
      LOOP
        ret := dbms_spm.drop_sql_plan_baseline(c.sql_handle);
      END LOOP;
    END;
    /
    */
    
    alter system flush shared_pool;
    
    ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
    
    declare
     l_n pls_integer;
     li_cnt pls_integer;
    begin
     l_n := 42;
     FOR i IN 1..2
     LOOP
      select count(*) into li_cnt from t where n = l_n;
     END LOOP;
    end;
    /
    
    ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
    
    COLUMN sql_handle NEW_VALUE l_sql_handle
    
    select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines
    where sql_text like 'SELECT COUNT(*) FROM T WHERE N =%';
    
    SELECT *
     FROM table(dbms_xplan.display_sql_plan_baseline(
                  sql_handle => '&l_sql_handle',
                  format     => 'basic'
    ));
    
    CREATE INDEX t_i1 ON t (n);
    
    declare
     l_n pls_integer;
     li_cnt pls_integer;
    begin
     l_n := 42;
     FOR i IN 1..2
     LOOP
      select count(*) into li_cnt from t where n = l_n;
     END LOOP;
    end;
    /
    
    SELECT *
     FROM table(dbms_xplan.display_sql_plan_baseline(
                  sql_handle => '&l_sql_handle',
                  format     => 'basic'
    ));
    
    declare
     l_n pls_integer;
     li_cnt pls_integer;
    begin
     l_n := 42;
     FOR i IN 1..100
     LOOP
      select count(*) into li_cnt from t where n = l_n;
     END LOOP;
    
     dbms_workload_repository.create_snapshot;
    end;
    /
    
    DECLARE
      l_args dbms_advisor.argList;
    BEGIN
      l_args := dbms_advisor.argList('ACCEPT_SQL_PROFILES', 'TRUE');
      dbms_sqltune.execute_tuning_task(
        task_name        => 'SYS_AUTO_SQL_TUNING_TASK',
        execution_params => l_args
      );
    END;
    /
    
    select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines
    where sql_text like 'SELECT COUNT(*) FROM T WHERE N =%';
    -- -- ---
    SELECT COUNT(*) FROM T WHERE N = :B1
    SYS_SQL_4955f400c9c02a12       SYS_SQL_PLAN_c9c02a123fdbb376  YES YES
    
    SELECT COUNT(*) FROM T WHERE N = :B1
    SYS_SQL_4955f400c9c02a12       SYS_SQL_PLAN_c9c02a12c8938b16  YES NO
    
    • February 19, 2009    

      Hi Charlie

      > Aha, DD = data dictionary, you created a new word ^_^.

      Not at all. It’s a “common” acronym…
      http://www.acronymfinder.com/DD.html

      Chris

    • Julian Dontcheff's Gravatar Julian Dontcheff
      October 28, 2012    

      Charlie: if you flush the shared pool before you run:

      declare
       l_n pls_integer;
       li_cnt pls_integer;
      begin
       l_n := 42;
       FOR i IN 1..100
       LOOP
        select count(*) into li_cnt from t where n = l_n;
       END LOOP;
      
       dbms_workload_repository.create_snapshot;
      end;
      /

      you will see an “YES” after executing the tuning task:

      select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines
      where sql_text like 'SELECT COUNT(*) FROM T WHERE N =%';
      -- -- ---
      SELECT COUNT(*) FROM T WHERE N = :B1
      SYS_SQL_4955f400c9c02a12       SYS_SQL_PLAN_c9c02a123fdbb376  YES YES
      
      SELECT COUNT(*) FROM T WHERE N = :B1
      SYS_SQL_4955f400c9c02a12       SYS_SQL_PLAN_c9c02a12c8938b16  YES YES
  8. Markus's Gravatar Markus
    October 4, 2011    

    Hi Chris,

    Do you think one should enable SQL plan baselines and automatic evolution as a global permanent systemwide default?
    In theory, SQL plans could only become better and would even become very stable at some point.
    Seems to be a good thing to me. No?

    Cheers,
    Markus

    • October 6, 2011    

      Hi Markus

      In theory I see no problem, in practice I never saw a system doing it and, therefore, I’m not able to advice such an approach. Hence, I would (stress) test it on a test system during a quite long period of time before using it on a production system.

      HTH
      Chris

  1. Plan Stability using Sql Profiles and SQL Plan Management | AskDba.org Weblog on December 20, 2011 at 06:45
  2. Tuning Exadata with parallel profiles and SPM baselines « Julian Dontcheff's Database Blog on October 29, 2012 at 04:34

Leave a Reply

Your email address will not be published. Required fields are marked *