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
