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 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn
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/>
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://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/B28359_01/license.111/b28287.pdf ;
<https://docs.oracle.com/cd/E11882_01/license.112/e47877.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>
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-m
anagement-11gr2-133099.pdf
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
<mailto:Dimensional.dba@xxxxxxxxxxx> Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn
<http://www.dimensionaldba.com/> www.dimensionaldba.com
From: oracle-l-bounce@xxxxxxxxxxxxx [ <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
mailto: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
