SQL Plan Management(SPM) - Demo with SQL Interface:
SQL> sho user
USER is "SH"
SQL> desc spm_test_table_1
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
SQL> desc spm_test_table_2
Name Null? Type
----------------------------------------- -------- ----------------------------
CHANNEL_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
PROD_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
UNIT_COST NOT NULL NUMBER(10,2)
UNIT_PRICE NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
QUANTITY_SOLD NOT NULL NUMBER(10,2)
TOTAL_COST NUMBER
SQL> select count(1) from spm_test_table_1;
COUNT(1)
----------
1
SQL> select count(1) from spm_test_table_2;
COUNT(1)
----------
1
we are using two tables for this demo
SQL> create index spm_index_table1 on spm_test_table_1(cust_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL> explain plan for select count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 1 | 8 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| SPM_TEST_TABLE_2 | 1 | 4 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SPM_INDEX_TABLE1 | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."CUST_ID"="T2"."CUST_ID")
16 rows selected.
SQL> sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
no rows selected
-- currently there is no baseline plan for this statement.
SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
COUNT(*)
----------
0
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
no rows selected
-- Still no baseline for this
SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
COUNT(*)
----------
0
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
-- We now have the base line plan which was captured automatically.
We will now populate the two test tables with some huge data.
SQL> select count(1) from spm_test_table_1;
COUNT(1)
----------
918844
SQL> select count(1) from spm_test_table_2;
COUNT(1)
----------
916040
SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1834K (1)| 06:06:59 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | NESTED LOOPS | | 119M| 1137M| 1834K (1)| 06:06:59 |
| 3 | TABLE ACCESS FULL| SPM_TEST_TABLE_2 | 916K| 4472K| 1644 (1)| 00:00:20 |
|* 4 | INDEX RANGE SCAN | SPM_INDEX_TABLE1 | 130 | 650 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."CUST_ID"="T2"."CUST_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement
20 rows selected.
-- We are still using the same baseline plan.
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
COUNT(*)
----------
172868759
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO YES
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
--- The new plan is captured but not inserted into the plan baseline.
We will now simulate the automatic SQL tuning by executing the task manually.
SQL> DECLARE
my_task_name varchar2(30);
sql_txt clob;
BEGIN
sql_txt := 'select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => 'Task_askm',
description => 'Tune SPM Query Task');
END;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.execute_tuning_task('Task_askm');
PL/SQL procedure successfully completed.
SQL> select dbms_sqltune.report_tuning_task('Task_askm') FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Task_askm
Tuning Task Owner : SH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status : COMPLETED
Started at : 10/02/2009 09:07:14
Completed at : 10/02/2009 09:08:06
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID : cvfq6n3ngyzzy
SQL Text : select /* SPM_AUTO */ count(*) from spm_test_table_1
t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.77%)
------------------------------------------
- Consider accepting the recommended SQL profile. The SQL plan baseline
corresponding to the plan with the SQL profile will also be updated to an
accepted plan.
execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',
task_owner => 'SH', replace => TRUE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1633133659
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 10 | 1834K (1)| 06
:06:59 |
| 1 | SORT AGGREGATE | | 1 | 10 | |
|
| 2 | NESTED LOOPS | | 119M| 1137M| 1834K (1)| 06
:06:59 |
| 3 | TABLE ACCESS FULL| SPM_TEST_TABLE_2 | 916K| 4472K| 1644 (1)| 00
:00:20 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | SPM_INDEX_TABLE1 | 130 | 650 | 2 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."CUST_ID"="T2"."CUST_ID")
2- Using SQL Profile
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------
Plan hash value: 3821076920
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1 | 10 | | 421
9 (10)| 00:00:51 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 10 | |
| |
|* 2 | HASH JOIN | | 119M| 1137M| 14M| 421
9 (10)| 00:00:51 |
| 3 | TABLE ACCESS FULL | SPM_TEST_TABLE_2 | 916K| 4472K| | 164
4 (1)| 00:00:20 |
| 4 | INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 | 918K| 4486K| | 72
3 (1)| 00:00:09 |
--------------------------------------------------------------------------------
-------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."CUST_ID"="T2"."CUST_ID")
-------------------------------------------------------------------------------
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO YES
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
-- We now need to accept the recommendation before the new plan is integrated into the plan baseline. This would be done automatically by automatic SQL Tuning.
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',task_owner => 'SH', replace => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO YES
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | SPM_TEST_TABLE_2 |
| 4 | INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement
16 rows selected.
SQL> var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'NO');
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO YES
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1834K (1)| 06:06:59 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | NESTED LOOPS | | 119M| 1137M| 1834K (1)| 06:06:59 |
| 3 | TABLE ACCESS FULL| SPM_TEST_TABLE_2 | 916K| 4472K| 1644 (1)| 00:00:20 |
|* 4 | INDEX RANGE SCAN | SPM_INDEX_TABLE1 | 130 | 650 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."CUST_ID"="T2"."CUST_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement
20 rows selected.
SQL> var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM%spm_test_table%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO YES
SYS_SQL_51bbbe86cf9ff2dd SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO YES
SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | SPM_TEST_TABLE_2 |
| 4 | INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement
16 rows selected.
SQL>
SQL Plan Manageability Demo.mp4
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.