SQL Plan Management
was introduced in Oracle 11g to provide a "conservative plan selection
strategy" for the optimizer. The basic concepts have not changed in
Oracle 12c, but there have been some changes to the process of evolving
SQL plan baselines. As with previous releases, auto-capture of SQL plan
baselines is disabled by default, but evolution of existing baselines is
now automated. In addition, manual evolution of sql plan baselines has
been altered to a task-based approach. This article focuses on the
changes in 12c.
- SYS_AUTO_SPM_EVOLVE_TASK
- Manually Evolving SQL Plan Baselines
SYS_AUTO_SPM_EVOLVE_TASK
In Oracle database 12c the evolution of existing baselines is automated as an advisor task called
SYS_AUTO_SPM_EVOLVE_TASK
, triggered by the existing "sql tuning advisor" client under the automated database maintenance tasks.You shouldn't alter the "sql tuning advisor" client directly to control baseline evolution. Instead, amend the parameters of theCONN sys@pdb1 AS SYSDBA COLUMN client_name FORMAT A35 COLUMN task_name FORMAT a30 SELECT client_name, task_name FROM dba_autotask_task; CLIENT_NAME TASK_NAME ----------------------------------- ------------------------------ auto optimizer stats collection gather_stats_prog auto space advisor auto_space_advisor_prog sql tuning advisor AUTO_SQL_TUNING_PROG SQL>
SYS_AUTO_SPM_EVOLVE_TASK
advisor task.If you don't wish existing baselines to be evolved automatically, set theCONN sys@pdb1 AS SYSDBA COLUMN parameter_name FORMAT A25 COLUMN parameter_value FORMAT a15 SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK' AND parameter_value != 'UNUSED' ORDER BY parameter_name; PARAMETER_NAME PARAMETER_VALUE ------------------------- --------------- ACCEPT_PLANS TRUE DAYS_TO_EXPIRE UNLIMITED DEFAULT_EXECUTION_TYPE SPM EVOLVE EXECUTION_DAYS_TO_EXPIRE 30 JOURNALING INFORMATION MODE COMPREHENSIVE TARGET_OBJECTS 1 TIME_LIMIT 3600 _SPM_VERIFY TRUE SQL>
ACCEPT_PLANS
parameter to FALSE
.BEGIN DBMS_SPM.set_evolve_task_parameter( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ACCEPT_PLANS', value => 'FALSE'); END; /
Typically, the
ACCEPT_PLANS
and TIME_LIMIT
parameters will be the only ones you will interact with. The rest of
this article assumes you have the default settings for these parameters.
If you have modified them, switch them back to the default values using
the following code.TheBEGIN DBMS_SPM.set_evolve_task_parameter( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ACCEPT_PLANS', value => 'TRUE'); END; / BEGIN DBMS_SPM.set_evolve_task_parameter( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'TIME_LIMIT', value => 3600); END; /
DBMS_SPM
package has a function called REPORT_AUTO_EVOLVE_TASK
to display information about the the actions taken by the automatic
evolve task. With no parameters specified it produces a text report for
the latest run of the task.SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100 SELECT DBMS_SPM.report_auto_evolve_task FROM dual; REPORT_AUTO_EVOLVE_TASK -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : EXEC_1 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 02/17/2015 06:00:04 Finished : 02/17/2015 06:00:04 Last Updated : 02/17/2015 06:00:04 Global Time Limit : 3600 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 0 Number of findings : 0 Number of recommendations : 0 Number of errors : 0 --------------------------------------------------------------------------------------------- SQL>
Manually Evolving SQL Plan Baselines
In previous releases, evolving SQL plan baselines was done using the
EVOLVE_SQL_PLAN_BASELINE
function. In 12c this has been replaced by a task-based approach, which typically involves the following steps.CREATE_EVOLVE_TASK
EXECUTE_EVOLVE_TASK
REPORT_EVOLVE_TASK
IMPLEMENT_EVOLVE_TASK
CANCEL_EVOLVE_TASK
RESUME_EVOLVE_TASK
RESET_EVOLVE_TASK
Query the table using an unindexed column, which results in a full table scan.CONN test/test@pdb1 DROP TABLE spm_test_tab PURGE; CREATE TABLE spm_test_tab ( id NUMBER, description VARCHAR2(50) ); INSERT /*+ APPEND */ INTO spm_test_tab SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000; COMMIT;
Identify theSET AUTOTRACE TRACE SELECT description FROM spm_test_tab WHERE id = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1107868462 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 | ----------------------------------------------------------------------------------
SQL_ID
of the SQL statement by querying the V$SQL
view.Use thisCONN sys@pdb1 AS SYSDBA SELECT sql_id FROM v$sql WHERE plan_hash_value = 1107868462 AND sql_text NOT LIKE 'EXPLAIN%'; SQL_ID ------------- gat6z1bc6nc2d SQL>
SQL_ID
to manually load the SQL plan baseline.TheSET SERVEROUTPUT ON DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => 'gat6z1bc6nc2d'); DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); END; / Plans Loaded: 1 PL/SQL procedure successfully completed. SQL>
DBA_SQL_PLAN_BASELINES
view provides information
about the SQL plan baselines. We can see there is a single plan
associated with our baseline, which is both enabled and accepted.Flush the shared pool to force another hard parse, create an index on the ID column, then repeat the query to see the affect on the execution plan.COLUMN sql_handle FORMAT A20 COLUMN plan_name FORMAT A30 SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%spm_test_tab%' AND sql_text NOT LIKE '%dba_sql_plan_baselines%'; SQL_HANDLE PLAN_NAME ENA ACC -------------------- ------------------------------ --- --- SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES SQL>
Notice the query doesn't use the newly created index, even though we forced a hard parse. The note explains the SQL plan baseline is used. Looking at theCONN sys@pdb1 AS SYSDBA ALTER SYSTEM FLUSH SHARED_POOL; CONN test/test@pdb1 CREATE INDEX spm_test_tab_idx ON spm_test_tab(id); EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE); SET AUTOTRACE TRACE SELECT description FROM spm_test_tab WHERE id = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1107868462 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=99) Note ----- - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement
DBA_SQL_PLAN_BASELINES
view we can see why.CONN sys@pdb1 AS SYSDBA SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_handle = 'SQL_7b76323ad90440b9'; SQL_HANDLE PLAN_NAME ENA ACC -------------------- ------------------------------ --- --- SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES NO SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES SQL>
The SQL plan baseline now contains a second plan, but it has not yet been accepted.
Note: If you don't see the new row in the
DBA_SQL_PLAN_BASELINES
view go back and rerun the query from "spm_test_tab" until you do. It
sometimes takes the server a few attempts before it notices the need for
additional plans.
For the new plan to be used we need to wait for the maintenance
window or manually evolve the SQL plan baseline. Create a new evolve
task for this baseline.
Execute the evolve task.SET SERVEROUTPUT ON DECLARE l_return VARCHAR2(32767); BEGIN l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_7b76323ad90440b9'); DBMS_OUTPUT.put_line('Task Name: ' || l_return); END; / Task Name: TASK_21 PL/SQL procedure successfully completed. SQL>
Report on the result of the evolve task.SET SERVEROUTPUT ON DECLARE l_return VARCHAR2(32767); BEGIN l_return := DBMS_SPM.execute_evolve_task(task_name => 'TASK_21'); DBMS_OUTPUT.put_line('Execution Name: ' || l_return); END; / Execution Name: EXEC_21 PL/SQL procedure successfully completed. SQL>
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100 SELECT DBMS_SPM.report_evolve_task(task_name => 'TASK_21', execution_name => 'EXEC_21') AS output FROM dual; OUTPUT ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : TASK_21 Task Owner : SYS Execution Name : EXEC_21 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 02/18/2015 08:37:41 Finished : 02/18/2015 08:37:41 Last Updated : 02/18/2015 08:37:41 Global Time Limit : 2147483646 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 1 Number of findings : 1 Number of recommendations : 1 Number of errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362 Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8 SQL Handle : SQL_7b76323ad90440b9 Parsing Schema : TEST Test Plan Creator : TEST SQL Text : SELECT description FROM spm_test_tab WHERE id = 99 Execution Statistics: ----------------------------- Base Plan Test Plan ---------------------------- ---------------------------- Elapsed Time (s): .000019 .000005 CPU Time (s): .000022 0 Buffer Gets: 4 0 Optimizer Cost: 14 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The plan was verified in 0.02000 seconds. It passed the benefit criterion because its verified performance was 15.00740 times better than that of the baseline plan. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 2, task_owner => 'SYS'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline Plan ----------------------------- Plan Id : 101 Plan Hash Value : 3059496904 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 14 | 00:00:01 | | * 1 | TABLE ACCESS FULL | SPM_TEST_TAB | 1 | 25 | 14 | 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("ID"=99) Test Plan ----------------------------- Plan Id : 102 Plan Hash Value : 911393634 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB | 1 | 25 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 | 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ID"=99) --------------------------------------------------------------------------------------------- SQL>
If the evolve task has completed and has reported recommendations, implement them. The recommendations suggests using
ACCEPT_SQL_PLAN_BASELINE
, but you should really use IMPLEMENT_EVOLVE_TASK
.TheSET SERVEROUTPUT ON DECLARE l_return NUMBER; BEGIN l_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_21'); DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return); END; / Plans Accepted: 1 PL/SQL procedure successfully completed. SQL>
DBA_SQL_PLAN_BASELINES
view shows the second plan as been accepted.Repeating the earlier test shows the more efficient plan is now available for use.CONN sys/pdb1 AS SYSDBA SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_handle = 'SQL_7b76323ad90440b9'; SQL_HANDLE PLAN_NAME ENA ACC -------------------- ------------------------------ --- --- SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES SQL>
If you want to remove the plans, drop them using theCONN test/test@pdb1 SET AUTOTRACE TRACE LINESIZE 130 SELECT description FROM spm_test_tab WHERE id = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 2338891031 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=99) Note ----- - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5t3652c362" used for this statement
DROP_SQL_PLAN_BASELINE
function.CONN sys@pdb1 AS SYSDBA SET SERVEROUTPUT ON DECLARE l_plans_dropped PLS_INTEGER; BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_7b76323ad90440b9'); DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped); END; / Plans Dropped: 2 PL/SQL procedure successfully completed. SQL>
No comments: