How to Decode SQL Profiles?

From: Steve Gardiner <steve.gardiner_at_hotsos.com>
Date: Fri, 30 Mar 2012 08:09:21 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01B8A291_at_WIN02.hotsos.com>

Oracle-l's  

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?  

 SELECT
  extractvalue(value(a), '.') sqlprofile_hints   FROM sqlobj$ o,
  sqlobj$data d,
  table(xmlsequence(extract(xmltype(d.comp_data),'/outline_data/hint'))) a   WHERE o.name = '&&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 o.name = '&&profile_name' new 6: WHERE o.name = '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)

OPTIMIZER_FEATURES_ENABLE(default)  

Thanks  

Steve Gardiner
Hotsos
steve.gardiner_at_hotsos.com
--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 30 2012 - 08:09:21 CDT