How to Decode SQL Profiles?

From: Steve Gardiner <>
Date: Fri, 30 Mar 2012 08:09:21 -0500
Message-ID: <>


I really like SQL tuning advisor's profile suggestions but I would like to 'decode' what they are doing. Maybe then I might make some special optimizer gathering or maybe but a hint in the code. Then disable the sql profile.  

This is a query from page 420 of Oracle Database 11g Tuning Receipes. How can I determine what this sql profile is doing?  

  extractvalue(value(a), '.') sqlprofile_hints   FROM sqlobj$ o,
  sqlobj$data d,
  table(xmlsequence(extract(xmltype(d.comp_data),'/outline_data/hint'))) a   WHERE = '&&profile_name'

  AND o. plan_id = d.plan_id
  AND o.signature = d.signature
  AND o.category = d.category
  AND o.obj_type = d.obj_type;

old 6: WHERE = '&&profile_name' new 6: WHERE = 'SYS_SQLPROF_013654e401ec0000' SQLPROFILE_HINTS

OPT_ESTIMATE(_at_"SEL$5DA710D3_1", TABLE, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1> ", SCALE_ROWS=8.743765663)
OPT_ESTIMATE(_at_"SEL$5DA710D3_1", INDEX_SCAN, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)
OPT_ESTIMATE(_at_"SEL$5DA710D3_2", TABLE, "RT"@"SEL$5DA710D3_2 <mailto:%22RT%22@%22SEL$5DA710D3_2> ", SCALE_ROWS=8.743765663)
OPT_ESTIMATE(_at_"SEL$5DA710D3_2", INDEX_SCAN, "RT"@"SEL$5DA710D3_2 <mailto:%22RT%22@%22SEL$5DA710D3_2> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)
OPT_ESTIMATE(_at_"SEL$14FC03BC", INDEX_SCAN, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)
OPT_ESTIMATE(_at_"SEL$14FC03BC", TABLE, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1> ", SCALE_ROWS=6.919621268)
OPT_ESTIMATE(_at_"SEL$14FC03BC", INDEX_FILTER, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=8.057543246)


Steve Gardiner
-- Received on Fri Mar 30 2012 - 08:09:21 CDT

Original text of this message