RE: Load SQL PLan into SQL Plan Baselines from AWR

From: Dimensional DBA <dimensional.dba_at_comcast.net>
Date: Fri, 12 Feb 2016 05:11:46 -0800
Message-ID: <059a01d16596$eda924c0$c8fb6e40$_at_comcast.net>

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_at_comcast.net

<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_at_nbs.sk] 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_at_comcast.net> 
To:        "'Marian Bednar'" <bednar_at_nbs.sk>, 
Cc:        "'oracle-l digest users'" <oracle-l_at_freelists.org> 
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_at_comcast.net> Dimensional.dba_at_comcast.net
<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_at_nbs.sk> mailto:bednar_at_nbs.sk] 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_at_comcast.net> dimensional.dba_at_comcast.net> 
To:        < <mailto:bednar_at_nbs.sk> bednar_at_nbs.sk>, "'oracle-l digest users'" < <mailto:oracle-l_at_freelists.org> oracle-l_at_freelists.org>, 
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

From: <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org [ <mailto:oracle-l-bounce_at_freelists.org> mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 12 2016 - 14:11:46 CET

This message: [ Message body ]
Next message: Hans Forbrich: "Re: Oracle Heap memory usage by listener"
Previous message: Marian Bednar: "RE: Load SQL PLan into SQL Plan Baselines from AWR"
In reply to: Marian Bednar: "RE: Load SQL PLan into SQL Plan Baselines from AWR"
Next in thread: Dominic Brooks: "RE: Load SQL PLan into SQL Plan Baselines from AWR"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message