Tuning the untunable

courtesy by http://dbaharrison.blogspot.com.tr/2015/02/tuning-untunable.html

 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.