All informations about SQL Profile, are saved into Data Dictionary. Obiously.
Those are some DBA_ views from the 12cR2 reference manual:
dba_advisor_actions
dba_advisor_recommendations
dba_advisor_findings
dba_advisor_rationale
Using this query you can read somethings
col owner for a10
col action_message for a20
col finding_message for a20
col more_info for a30
col finding_name for a20
col RATIONALE_MESSAGE for a25
col ATTR5 for a40
col raccomandation_type for a15
col RATIONAL_TYPE for a15
set pages 99
SELECT
a.owner
, t.rationale_id
, a.message action_message
, r.type raccomandation_type
, f.finding_name
, f.type finding_type
, f.message finding_message
, f.more_info
, t.message rationale_message
, t.type rational_type
, t.attr5
FROM
dba_advisor_actions a
, dba_advisor_recommendations r
, dba_advisor_findings f
, dba_advisor_rationale t
WHERE
a.task_name = 'task_select'
AND a.task_id = r.task_id
AND a.rec_id = r.rec_id
AND a.task_id = t.task_id
AND a.rec_id = t.rec_id
AND f.task_id = r.task_id
AND f.finding_id = r.finding_id
ORDER BY
t.rationale_id;
You can find the output here (quey 1, section).
Interesting thing is that SQL Profile is just a "HINT" saved into Data Dictionary. This HINT is visible queryng SQLOBJ$DATA, table.
col outline_hints for a30
select hint as outline_hints
from (select p.name, p.signature, p.category, row_number()
over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, dba_sql_profiles p,
table(xmlsequence(extract(xmltype(sd.comp_data),
'/outline_data/hint'))) t
where sd.obj_type = 1
and p.signature = sd.signature
and p.category = sd.category
and p.name like ('profile_7srkyyv9jxhzm'))
order by row_num;
OUTLINE_HINTS
------------------------------
PARALLEL
Reference.
You can find more about HINTs of SQL Profile, in a Christian Antognini's pdf (page 22), and in him another post. In the pdf Antognini show the reletionship between data dictionary views. Keep in mind that "As of Oracle Database 11g the previous query can no longer be used. In fact, the data dictionary has been changed. The tables SQLPROF$ and SQLPROF$ATTR no longer exist. As of Oracle Database 11g the information is stored in the tables SQLOBJ$ and SQLOBJ$DATA".
Also, there are a lot of post by Kerry Osborne. Here I link just 3 of them:
- How to Lock SQL Profiles Generated by SQL Tuning Advisor (November 30, 2010)
- Single Hint SQL Profiles (February 1, 2010)
- SQL Profile (April 4, 2009)
SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept
Nessun commento:
Posta un commento