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.