Re: set optimizer_features_enable to lower version SQL run faster?

Too bad you can't set it to version 2. That SQL statement would probably run in 1/1000 of a second.


Seriously, have a look at the statement and see if you can figure out why changing the parameter caused it to run faster (if it did). Hints tuning the statement to a certain version of the optimizer are one reason this could happen. But at this point it sounds like you're just shooting in the dark.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com






On Feb 2, 2010, at 5:09 PM, dba1 mcc wrote:

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.






--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


Other related posts: