Using Advanced Query Rewrite to Fix Vendor SQL

Have you ever had vendor software that performed poorly and there was no way to tune it by creating indexes – or the vendor restricted you from trying?  If so, here is a way you may be able to help the offending query.

Advanced Query Rewrite (DBMS_ADVANCED_REWRITE) gives the DBA a powerful method to influence the query execution.  Using this method you can tell Oracle when it sees a particular query you want it to run something different.  It is similar to using materialized views (which are sadly underused in Oracle) and allows Oracle to rewrite the query – except in this case you are telling Oracle the exact query to substitute for another query (in our case, a poorly performing query) .

The Situation

I was recently faced with a poorly performing query in some vendor software (StarTeam Datamart Extractor).  There was a query executing for 2 hours and we recognized that if the query were written a little bit differently we would get the same result set with a better execution plan.  So, we can either get the vendor to change their software – which is not easy – or we can use Advanced Query Rewrite to substitute the better query anytime it saw the poorly performing query.

How we fixed it (Step-by-Step)

  1. Configure the Oracle user with the proper permissions
  2. Identify the bad query
  3. Identify the query with better performance
  4. Tell Oracle to substitute the bad query with the good query
  5. Test to make sure Oracle runs the correct query
  6. Set the query_rewrite_integrity session parameter using a logon trigger

Configure the Oracle user with the proper permissions

The user who will create the rule for advanced query rewrite requires a couple of privileges so let’s do that first.  This assumes the owner of the StarTeam Datamart schema is named stde.

grant execute on dbms_advanced_rewrite to stde;
grant create materialized view to stde;

Identify the bad query

Let’s have a look at the query that was performing poorly.  As you can see there were 107248578 consistent gets and this query ran for roughly 2 hours.  This is obviously unacceptable.

select  *
from    st_files
where   serverid = 1
and     id not in
                (
                select id
                from   st_viewmember
                where  serverid = 1
                and    classid = 31
                );

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   193K|    40M|   115M  (1)|383:55:25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ST_FILES    |  9680 |  2079K|  1518   (1)| 00:00:19 |
|*  2 |   INDEX RANGE SCAN          | PK_ST_FILES |  9680 |       |   489   (1)| 00:00:06 |
|*  3 |    INDEX RANGE SCAN         | IND_CLASSID |     1 |    12 |   630   (1)| 00:00:08 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
  107248578  consistent gets
       1469  physical reads
          0  redo size
       2382  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Identify the query with better performance

Below we have change the NOT IN to a NOT EXISTS.  This improved the query performance by a great deal.  The consistent gets decreased by more than 99% (from 107248578 to 7487).

set autotrace traceonly explain statistics

select  *
from    st_files
where   serverid = 1
and     not exists
                (
                select null
                from   st_viewmember
                where  serverid = 1
                and    classid = 31
                and    st_files.id = st_viewmember.id
                );

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   232 |       |  4561   (1)| 00:00:55 |
|*  1 |  HASH JOIN RIGHT ANTI |             |     1 |   232 |  7968K|  4561   (1)| 00:00:55 |
|*  2 |   INDEX FAST FULL SCAN| IND_CLASSID |   339K|  3981K|       |   359   (2)| 00:00:05 |
|*  3 |   TABLE ACCESS FULL   | ST_FILES    |   193K|    40M|       |  1686   (1)| 00:00:21 |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7487  consistent gets
       6493  physical reads
          0  redo size
       2382  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tell Oracle to substitute the bad query with the good query

Using the DBMS_ADVANCED_REWRITE package we now can tell Oracle that when ever it sees the bad query we want it to run our new and improved query in place of the bad query.  Oracle does this behind the scenes and the user has no knowledge of the switch.

begin
        sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'fix_stde_01',
                'select *
                from    st_files
                where   serverid = 1
                and     id not in
                        (
                        select id
                        from   st_viewmember
                        where  serverid = 1
                        and    classid = 31
                        )',
                'select *
                from    st_files
                where   serverid = 1
                and not exists
                        (
                        select null
                        from   st_viewmember
                        where  serverid = 1
                        and    classid = 31
                        and    st_files.id = st_viewmember.id
                        )',
                false );
end;
/

Test to make sure Oracle runs the correct query

Now we should login and run the old query to make sure Oracle is “rewriting” the query.  This does require that we set a session parameter named query_rewrite_integrity so that Oracle is permitted to rewrite the query for us.

alter session set query_rewrite_integrity=trusted;

set autotrace traceonly explain statistics

select  *
from    st_files
where   serverid = 1
and     id not in(
                select  id
                from    st_viewmember
                where   serverid = 1
                and     classid = 31 );

Set the query_rewrite_integrity session parameter using a logon trigger

Since this is vendor software that we cannot change we need to come up with a way to set the query_rewrite_integrity so that our new, better performing, query gets executed.  We have two options that I know of.

Set the query_rewrite_integrity for the entire database by issuing the following:

alter system set query_rewrite_integrity=trusted;

Set the query_rewrite_integrity for the STDE user only by issuing the following:

alter session set query_rewrite_integrity=trusted;

It is highly unlikely you are going to find a DBA out there who is willing to set the query_rewrite_integrity on a database wide level so we will probably need to set it on an individual basis.  Since this is vendor software that we cannot change we need to come up with a way to set the query_rewrite_integrity so that our new, better performing, query gets executed.  This is where a database logon trigger can give us what we need.

Our goal is to set the query_rewrite_integrity parameter for any session logging in as the STDE user.  The following database trigger can take care of that quite nicely.

create or replace trigger alter_session_parameters
after logon on database
declare
        s_username        varchar2(30);
begin
        s_username := sys_context( 'userenv', 'session_user' );
        if s_username = 'STDE' then
                execute immediate 'alter session set query_rewrite_integrity=trusted';
        end if;
exception
        when others then
                null;
end;
/

Testing to make sure we have query_rewrite_integrity=trusted when logging in as STDE

Run the following query to verify that when you log in as the stde user your query_rewrite_integrity session variable is set to “trusted”.  This will enable the session to benefit from the advanced query rewrite feature.

connect stde/stde@stde

select  *
from    v$ses_optimizer_env
where   sid = ( select sid from v$mystat where rownum = 1 )
and     name = 'query_rewrite_integrity';

 SID   ID NAME                           ISD VALUE
---- ---- ------------------------------ --- ----------
 151   71 query_rewrite_integrity        NO  trusted

--
-- NOTE: If you get a 'table or view does not exist' error you will
-- need to grant permissions as such.
--
grant select_catalog_role to stde;

Post a Comment