Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘sql profile

Optimizing Unoptimizeable SQL – dbms_advanced_rewrite

with one comment

One of the common requests very difficult to solve.

I have a bad performing query, but I don’t have access to source file. How can I change the execution plan?

Very bad request, but there are people who are in trouble as in this OTN form thread.

The good news is that some smart guys struggled to fight this and now we do have some well known techniques.

The last one – advanced query rewrite – is the topic today. This technique is especially useful under following situations.

  • Oracle 10g+
  • When stored outline and sql profile do not help – they use hints to control the execution plan, but there are cases when hints are useless.
  • Select, not DML
  • With no bind variables

Advanced query rewrite is designed as an assistance to mview query rewrite, but with above conditions met, we can enjoy it’s power with non-mview queries.

Following is a simple demonstration.

I have following query.

create table t1(c1, c2, c3, c4) ...;
create index t1_n1 on t1(c1);

select * from t1 where c1 like '%11%';



I have index on t1.c1, but Oracle can’t use it. What I like to do is convert the original query to this form.

select /*+ leading(x) use_nl(x t1) */ t1.*
from 
  (select /*+ index_ffs(t1) */ rowid as row_id, c1 
    from t1 where c1 like '%11%') x,
  t1
where
  t1.rowid = x.row_id
;



By preprocessing with index fast full scan, I’m trying to avoid the danger of full scan on very large table.

Look how I achieve it using advanced query rewrite.

UKJA@ukja116> create table t1(c1, c2, c3, c4)
UKJA@ukja116> as
UKJA@ukja116> select to_char(level), to_char(level), to_char(level), to_char(level)
UKJA@ukja116> from dual
UKJA@ukja116> connect by level  ;
UKJA@ukja116> 
UKJA@ukja116> create index t1_n1 on t1(c1);
UKJA@ukja116> 
UKJA@ukja116> -- This is current problematic query
UKJA@ukja116> explain plan for
  2  select *
  3  from t1
  4  where c1 like '%11%'; -- Look here! 

Explained.

UKJA@ukja116> 
UKJA@ukja116> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------                                              
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                              
--------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT  |      | 50000 |  1318K|  1182   (3)| 00:00:06 |                                              
|*  1 |  TABLE ACCESS FULL| T1   | 50000 |  1318K|  1182   (3)| 00:00:06 |                                              
--------------------------------------------------------------------------                                              
                                                                                                                        
UKJA@ukja116> 
UKJA@ukja116> -- This is what I want
UKJA@ukja116> explain plan for
  2  select /*+ leading(x) use_nl(x t1) */ t1.*
  3  from
  4    (select /*+ index_ffs(t1) */ rowid as row_id, c1
  5  	 from t1 where c1 like '%11%') x,
  6    t1
  7  where
  8    t1.rowid = x.row_id
  9  ;

Explained.

UKJA@ukja116> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------                                   
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT            |       | 50000 |  2246K| 50697   (1)| 00:04:14 |                                   
|   1 |  NESTED LOOPS               |       | 50000 |  2246K| 50697   (1)| 00:04:14 |                                   
|*  2 |   INDEX FAST FULL SCAN      | T1_N1 | 50000 |   927K|   653   (5)| 00:00:04 |                                   
|   3 |   TABLE ACCESS BY USER ROWID| T1    |     1 |    27 |     1   (0)| 00:00:01 |                                   
-------------------------------------------------------------------------------------                                   
                                                                                                                        
UKJA@ukja116> 
UKJA@ukja116> -- Advanced query rewrite is the answer
UKJA@ukja116> 
UKJA@ukja116> -- grant priv to ukja (as sys user)
UKJA@ukja116> -- grant execute on dbms_advanced_rewrite to ukja;
UKJA@ukja116> -- grant create materialized view to ukja;
UKJA@ukja116> 
UKJA@ukja116> alter session set query_rewrite_integrity = trusted;

Session altered.

UKJA@ukja116> 
UKJA@ukja116> 
UKJA@ukja116> begin
  2    sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
  3  	  name		 => 'rewrite1',
  4  	  source_stmt =>
  5  'select *
  6  from t1
  7  where c1 like ''%11%''',
  8  	 destination_stmt =>
  9  'select /*+ leading(x) use_nl(x t1) */ t1.*
 10  from
 11    (select /*+ index_ffs(t1) */ rowid as row_id, c1
 12  	 from t1 where c1 like ''%11%'') x,
 13    t1
 14  where
 15    t1.rowid = x.row_id',
 16  	  validate	 => false,
 17  	  rewrite_mode	 => 'text_match');
 18  end;
 19  /

PL/SQL procedure successfully completed.

UKJA@ukja116> -- See how the execution plan is changed
UKJA@ukja116> explain plan for
  2  select *
  3  from t1
  4  where c1 like '%11%'
  5  ;

Explained.

UKJA@ukja116> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------                                   
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT            |       | 50000 |  1660K| 50697   (1)| 00:04:14 |                                   
|   1 |  NESTED LOOPS               |       | 50000 |  1660K| 50697   (1)| 00:04:14 |                                   
|*  2 |   INDEX FAST FULL SCAN      | T1_N1 | 50000 |   341K|   653   (5)| 00:00:04 |                                   
|   3 |   TABLE ACCESS BY USER ROWID| T1    |     1 |    27 |     1   (0)| 00:00:01 |                                   
-------------------------------------------------------------------------------------                                   
                                                                                                                        
UKJA@ukja116> -- drop rewrite equivalence
UKJA@ukja116> begin
  2    sys.dbms_advanced_rewrite.drop_rewrite_equivalence( name=> 'rewrite1');
  3  end;
  4  /

PL/SQL procedure successfully completed.



Magical approach, isn’t it?

The stored outline and SQL profile are easy and common approaches, but this special case does not allow us to use them. The basic mechanism of both tricks is hints. I can’t make Oracle choose the index preprocessing(index fast full scan first, then table lookup by rowid) just by hints.

Consider to apply following technqiues before you beg of the arrogant developers to modify the source. :)

Written by Dion Cho

March 6, 2009 at 7:54 am

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 61 other followers