Home » » SQL Plan Management(SPM) - Demo with SQL Interface

SQL Plan Management(SPM) - Demo with SQL Interface

Written By Srikrishna Murthy Annam on Friday, October 2, 2009 | 1:45 PM

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



Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger