Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

sabato, maggio 06, 2017

SQL Profile: Case 2 - Part 2: Data Dictionary

SQL Profile: Case 2 - Part 1: Create/Execute/Report/Accept

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 2 - Part 1: Create/Execute/Report/Accept

Nessun commento: