Re: SQL Plan Management
From: Lyall Barbour <lyallbarbour_at_sanfranmail.com>
Date: Mon, 07 Oct 2013 15:45:00 -0400
Message-ID: <20131007194500.147510_at_gmx.com>
The column is YES on the SQL Plans for this SQL_ID
----- Original Message -----
From: Carlos Sierra
Sent: 10/02/13 11:09 AM
To: Michael.J.Tefft_at_snapon.com
Subject: Re: SQL Plan Management
Use column DBA_SQL_PLAN_BASELINES.REPRODUCED to determine if your plan is reproducible. Cheers, Carlos Sierra blog: carlos-sierra.net twitter: _at_csierra_usa Life's Good! On Oct 2, 2013, at 11:04 AM, "Tefft, Michael J" <Michael.J.Tefft_at_snapon.com> wrote: > A few questions: > 1. What is the setting for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES? > > 2. Are any of your baselines being used? Check DBA_SQL_PLAN_BASELINES.LAST_EXECUTED. It may or may not be accurate as an actual timestamp, but the difference between NULL and something NOT NULL was a reliable test for whether it had actually been executed. > > If you are getting some baselines used, and others not, then you may have 'non-reproducible' baselines. That is, the plans captured in your 10g database are not valid in your 11g database. This may be especially common in queries that use SYS views, because there were many views that changed significantly between 10g and 11g. > > Mike Tefft > > > -----Original Message----- > From: or acle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour > Sent: Wednesday, October 02, 2013 8:23 AM > To: oracle-l > Subject: Re: SQL Plan Management > > it's set to 11.2.0.4 > I'm going to try to bounce the db, delete my baseline and the load the baseline back in this morning. > Lyall Barbour > ----- Original Message ----- > From: David Fitzjarrell > Sent: 10/01/13 05:10 PM > To: lyallbarbour_at_sanfranmail.com, oracle-l > Subject: Re: SQL Plan Management > How do you have optimizer_features_enable set in the 11.2 database? > > David Fitzjarrell > > From: Lyall Barbour <lyallbarbour_at_sanfranmail.com> > To: oracle-l <oracle-l_at_freelists.org> > Sent: Tuesday, October 1, 2013 3:01 PM > Subject: SQL Plan Management > Hello everyone, > I'm in the middle of upgrading our 10.2.0.5 databases to 11.2.0.4 I'm trying to get SQL Plan Management to make the 11g database run all our queries the same as 10g was. This is what i did > 1) Upgraded a database, which flushed SQL Cache memory > 2) Created a Tuning Set on the Production database, with about 12k sql plans > 3) Packed that set > 4) expdp the tables (forget what they are called) > 5) imported that dump into the 11g database > 6) unpacked that Tuning Set > 7) Loaded as a Baseline. > Everything looked good, i can see the Tuning Set, i can see all the Plans in the Baseline. But when i run this one query in 11g, and it *looks* like it's using the Baseline, it's really slow and the plan looks nothing like the 10g plan in our current Prod database. > I have the OPTIMIZER_USE_SQL_PLAN_BASELINES set to true (default) Did i miss a step? > Thanks, > Lyall Barbour > > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > -- http://www.freelists.org/webpage/oracle-l
Date: Mon, 07 Oct 2013 15:45:00 -0400
Message-ID: <20131007194500.147510_at_gmx.com>
The column is YES on the SQL Plans for this SQL_ID
----- Original Message -----
From: Carlos Sierra
Sent: 10/02/13 11:09 AM
To: Michael.J.Tefft_at_snapon.com
Subject: Re: SQL Plan Management
Use column DBA_SQL_PLAN_BASELINES.REPRODUCED to determine if your plan is reproducible. Cheers, Carlos Sierra blog: carlos-sierra.net twitter: _at_csierra_usa Life's Good! On Oct 2, 2013, at 11:04 AM, "Tefft, Michael J" <Michael.J.Tefft_at_snapon.com> wrote: > A few questions: > 1. What is the setting for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES? > > 2. Are any of your baselines being used? Check DBA_SQL_PLAN_BASELINES.LAST_EXECUTED. It may or may not be accurate as an actual timestamp, but the difference between NULL and something NOT NULL was a reliable test for whether it had actually been executed. > > If you are getting some baselines used, and others not, then you may have 'non-reproducible' baselines. That is, the plans captured in your 10g database are not valid in your 11g database. This may be especially common in queries that use SYS views, because there were many views that changed significantly between 10g and 11g. > > Mike Tefft > > > -----Original Message----- > From: or acle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour > Sent: Wednesday, October 02, 2013 8:23 AM > To: oracle-l > Subject: Re: SQL Plan Management > > it's set to 11.2.0.4 > I'm going to try to bounce the db, delete my baseline and the load the baseline back in this morning. > Lyall Barbour > ----- Original Message ----- > From: David Fitzjarrell > Sent: 10/01/13 05:10 PM > To: lyallbarbour_at_sanfranmail.com, oracle-l > Subject: Re: SQL Plan Management > How do you have optimizer_features_enable set in the 11.2 database? > > David Fitzjarrell > > From: Lyall Barbour <lyallbarbour_at_sanfranmail.com> > To: oracle-l <oracle-l_at_freelists.org> > Sent: Tuesday, October 1, 2013 3:01 PM > Subject: SQL Plan Management > Hello everyone, > I'm in the middle of upgrading our 10.2.0.5 databases to 11.2.0.4 I'm trying to get SQL Plan Management to make the 11g database run all our queries the same as 10g was. This is what i did > 1) Upgraded a database, which flushed SQL Cache memory > 2) Created a Tuning Set on the Production database, with about 12k sql plans > 3) Packed that set > 4) expdp the tables (forget what they are called) > 5) imported that dump into the 11g database > 6) unpacked that Tuning Set > 7) Loaded as a Baseline. > Everything looked good, i can see the Tuning Set, i can see all the Plans in the Baseline. But when i run this one query in 11g, and it *looks* like it's using the Baseline, it's really slow and the plan looks nothing like the 10g plan in our current Prod database. > I have the OPTIMIZER_USE_SQL_PLAN_BASELINES set to true (default) Did i miss a step? > Thanks, > Lyall Barbour > > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > -- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 07 2013 - 21:45:00 CEST