This forum is currently locked. You can't register or post questions at this time. (read more)

Issue with SQL in 11g

All posts relating to Oracle database administration.

Moderator: Tim...

suddhasatwa_bhaumik
Member
Posts: 3
Joined: Thu Nov 15, 2012 5:53 am

Issue with SQL in 11g

Postby suddhasatwa_bhaumik » Thu Nov 15, 2012 6:01 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Issue with SQL in 11g

Postby Tim... » Thu Nov 15, 2012 9:46 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

suddhasatwa_bhaumik
Member
Posts: 3
Joined: Thu Nov 15, 2012 5:53 am

Re: Issue with SQL in 11g

Postby suddhasatwa_bhaumik » Thu Nov 15, 2012 10:48 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Issue with SQL in 11g

Postby Tim... » Thu Nov 15, 2012 11:33 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron