Using DBMS_ADVANCED_REWRITE with an HINT to change the execution plan

In one of my earlier posts, I had illustrated a use case of DBMS_ADVANCED_REWRITE and its use in cases where we cannot change the code, but can still influence and change the way the optimizer executes the same SQL statement.

In case of many vendor provided and packaged applications, we do not have access to the SQL code and we cannot rewrite the SQL statements.

So there could be cases where even though indexes are present on the table, they are not being used by the optimizer and one way we can force the optimizer to use an index is via the INDEX hint.

So how we change the execution plan of the optimizer for a particular statement without changing the original SQL statement that the application is executing?

We do it using the powerful new feature introduced in 10g called DBMS_ADVANCED_REWRITE or “tuning without touching the code”.

To illustrate this, let us create a small table called MYOBJECTS which is based on DBA_OBJECTS and I have made the data skewed on purpose where in the 55000 row table, 1000 rows have the OWNER column with the value ‘PUBLIC’ and the remaining 54000 rows have the value ‘GAVIN’ for the OWNER column.

So the CBO will choose a FULL TABLE SCAN over the INDEX scan when the WHERE clause includes the predicate ‘GAVIN’ because majority of the rows are being accessed by the query and the CBO considers it more optimal in that case to just scan the table rather than both the table and the index.

SQL> create table myobjects as select * from dba_objects;

Table created.

SQL> update myobjects set owner='GAVIN';

56575 rows updated.

SQL> update myobjects set owner='PUBLIC' where rownum <1001;

1000 rows updated.

SQL> commit;

Commit complete.

SQL> create index myobjects_ind on myobjects(owner);

Index created.

SQL> explain plan for select object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2581838392

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 52826 |  4849K|   225   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| MYOBJECTS | 52826 |  4849K|   225   (3)| 00:00:03 |
-------------------------------------------------------------------------------

So if we feel that the CBO should be still using an index regardless, we can provide an INDEX hint and we see now the index is being used as opposed to a full table scan.

SQL> explain plan for select /*+ INDEX (myobjects myobjects_ind) */
  2  object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2745750972

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 52826 |  4849K|   916   (1)| 00:00:11 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYOBJECTS     | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  2 |   INDEX RANGE SCAN          | MYOBJECTS_IND | 52826 |       |   134   (3)| 00:00:02 |
---------------------------------------------------------------------------------------------

But now the question is if I cannot change the SQL statement or have access to the code, how do I still enforce the INDEX hint?

DBMS_ADVANCED_REWRITE to the rescue.

Remember SYS needs to grant execute privileges on the DBMS_ADVANCED_REWRITE package to the user who is going to use this.

Since we have created the table as SYSTEM, we connect as SYSTEM and execute this statement shown below.

Basically the two main parameters we are providing here is the SOURCE STATEMENT or the original SQL and the DESTINATION STATEMENT which is the modified piece of SQL we want the optimizer to execute every time it sees the “source or original” statement.

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'Use_Myobjects_Index',
source_stmt =>'select object_name,object_type from myobjects where owner=''PUBLIC''',
destination_stmt => 'select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobjects where owner=''PUBLIC''' , 
validate => false,
rewrite_mode => 'text_match');
end;
/

But since both the source and destination statements are the same in this case (Oracle considers the INDEX hint to be just a comment), we get an error like the one shown below:

ERROR at line 1:
ORA-30394: source statement identical to the destination statement
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

Okay. so now how do we make the second statement differ so that it is recognised as a new statement different from the original one without changing radically the SQL itself?

I looked up various user forums because this problem has been faced by others as well and one of the suggestions given was to add the clause SYSDATE=SYSDATE which I think is a good suggestion as it will not affect the outcome of the SQL statement and should not cause any real performance overhead.

So this is what the new DBMS_ADVANCED_REWRITE statement looked like:

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'Use_Myobjects_Index',
source_stmt =>'select object_name,object_type from myobjects where owner=''GAVIN''',
destination_stmt => 'select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobjects where owner=''GAVIN'' and sysdate=sysdate' , 
validate => false,
rewrite_mode => 'text_match');
end;
/

After executing the piece of PL/SQL, let us now see what has happens when we run our SQL statement which was originally going for a FULL TABLE SCAN.

SQL> explain plan for select object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 792246183

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  1 |  FILTER                      |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYOBJECTS     | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  3 |    INDEX RANGE SCAN          | MYOBJECTS_IND | 52826 |       |   134   (3)| 00:00:02 |
----------------------------------------------------------------------------------------------

We see that now the CBO is using the index MYOBJECTS_IND and doing an INDEX RANGE SCAN instead of the full table scan and we have actually transformed the SQL statement which the application executes without altering the SQL itself.

We can use the view USER_REWRITE_EQUIVALENCES to check what all query rewrites we have set up in the database.

SQL> set long 500000

SQL> select NAME,SOURCE_STMT,DESTINATION_STMT,REWRITE_MODE from user_rewrite_equivalences;

NAME                           SOURCE_STMT


NAME                           SOURCE_STMT
------------------------------ --------------------------------------------------------------------------------
DESTINATION_STMT                                                                 REWRITE_MO
-------------------------------------------------------------------------------- ----------
USE_MYOBJECTS_INDEX            select object_name,object_type from myobjects where owner='GAVIN'
select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobj TEXT_MATCH
ects where owner='GAVIN' and sysdate=sysdate

A very good feature!

Read more about this here …