Hi,
I know one solution, load to Plan baselines hinted original query a thenfake it -*https://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/*
but exists any other solution to load directly from awr without manuallyhinting?
Yep, you are right. I had to go open one of my scripts. It has been awhile
since the last outln.
There is a way but it is not pretty and I never finished writing the
automation for the construction of the rows in the outln tables and of
course it wouldn’t be supported by Oracle.
I would recommend
1. Turn on optimizer_capture_sql_plan_baselines and start
collecting baselines on all SQL statements in memory or you can load them
directly, but you still need to turn on collection.
2. You can then perform a diff between the outlns collected versus
what you have in AWR. Much smaller set to work with of what’s left that is
not currently in memory.
3. Then perform regular SPM activity to isolate the baselines you
want, purge ones you don’t and isolate any that your collected baseline
doesn’t match what you have in the AWR that you want.
For the ones that are left, I don’ t have an 11g database up and running
at this point so these will be general
1. Perform a sql joining dba_sql_plan_baselines and v$sql on
signature and exact_matching_signature.
2. The output from the query 1 above can then be driven into your
awr repository table of your choice as a minus query. The output of this
can be constrained by the time frame of your choice to determine what
sql_ids are available in what time frames to sort of determine how long it
will take for them to be captured or if they will be captured at all based
on AWR data.
3. The last part of 2 that is taking the query from 1 and union
with query in 2 constrained to a time window of your choice and minus from
your complete AWR repository table of your choice. This will give you the
what has been captured that is in memory+what should be captured over next
X days minus from complete AWR will give you what you will not capture. You
can of course all along the way exclude queries off of schema (like SYS) or
other exclusions you wish to include to limit the set to what you really
need to be concerned with and what will require the extra work.
4. Then you can determine if the time frame to capture will work
for you or which ones are left and how many that you would have to follow
the fake-baselines versus writing some simple automation to do the work for
you.
Not what you really wanted, but you can get to the place you are trying to
achieve which is to have baselines running as you do not own Tuning pack
for SQL Tuning sets and do it fairly quickly without a lot of work.
*Matthew Parker*
*Chief Technologist*
*Dimensional DBA*
*425-891-7934 <425-891-7934> (cell)*
*D&B *047931344
*CAGE *7J5S7
*Dimensional.dba@xxxxxxxxxxx <Dimensional.dba@xxxxxxxxxxx>*
*View Matthew Parker's profile on LinkedIn*
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com
*From:* Marian Bednar [mailto:bednar@xxxxxx]
*Sent:* Thursday, February 11, 2016 11:24 PM
*To:* Dimensional DBA
*Cc:* 'oracle-l digest users'
*Subject:* RE: Load SQL PLan into SQL Plan Baselines from AWR
yes, I can use dbms_spm and standard method for loading Plan Baselines
from AWR is by using SQL Tunning Sets (STS) - but for using STS is required
Tunning Pack
therefore I am trying to find another solution
I know one solution, load to Plan baselines hinted original query a then
fake it -https://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/
but exists any other solution to load directly from awr without manually
hinting?
From: "Dimensional DBA" <dimensional.dba@xxxxxxxxxxx>
To: <bednar@xxxxxx>, "'oracle-l digest users'" <
oracle-l@xxxxxxxxxxxxx>,
Date: 12. 02. 2016 08:04
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
------------------------------
The diagnostic pack gives you access to AWR.
You can use standard SQL Plan Management as the license is included with
the EE database, no extra licensing required.
https://blogs.oracle.com/optimizer/entry/does_the_use_of_sql
https://docs.oracle.com/cd/B28359_01/license.111/b28287.pdf
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf
dbms_outln or dbms_spm.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
*Matthew Parker*
*Chief Technologist*
*Dimensional DBA*
*425-891-7934 <425-891-7934> (cell)*
*D&B *047931344
*CAGE *7J5S7
*Dimensional.dba@xxxxxxxxxxx* <Dimensional.dba@xxxxxxxxxxx>
*View Matthew Parker's profile on LinkedIn*
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com
*From:* oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx ;<oracle-l-bounce@xxxxxxxxxxxxx>] *On
Behalf Of *Marian Bednar
*Sent:* Thursday, February 11, 2016 10:26 PM
*To:* oracle-l digest users
*Subject:* Load SQL PLan into SQL Plan Baselines from AWR
Oracle 11.2.0.4
I need to load good sql plan into SQL PLan Baselines from AWR.
But I have only Diagnostic Pack License, so I cannot use SQL Tuning Set .
Exists any other method to load from AWR?
In v$sql_plan exist only "bad plans".
Thanks.
Marian
