RE: set optimizer_features_enable to lower version SQL run faster?
Date: Wed, 3 Feb 2010 03:07:55 -0500
Message-ID: <B5AFE25D6C394F6CA7D2923CAADCDE46_at_rsiz.com>
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of dba1 mcc
Sent: Tuesday, February 02, 2010 6:09 PM
To: oracle-l_at_freelists.org
Cc: oracle-db-l_at_Groups.ITtoolbox.com
Subject: set optimizer_features_enable to lower version SQL run faster?
we have ORACLE enterprise version 10.2.0.4 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=
10.2.0.4 73 seconds
10.2.0.3 50 seconds
9 30 seconds
8 1 second.
- does there has some way we can do (NOT set optimizer_features_enable to lower version) still get better SQL performance?
- if unfortunately we need set 'optimizer_feature_enable to lower version like 8.0) does database will have any problem?
Thanks.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 03 2010 - 02:07:55 CST