Privileges
ADMINISTER SQL MANAGEMENT OBJECT
Concepts
sql_handle SQL handle is a unique identifier for each SQL statement
sql_id
plan_name
Manual
To find sql_id from query
SELECT sql_id
FROM v$sql
WHERE plan_hash_value = 1107868462
AND sql_text NOT LIKE 'EXPLAIN%';
or
WHERE sql_text LIKE '%&query%';
To manually load the SQL plan baseline.
vi exec_dbms_spm.load_plans_from_cursor_cache.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;
/
note : ATTRIBUTE_NAME and ATTRIBUTE_VALUE
To see plan associate with our baseline (dba_sql_plan_baselines)
vi select_dba_sql_plan_baselines_sql_handle.sql
vi select_dba_sql_plan_baselines_sql_text.sql
COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30
Select SQL_TEXT, sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_7b76323ad90440b9';
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
---------------------------------------- -------------------- ------------------------------ --- ---
SELECT description SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES NO
FROM spm_test_tab
WHERE id = 99
SELECT description SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
FROM spm_test_tab
WHERE id = 99
To Create Evolve task (you'll get a task_name)
vi exec_1_dbms_spm.create_evolve_task.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_1771
To execute Evolve Task (you'll get a exec_name)
vi exec_2_dbms_spm.execute_evolve_task.sql
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => 'TASK_1771');
DBMS_OUTPUT.put_line('Execution Name: ' || l_return);
END;
/
Execution Name: EXEC_21
To report
vi exec_3_dbms_spm.report_evolve_task.sql
note task_name come from create_evolve_task in DBMS_SPM
exec_name come from execute_evolve_task in DBMS_SPM
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => 'TASK_1771', execution_name => 'EXEC_2131') AS output FROM dual;
OUTPUT
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_1771
Task Owner : SYS
Execution Name : EXEC_2131
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 10/22/2017 15:53:04
Finished : 10/22/2017 15:53:04
Last Updated : 10/22/2017 15:53:04
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 : SCOTT
Test Plan Creator : SCOTT
SQL Text : SELECT description FROM spm_test_tab WHERE id = 99
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000011 .000001
CPU Time (s): 0 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.03000 seconds. It passed the benefit criterion
because its verified performance was 15.00000 times better than that of the
baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1771', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 1701
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 : 1702
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>
Note: you can tell first plan ins full table scan and second by using index
To implement plan
vi exec_4_accept_DBMS_SPM.implement_evolve_task.sql
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_1771');
DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return);
END;
/
to check implementation
vi select_dba_sql_plan_baselines_sql_handle.sql
COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30
Select SQL_TEXT, sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_7b76323ad90440b9';SQL> SQL> SQL> 2 3
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
------------------------------------------------------------ -------------------- ------------------------------ --- ---
SELECT description SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES
FROM spm_test_tab
WHERE id = 99
SELECT description SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
FROM spm_test_tab
WHERE id = 99
To report
vi exec_3_dbms_spm.report_evolve_task.sql
SELECT dbms_spm.report_auto_evolve_task() FROM dual;
DBMS_SPM.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 : 10/29/2017 08:43:48
Finished : 10/29/2017 08:43:49
Last Updated : 10/29/2017 08:43:49
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>
