Re: How to Decode SQL Profiles?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 30 Mar 2012 14:35:10 +0100
Message-ID: <4650F9431163407E81E93EA51D2A1BD4_at_Primary>

I think your best bet is to get Christian Antognini's book which has an explanation of what Oracle is doing here - but the point of profiles is to correct the optimizer arithmetic rather than fix an execution plan, so trying to translate the current effects of a profile into a fixed plan through hints is, in principle, contrary to the spirit of profiles.

As a first step, use the 'alias' option of the dbms_xplan calls to get the final query block names (and full table aliases) of the optimized query leaf blocks, because that gives you the references in the hints; then, for example, you can translate things like:

OPT_ESTIMATE(_at_SEL$5DA710D3_1, INDEX_SCAN, RT_at_SEL$1 RT_at_SEL$1, RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262) In query block sel$5da710d3_1 (and the "_1" suggests to me that you've got a concatenation in your plan), you have table (alias) RT that appeared in the original query block sel$1. This has an index rcv_transactions_n16 and the optimizer is going to use an index range scan on it, but the default arithmetic will produce the wrong estimate of rowids returned and needs to be multiplied by 3.167525262 to get a better estimate of cardinality).

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Steve Gardiner" <steve.gardiner_at_hotsos.com> To: <oracle-l_at_freelists.org> Sent: Friday, March 30, 2012 2:09 PM Subject: How to Decode SQL Profiles?

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_at_%22SEL$1> ", SCALE_ROWS=8.743765663)

OPT_ESTIMATE(_at_"SEL$5DA710D3_1", INDEX_SCAN, "RT"@"SEL$1 


<mailto:%22RT%22_at_%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)

OPT_ESTIMATE(_at_"SEL$5DA710D3_2", TABLE, "RT"@"SEL$5DA710D3_2 


<mailto:%22RT%22_at_%22SEL$5DA710D3_2> ", SCALE_ROWS=8.743765663)

OPT_ESTIMATE(_at_"SEL$5DA710D3_2", INDEX_SCAN, "RT"@"SEL$5DA710D3_2 

<mailto:%22RT%22_at_%22SEL$5DA710D3_2> ", RCV_TRANSACTIONS_N16,
SCALE_ROWS=3.167525262)

OPT_ESTIMATE(_at_"SEL$14FC03BC", INDEX_SCAN, "RT"@"SEL$1 

<mailto:%22RT%22_at_%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_at_%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




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4903 - Release Date: 03/29/12


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 30 2012 - 08:35:10 CDT