Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘dbms_sqltune

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.

Written by Dion Cho

February 3, 2009 at 7:09 am

Follow

Get every new post delivered to your Inbox.

Join 60 other followers