Fixing third party application performance problems

Hi,

I bet you have all been in this situation - you are in charge of an Oracle DB for a third party application your company has purchased.

I am SURE that you make the top most effort at your company to tune your in-house applications, but the company that wrote this third party application has made some mistakes and written uneficient, badly tuned SQL's.

Now you have Added indexes (or dropped them), tried changing different init.ora parameters, but this can only make a limited contribution, especially when the design of the SQL's was really bad.

Until Oracle 10g you did not have many solutions to this problem.
Oracle introduced in 10g a package named DBMS_ADVANCED_REWRITE.
Basically this package enables you to strech the capabilities you know from query rewrite using materialized views, and lets you decide what the alternative SQL will be.

This is great !

I have been looking for this for a long time (even tried manipulating VPD and put in the VPD procedure somthing like ' 1=2 union all select ...', only to find out that the VPD comes in to play late in the parsing stage and only valid predicates can be used...)

I have built a simple senario demonstrating the use of DBMS_ADVANCED_REWRITE, note that some of the things I did where purely for the sake of demonstration and you would probably not use them in real life.

Additionaly, I have not tested this in a full blown production environment.

To the demo:

1) Connect to the DB as sys and create a user for the demonstration:

conn sys as sysdba
create user guy identified by guy;

2) Grant the user the appropriate privs:

grant connect,resource to guy;
grant execute on dbms_advanced_rewrite to guy;
grant create materialized view to guy;

3) Connect to the new user and create a simple table:

conn guy/guy
create table SECRET_DATA (CREDIT_CARD NUMBER);
insert into secret_data values (12345678);
insert into secret_data values (1000);
commit;

4) Issue a select to validate the rows:

SQL> select * from secret_data;

CREDIT_CARD
-----------
12345678
1000

5) Set the appropriate session parameters:

alter session set query_rewrite_enabled=true;
#Default if OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher

alter session set query_rewrite_integrity=trusted;
#This was set to allow a rewrite with different results, in a real life senario you would
consider to have this enforce the integrity of the rewrite SQL

6) Execute the DBMS_ADVANCED_REWRITE package:

SQL> exec sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
('test_equiv','select * from secret_data','select * from secret_data where credit_card=1000',
false,'TEXT_MATCH');

7) You can view your rewrite equivalences:

SQL> select * from USER_REWRITE_EQUIVALENCES;

OWNER NAME SOURCE_STMT DESTINATION_STMT
--------- ------- ------------------ -------------------------
REWRITE_MO
----------------
GUY TEST_EQUIV select * from secret_data select * from secret_data where credit_card=1000
TEXT_MATCH

8) Issue the SQL again, this time with the rewrite:

# As you can see the rewrite works and we get only the "1000" row
SQL> select * from secret_data;

CREDIT_CARD
-----------
1000

9) You can disable the rewrite equivalence and see the normal result again:

SQL> exec sys.dbms_advanced_rewrite.alter_rewrite_equivalence('test_equiv','DISABLED');
SQL> select * from secret_data;

CREDIT_CARD
-----------
12345678
1000


That's all for the basic demo, for further information you can visit the otn documentation.

Regards,
Guy