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