Re: How to Decode SQL Profiles?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2012 17:23:56 +0100
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@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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(@SEL$5DA710D3_1, INDEX_SCAN, RT@SEL$1 RT@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@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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(@"SEL$5DA710D3_1", TABLE, "RT"@"SEL$1
<mailto:%22RT%22@%22SEL$1> ", SCALE_ROWS=8.743765663)
OPT_ESTIMATE(@"SEL$5DA710D3_1", INDEX_SCAN, "RT"@"SEL$1
<mailto:%22RT%22@%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)
OPT_ESTIMATE(@"SEL$5DA710D3_2", TABLE, "RT"@"SEL$5DA710D3_2
<mailto:%22RT%22@%22SEL$5DA710D3_2> ", SCALE_ROWS=8.743765663)
OPT_ESTIMATE(@"SEL$5DA710D3_2", INDEX_SCAN, "RT"@"SEL$5DA710D3_2
<mailto:%22RT%22@%22SEL$5DA710D3_2> ", RCV_TRANSACTIONS_N16,
SCALE_ROWS=3.167525262)
OPT_ESTIMATE(@"SEL$14FC03BC", INDEX_SCAN, "RT"@"SEL$1
<mailto:%22RT%22@%22SEL$1> ", RCV_TRANSACTIONS_N16, SCALE_ROWS=3.167525262)
OPT_ESTIMATE(@"SEL$14FC03BC", TABLE, "RT"@"SEL$1 <mailto:%22RT%22@%22SEL$1>
", SCALE_ROWS=6.919621268)
OPT_ESTIMATE(@"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@xxxxxxxxxx
--
//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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
