Re: set optimizer_features_enable to lower version SQL run faster?

  • From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
  • To: mccdba1@xxxxxxxxx
  • Date: Tue, 2 Feb 2010 17:26:41 -0600

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

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

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?




Other related posts: