Mohamed Houri’s Oracle Notes

March 9, 2013

SQL Patch and SQL Plan Baseline how do they collaborate

Filed under: Oracle — hourim @ 8:39 pm

In my continuing process of investigating SQL Patch and SQL Plan Baseline I wanted to know how these two technologies collaborate together. As I did in my preceding blog article I used the demo proposed by the optimizer group. Remember from the previous post that I have used package dbms_sqldiag_internal.i_create_patch in order to inject an index hint into a packaged query and have obtained the following execution plan

SQL>  Select count(*), max(emp1no)
  2  From   (Select *
  3          From   emp1
  4          Where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

SQL>  select * from table(dbms_xplan.display_cursor);

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|    
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 (100)|          
|   1 |  SORT AGGREGATE              |         |     1 |    16 |            |          
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |  1484K|   912   (1)|
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   (2)| 
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

And then I decided to let the optimizer capturing my sql baseline

SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;

Session altered.

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

SQL> select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement
   - SQL plan baseline SQL_PLAN_fynfh13fg894pda1b6c69 used for this statement

My packaged query is now using a SQL patch named “index_patch” which has been captured by a SQL baseline as shown below:

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_fynfh13fg894pda1b6c69'));

---------------------------------------------------------------------------
SQL handle: SYS_SQL_ef51d008dcf42495
SQL text: Select count(*), max(emp1no) From   (Select * From   emp1 Where  deptno= 10)
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan name: SQL_PLAN_fynfh13fg894pda1b6c69         Plan id: 3659230313
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------------

Plan hash value: 20205423
---------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |     8 |   912   
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

What if I disable the SQL patch “index_patch”? Will my query still use the hinted index ?


SQL> begin
  2          dbms_sqldiag.alter_sql_patch( name =>  'index_patch'
  3                                       ,attribute_name => 'STATUS'
  4                                       ,value =>  'DISABLED'
  5                                       );
  6          end;
  7    /

PL/SQL procedure successfully completed.

SQL> select
  2         name
  3        ,status
  4   from
  5     dba_sql_patches
  6    where name = 'index_patch';

NAME                           STATUS
------------------------------ --------
index_patch                    DISABLED

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000
SQL> select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)

Note
-----
   - SQL plan baseline SQL_PLAN_fynfh13fg894pda1b6c69 used for this statement

Wonderful!!! My SQL plan baseline is still using the index despite I have disabled the SQL patch which, don’t forget, is the technology I have used to inject the hint.

Let me now drop this “index_patch” SQL patch

SQL> begin
  2    sys.dbms_sqldiag.drop_sql_patch('index_patch');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> select
  2         name
  3        ,status
  4   from
  5     dba_sql_patches
  6    where name = 'index_patch';

no rows selected

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

SQL> select * from table(dbms_xplan.display_cursor);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP1 | 95000 |   742K|   241   (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=10)

See how the index is not used anymore. Frankly speaking, I don’t understand what disabling a SQL PATCH means!!!

SQL> select
  2        plan_name,
  3        origin,
  4        enabled,
  5        accepted,
  6        fixed
  7      from dba_sql_plan_baselines
  8  where sql_text like '%emp1%';

PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ -------------- --- --- ---
SQL_PLAN_fynfh13fg894pda1b6c69 AUTO-CAPTURE   YES YES NO
SQL_PLAN_fynfh13fg894p443f3a3e AUTO-CAPTURE   YES NO  NO

My SQL Plan baseline is still enabled and accepted but is not reproducible because it has been based on a SQL patch that doesn’t exist anymore

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_fynfh13fg894pda1b6c69'));

---------------------------------------------------------------------------
Plan name: SQL_PLAN_fynfh13fg894pda1b6c69         Plan id: 3659230313
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------------

Plan hash value: 20205423

----------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |     8 |   912
|   1 |  SORT AGGREGATE              |         |     1 |     8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)

So in summary when a SQL baseline uses a SQL patch to produce a best plan then the reproducibility of this SQL baseline depends on this SQL patch exactly as it depends on any other database object like index or table. Disabling the underlying SQL patch has no effect on the reproducibility of the SQL PLAN baseline while dropping the SQL patch will definitely impeach the parent baseline to be selected by the Optimizer

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: