SQL Plan Management and SQL Baseline

Before you read this article, I would suggest you to read my articles about bind variable peeking and adaptive cursor sharing.

Starting 11g release, adaptive cursor sharing makes optimizer to generate and select multiple execution plans based on the bind variable values passed during the execution. Oracle also introduced SQL Plan Management feature starting 11g whereby the history of plans of an SQL is maintained in SQL Baseline and out of which one or more plans can become “accepted” and can be used for that SQL. This is our choice to make plan(s) “accepted” if think that plan is optimal. We may ignore sub-optimal plans. In this will explain SQL Plan Management using an example.

One day we suddenly observed 100% CPU usage on our 48 CPU machine. Later investigation revealed that more than 60 sessions were stuck on execution of a single SQL, and all executions of this SQL were using same execution plan (DBA_HIST_ACTIVE_SESS_HIST can provide you all this detail). Following is an image from OEM that shows this SQL had total 5 different plans, the current plan in use during the problematic time was the one highlighted bellow.  

Once we found that, we thought that we should create a SQL plan baseline for this SQL and should make sure that the worst available plan does not get accepted, and hence never be used by the optimizer. In the following I will use a simple example to show how we implement the SQL Plan Base line for such scenarios to make sure that only good (accepted) plans should be used by the optimizer for an SQL.

SQL> create table test as select * from dba_objects; Table created. --Execute Following SQL 17 times. In the end you will have 65536 rows for object_id=100. SQL> insert into test select * from test where object_id=100; 65536 rows created. SQL> commit; Commit complete. SQL> select count(*) from test;   COUNT(*) ----------     222501 --Create index on object_id SQL> create index test_idx on test (object_id); Index created. --Gather statistics on the table and index SQL> exec dbms_stats.gather_table_stats('c##salman','test',cascade=>true) PL/SQL procedure successfully completed.

optimizer_use_sql_plan_baselines

Before we can start using SQL Plan Baseline, we need to enable plan baseline by using parameter optimizer_use_sql_plan_baselines. By default its value is set to TRUE. If it is set to FALSE, we would need to set value to TRUE using ALTER SYSTEM command so that SQL Plan Baseline can be used.

SQL> show parameter optimizer_use_sql_plan_baselines NAME                                                       TYPE                                         VALUE ------------------------------------ ----------- -------------------------------------------------- optimizer_use_sql_plan_baselines            boolean                                       TRUE

optimizer_capture_sql_plan_baselines

This parameter is used to start capture of SQL plans for all SQLs running in the database. It means that every SQL will have its plans(s) captured and stored in SQL Management Base if this parameter is set to TRUE. By default this is set to FALSE. I am not enabling this parameter and merely giving an introduction of this parameter here.

SQL> show parameter optimizer_capture_sql_plan_baselines NAME                                                           TYPE              VALUE ------------------------------------ ----------- -------------------------------- optimizer_capture_sql_plan_baselines          boolean             FALSE

Since I am not enabling auto capture of all SQLs, I would need to manually load the plans of my SQL in SQL Management Base, for this demonstration. There are different methods for specific SQLs using different methods.

Manually Loading SQL Plans from SQL Tuning Set

We can create, or already have SQL Tuning Sets from where we can load SQL plans for specific SQLs to the SQL Management Base using procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET.

Manually Loading SQL Plans from AWR

If SQLs are already aged out of shared pool, we can also load plans for the required SQLs from the AWR using procedure DBMS_SPM.LOAD_PLANS_FROM_AWR. This procedure is available starting 12c Release 2. In older releases, first we would need to create SQL Tuning Set and load SQLs from the AWR into the SQL Tuning Set, and then we can load plans in the SQL Management Base from that SQL Set.

Manually Loading SQL Plans from Cursor Cache

Lastly, we can load plans for SQLs, from the Cursor Cache using procedure BMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. I will be using this method for this example.

It will be pertinent to mention that when we load plans manually in the SQL Management Base, all the plans are automatically ACCEPTED

Now I will open a session and execute the SQL I already mentioned above, and then will load its plan in the SQL Management Base from the cursor cache to create baseline for this SQL.

SQL> VARIABLE v_objid NUMBER SQL> exec  :v_objid:=100 PL/SQL procedure successfully completed. SQL> select count(object_name) from test where object_id=:v_objid; COUNT(OBJECT_NAME) ------------------             131072

Since most of the rows in the table contain object_id=100, optimizer should be using a FULL TABLE SCAN based plan for this SQL, as we can see below.

Let’s check which plan optimizer chose for the SQL this time. I am passing value “0” in second argument of dbms_xplan.display_cursor because this is the first child of this cursor (SQL).

SQL>  select * from table(dbms_xplan.display_cursor('bdgffww5rp2bn',0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  bdgffww5rp2bn, child number 0 ------------------------------------- select count(object_name) from test where object_id=:v_objid Plan hash value: 1950795681 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |        |            |   932 (100)    |              | |   1 |  SORT AGGREGATE     |      |     1|       18 |                        |              | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------ |*  2 |   TABLE ACCESS FULL| TEST |   134K|  2368K|   932   (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_ID"=:V_OBJID)

We can query V$SQL to check the plan and executions.

SQL> select sql_id,plan_hash_value,executions from v$sql where sql_id='bdgffww5rp2bn'; SQL_ID        PLAN_HASH_VALUE EXECUTIONS ------------- --------------- ---------- bdgffww5rp2bn      1950795681          1

Now I will load the plan for this SQL into SQL Management Base, so that SQL Plan Base line gets created for this SQL.

SQL> VARIABLE v_total_plans_loaded NUMBER SQL> exec :v_total_plans_loaded:=dbms_spm.load_plans_from_cursor_cache ( sql_id => 'bdgffww5rp2bn') PL/SQL procedure successfully completed. SQL> print :v_total_plans_loaded V_TOTAL_PLANS_LOADED ---------------------------------                                            1

So one plan is loaded and Plan Baseline has been created for this SQL as you can see below.

SQL> set lines 200 SQL> col plan_name for a50 SQL> col sql_handle for a50 SQL> select sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select count(object_name) from test%'; SQL_HANDLE                       PLAN_NAME                                                  ENA    ACC      FIX ---------------------------------------- -------------------------------------------------- --- --- --- --------------- SQL_cb00aa62fe641824        SQL_PLAN_cq05acbz686146b581ab9            YES     YES       NO

As you can see that this plan is ACCEPTED, which means that this plan will be used by the optimizer for all the executions of this SQL. If there were 2 or more plans available in the shared pool, all of those would have been loaded and become ACCEPTED automatically.

Now I will open another new session in a separate window and would execute the same query with bind variable value set to 102, so that optimizer selects an indexed based plan for the SQL

SQL> variable v_objid number; SQL> exec  :v_objid:=102; PL/SQL procedure successfully completed. SQL> select count(object_name) from test where object_id=:v_objid;   COUNT(*) ----------          1

Now let’s check if a new indexed based plan has been loaded in the SQL Plan Baseline for this SQL

SQL> select sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select count(object_name) from test%'; SQL_HANDLE                     PLAN_NAME                                                  ENA   ACC   FIX -------------------------------------------------- ------------------------------------ --- --- --- --------------- SQL_cb00aa62fe641824       SQL_PLAN_cq05acbz68614369278f9          YES      NO     NO SQL_cb00aa62fe641824        SQL_PLAN_cq05acbz686146b581ab9           YES      YES    NO

You can see that a new plan has been loaded in the SQL Plan Baseline, but is NOT ACCEPTED, and will not be used for execution. To proof this point you can keep executing this SQL with v_objid=100 from one session and v_objid=102 form the other session, and you will see that only first ACCEPTED execution plan is used for every execution. Following result from V$SQL gives us the proof as count of execution for the execution plan 1950795681 increases with every execution of the SQL.

SQL> select sql_id,plan_hash_value,executions from v$sql where sql_id='bdgffww5rp2bn'; SQL_ID        PLAN_HASH_VALUE EXECUTIONS ------------- --------------- ---------------------------------- bdgffww5rp2bn      1950795681          1 SQL> select sql_id,plan_hash_value,executions from v$sql where sql_id='bdgffww5rp2bn'; SQL_ID        PLAN_HASH_VALUE EXECUTIONS ------------- --------------- ---------------------------------- bdgffww5rp2bn      1950795681          5

To “ACCEPT” the plans for an SQL, we use procedure   DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. Evolving the baseline would analyze the non-accepted plan(s), and if any plan found to be performing better than already accepted plan, it would be set as ACCEPTED, so that optimizer can choose this plan for execution. We can ACCEPT a single non-accepted plan by passing plan name, or can ACCEPT all non-accepted plans at once as well.

SQL> VARIABLE v_evolve clob SQL> exec :v_evolve := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_cb00aa62fe641824') PL/SQL procedure successfully completed. --If I print the variable v_evolve_clob, I can see how EVOLVE process worked SQL> set long 200000 SQL> set pages 1000 SQL> print :v_evolve V_EVOLVE -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------  Task Information:  ---------------------------------------------  Task Name            : TASK_1649  Task Owner           : SYS  Execution Name       : EXEC_1719  Execution Type       : SPM EVOLVE  Scope                : COMPREHENSIVE Status               : COMPLETED  Started              : 10/10/2017 15:04:51  Finished             : 10/10/2017 15:04:51  Last Updated         : 10/10/2017 15:04:51  Global Time Limit    : 2147483646  Per-Plan Time Limit  : UNUSED  Number of Errors     : 0 -------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------   Number of plans processed  : 1   Number of findings         : 2   Number of recommendations  : 1   Number of errors           : 0 -------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------  Object ID          : 2  Test Plan Name     : SQL_PLAN_cq05acbz68614369278f9  Base Plan Name     : SQL_PLAN_cq05acbz686146b581ab9  SQL Handle         : SQL_cb00aa62fe641824  Parsing Schema     : C##SALMAN Test Plan Creator  : C##SALMAN  SQL Text           : select count(object_name) from test where                     object_id=:v_objid Bind Variables: -----------------------------  1  -  (NUMBER):  102 Execution Statistics: -----------------------------         Base Plan                     Test Plan ----------------------------  ----------------------------  Elapsed Time (s):  .000762                       .000001  CPU Time (s):      .000694                       0  Buffer Gets:       335                           0  Optimizer Cost:    932                           2  Disk Reads:        0                             0  Direct Writes:     0                             0 Rows Processed:    0                             0  Executions:        10                            10 FINDINGS SECTION -------------------------------------------------------------------------------- Findings (2): -----------------------------  1. The plan was verified in 0.10900 seconds. It passed the benefit criterion     because its verified performance was 1118.89813 times better than that of     the baseline plan.  2. The plan was automatically accepted. Recommendation: ----------------------------- Consider accepting the plan. EXPLAIN PLANS SECTION -------------------------------------------------------------------------------- Baseline Plan -----------------------------  Plan Id          : 2306  Plan Hash Value  : 1800936121 ---------------------------------------------------------------------- | Id  | Operation            | Name | Rows | Bytes | Cost | Time     | ---------------------------------------------------------------------- |   0 | SELECT STATEMENT     |      |    1 |    18 |  932 | 00:00:01 | |   1 |   SORT AGGREGATE     |      |    1 |    18 |      |          | | * 2 |    TABLE ACCESS FULL | TEST |    1 |    18 |  932 | 00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - filter("OBJECT_ID"=:V_OBJID) Test Plan -----------------------------  Plan Id          : 2307  Plan Hash Value  : 915568889 -------------------------------------------------------------------------------- | Id  | Operation                              | Name     | Rows | Bytes | Cost  | Time     | -------------------------------------------------------------------------------- ------------ |   0 | SELECT STATEMENT                       |          |    1 |    18 |    2  | 00:00:01 | |   1 |   SORT AGGREGATE                       |          |    1 |    18 |       |          | |   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | TEST     |    1 |    18 |    2  | 00:00:01 | | * 3 |     INDEX RANGE SCAN                   | TEST_IDX |    1 |       |    1  | 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - access("OBJECT_ID"=:V_OBJID)

As you can see in bellow output, the new plan has been accepted. Now we can expect optimizer to choose either FULL TABLE SCAN plan, or INDEX based plan, based on value passed to the bind variable.

SQL> select sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select count(object_name) from test%'; SQL_HANDLE                       PLAN_NAME                                                ENA   ACC   FIX ----------------------------------------------- --------------------------------------- --- --- --------------------- SQL_cb00aa62fe641824        SQL_PLAN_cq05acbz68614369278f9        YES   YES    NO SQL_cb00aa62fe641824         SQL_PLAN_cq05acbz686146b581ab9         YES   YES    NO

After we have a new ACCEPTED plan, let’t test it by executing the same SQL from 2 different sessions with v_objid=100 from one session and v_objid=102 from the other session. Once you do a few executions of SQL form both session. You can query V$SQL to check the executions of plans. I found following which shows that both plans are in use now.

SQL> select sql_id,plan_hash_value,executions from v$sql where sql_id='bdgffww5rp2bn'; SQL_ID        PLAN_HASH_VALUE         EXECUTIONS ------------- --------------- ------------------------------------ bdgffww5rp2bn      1950795681                  3 bdgffww5rp2bn       939327728                   2

We can conclude that both accepted plans are now in use and optimizer is using both of these plans because a suitable plan is chosen based on the values passed to the bind variable.

You can drop the SQL Plan Base line with DBMS_SPM.DROP_SQL_PLAN_BASELINE.

SQL> VARIABLE v_total_plans_dropped NUMBER SQL> exec :v_total_plans_dropped:=dbms_spm.drop_sql_plan_baseline( sql_handle => 'SQL_cb00aa62fe641824') PL/SQL procedure successfully completed. SQL> print :v_total_plans_dropped V_TOTAL_PLANS_DROPPED ---------------------------------------                     2

There is a possibility that newly loaded plan does not get accepted after we “evolve” it because new plan should have lower cost than the currently accepted plan(s) in order to have any new plan(s) “accepted”. The EVOLVE process would return something similar to the following if loaded plan is worse than an already ACCEPTED plan.

… … … FINDINGS SECTION -------------------------------------------------------------------------------- Findings (1): -----------------------------  1. The plan was verified in 0.34400 seconds. It failed the benefit criterion     because its verified performance was 0.62457 times worse than that of the     baseline plan. … … …

DBMS_SPM.ALTER_SQL_PLAN_BASELINE

We can use this procedure to do the following:
FIX a Plan: Fixed plans are preferred plans (it has to be accepted to be used) and optimizer chooses from Fixed Accepted plans

SQL> VARIABLE :v_plans_affected exec :v_plans_affected:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_cb00aa62fe641824','SQL_PLAN_cq05acbz68614369278f9','fixed','yes')

Enable/Disable a plan: You can disable a plan if you do not want it to be used or analyzed during evolve process.

SQL> VARIABLE :v_plans_affected exec :v_plans_affected:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_cb00aa62fe641824','SQL_PLAN_cq05acbz68614369278f9','enabled','yes')

Retention or Purge of plans: A plan will be purged from the baseline if it is not get used after a certain period of time (this time is also adjustable). By default every plan is set to auto purge and can be set for not to be purged.

SQL> VARIABLE :v_plans_affected exec :v_plans_affected:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_cb00aa62fe641824','SQL_PLAN_cq05acbz68614369278f9','autopurge','yes')