Dion Cho – Oracle Performance Storyteller

We are natural born scientists

How to tune SQL statement without changing text – Plan baseline version

leave a comment »

One of the common requests that embarasses DBAs or developers is as following

I need to tune the SQL statement. But I can’t modify the SQL text because it’s embedded in the software

Quite familiar, huh?

Many people had shared their techniques, and now we have a couple of proven solutions.

  • Controlling the statistics – gathering or setting
  • Editing or switching the stored outlines
  • Importing the SQL profile (10g)

Outline switching and SQL profile importing are very powerful techniqiues and well described here.

http://sites.google.com/site/ukja/books-cbo-in-depth/chapter7/06–stored-outlines-vs-sql-profile-sql

Anyway, this blog post helped me realize that Oracle 11′s proud-new-feature SQL plan management(a.k.a SPM) also has a similiar mechanism – manually changing plan without chaing SQL text.

Following simple test case would help you to understand what I mean.


– Oracle version is 11.1.0.6

UKJA@ukja116> select * from v$version;

BANNER
------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.04

– Create tables and indexes

UKJA@ukja116> create table t1(c1, c2)
  2  as
  3  select level, level from dual connect by level <= 100000
  4  ;

Table created.

Elapsed: 00:00:00.45
UKJA@ukja116>
UKJA@ukja116> create index t1_n1 on t1(c1)
  2  ;

Index created.

Elapsed: 00:00:00.21
UKJA@ukja116>
UKJA@ukja116> @gather t1
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.70
UKJA@ukja116>

– Capture the plan baseline

– Note the ugly FULL hint

UKJA@ukja116> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja116>
UKJA@ukja116> select /*+ full(t1) */ * from t1 where c1 = 1;

        C1         C2                                                                              
---------- ----------                                                                              
         1          1                                                                               

Elapsed: 00:00:00.04
UKJA@ukja116> select /*+ full(t1) */ * from t1 where c1 = 1;

        C1         C2                                                                              
---------- ----------                                                                              
         1          1                                                                               

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116> alter session set optimizer_capture_sql_plan_baselines = false;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja116>

– We have SQL plan baseline captured and accepted

UKJA@ukja116> col sql_handle new_value v_sql_handle
UKJA@ukja116>
UKJA@ukja116> select sql_handle from dba_sql_plan_baselines
  2  where sql_text like 'select /*+ full(t1) */ * from t1 where c1 = 1%'
  3  ;

SQL_HANDLE                                                                                         
------------------------------------------------------------------------------------------         
SYS_SQL_8bab62f0bc1db3bc                                                                            

Elapsed: 00:00:00.00
UKJA@ukja116>
UKJA@ukja116>
UKJA@ukja116> set serveroutput on
UKJA@ukja116> exec print_table('select * from dba_sql_plan_baselines where sql_handle = ''&v_sql_handle''');
SIGNATURE                     : 10064246578377241532                                               
SQL_HANDLE                    : SYS_SQL_8bab62f0bc1db3bc                                           
SQL_TEXT                      : select /*+ full(t1) */ * from t1 where c1 = 1                      
PLAN_NAME                     : SYS_SQL_PLAN_bc1db3bcdbd90e8e                                      
CREATOR                       : UKJA                                                               
ORIGIN                        : AUTO-CAPTURE                                                       
PARSING_SCHEMA_NAME           : UKJA                                                               
DESCRIPTION                   :                                                                    
VERSION                       : 11.1.0.6.0                                                         
CREATED                       : 03-FEB-09 03.34.12.000000 PM                                       
LAST_MODIFIED                 : 03-FEB-09 03.34.12.000000 PM                                       
LAST_EXECUTED                 :                                                                    
LAST_VERIFIED                 :                                                                    
ENABLED                       : YES                                                                
ACCEPTED                      : YES                                                                
FIXED                         : NO                                                                 
AUTOPURGE                     : YES                                                                
OPTIMIZER_COST                : 58                                                                 
MODULE                        : SQL*Plus                                                           
ACTION                        :                                                                    
EXECUTIONS                    : 0                                                                  
ELAPSED_TIME                  : 0                                                                  
CPU_TIME                      : 0                                                                  
BUFFER_GETS                   : 0                                                                  
DISK_READS                    : 0                                                                  
DIRECT_WRITES                 : 0                                                                  
ROWS_PROCESSED                : 0                                                                  
FETCHES                       : 0                                                                  
END_OF_FETCH_COUNT            : 0                                                                  
-----------------                                                                                   

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
UKJA@ukja116> set serveroutput off
UKJA@ukja116>
UKJA@ukja116>

-- Now I want to the change the original SQL to following one

UKJA@ukja116> col sql_id new_value v_sql_id
UKJA@ukja116> col plan_hash_value new_value v_plan_hash_value
UKJA@ukja116>
UKJA@ukja116> select /*+ index(t1) */ * from t1 where c1 = 1
  2  ;

        C1         C2
---------- ----------
         1          1

Elapsed: 00:00:00.01


– But How? How can we change the “select /+ full(t1) */ …” sql to scan index not table?

– dbms_spm.load_plans_from_cursor_cache is the key

UKJA@ukja116> select sql_id, plan_hash_value from v$sql
  2  where sql_text like 'select /*+ index(t1) */ * from t1 where c1 = 1%'
  3  ;

SQL_ID                                  PLAN_HASH_VALUE                                            
--------------------------------------- ---------------                                            
ftm2rdbpvw96f                                1420382924                                             

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116>
UKJA@ukja116> var ret number;
UKJA@ukja116>

– We can load the plan of “select /*+ index(t1) */ …” statement

– to the plan baseline of the “select /*+ full(t1) */ …” statement.

– This is the key trick

UKJA@ukja116> exec :ret := dbms_spm.load_plans_from_cursor_cache(-
    sql_id=>'&v_sql_id', -
    plan_hash_value=>&v_plan_hash_value, -
    sql_handle=>'&v_sql_handle');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116>

– Now, Oracle has 2 plan baseline

UKJA@ukja116> select sql_handle, sql_text from dba_sql_plan_baselines
  2  where sql_text like 'select /*+ full(t1) */ * from t1 where c1 = 1%'
  3  ;

SQL_HANDLE                                                                                         
------------------------------------------------------------------------------------------         
SQL_TEXT                                                                                           
--------------------------------------------------------------------------------                   
SYS_SQL_8bab62f0bc1db3bc                                                                           
select /*+ full(t1) */ * from t1 where c1 = 1                                                       

SYS_SQL_8bab62f0bc1db3bc                                                                           
select /*+ full(t1) */ * from t1 where c1 = 1                                                       

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116>

– We can display the registered plans for plan history using dbms_xplan.display_sql_plan_baseline function

UKJA@ukja116> select * from table(dbms_xplan.display_sql_plan_baseline('&v_sql_handle'));
old   1: select * from table(dbms_xplan.display_sql_plan_baseline('&v_sql_handle'))
new   1: select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_8bab62f0bc1db3bc'))

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------                   
SQL handle: SYS_SQL_8bab62f0bc1db3bc                                                               
SQL text: select /*+ full(t1) */ * from t1 where c1 = 1                                            
--------------------------------------------------------------------------------                    

--------------------------------------------------------------------------------                   
Plan name: SYS_SQL_PLAN_bc1db3bc750635c2                                                           
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD                              
--------------------------------------------------------------------------------                    

Plan hash value: 1420382924                                                                         

-------------------------------------------------------------------------------------              
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |              
-------------------------------------------------------------------------------------              
|   0 | SELECT STATEMENT            |       |     1 |     9 |     2   (0)| 00:00:01 |              
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     9 |     2   (0)| 00:00:01 |              
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |              
-------------------------------------------------------------------------------------               

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

   2 - access("C1"=1)                                                                               

--------------------------------------------------------------------------------                   
Plan name: SYS_SQL_PLAN_bc1db3bcdbd90e8e                                                           
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE                             
--------------------------------------------------------------------------------                    

Plan hash value: 3617692013                                                                         

--------------------------------------------------------------------------                         
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
--------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |      |     1 |     9 |    59   (6)| 00:00:01 |                         
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |    59   (6)| 00:00:01 |                         
--------------------------------------------------------------------------                          

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

   1 - filter("C1"=1)                                                                               

44 rows selected.

Elapsed: 00:00:00.15
UKJA@ukja116>
UKJA@ukja116>

– How about the plan? With the plan baseline off, we have following plan.

UKJA@ukja116> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1
  3  ;

Explained.

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116> @plan
UKJA@ukja116> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                         

--------------------------------------------------------------------------                         
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
--------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |      |     1 |     9 |    59   (6)| 00:00:01 |                         
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |    59   (6)| 00:00:01 |                         
--------------------------------------------------------------------------                          

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

   1 - filter("C1"=1)                                                                               

13 rows selected.

Elapsed: 00:00:00.04
UKJA@ukja116>
UKJA@ukja116>

– What if sql plan baseline is used?

UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = true;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja116>
UKJA@ukja116> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1
  3  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja116>
UKJA@ukja116>
UKJA@ukja116> @plan
UKJA@ukja116> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                         

--------------------------------------------------------------------------                         
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
--------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |      |     1 |     9 |    59   (6)| 00:00:01 |                         
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |    59   (6)| 00:00:01 |                         
--------------------------------------------------------------------------                          

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

   1 - filter("C1"=1)                                                                               

Note                                                                                               
-----                                                                                              
   - SQL plan baseline "SYS_SQL_PLAN_bc1db3bcdbd90e8e" used for this statement                      

17 rows selected.

Elapsed: 00:00:00.06
UKJA@ukja116>

UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = false;

Session altered.

Elapsed: 00:00:00.00

– Hm… the original plan is still chosen even if there is a better plan with lower cost

– Now I’m going to disable the original plan using dbms_spm.alter_sql_plan_baseline

UKJA@ukja116>
UKJA@ukja116> -- disable the current plan
UKJA@ukja116> exec :ret := dbms_spm.alter_sql_plan_baseline('&v_sql_handle', '&org_plan_name', 'enabled', 'NO');
Enter value for org_plan_name: SYS_SQL_PLAN_bc1db3bcdbd90e8e

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

– Now, new plan is chosen.

UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = true;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja116>
UKJA@ukja116> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1
  3  ;

Explained.

Elapsed: 00:00:00.09
UKJA@ukja116>
UKJA@ukja116> @plan
UKJA@ukja116> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 1420382924                                                                         

-------------------------------------------------------------------------------------              
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |              
-------------------------------------------------------------------------------------              
|   0 | SELECT STATEMENT            |       |     1 |     9 |     2   (0)| 00:00:01 |              
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     9 |     2   (0)| 00:00:01 |              
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |              
-------------------------------------------------------------------------------------               

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

   2 - access("C1"=1)                                                                               

Note                                                                                               
-----                                                                                              
   - SQL plan baseline "SYS_SQL_PLAN_bc1db3bc750635c2" used for this statement                      

18 rows selected.

Elapsed: 00:00:00.07
UKJA@ukja116>
UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = false;

Session altered.

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116>
UKJA@ukja116> -- drop sql plan baseline
UKJA@ukja116> exec :ret := dbms_spm.drop_sql_plan_baseline('&v_sql_handle');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Above test case cleary shows that manual loading of plan baseline from cursor cache is another powerful techique which enables us to tune the unmodifiable SQL.

I hope that this post could be one of the valuable tips for those who plan to adopt SPM on Oracle 11g database.

About these ads

Written by Dion Cho

February 3, 2009 at 7:09 am

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

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: