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@xxxxxxxxxxx
To: bednar@xxxxxx; dimensional.dba@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Subject: RE: Load SQL PLan into SQL Plan Baselines from AWR
From: bednar@xxxxxx
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@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
