Re: How to Decode SQL Profiles?
Date: Fri, 30 Mar 2012 17:23:56 +0100
Message-ID: <E4D208F01452416BB5B7B75ACD52C73A_at_Primary>
Unless the data, statistics, and queries on the development system are consistent and an appropriate model for the production system then generating profiles on development isn't likely to be a robust strategy for producing production profiles. On the other hand, if you think the profiles produce plans that would be very good on production, and since you're running 11g, why not get the profiles to run the queries, then pull the SQL Baselines from memory and use those on production ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Venkat Perumal" <venpdba_at_yahoo.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Friday, March 30, 2012 2:59 PM Subject: Re: How to Decode SQL Profiles?
Hi Jonathan,
I have implemented SQL profiles to fix key
performance issues after the 11g upgrade in the development environment and
planning to promote to production. How reliable is SQL profile?. It would
hurt us badly if something goes
wrong with the SQL profile explain plan.
Would you recommend modifying the source SQL's rather than depending on SQL profile to improve the performance?
Thank you
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: oracle-l_at_freelists.org
Sent: Friday, March 30, 2012 9:35 AM
Subject: Re: How to Decode SQL Profiles?
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 ----- 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-lReceived on Fri Mar 30 2012 - 11:23:56 CDT