RE: set optimizer_features_enable to lower version SQL run faster?
- From: "Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "mccdba1@xxxxxxxxx" <mccdba1@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Feb 2010 07:10:31 -0500
We are fighting the same battle right now with Oracle 10.2.0.4. We upgraded
from Oracle 9.2.0.8 to 10.2.0.4 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
10.2.0.4 - 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. http://www.dba-oracle.com/oracle_tips_cost_adj.htm
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.
Bill
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mark W. Farnham
Sent: Wednesday, February 03, 2010 3:08 AM
To: mccdba1@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc: oracle-db-l@xxxxxxxxxxxxxxxxxxxx
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of dba1 mcc
Sent: Tuesday, February 02, 2010 6:09 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-db-l@xxxxxxxxxxxxxxxxxxxx
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.
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?
Thanks.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
