Issue with SQL in 11g

[suddhasatwa_bhaumik]

Hello Tim,

Good Morning.
Below issue pertains to Solaris 10 with Oracle 11.2.0.3 and 10.2.0.3.

There was one particular query which is reported to be working fine on a database running on 10.2.0.3. The same was running fine on all such databases on same version in the platform until one of the databases were upgraded to 11.2.0.3. Initially after upgrade the query was taking 3 mins to run, but as per client requirement it should complete by 30 seconds! I used all possible ways to tune it and the execution time of the query came down to 1min20secs, which was not acceptable.

However when I changes the "optimizer_features_enable" parameter on this newly upgraded databases from 11.2.0.3 to 10.2.0.3 (I.e., I am making it similar to what is there in other databases where this query is performing good!), then the query returns in 5 seconds! Even if I remove the indexes I created to tune it, it completes by 10 secs.

I am not entirely sure (especially after this incident!) what "optimizer_features_enable" parameter is doing internally?

Can you please throw some light on it?

Note: I can provide you with the SQL Query and its previous and new Explain plans, if you want to have a look.

Best Regards,
Suddhasatwa

[Tim...]

Hi.

Your options are:

1) Compare the two plans and investigate the reasons for the differences. If all else fails, force the plans to match using hints generated using the DBMS_XPLAN and the "format=>'ADVANCED'" option with the old setting.

2) Use SQL Plan Management to capture the old plan baseline, the set the optimizer back to the latest setting.

http://www.oracle-base.com/articles/11g ... _baselines

Cheers

Tim...

[suddhasatwa_bhaumik]

Hello Tim

Thanks for the note above.

Can you please explain how I can generate a SQL Profile on the 10.2.0.3 database for this query and import/apply it for the same query running on 11.2.0.3?

Thanks,

Suddhasatwa

[Tim...]

Hi.

It's explained in the article I linked to.

http://www.oracle-base.com/articles/11g ... an_loading

- Set the parameter.
- Run the query and load the plan.
- Set the parameter to the latest version. The plan will stay consistent, even though the parameter has changed.

Cheers

Tim...