Adaptive SQL Plan Management (SPM) in Oracle Database 12c Release 1 (12.1)
Check out the changes to SQL Plan Management (SPM) in Oracle Database 12c Release 1 (12.1).
oracle 12cconfigurationintermediate
by OracleDba
17 views
📋Steps in this guide1/2
1
SYS_AUTO_SPM_EVOLVE_TASK
In Oracle database 12c the evolution of existing baselines is automated as an advisor task called , 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 the advisor task.
If you don't wish existing baselines to be evolved automatically, set the parameter to .
Typically, the and 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.
The package has a function called 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.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101
CONN 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>
CONN 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>
BEGIN
DBMS_SPM.set_evolve_task_parameter(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
parameter => 'ACCEPT_PLANS',
value => 'FALSE');
END;
/
BEGIN
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;
/
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>2
Manually Evolving SQL Plan Baselines
In previous releases, evolving SQL plan baselines was done using the function. In 12c this has been replaced by a task-based approach, which typically involves the following steps.
-
In addition, the following routines can interact with an evolve task.
-
In order to show this in action we need to create a SQL plan baseline, so the rest of this section is an update of the 11g process to manually create a baseline and evolve it, described here .
Create and populate a test table.
Query the table using an unindexed column, which results in a full table scan.
Identify the of the SQL statement by querying the view.
Use this to manually load the SQL plan baseline.
The 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.
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 the view we can see why.
The SQL plan baseline now contains a second plan, but it has not yet been accepted.
If you don't see the new row in the 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.
Report on the result of the evolve task.
If the evolve task has completed and has reported recommendations, implement them. The recommendations suggests using , but you should really use .
The view shows the second plan as been accepted.
Repeating the earlier test shows the more efficient plan is now available for use.
If you want to remove the plans, drop them using the function.
For more information see:
Hope this helps. Regards Tim...
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337
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;
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 |
----------------------------------------------------------------------------------
CONN 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>
SET 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>
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>
CONN 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
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>
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>
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>
SET 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>
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>
CONN 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
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>Comments (0)
Please to add comments
No comments yet. Be the first to comment!