SQL Plan Management

Posted on , updated on by Yannick Jaquier
Oracle , ,

Table of contents

Preamble

As I have seen multiple contradictory posts on Internet on this subject it’s worth to clarify that SQL Plan Management (SPM) is included in Oracle Enterprise and so is a free option. It has been clearly stated by Nigel Bayliss in his post.

The aim of SPM is all about plan stability. Starting with Oracle 9i we are able to create stored outlines (DBMS_OUTLN package) that allow you with a set of hints to achieve plan stability. Starting with Oracle 10g outlines are replaced by SQL profiles to achieve same target. If you have Tuning Pack Enterprise option SQL Profiles are part of the optimization proposed by SQL Tuning Advisor (STA), DBMS_SQLTUNE package.

In 11g SQL Profiles are still there for STA and for automatic SQL tuning task (Tuning pack required):

SQL> SET lines 150
SQL> col OPERATION_NAME FOR a25
SQL> col task_name FOR a25
SQL> SELECT task_name, operation_name, status, last_good_date FROM DBA_AUTOTASK_TASK;
 
TASK_NAME                 OPERATION_NAME            STATUS   LAST_GOOD_DATE
------------------------- ------------------------- -------- ---------------------------------------------------------------------------
AUTO_SQL_TUNING_PROG      automatic SQL tuning task ENABLED  19-DEC-11 05.43.55.772563 PM +01:00
gather_stats_prog         auto optimizer stats job  ENABLED  19-DEC-11 05.32.21.651905 PM +01:00
auto_space_advisor_prog   auto SPACE advisor job    ENABLED  19-DEC-11 05.32.08.105007 PM +01:00

When a SQL profile is implemented the plan is automatically added and accepted to the existing SQL Plan Baseline. When SPM is activated Oracle manage a SQL plan history for every repeatable SQL (executed twice) and the set of accepted plans is called a SQL Plan Baseline. All those objects are stored in SQL Management Base (SMB) located in SYSAUX tablespace.

So outlines moved to SQL Profile and in 11g we have now a mix of SQL Profile and SQL Plan Baseline (outlines can still be created in 11g !). What Oracle state is that SQL Profile is more reactive feature when a particular SQL is not performing well where SQL Plan Baseline is more proactive as only better performing plan are activated. This avoid plan regression when changing your application data model (statistics, tables’ number of rows,…).

This post has been done with the help of an Enterprise Edition Release 11.2.0.1.0 database running on Red Hat Enterprise Linux Server release 5.5 (Tikanga).

For testing I will use the below table (that I used in my post on adaptive cursor sharing):

DROP TABLE test1;
 
CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users;
 
DECLARE
i NUMBER;
nbrows NUMBER;
BEGIN
  i:=1;
  nbrows:=50000;
  LOOP
    EXIT WHEN i>nbrows;
    IF (i=1) THEN
      INSERT INTO test1 VALUES(1,RPAD('A',49,'A'));
    ELSE
      INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
    END IF;
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/
 
CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users;

Testing

Note that, by default, Oracle 11gR2 is ready to use SQL Plan Baselines but does not generate them by default:

SQL> show parameter optimizer%baselines
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines BOOLEAN     FALSE
optimizer_use_sql_plan_baselines     BOOLEAN     TRUE

So to start generating SQL Plan Baselines either you change optimizer_capture_sql_plan_baselines to tell Oracle to create SQL plan baselines or you use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE / DBMS_SPM.LOAD_PLANS_FROM_SQLSET to respectively load SQL Plan baselines from cursor (library cache) or from SQL Tuning Set (STS).

Let’s activate the auto capture of SQL Plan Baselines with:

SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE;
 
SYSTEM altered.

If you perform multiple test ensure all is clean by flushing the shared pool and deleting existing SQL Plan Baselines linked to your SQL statement:

SQL> DECLARE
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_eda5ceb39ef27944',plan_name=>NULL);
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
SQL> ALTER SYSTEM flush shared_pool;
 
SYSTEM altered.
 
SQL> ALTER SYSTEM flush shared_pool;
 
SYSTEM altered.

Remark:
In 11.2.0.2 Oracle has changed the naming convention and sql handle are now like SQL_eda5ceb39ef27944.

In this example I have used below statement to gather statistics and so histogram on id column is not there (see my post to understand why):

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram
FROM DBA_TAB_COL_STATISTICS
WHERE owner='YJAQUIER'
AND table_name='TEST1';
 
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ------------ ----------- ---------------
TEST1                          ID                                        2           1 NONE

Then I execute the query where we would expect an index range scan while, as no histogram, a Full Table Scan (FTS) will be performed:

SQL> SET lines 130
SQL> SET autotrace traceonly STATISTICS
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1;
 
 
STATISTICS
----------------------------------------------------------
        421  recursive calls
         46  db block gets
        583  consistent gets
          0  physical reads
       5076  redo SIZE
        637  bytes sent via SQL*Net TO client
        524  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

At first run V$SQL table is empty (!!) and a SQL Plan Baseline is created:

SQL> SET lines 150
SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4fe026eff AUTO-CAPTURE   YES YES NO

If you execute the query on the test table a second time you finally get in V$SQL:

SQL> SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE
------------- ------------ --------------- - - - - ------------------------------
0qv83mgpj3g7z            0      3254990620 N N Y N SQL_PLAN_fv9ffqfgg4ya4fe026eff

With below explain plan, as expected Oracle is doing a FTS for missing histogram on id column:

SQL> SET pages 200
SQL> col PLAN_TABLE_OUTPUT FOR a100
SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_eda5ceb39ef27944','SQL_PLAN_fv9ffqfgg4ya4fe026eff','ALL')) t;
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_eda5ceb39ef27944
SQL text: SELECT /* Yannick */ * FROM test1 WHERE id=1
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
PLAN name: SQL_PLAN_fv9ffqfgg4ya4fe026eff         PLAN id: 4261572351
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
PLAN hash VALUE: 4122059633
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 25000 |  1293K|   111   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST1 | 25000 |  1293K|   111   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
 
34 ROWS selected.

Next step is to generate an histogram on id column and invalidate at same time the plan that is in library cache to force Oracle to hard re-parse it. I thought that simply re-gathering statistics would be enough but I was wrong as with default value the invalidation is really erratic and may take some time so either you use no_invalidate parameter of DBMS_STATS.GATHER_TABLE_STATS procedure (you can again use AUTO for histograms as Oracle learn from select that have been executed) or, thanks to 11g, with DBMS_SHARED_POOL.PURGE procedure:

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE, no_invalidate=>FALSE);
 
SQL> SELECT address,hash_value
     FROM v$sql
     WHERE sql_id='0qv83mgpj3g7z';
 
ADDRESS          HASH_VALUE
---------------- ----------
0000000092E715E8 3943808255
 
SQL> EXEC dbms_shared_pool.purge('0000000092E715E8, 3943808255','c');
 
PL/SQL PROCEDURE successfully completed.

When you execute the test query it empty V$SQL (execute a second time the test query to fill it) and creates a new SQL Plan Baseline:

SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
     FROM dba_sql_plan_baselines
     WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4467428db AUTO-CAPTURE   YES NO  NO
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4fe026eff AUTO-CAPTURE   YES YES NO

With the expected explain plan:

SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_eda5ceb39ef27944','SQL_PLAN_fv9ffqfgg4ya4467428db','ALL')) t;
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_eda5ceb39ef27944
SQL text: SELECT /* Yannick */ * FROM test1 WHERE id=1
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
PLAN name: SQL_PLAN_fv9ffqfgg4ya4467428db         PLAN id: 1182017755
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
PLAN hash VALUE: 3254990620
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    53 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    53 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
   2 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
   2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
 
37 ROWS selected.

But this plan needs to be evolved and approved to be considered by optimizer (need to be 50% faster). What Oracle is really doing to evolve a plan is a mystery and even more in case of DML statements:

SQL> variable report clob
SQL> EXEC :report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE('SYS_SQL_eda5ceb39ef27944','SQL_PLAN_fv9ffqfgg4ya4467428db');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET LONG 999999
SQL> print report
 
REPORT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
                        Evolve SQL PLAN Baseline Report
-------------------------------------------------------------------------------
 
Inputs:
-------
  SQL_HANDLE = SYS_SQL_eda5ceb39ef27944
  PLAN_NAME  = SQL_PLAN_fv9ffqfgg4ya4467428db
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
 
PLAN: SQL_PLAN_fv9ffqfgg4ya4467428db
------------------------------------
  PLAN was verified: TIME used .37 seconds.
  PLAN passed performance criterion: 146.89 times better than baseline PLAN.
  PLAN was changed TO an accepted PLAN.
 
                            Baseline PLAN      Test PLAN       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  ROWS Processed:                       1              1
  Elapsed TIME(ms):                 3.011           .039             77.21
  CPU TIME(ms):                     2.999           .111             27.02
  Buffer Gets:                        442              3            147.33
  Physical Read Requests:               0              0
  Physical WRITE Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical WRITE Bytes:                 0              0
  Executions:                           1              1
 
-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
NUMBER OF plans verified: 1
NUMBER OF plans accepted: 1

Then if you execute your test query (two times ?) you will finally get:

SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4467428db AUTO-CAPTURE   YES YES NO
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4fe026eff AUTO-CAPTURE   YES YES NO
 
SQL> SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE
------------- ------------ --------------- - - - - ------------------------------
0qv83mgpj3g7z            0      3254990620 N N Y N SQL_PLAN_fv9ffqfgg4ya4467428db

Remark:
I could have done the opposite and have histogram on id column generated at first DBMS_STATS.GATHER_TABLE_STATS command. And if by mistake good statistics would have been deleted and explain plan aged out then we would have seen main objective of SQL Plan Management i.e. the good plan would have been kept (Index range scan). The one doing a FTS would be ready to be evolved but would not be accepted as less efficient. So in this situation nothing to do and even if you forget to evolve the pending plans in SQL Plan baseline then no harm but I wanted to show that the opposite can occur and sometimes evolving pending plans in time is a must !

Testing with Adaptive Cursor Sharing (ACS)

Use below statement to directly gather histogram on id column:

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);

So if I start by selecting in my table with a value triggering a FTS on my test table:

SQL> SET lines 130
SQL> SET autotrace traceonly STATISTICS
SQL> variable id NUMBER;
SQL> EXEC :id:=50000;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
49999 ROWS selected.
 
 
STATISTICS
----------------------------------------------------------
        357  recursive calls
         42  db block gets
       3845  consistent gets
         25  physical reads
       5220  redo SIZE
     860665  bytes sent via SQL*Net TO client
      37186  bytes received via SQL*Net FROM client
       3335  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
      49999  ROWS processed

Let’s check in dictionary tables:

SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_0f5b837add235ab6       SQL_PLAN_0yqw3gbfk6qpqfe026eff AUTO-CAPTURE   YES YES NO
 
SQL> SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
no ROWS selected

Noting in V$SQL (!!) and an accepted SQL Plan Baselines has been created…

If I execute a second time my query on my test table I can now find a row in V$SQL table (note the SQL Plan Baseline attached to our sql_id):

SQL> SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE
------------- ------------ --------------- - - - - ------------------------------
5vywrtthcud8x            0      4122059633 Y N Y N SQL_PLAN_0yqw3gbfk6qpqfe026eff

Now let’s execute our test query with a value where we would expect a index range scan (execute it two times to have Oracle generating a new plan):

SQL> EXEC :id:=1;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        443  consistent gets
          0  physical reads
          0  redo SIZE
        637  bytes sent via SQL*Net TO client
        523  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

We notice that V$SQL still contains one row, ACS completely bypassed and SQL Plan Baselines contains two rows with two different plan but only one is accepted:

SQL> SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE
------------- ------------ --------------- - - - - ------------------------------
5vywrtthcud8x            0      4122059633 Y N Y N SQL_PLAN_0yqw3gbfk6qpqfe026eff
 
SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_0f5b837add235ab6       SQL_PLAN_0yqw3gbfk6qpq467428db AUTO-CAPTURE   YES NO  NO
SYS_SQL_0f5b837add235ab6       SQL_PLAN_0yqw3gbfk6qpqfe026eff AUTO-CAPTURE   YES YES NO

Let’s have a look to explain plan to understand. First on the cursor (usage of SQL Plan baseline is clearly stated):

SQL> SET pages 200
SQL> SET lines 130
SQL> col PLAN_TABLE_OUTPUT FOR a100
SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5vywrtthcud8x',NULL,'ALL')) t;
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5vywrtthcud8x, child NUMBER 0
-------------------------------------
SELECT /* Yannick */ * FROM test1 WHERE id=:id
 
PLAN hash VALUE: 4122059633
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   111 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST1 | 49999 |  2587K|   111   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("ID"=:ID)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
 
Note
-----
   - SQL PLAN baseline SQL_PLAN_0yqw3gbfk6qpqfe026eff used FOR this statement
 
 
32 ROWS selected.

Then on the different plans of the SQL Plan Baseline:

SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_0f5b837add235ab6',NULL,'ALL')) t;
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_0f5b837add235ab6
SQL text: SELECT /* Yannick */ * FROM test1 WHERE id=:id
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
PLAN name: SQL_PLAN_0yqw3gbfk6qpq467428db         PLAN id: 1182017755
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
PLAN hash VALUE: 3254990620
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 25000 |  1293K|   251   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        | 25000 |  1293K|   251   (1)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX_ID | 25000 |       |    49   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
   2 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=TO_NUMBER(:ID))
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
   2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
 
--------------------------------------------------------------------------------
PLAN name: SQL_PLAN_0yqw3gbfk6qpqfe026eff         PLAN id: 4261572351
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
PLAN hash VALUE: 4122059633
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 25000 |  1293K|   112   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST1 | 25000 |  1293K|   112   (2)| 00:00:02 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("ID"=TO_NUMBER(:ID))
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
 
66 ROWS selected.

So clearly the one performing an index range scan is not used (Accepted: NO). Even if we have the ACS table filled with optimizer statistics information:

SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000925C3C60 1624061213 5vywrtthcud8x            0          0          1
00000000925C3C60 1624061213 5vywrtthcud8x            0          1          1
00000000925C3C60 1624061213 5vywrtthcud8x            0          2          0
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
00000000925C3C60 1624061213 5vywrtthcud8x            0          1293584543 Y          1          49999        3776          0
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
no ROWS selected

Let’s try to evolve it:

SQL> var output clob;
SQL> EXEC :output:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE('SYS_SQL_0f5b837add235ab6','SQL_PLAN_0yqw3gbfk6qpq467428db');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET LONG 999999
SQL> print :output
 
OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
                        Evolve SQL PLAN Baseline Report
-------------------------------------------------------------------------------
 
Inputs:
-------
  SQL_HANDLE = SYS_SQL_0f5b837add235ab6
  PLAN_NAME  = SQL_PLAN_0yqw3gbfk6qpq467428db
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
 
PLAN: SQL_PLAN_0yqw3gbfk6qpq467428db
------------------------------------
  PLAN was verified: TIME used .2 seconds.
  PLAN passed performance criterion: 147.38 times better than baseline PLAN.
  PLAN was changed TO an accepted PLAN.
 
                            Baseline PLAN      Test PLAN       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  ROWS Processed:                       1              1
  Elapsed TIME(ms):                 1.393           .023             60.57
  CPU TIME(ms):                     1.333              0
  Buffer Gets:                        442              3            147.33
  Physical Read Requests:               0              0
  Physical WRITE Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical WRITE Bytes:                 0              0
  Executions:                           1              1
 
-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
NUMBER OF plans verified: 1
NUMBER OF plans accepted: 1

We can see that the evolved plan has been now accepted (V$SQL again empty):

SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_0f5b837add235ab6       SQL_PLAN_0yqw3gbfk6qpq467428db AUTO-CAPTURE   YES YES NO
SYS_SQL_0f5b837add235ab6       SQL_PLAN_0yqw3gbfk6qpqfe026eff AUTO-CAPTURE   YES YES NO

If we now again execute our test query with id variable equal to 1 and 50000 then SPM and ACS work in concert and all is back to expected situation:

SQL> SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';   2    3
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE
------------- ------------ --------------- - - - - ------------------------------
5vywrtthcud8x            0      3254990620 Y N Y N SQL_PLAN_0yqw3gbfk6qpq467428db
5vywrtthcud8x            1      4122059633 Y Y Y N SQL_PLAN_0yqw3gbfk6qpqfe026eff
 
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000925C3C60 1624061213 5vywrtthcud8x            1          0          0
00000000925C3C60 1624061213 5vywrtthcud8x            1          1          1
00000000925C3C60 1624061213 5vywrtthcud8x            1          2          0
00000000925C3C60 1624061213 5vywrtthcud8x            0          0          1
00000000925C3C60 1624061213 5vywrtthcud8x            0          1          1
00000000925C3C60 1624061213 5vywrtthcud8x            0          2          0
 
6 ROWS selected.
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
00000000925C3C60 1624061213 5vywrtthcud8x            1          1293584543 Y          1          49999        3776          0
00000000925C3C60 1624061213 5vywrtthcud8x            0          2342552567 Y          1              2           4          0
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000925C3C60 1624061213 5vywrtthcud8x            1 =ID                                               0 0.899973   1.099967

Then the sentence in Oracle training material is quite strange as they suggest to not set optimizer_capture_sql_plan_baselines to true and to load cursor cache in SQL Plan Baselines using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

Testing with SQL Profile

For this testing with SQL Profile ensure you have histogram on id column and SQL Plan Management automatic capture is disabled. I will use the below query, that is performing a FTS (due to the hint) while an index range scan would have been expected:

SQL> SET autotrace traceonly STATISTICS
SQL> SELECT /* Yannick */ /*+ no_index (test1) */ * FROM test1 WHERE id=1;
 
 
STATISTICS
----------------------------------------------------------
        333  recursive calls
          0  db block gets
        492  consistent gets
        446  physical reads
          0  redo SIZE
        637  bytes sent via SQL*Net TO client
        524  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

We will then manually load it in SQL Plan Baseline (unless you do not see anything), execute SQL Tuning Advisor (STA) and accept the generated SQL Profile:

SQL> SELECT sql_id, address, hash_value, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline, sql_profile
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        ADDRESS          HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE              SQL_PROFILE
------------- ---------------- ---------- ------------ --------------- - - - - ------------------------------ ----------------------------------------------------------------
6zbcy2xrvxxg9 000000009269FCA8 1874785769            0      4122059633 N N Y N
 
SQL> var spm NUMBER;
SQL> EXEC :spm:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '6zbcy2xrvxxg9');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> print :spm
 
       SPM
----------
         1
 
SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_279203b5a4a8ca53       SQL_PLAN_2g4h3qqkajkkmfe026eff MANUAL-LOAD    YES YES NO

Once the SQL Plan Baseline has been manually loaded let’s create the STA task and execute it:

SQL> DECLARE
       stmt_task VARCHAR2(64);
     BEGIN
       stmt_task:=dbms_sqltune.create_tuning_task(sql_id => '6zbcy2xrvxxg9', plan_hash_value => '4122059633', time_limit => 3600, task_name => 'Tune_6zbcy2xrvxxg9', description => 'Task to tune 6zbcy2xrvxxg9 sql_id');
     END;
     /
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXECUTE dbms_sqltune.execute_tuning_task('Tune_6zbcy2xrvxxg9');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SET lines 200
SQL> col description FOR a40
SQL> SELECT task_name, description, advisor_name, execution_start, execution_end, status
     FROM dba_advisor_tasks
     WHERE task_name='Tune_6zbcy2xrvxxg9'
     ORDER BY task_id DESC;
 
TASK_NAME                      DESCRIPTION                              ADVISOR_NAME                   EXECUTION_START      EXECUTION_END        STATUS
------------------------------ ---------------------------------------- ------------------------------ -------------------- -------------------- -----------
Tune_6zbcy2xrvxxg9             Task TO tune 6zbcy2xrvxxg9 sql_id        SQL Tuning Advisor             22-dec-2011 12:26:04 22-dec-2011 12:26:06 COMPLETED

Providing the expected result (ouf):

SQL> SET LONG 999999999
SQL> SET pages 1000
SQL> SET longchunksize 20000
SQL> SELECT dbms_sqltune.report_tuning_task('Tune_6zbcy2xrvxxg9', 'TEXT', 'ALL') FROM dual;
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_6ZBCY2XRVXXG9','TEXT','ALL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tune_6zbcy2xrvxxg9
Tuning Task Owner  : SYS
Tuning Task ID     : 2411
Workload TYPE      : Single SQL Statement
Execution COUNT    : 1
CURRENT Execution  : EXEC_2429
Execution TYPE     : TUNE SQL
Scope              : COMPREHENSIVE
TIME Limit(seconds): 3600
Completion Status  : COMPLETED
Started AT         : 12/22/2011 12:26:04
Completed AT       : 12/22/2011 12:26:06
 
-------------------------------------------------------------------------------
SCHEMA Name: YJAQUIER
SQL ID     : 6zbcy2xrvxxg9
SQL Text   : SELECT /* Yannick */ /*+ no_index (test1) */ * FROM test1 WHERE
             id=1
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- SQL PROFILE Finding (see EXPLAIN plans section below)
--------------------------------------------------------
  A potentially better execution PLAN was found FOR this statement.
 
  Recommendation (estimated benefit: 99.49%)
  ------------------------------------------
  - Consider accepting the recommended SQL PROFILE. A SQL PLAN baseline
    corresponding TO the PLAN WITH the SQL PROFILE will also be created.
    EXECUTE dbms_sqltune.accept_sql_profile(task_name =>
            'Tune_6zbcy2xrvxxg9', task_owner => 'SYS', REPLACE => TRUE);
 
  Validation results
  ------------------
  The SQL PROFILE was tested BY executing both its PLAN AND the original PLAN
  AND measuring their respective execution STATISTICS. A PLAN may have been
  only partially executed IF the other could be run TO completion IN less TIME.
 
                           Original PLAN  WITH SQL PROFILE  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed TIME(us):                2602                36      98.61 %
  CPU TIME(us):                    2599               100      96.15 %
  USER I/O TIME(us):                  0                 0
  Buffer Gets:                      397                 2      99.49 %
  Physical Read Requests:             0                 0
  Physical WRITE Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical WRITE Bytes:               0                 0
  ROWS Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1
 
  Notes
  -----
  1. The original PLAN was FIRST executed TO warm the buffer cache.
  2. STATISTICS FOR original PLAN were averaged over next 9 executions.
  3. The SQL PROFILE PLAN was FIRST executed TO warm the buffer cache.
  4. STATISTICS FOR the SQL PROFILE PLAN were averaged over next 9 executions.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
PLAN hash VALUE: 4122059633
 
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    53 |   111   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |    53 |   111   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
 
Note
-----
   - SQL PLAN baseline "SQL_PLAN_2g4h3qqkajkkmfe026eff" used FOR this statement
 
2- Original WITH Adjusted COST
------------------------------
PLAN hash VALUE: 4122059633
 
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    53 |   111   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |    53 |   111   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
 
Note
-----
   - SQL PLAN baseline "SQL_PLAN_2g4h3qqkajkkmfe026eff" used FOR this statement
 
3- USING SQL PROFILE
--------------------
PLAN hash VALUE: 3254990620
 
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    53 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    53 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
   2 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
   2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
 
-------------------------------------------------------------------------------

If we generate the recommendation script:

SQL> SELECT dbms_sqltune.script_tuning_task('Tune_6zbcy2xrvxxg9', 'ALL') FROM dual;
 
DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TUNE_6ZBCY2XRVXXG9','ALL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.                             --
--                                                             --
-- NOTE: this script may need to be edited for your system     --
--       (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'Tune_6zbcy2xrvxxg9', REPLACE => TRUE);

So let’s activate it it and clean our tuning task:

SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'Tune_6zbcy2xrvxxg9', REPLACE => TRUE, description => 'Tuning test for sql_id 6zbcy2xrvxxg9');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXECUTE dbms_sqltune.drop_tuning_task('Tune_6zbcy2xrvxxg9');
 
PL/SQL PROCEDURE successfully completed.

Let’s confirm the SQL profile is active and check what’s in V$SQL (so far nothing !):

SQL> SELECT name, created, description, status FROM dba_sql_profiles;
 
NAME                           CREATED                                                                     DESCRIPTION                              STATUS
------------------------------ --------------------------------------------------------------------------- ---------------------------------------- --------
SYS_SQLPROF_01346587150f0001   22-DEC-11 12.27.58.000000 PM                                                Tuning test FOR sql_id 6zbcy2xrvxxg9     ENABLED
 
SQL> SELECT sql_id, address, hash_value, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline, sql_profile
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        ADDRESS          HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE              SQL_PROFILE
------------- ---------------- ---------- ------------ --------------- - - - - ------------------------------ ----------------------------------------------------------------
6zbcy2xrvxxg9 000000009269FCA8 1874785769            0      4122059633 N N Y N

But we can see than accepting the SQL Profle has created an accepted SQL Plan Baseline:

SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SYS_SQL_279203b5a4a8ca53       SQL_PLAN_2g4h3qqkajkkm467428db MANUAL-SQLTUNE YES YES NO
SYS_SQL_279203b5a4a8ca53       SQL_PLAN_2g4h3qqkajkkmfe026eff MANUAL-LOAD    YES YES NO

Then, stragnely, you need to execute three times the test query to see somehting in V$SQL (!!). First run is emptying V$SQL and then two runs are need to fill it:

SQL> SELECT sql_id, address, hash_value, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable, is_obsolete, sql_plan_baseline, sql_profile
FROM v$sql
WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        ADDRESS          HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE              SQL_PROFILE
------------- ---------------- ---------- ------------ --------------- - - - - ------------------------------ ----------------------------------------------------------------
6zbcy2xrvxxg9 000000009269FCA8 1874785769            0      3254990620 N N Y N SQL_PLAN_2g4h3qqkajkkm467428db SYS_SQLPROF_01346587150f0001

Let’s confirm the SQL Plan Baseline used is the expected plan:

SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_279203b5a4a8ca53','SQL_PLAN_2g4h3qqkajkkm467428db','ALL')) t;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_279203b5a4a8ca53
SQL text: SELECT /* Yannick */ /*+ no_index (test1) */ * FROM test1 WHERE id=1
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
PLAN name: SQL_PLAN_2g4h3qqkajkkm467428db         PLAN id: 1182017755
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-SQLTUNE
--------------------------------------------------------------------------------
 
PLAN hash VALUE: 3254990620
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    53 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    53 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
   2 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
   2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
 
Note
-----
   - SQL PROFILE "SYS_SQLPROF_01346587150f0001" used FOR this statement
 
41 ROWS selected.

And the cursor, kind of belt and brasses, is using both SQL Profile and SQL Plan Baseline. Oracle official documentation states that they work in concert and share information between each others:

SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6zbcy2xrvxxg9',NULL,'ALL')) t;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6zbcy2xrvxxg9, child NUMBER 0
-------------------------------------
SELECT /* Yannick */ /*+ no_index (test1) */ * FROM test1 WHERE id=1
 
PLAN hash VALUE: 3254990620
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    53 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST1@SEL$1
   2 - SEL$1 / TEST1@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=1)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,200]
   2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
 
Note
-----
   - SQL PROFILE SYS_SQLPROF_01346587150f0001 used FOR this statement
   - SQL PLAN baseline SQL_PLAN_2g4h3qqkajkkm467428db used FOR this statement
 
 
36 ROWS selected.

Link between sql_id and SQL Plan Baselines

In all my example I track my test query using a comment I put in SQL code, which may not be possible in real life. If you look into DBA_SQL_PLAN_BASELINES you will not see any sql_id, address or hash_value columns so then how to make the link without using sql_text columns which is not really user friendly ?

They are posts from Tanel Poder, Marcin Przepiorowski and Kerry Osborne on the subject (see references) and the easiest way I have seen is using the function proposed by Kerry Osborne:

CREATE OR REPLACE FUNCTION baseline_info (p_plan_name VARCHAR2, p_info_type VARCHAR2)
RETURN VARCHAR2
IS
  ----------------------------------------------------------------------------------------
--
-- File name:   create_baseline_info.sql
--
-- Purpose:     Return SQL_ID or PLAN_HASH_VALUE associated with a SQL Plan Management Baseline.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts creates a function called basline_info. The function returns a
--              SQL_ID or a PLAN_HASH_VALUE for a baseline It takes as input a PLAN_NAME
--              for a Baseline and a text field that specifies what info to return (at
--              this point the valid values are SQL_ID or PLAN_HASH_VALUE). 
--
-- Description:
--
--              This function is based on work done by Marcin Przepiorowski published 
--              here: http://oracleprof.blogspot.com/2011/07/how-to-find-sqlid-and-planhashvalue-in.html
--              Marcin's work was based on research by Tanel Poder.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
  v_sqlid VARCHAR2(13);
  v_num NUMBER;
BEGIN
  FOR a IN (SELECT sql_handle, plan_name, TRIM(SUBSTR(g.PLAN_TABLE_OUTPUT,INSTR(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
              FROM (SELECT t.*, c.sql_handle, c.plan_name, c.sql_text FROM dba_sql_plan_baselines c, TABLE(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
              WHERE c.plan_name = p_plan_name) g
              WHERE PLAN_TABLE_OUTPUT LIKE 'Plan hash value%') LOOP
    v_num := TO_NUMBER(sys.UTL_RAW.REVERSE(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || CHR(0),'AL32UTF8'), typ => 2),9,4)) || 
             sys.UTL_RAW.REVERSE(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || CHR(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32)) LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    IF UPPER(p_info_type) = 'SQL_ID' THEN
       RETURN LPAD(v_sqlid,13,'0') ;
    ELSIF UPPER(p_info_type) = 'PLAN_HASH_VALUE' THEN
       RETURN RPAD(a.plan_hash_value,15);
    ELSE
       RETURN NULL;
    END IF;
  END LOOP;
  RETURN 'UNKOWN';
EXCEPTION WHEN OTHERS THEN
  RETURN 'UNKOWN';
END;
/

Usage example:

SQL> SET lines 150
SQL> col sql_id FOR a20
SQL> col PLAN_HASH_VALUE FOR a20
SQL> SELECT sql_handle, plan_name, baseline_info(plan_name, 'sql_id') sql_id,baseline_info(plan_name, 'plan_hash_value') plan_hash_value
     FROM dba_sql_plan_baselines a
     WHERE sql_handle='SYS_SQL_eda5ceb39ef27944';
 
SQL_HANDLE                     PLAN_NAME                      SQL_ID               PLAN_HASH_VALUE
------------------------------ ------------------------------ -------------------- --------------------
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4467428db 0qv83mgpj3g7z        3254990620
SYS_SQL_eda5ceb39ef27944       SQL_PLAN_fv9ffqfgg4ya4fe026eff 0qv83mgpj3g7z        4122059633

Conclusion

So clearly when using SPM having a close look to SQL Plan Baselines and evolving plan is a must. Unless you always stick to initially loaded SQL plan, which is, in reality the main objective of SPM: plan stability. But Stability in our ACS example is not synonym of optimal plan in every situation.

So the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE() procedure (yes with no argument) to act on all SQL handles that have unaccepted plans must be schedule to run in maintenance window once you have decided to use SPM.

If for a particular SQL statement you even do not want any change in the existing plan, means avoid another one be evolved and accepted you can used DBMS_SPM.ALTER_SQL_PLAN_BASELINE to modiy the FIXED value of your plan: a fixed plan has preference over and accepted plan. Please note that same function even allow you to accept a plan without any check, which is quite risky.

To go further you can even associate plan and SQL statement using SQL Plan Management. In other words you can associate SQL plan of a tuned SQL with hints to the original SQL statement with no hints (using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and explicitly specifying an existing sql_handle).

To delete all SQL Plan Baselines you can use below PL/SQL code:

DECLARE
  xx PLS_INTEGER;
  CURSOR cursor1 IS SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines;
  item1 cursor1%ROWTYPE;
BEGIN
  OPEN cursor1;
  LOOP
    FETCH cursor1 INTO item1;
    EXIT WHEN cursor1%notfound;
    xx:=dbms_spm.drop_sql_plan_baseline(sql_handle => item1.sql_handle);
  END LOOP;
  dbms_output.put_line('Number of deleted sql_handle: ' || cursor1%rowcount);
  CLOSE cursor1;
END;
/

Even if I used command lines in this post you can do everything with Grid Control:

spm1
spm1

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

2 thoughts on “SQL Plan Management”

  1. Pingback: Automatic SQL Tuning Sets (ASTS) hidden feature hands-on

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>