Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

SQL Profiles in the Data Dictionary

19 August 2008 6 Comments Written by Christian Antognini

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)
10gR1, 10gR2, 11gR1, Query Optimizer
Ad: Tanel Poder's Seminars in Europe
TOP: Downloadable Files Are Available

6 Comments

3 Pings/Trackbacks

  1. Peter Peter
    17 February 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

    Reply
  2. Christian Antognini Christian Antognini
    18 February 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

    Reply
  3. Peter Peter
    20 February 2009    

    I just got your book yesterday ;-)

    Will have a look

    Thanks!

    Peter

    Reply
  4. Buggy overhead of SQL Plan Baseline Capture « OraStory Buggy overhead of SQL Plan Baseline Capture « OraStory
    1 March 2011    

    […] surprises it’s the baseline data. See also: Kerry Osborne – Do SQL Baselines Use Hints/ Christian Antognini – SQL Profiles in the Data Dictionary Christian Antognini – Troubleshooting Oracle […]

  5. SQL Profiles in the Data Dictionary | Oradblife SQL Profiles in the Data Dictionary | Oradblife
    22 September 2013    

    […] 转自:http://www.antognini.ch/2008/08/sql-profiles-in-data-dictionary/ […]

  6. In praise of bloggers (along with sql_profiles and RAC interconnect pings) « Oracle DBA – A lifelong learning experience In praise of bloggers (along with sql_profiles and RAC interconnect pings) « Oracle DBA – A lifelong learning experience
    12 March 2014    

    […] than that. However once I had the correct keyword to search on,  Google pointed me at a blog by Christian Antognini that gave the exact query I was looking for. How he got to that information I do not know, probably […]

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

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.