SQL Plan Baselines

 

Description of SQL Plan Baselines

  A SQL Plan Baseline influences the query optimizer and forces the optimizer to create

  specific execution plans for a given SQL statement. It is applied to a specific SQL statement

  without needing any modification of the SQL statement itself.

 

When To Use

  When optimizing a SQL statement that can’t be changed in the application.

  If experiencing execution plan instability for a specific SQL statement

 

Note: SQL Plan Baselines should be used only when it is necessary to explicitly restrict the query optimizer’s choice to specific execution plans.

 

Overview of differences between SQL Plan Baselines and SQL Profile

  Baseline is proactive

  Profile is reactive

  Baselines are created before problems occur

  Profile is created by SQL Tuning Advisor after a problem

  Baselines reproduce a specific plan

  Profile corrects optimizer cost estimates

 

Sources of SQL Plan Baselines

  Automatic capture

  Shared SQL Area (from SGA)

  SQL Tuning Set(STS)

  Staging table

  Stored Outline

 

Display SQL Plan Baselines information

Views:

  dba_sql_plan_baselines

  cdb_sql_plan_baselines

 

Check the current settings for SQL Plan Management:

 

SQL>SHOW PARAMETER SQL_PLAN

 

NAME TYPE VALUE

------------------------------------ ----------- ----------

optimizer_capture_sql_plan_baselines boolean FALSE

optimizer_use_sql_plan_baselines boolean TRUE

 

Display information about a particular SQL Plan Baseline:

 

SQL> SELECT *

  FROM table (dbms_xplan.display_sql_plan_baseline(sql_handle =>’QL_871650b23f879es7’));.

 

Capturing SQL Plan Baselines

  Automatic Capture

  Set the optimizer_capture_sql_plan_baselines = TRUE at the session or system level.

 

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

 

Note: Oracle 12cR2 allows greater filtering of which statements are captured. The configurable parameters and their settings are shown in the view, dba_sql_management_config.

 

  Once OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to true then the DBMS_SPM.CONFIGURE procedure can be used to configure a filter for the automatic capture of repeatable sql statements. The parameter in the following example is available in Oracle 12.2

 

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SCOTT',true);

 

  The first time a query is run a signature is created and placed in the SQL log

  The second time a query is run, the execution plan is used to create a SQL Plan Baseline which is marked as accepted.

  The third time and beyond that a query is run, the execution plan is compared to the existing SQL Plan Baseline execution plan. If they don't match, the new execution plan is still added to the SQL Plan Baseline but is marked as non-accepted. However, the plan marked as non-accepted can be manually “evolved” to make it available to the query optimizer.

 

Note: The first plan captured is automatically accepted even if it is not the best performer. Therefore automatic capture should only be used when the SQL statements are performing as expected.

 

  Manually Loading SQL Plan Baselines            

  Shared SQL area (load cursors stored in the library cache)

  Execute a query:

  ex. select id, pad from T where n = 24

  Query the cursor cache:

  Select * from table(dbms_xplan.display_cursor);

 

SQL>Select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 6a5k6v87gwusj, child number 0

-------------------------------------

select id, pad from T where n = 24

 

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 23 (100) | |

|* 1 | TABLE ACCESS FULL| T | 1 | 509 | 23 (0) | 00:00:01 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  1 - filter("N"=24)

 

If the last query is not the one you want to display then the SQL_ID and child number will be needed. The way to get the SQL ID and child number is to query the V$SQL view.

 

SELECT sql_id, child_number, sql_text

FROM v$sql

WHERE sql_text LIKE '%select id, pad from T where n%';

 

SQL_ID CHILD_NUMBER SQL_TEXT

---------- -------------------- ----------------------------------

6a5k6v87gwusj 0 select id, pad from T where n = 24

 

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('6a5k6v87gwusj',0));

 

Another way to get the information and view the explain plan in one statement

SELECT t.*

FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%select id, pad from T where n%';

 

The next step is to load the plans for the specified statements into the SQL plan baseline.

 

 example 1:

  DECLARE

  cnt NUMBER;

  BEGIN

  cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '6a5k6v87gwusj', parsing_schema_name=> ’my_user’ ,plan_hash_value => NULL);

  dbms_output.put_line( cnt || ' SQL plan baseline(s) created');

END;

/

example 2:

  SQL>VARIABLE cnt NUMBER

  SQL> EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(attribute_name => 'sql_text', attribute_value => '%select id, pad from T where n%');

  SQL> execute dbms_output.put_line( :cnt || ' SQL plan baseline(s) created');

 

Execution plans loaded using dbms_spm.load_plans_from_cursor_cache are stored as accepted.

Query the data dictionary to ensure that the query plans were loaded into the baseline

 

  SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,

                            ORIGIN, ENABLED, ACCEPTED

                            FROM DBA_SQL_PLAN_BASELINES

  WHERE created > systimestamp - to_dsinterval('0 00:15:00');

 

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC

---------------------- ------------------------------ ------------------------------ -------------- --- ---

SQL_22de452b06b82caf select * from T where n = 24 SQL_PLAN_25rk55c3bhb5g94ecae5c MANUAL-LOAD YES YES

 

Use the information from above to view baseline information:

  SELECT *

  FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => '&sql_handle'));

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

 

SQL handle: SQL_22de452b06b82caf

--------------------------------------------------------------------------------

SQL text: select * from T where n = 24

--------------------------------------------------------------------------------

 

Plan name: SQL_PLAN_25rk55c3bhb5g94ecae5c Plan id: 2498539100

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

Plan rows: From dictionary

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

 

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 23 (100)| |

|* 1 | TABLE ACCESS FULL| T | 1 | 509 | 23 (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

  1 - filter("N"=24)

 

  Change Explain Plan using SQL Plan Baseline

 

If an application cannot be altered but it makes the optimizer use a less efficient execution plan, such as having an embedded hint, the execution plan can still be changed by using a Baseline.

As an example a SQL statement has a full table scan hint:

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110;

However, an index for n is available and should provide a more efficient execution plan.

 

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 5110;

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------

SQL_ID 297f098q1j86m, child number 0

-------------------------------------

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 5110

 

Plan hash value: 3486652657

 

 

----------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100) | |

| 1 | SORT AGGREGATE | | 1 | 505 | | |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 505 | 2 (0) | 00:00:01 |

|* 3 | INDEX RANGE SCAN | TIDX | 1 | | 1 (0) | 00:00:01 |

---------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  3 - access("N"=5110)

 

 

First capture a SQL plan baseline for the SQL that needs to be optimized.

 

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110;

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110;

ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

 

Now that the SQL plan baseline has been created, check that it’s being used. The name of the SQL Plan Baseline will be shown at the end of the Plan Table Output under Note

 

SQL>SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110;

SQL>Select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------

SQL_ID 1r2vtf4588bd6, child number 1

-------------------------------------

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110

 

Plan hash value: 2966233522

.

.

.

Note

-----

  - SQL plan baseline SQL_PLAN_6wctvsbdrd3bd3fdbb376 used for this statement

 

Use the SQL Plan name from above to find the SQL handle.

 

SELECT sql_handle

FROM dba_sql_plan_baselines

WHERE plan_name = '&plan_name';

Enter value for plan_name: SQL_PLAN_6wctvsbdrd3bd3fdbb376

old 3: WHERE plan_name = '&plan_name'

new 3: WHERE plan_name = 'SQL_PLAN_6wctvsbdrd3bd3fdbb376'

 

SQL_HANDLE

------------------------------

SQL_6e333bc2db768d6d

 

Replace the SQL plan baseline for the SQL, with the full table scan hint, with one that uses the index.

 

DECLARE

  ret PLS_INTEGER;

BEGIN

  ret := dbms_spm.load_plans_from_cursor_cache(

  sql_handle => 'SQL_6e333bc2db768d6d',

  sql_id => '297f098q1j86m',

  plan_hash_value => '3486652657'

  );

  dbms_output.put_line(ret || ' SQL plan baseline(s) created');

  ret := dbms_spm.drop_sql_plan_baseline(

  sql_handle => 'SQL_6e333bc2db768d6d',

  plan_name => 'SQL_PLAN_6wctvsbdrd3bd3fdbb376'

  );

  dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');

END;

/

 

Check that the replacement has occurred by running the sql statement twice and then check the sql cursor.

SQL>SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110;

SQL> /

SQL>Select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

SQL_ID 1r2vtf4588bd6, child number 1

-------------------------------------

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 5110

 

Plan hash value: 3486652657

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | |2(100) | |

| 1 | SORT AGGREGATE | | 1 | 505 | | |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 505 |2 (0) |00:00:01|

|* 3 | INDEX RANGE SCAN | TIDX | 1 | | 1(0) |00:00:01|

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  3 - access("N"=5110)

 

Note

-----

  - SQL plan baseline SQL_PLAN_6wctvsbdrd3bdda776350 used for this statement

 

24 rows selected.

 

Although the SQL has the ‘full’ hint, the execution plan no longer uses a full table scan; it now uses the index.

It is also possible to check if a SQL Plan Baseline was used by a specific SQL statement by quering the sql_plan_baseline column in the v$sql view. The column shows the SQL Plan name.

 

Displaying SQL Plan Baselines

 

To show all the explain plans for a given SQL statement:

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_987654321abcdefghi'));

 

To show only one particular plan for a given SQL statement include plan_name.

To show the hints associated with a SQL Plan Baseline:

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_987654321abcdefghi',

plan_name => ’SQL_PLAN_6wctvsbdrd3bdda776350',

format => 'outline'));

 

 

NOTE: not all hints can be stored in SQL Plan Baselines. To check : select name from v$sql_hint where version_outline IS NULL

 

Load SQL Plan Baselines using SQL tuning set (STS)

 

  Execute a query or queries . ex. Select * from atable where id = 3124:

  Create SQL Tuning Set:

 

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'MY_SQLSET');

 

Load the plan from the shared SQL area into a SQL tuning set.

DECLARE

  l_cursor dbms_sqltune.sqlset_cursor;

BEGIN

  OPEN l_cursor FOR

  SELECT VALUE(test_sqlset)

  FROM TABLE(

  dbms_sqltune.select_cursor_cache(

  basic_filter => 'sql_text LIKE ''%atable where id%'' and parsing_schema_name = ''MY_USER''', attribute_list => 'ALL')

  ) test_sqlset;

                 DBMS_SQLTUNE.load_sqlset(sqlset_name => 'MY_SQLSET',

  populate_cursor => l_cursor);

END;

/

 

The execution plans loaded from the SQL Tuning Set are stored as accepted and are immediately available for use by the optimizer.

 

another example using AWR baseline -

... FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

  'peak baseline',

  NULL, NULL,

  'elapsed_time',

  NULL, NULL, NULL,

  30))

 

Verify SQL is in SQL tuning set.

SELECT SQL_TEXT

FROM DBA_SQLSET_STATEMENTS

WHERE SQLSET_NAME = 'MY_SQLSET';

 

Load the plan from the STS into the SQL Plan Baseline

VARIABLE cnt NUMBER

EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'MY_SQLSET',

  basic_filter => 'sql_text like ''%atable where id%''' );

 

Query the data dictionary to ensure that the plan was loaded.

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,

ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES;

 

Or if necessary, drop the plan

EXEC SYS.DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'MY_SQLSET',

 sqlset_owner => 'SPM' );

 

Evolving SQL Plan Baselines

  When the query optimizer creates more than one execution plan in the SQL plan baseline for a given statement, the new execution plan is accepted but marked as non-accepted. To verify if non-accepted execution plans will perform better, an evolution must be tried. If a non-accepted plan is better then it is set to accepted. Normally the optimizer will switch to the better execution plan during the next maintenance window. However if it is important not to wait then the plan can be accepted by using an evolve task.

 

Manually evolving SQL Plan Baselines

Create a new evolve task for the baseline

 

DECLARE

  l_return VARCHAR2(32767);

BEGIN

  l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_6e333bc2db768d6d');

  DBMS_OUTPUT.put_line('Task Name: ' || l_return);

END;

/

 

Task Name: TASK_14

 

Execute the evolve task

 

DECLARE

  l_return VARCHAR2(32767);

BEGIN

  l_return := DBMS_SPM.execute_evolve_task(task_name => 'TASK_14');

  DBMS_OUTPUT.put_line('Execution Name: ' || l_return);

END;

/

 

Execution Name: EXEC_14

 

Output the result of the evolve task

 

SELECT DBMS_SPM.report_evolve_task(task_name => 'TASK_14', execution_name => 'EXEC_14') AS output

FROM dual;

 

Implement the report recommendations

 

DECLARE

  l_return NUMBER;

BEGIN

  l_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_14');

  DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return);

END;

/

 

Plans Accepted: 1

 

Query the data dictionary to ensure that the new plan is accepted

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED

  FROM DBA_SQL_PLAN_BASELINES

  WHERE SQL_HANDLE IN ('SQL_6e333bc2db768d6d')

  ORDER BY SQL_HANDLE, ACCEPTED;

 

Automatically evolving SQL Plan Baselines

 

SPM Evolve Advisor

  The SPM Evolve Advisor (added in 12.1) is a SQl Advisor that evolves recently added SQL Plan Baselines. It’s task SYS_AUTO_SPM_EVOLVE_TASK runs daily during the maintenance window. Using a cost-based algorithm it analyzes unaccepted plans and automatically accepts them if they are better than the accepted plan.

  The DBMS_SPM package enables you to configure automatic plan evolution parameters.

  When the parameter ACCEPT_PLANS is true (default), SQL plan management automatically accepts all plans recommended by the task. When set to false, the task verifies the plans and generates a report of its findings, but does not evolve the plans.

 

Configuring the SPM Evolve Advisor Task:

 

First query the current task settings

COL PARAMETER_NAME FORMAT a25

COL VALUE FORMAT a10

              SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"

              FROM DBA_ADVISOR_PARAMETERS

              WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK')

  AND ( (PARAMETER_NAME = 'ACCEPT_PLANS')

  OR (PARAMETER_NAME = 'TIME_LIMIT') ) );

 

Then set parameters using PL/SQL code of the following form:

BEGIN

  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

                task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

                                           parameter => parameter_name,

                                           value => value );

END;

 /

 

For example, the following PL/SQL block sets a time limit to 20 minutes, and also automatically accept plans:

BEGIN

  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

  task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

  parameter => 'LOCAL_TIME_LIMIT',

  value => 1200

);

  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

  task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

  parameter => 'ACCEPT_PLANS',

  value => 'true'

);

END;

/

 

Altering SQL Plan Baselines

Disable execution plan for a SQL Plan Baseline:

ret := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_6e333bc2db768d6d', plan_name => 'SQL_PLAN_6wctvsbdrd3bd3fdbb376',

attribute_name => 'enabled',

attribute_value => 'no');

 

Moving SQL Plan Baselines

This is especially useful for transferring SQL Plan Baselines from testing to production.

Using a Staging table to transfer Baseline information to another database

 

Create a staging table (example uses table name stage1)

BEGIN

  DBMS_SPM.CREATE_STGTAB_BASELINE (

  table_name => 'stage1');

END;

/

 

Pack the SQL plan baselines into the staging table

DECLARE

  my_plans NUMBER;

BEGIN

                my_plans := DBMS_SPM.PACK_STGTAB_BASELINE (

  table_name => 'stage1',

              enabled => 'yes',

              creator => 'spm'

              );

dbms_output.put_line(my_plans || ' SQL plan baseline(s) exported

END;

/

 

Export table using expdp

Transfer dump file to new database and import using impdp.

Unpack the SQL plan baselines from the staging table into the SQL management base

 

DECLARE

 my_plans NUMBER;

BEGIN

                my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (

                table_name => 'stage1',

              fixed => 'yes'

              );

dbms_output.put_line(my_plans || ' SQL plan baseline(s) imported');

END;

/

 

Check that SQL Plan Baselines are imported:

SELECT sql_handle, plan_name, sql_text, enabled, accepted

FROM dba_sql_plan_baselines;