I was thinking you needed to upload the whole repository not just one query. My
mistake.
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: Friday, February 12, 2016 5:05 AM
To: Dimensional DBA
Cc: 'oracle-l digest users'
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
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
<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: Marian Bednar [ <mailto:bednar@xxxxxx> 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" < <mailto:dimensional.dba@xxxxxxxxxxx>
dimensional.dba@xxxxxxxxxxx>
To: < <mailto:bednar@xxxxxx> bednar@xxxxxx>, "'oracle-l digest users'" <
<mailto:oracle-l@xxxxxxxxxxxxx> 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-management-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: <mailto:oracle-l-bounce@xxxxxxxxxxxxx> 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
