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

Automatic sql plan managment

All posts relating to Oracle database administration.

Moderator: Tim...

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Automatic sql plan managment

Postby shrinika » Wed Jul 24, 2013 4:14 pm

Tim, I was reading the link http://www.oracle-base.com/articles/11g ... -11gr1.php

My database is 11.1.0.7 and 11.2.0.3.

The parameter optimizer_capture_sql_plan_baselines is set to TRUE. Once it is set, oracle will
collect the sql plan in base line. But i don't see any record on the below view.

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines

Am i miss understanding about this parameter?

Please help me on this.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Automatic sql plan managment

Postby Tim... » Wed Jul 24, 2013 4:38 pm

Hi.

Not really sure. I never use the automatic capture.

According to the docs Oracle decides what plans are captured. It is looking to generate baselines for "repeatable SQL statements". Have you put any real load on the system to allow it to have something to work with?

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

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: Automatic sql plan managment

Postby shrinika » Wed Jul 24, 2013 6:19 pm

Thanks for the update. I did not put any real load the database. However, one of my database is very high load DB.
This parameter is set to TRUE. But i don't see any record in dba_sql_plan_baselines view.

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: Automatic sql plan managment

Postby shrinika » Wed Jul 24, 2013 6:44 pm

My other question about FIXED parameter.

I understand about ENABLED. But not sure about FIXED. I am able to evolve after FIXED = YES.

What does it mean? the SQL plan baseline will not evolve over time

Code: Select all

Altering Plan Baselines

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Automatic sql plan managment

Postby Tim... » Wed Jul 24, 2013 8:39 pm

Hi.

I don't think this has anything to do with manually evolving a baseline. I'm guessing that overides any manual setting. I think this is to stop automatic evolution of that baseline. SO let's say you turn on automatic baselines, but want to make this plan an exception from that automatic evolution.

Just to clear up a point here, I believe the key to this deature is not to let things progress automatically. If you want things to change over time, you might as well not use baselines and just let the optimizer do it's thing. I feel the true value in this feature is to prevent degeneration. I mostly use SPM during upgrades. When we see a reduction in performance from a move between 10g and 11g, it raises alarm bells. What do I do? I set the optimizer_features_enable back to 10g, run for a while, capturing the baselines. Then switch to optimizer_features_enable of 11g and keep using the original baselines. Then manually evolve anything that looks like it might be improved in 11g.

I spoke to Maria Colgan (from the optimizer team) about this and she agreed it is a common usage.

In my opinion, and I could be wrong, letting this feature run in automatic mode is a mistake. This is something to stabalize the situation and allowing automatic capture and evolution does not stabalize the situation in my mind.

I've been to a number of talks about this feature over the years and few of those talk about this feature in any sense other than what I've just described. Once again, it is just an opinion.

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

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: Automatic sql plan managment

Postby shrinika » Thu Aug 01, 2013 2:02 pm

Tim, thank you so much for your detailed explanation!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Automatic sql plan managment

Postby Tim... » Thu Aug 01, 2013 9:05 pm

:)
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 11 guests