Automatic Evolution of SQL Plan Baselines

10 December 2008Written by Christian Antognini

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.

11gR1, Query Optimizer, TOP

11 Comments

2 Pings/Trackbacks

Sriram

5 January 2009 - 22:55

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

Reply
Charlie Yi 木匠

27 January 2009 - 20:41

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

Reply
Charlie Yi 木匠

29 January 2009 - 02:23

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

Reply
Christian Antognini

2 February 2009 - 18:47

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

Reply
Charlie Yi 木匠

13 February 2009 - 18:51

> 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

Reply
Christian Antognini

16 February 2009 - 18:25

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

Reply

Charlie Yi 木匠

19 February 2009 - 01:37

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

Christian Antognini

19 February 2009 - 21:43

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

Reply

Julian Dontcheff

28 October 2012 - 07:25

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

Markus

4 October 2011 - 11:55

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

Reply
1. Christian Antognini
  
  6 October 2011 - 20:22
  
  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
  
  Reply
Plan Stability using Sql Profiles and SQL Plan Management | AskDba.org Weblog Pingback

20 December 2011 - 06:45

[…] Antognini has written a good article on Automatic Evolution of SQL Plan Baselines which explains this feature. Share this:EmailDiggRedditPrintFacebookStumbleUpon Related Tags: […]

Reply
Tuning Exadata with parallel profiles and SPM baselines « Julian Dontcheff's Database Blog Pingback

29 October 2012 - 04:34

[…] The process of automatic evolution of SQL Plan Baselines is described with a perfect example by Christian Antognini. […]

Reply

Striving for Optimal Performance

Automatic Evolution of SQL Plan Baselines

11 Comments

2 Pings/Trackbacks

Leave a Reply Cancel reply