Posts Tagged ‘sql profile’
Optimizing Unoptimizeable SQL – dbms_advanced_rewrite
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.
- Modifying optimizer parameters
- Controlling statistics(including histogram) – Systemized by Wolfgang Breitling
- Stored outline – Visit here
- SQL profile – Visit here
- Advanced query rewrite
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. :)
- Modifying optimizer parameters
- Controlling statistics(including histogram) – Systemized by Wolfgang Breitling
- Stored outline – Visit here
- SQL profile – Visit here
- Advanced query rewrite
How to tune SQL statement without changing text – Plan baseline version
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.
