Thank you, Matthew, for detailed explanation.
My "problematic" query is not in memory and I couldn't wait until oracle
optimalizer uset it again.
So, fast solution is load hinted query (hinting is easy after Maris
recommendation) a then fake original query.
From: "Dimensional DBA" <dimensional.dba@xxxxxxxxxxx>
To: "'Marian Bednar'" <bednar@xxxxxx>,
Cc: "'oracle-l digest users'" <oracle-l@xxxxxxxxxxxxx>
Date: 12. 02. 2016 10:11
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
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
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/ ;
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 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com
From: 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
