SQL Profiles in the Data Dictionary

In the paper SQL Profiles (page 22) I described the data dictionary tables where the hints belonging to SQL profiles are stored. For example, with the following query it is possible to display the hints associated to the SQL profile named opt_estimate.

SQL> SELECT attr_val
  2  FROM sys.sqlprof$ p, sys.sqlprof$attr a
  3  WHERE p.sp_name = 'opt_estimate'
  4  AND p.signature = a.signature
  5  AND p.category = a.category;

ATTR_VAL
---------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", JOIN, ("T"@"SEL$1", "CH"@"SEL$1"), SCALE_ROWS=276.7754079)
OPT_ESTIMATE(@"SEL$1", TABLE, "CH"@"SEL$1", SCALE_ROWS=40.15499105)

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. The following query shows how to query the new tables. Note that since hints are stored in XML format, a conversion is necessary to have a readable output.

SQL> SELECT extractValue(value(h),'.') AS hint
  2  FROM sys.sqlobj$data od, sys.sqlobj$ so,
  3  table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
  4  WHERE so.name = 'opt_estimate'
  5  AND so.signature = od.signature
  6  AND so.category = od.category
  7  AND so.obj_type = od.obj_type
  8  AND so.plan_id = od.plan_id;

HINT
---------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "CH"@"SEL$1", SCALE_ROWS=39.20843548)
OPT_ESTIMATE(@"SEL$1", JOIN, ("T"@"SEL$1", "CH"@"SEL$1"), SCALE_ROWS=281.2054138)
OPTIMIZER_FEATURES_ENABLE(default)

3 Comments

  1. Peter's Gravatar Peter
    February 17, 2009    

    Hi Christian

    In grid control if we look at sql tuning advisor recommendations there is an option to show recommended execution plan and original plan. Do you know if with DBMS API is possible to obtain these outputs?

    Thank you

    Peter

  2. February 18, 2009    

    Hi Peter

    Yes, when you generate the report with dbms_sqltune.report_tuning_task both execution plans are shown. In my book (pages 268-270) there is an example. If you don’t own a copy, you can preview it through Google Books.

    Cheers,
    Chris

  3. Peter's Gravatar Peter
    February 20, 2009    

    I just got your book yesterday ;-)

    Will have a look

    Thanks!

    Peter

  1. Buggy overhead of SQL Plan Baseline Capture « OraStory on March 1, 2011 at 17:41
  2. SQL Profiles in the Data Dictionary | Oradblife on September 22, 2013 at 08:42
  3. In praise of bloggers (along with sql_profiles and RAC interconnect pings) « Oracle DBA – A lifelong learning experience on March 12, 2014 at 18:36

Leave a Reply

Your email address will not be published. Required fields are marked *