Dion Cho – Oracle Performance Storyteller

We are natural born scientists

DBMS_ADVANCED_REWRITE and DML

with 5 comments

Oracle 10g has introduced DBMS_ADVANCED_REWRITE package which enables a specific query to be transformed to another form. Very powerful feature, but this is designed for DW not OLTP. Thus has following restrictions.

  1. Does not work with the bind variables.(Metalink Doc ID. 392214.1)
  2. Only works for the SELECT statement.
  3. Does not work when the base table is modified through DML.

It’s not well known how to avoid 3rd restriction. The only way I got from some test cases is to use QUERY_REWRITE_INTEGRITY parameter.

Let me explain through simple test case.

1. Make objects.

UKJA@ukja1106> create table t1
  2  as select level as c1, level as c2
  3  from dual
  4  connect by level <= 1000;

Table created.

Elapsed: 00:00:00.04

UKJA@ukja1106> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.03

UKJA@ukja1106> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

2. The SELECT statement has FULL hint and (naturally) follows full table scan.

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

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

3. How to fix it? DBMS_ADVANCED_REWRITE package would give you the power to change the SELECT statement on the fly with QUERY_REWRITE_INTEGRITY parameter set to TRUSTED.

UKJA@ukja1106> begin
  2  
  3  	     sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  4  	     'test_rewrite',
  5  	     'select /*+ full(t1) */ * from t1 where c1 = 1',
  6  	     'select /*+ index(t1) */ c1, c2 from t1 where c1 = 1',
  7  	     false,
  8  	     'text_match');
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

UKJA@ukja1106> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.00

4. Now you have a plan with magical index range scan.

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

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

5. But one simple DML would prevent rewrite from happening.

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.00

UKJA@ukja1106> commit;

Commit complete.

Elapsed: 00:00:00.00

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

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

6. One way to avoid this restriction is to set QUERY_REWRITE_INTEGRITY parameter to STALE_TOLERATED.

UKJA@ukja1106> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

Elapsed: 00:00:00.00

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

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

7. Now DML does not prevent the rewrite from happening.

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

UKJA@ukja1106> commit;

Commit complete.

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

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

8. But here you have another restriction. If the DML is not committed, the rewrite would fail even with STALE_TOLERATED setting.

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

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

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

Hm… there seems to be really many restrictions for OLTP, but these would not make any problem for DW. But I believe that even OLTP would get a big and good effect when used in the right place.

Footnote:It makes me a bit gloomy not to post on OOW events but on these little tips. Well, the only consolation is that San Francisco is very far from here and I really hate long-distance journey. :(

About these ads

Written by Dion Cho

October 13, 2009 at 5:43 am

Posted in Misc.

5 Responses

Subscribe to comments with RSS.

  1. At least you can post the tips. I’m also not in SF and I’m finding it hard to find any subjects to blog on which aren’t a breach of my client’s confidence :-(

    Keep up the good work!

    Regards Nigel

    Nigel Thomas

    October 13, 2009 at 7:27 am

  2. From the stale_tolerated observation, I presume that DBMS_ADVANCED_REWRITE uses materialized views behind the scenes. Not a surprise as I guess the whole rewrite framework stems from there.

    dombrooks

    October 13, 2009 at 8:20 am

    • Dombrooks.

      Yes, the total mechanism should be based on the materialized view. But if what you mean is that Oracle creates the implicit materialized view to handle above rewrite(is that it?), I would not agree.

      Dion Cho

      October 13, 2009 at 8:27 am

      • I didn’t know how it worked and at the time I didn’t have access to investigate. I can see now that it creates an object of type “REWRITE EQUIVALENCE”. I need to look further into this query rewrite engine which I’ve just taken for granted in terms of working with MVs.

        dombrooks

        October 15, 2009 at 9:32 pm

        • Dombrooks.

          Thanks for the investigation and sharing. Let me know whenver you’ve got another insight on this.

          Dion Cho

          October 15, 2009 at 11:30 pm


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

%d bloggers like this: