Tuning the untunable?



As part of an application migration we have to run a script that moves the schema from version x to version y. This schema upgrade process involves running an executable that works out what version of the app we are on and then running a whole series of migration scripts. This executable connects using an odbc driver and unfortunately the driver seems to have some built in code that runs a query against the data dictionary that is very very slow.

This is the query:

select /*+ RULE  * /  a.owner,a.table_name,b.column_name,c.owner,c.table_name,d.column_name,b.position,c.delete_rule,c.constraint_name,a.constraint_name
from sys.all_constraints a,sys.all_cons_columns b,sys.all_constraints c,sys.all_cons_columns d
where a.owner=b.owner and a.constraint_name=b.constraint_name and c.owner=d.owner and c.constraint_name=d.constraint_name and
a.constraint_type='P'and c.constraint_type='R'and a.constraint_name=c.r_constraint_name and a.owner=c.r_owner and b.position=d.position and 
c.owner='ZAINET' and c.table_name='RTPROFILE'

The table name changing all the time as the script works its way through the system.

The same table can be modified a number of times.

This upgrade is happening on 12.1.0.1 and when the SQL is run here (with fairly default settings) the above statement takes about 36 seconds to run, the 2 screenshots below show an overall view and then a drill into one of the SQL statements.



Now these 36 seconds add up to a huge amount of time (many hours in fact) with the number of scripts that have to be run and this makes the outage time for migration unacceptable.

So how do we tune this?

We cannot change the code in any way - it's hardcoded into the odbc driver and we can't touch that - so what do we do?

Well the first idea was to get rid of the hint - that's clearly not what we want to have  - but how can we do that?

Well the answer is to use


"_optimizer_ignore_hints"=true

This set at the database level tells oracle to ignore all hints.

So we enable that and now the code is down to 15 seconds to run - so much better - but still way too slow - so what next?

Well looking at the query the most likely issue is with ALL_CONSTRAINTS and i know there have been some performance issues with that in some versions.

A quick metalink search reveals something that looks useful

Oracle Support Document 796359.1 (SUB-OPTIMAL PLAN FOR JDBC GENERATED SQL USING ALL_CONSTRAINTS AND ALL_CONS_COLUMNS) can be found at: 

https://support.oracle.com/epmos/faces/DocumentDisplay?id=796359.1

Going through this doc and the suggestion is to revert optimizer behaviour to that of earlier versions.

So we try that

 alter system set optimizer_features_enable='10.1.0';

Now when we run the code it takes 0 seconds!

So we've managed to almost completely remove any overhead from this odbc driver - we tuned the untunable!

Post the migration we'll remove these two settings at let 12c do it's thing as the normal app makes no use of this odbc driver.

2 comments:

  1. Hi, encountered the same issue, application is upgrading from x to y, and database also being upgraded from 11204 to 12102, the application query which access data_dictionary tables all_constraints takes less than 3 sec in 11204 version, where as the same query in 12102 takes 10 seconds, i tried changing the above parameters at session level but that didn't help much.
    But if i change the same optimizer parameters at system level (Pfile/spfile) the query is superrr fast less than 1 sec, i think changing those parameters at system level might hit other queries performance where HINTS are used, any advice here much appreciated.

    ReplyDelete
    Replies
    1. Hi,
      The session level change should work fine - are you sure it was activated correctly? You should just be able to create a logon trigger that does this for you for those specifc sessions.

      There are quite a few docs on MOS about this as those views changed a lot on 11g with edition based stuff being added that made performance worse.

      Changing anything system wide will affect other things so is to be avoided if possible.

      Cheers,
      Rich

      Delete