RE: Load SQL PLan into SQL Plan Baselines from AWR
Date: Fri, 12 Feb 2016 16:36:41 +0000
Message-ID: <DUB123-W52478967F8AE500B28BA1DA1A90_at_phx.gbl>
Just fyi... I've been thinking about how you can go from AWR specifically to a sql plan baseline without injecting the hints into the sql and running or manual hinting and without going via tuning-pack licensed DBMS_SQLTUNE... just for fun.
The problems are EXACT_MATCHING_SIGNATURE and SQL_HANDLE. SQL_HANDLE is the hex of EXACT_MATCHING_SIGNATURE (thanks to http://www.lab128.com/all_these_oracle_ids/article_text_sql_ids.html)
EXACT_MATCHING_SIGNATURE isn't in AWR.
Only FORCE_MATCHING_SIGNATURE is (DBA_HIST_SQLSTAT).
And going from SQL_TEXT -> EXACT_MATCHING_SIGNATURE can be done via DBMS_SQLTUNE so we're back to extra license or writing your own implementation to match the algorithm.
I've questioned before why that column was not included - it seemed like a glaring oversight. Perhaps just to ensure that you couldn't go AWR -> SPM without Tuning pack...
From: dombrooks_at_hotmail.com
To: bednar_at_nbs.sk; dimensional.dba_at_comcast.net
CC: oracle-l_at_freelists.org
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
Date: Fri, 12 Feb 2016 11:02:01 +0000
You have most of the information you need via DBA_HIST_SQL_PLAN.
So in theory it's just a case of manually loading the right data in the right format into a staging table (DBMS_SPM.CREATE_STGTAB_BASELINE) and then unpacking it (DBMS_SPM.UNPACK_STGTAB_BASELINE).
In terms of manually hacking the data, the only columns with any significant question marks over them are SQL_HANDLE and OBJECT_NAME...
To: dimensional.dba_at_comcast.net
CC: oracle-l_at_freelists.org
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
From: bednar_at_nbs.sk
Date: Fri, 12 Feb 2016 08:23:54 +0100
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_at_comcast.net> To: <bednar_at_nbs.sk>, "'oracle-l digest users'" <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://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.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba_at_comcast.net
View
Matthew Parker's profile on LinkedIn
www.dimensionaldba.com
From: 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-lReceived on Fri Feb 12 2016 - 17:36:41 CET