RE: set optimizer_features_enable to lower version SQL run faster?

From: Johnson, William L (TEIS) <"Johnson,>
Date: Wed, 3 Feb 2010 07:10:31 -0500
Message-ID: <>

We are fighting the same battle right now with Oracle We upgraded from Oracle to running on Solaris 10 on January 16. We did not change any OS or disk configuration - and yes we had the proper patch levels for the OS. Bottom line is that general performance sucks in Oracle - but of course that performance did not show up in dev, QA or volume test since we have a hard time mimicking our production load. We opened an SR with Oracle, and they have been sitting on it for 2 days without any real input while our production system crawls. After some digging around, we did trace a couple of sessions and determined that we were doing index range scans where Oracle 9i had done full table scans. In one case we were doing 128,000 index block reads for a select count(*) operation where a full table scan only reads 6,000 blocks with the benefit of reading 16 blocks at one time. The parameter that seems to have helped us the most is optimizer_index_cost_adj - we had it set at 10 in Oracle 9i and it seems like we need to bump it to around 36 or 50 in 10g without anything else changing. The following note (some will like it some will not) seems to have some great information in it related to this issue.

I am not sure why we pay Oracle so much for annual support and maintenance when things like this are not well published via Metalink. It makes our lives very painful...

Hope this helps.

-----Original Message-----

From: [] On Behalf Of Mark W. Farnham Sent: Wednesday, February 03, 2010 3:08 AM To:; Cc:
Subject: RE: set optimizer_features_enable to lower version SQL run faster?

If these are stock queries, then do an explain plan of the fastest one. Then clone the query and hint it or do whatever else you need to do to get the same plan in your current release, and do whatever is the equivalent of a stored outline for your current release.

Keep a list of which queries have been thus tinkered with and give each a chance with natural plan creation at each new release (hoping to remove it from the "tinkered with" list.)

If the "whatever else you need to do" includes tinkering with init parameters or session parameters, consider carefully whether the changes from defaults are overall positive for your instance that executes the query and record that you made the changes and why (again with the notion to at least give defaults a try as time allows after upgrades or optimizer oriented patches.)

Also, as time allows, examine whether the query was optimized for older release optimizers in a way that prevents a newer optimizer from generating a possibly better plan and consider recasting the query "naturally" to see whether the newer optimizer can do better.

If these are generated queries or dynamic or ad hoc queries you have a tricker job do to and the possibilities are np incomplete.

There has been somewhat of a trend toward more "hash" based plans as you move ahead in releases. You may need to allocate more room in memory areas for those plans to operate reasonably. The explain plans from the different releases will give you an idea whether this is a potential problem for your actual case.

I hope this brief process outline helps you a bit.

-----Original Message-----

From: [] On Behalf Of dba1 mcc
Sent: Tuesday, February 02, 2010 6:09 PM To:
Subject: set optimizer_features_enable to lower version SQL run faster?

we have ORACLE enterprise version on LINUX. recently we found some SQL statemnets run very slow (even we run analyze statistics). But if we set optimizer_features_enable to lower version it run much faster. Following is data we got:

When the SQL is run with OPTIMIZER_FEATURE_ENABLE= 73 seconds 50 seconds

 9 30 seconds

 8 1 second.

  1. does there has some way we can do (NOT set optimizer_features_enable to lower version) still get better SQL performance?
  2. if unfortunately we need set 'optimizer_feature_enable to lower version like 8.0) does database will have any problem?




-- Received on Wed Feb 03 2010 - 06:10:31 CST

Original text of this message