Re: understanding OPT_ESTIMATE and SCALE_ROWS !!
- From: Prem Khanna J <jprem@xxxxxxxxxxx>
- To: Marko Sutic <marko.sutic@xxxxxxxxx>, "christopherdtaylor1994@xxxxxxxxx" <christopherdtaylor1994@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Feb 2017 01:40:26 +0000
Hi Chris - Thanks for your reply.
The other thing you can do without a SQL profile is take the hint text and
add it directly to the SQL ...
Yes, would like to fix the problem (the root cause) without using profiles as
far as possible. Want to avoid the issues like the profile does not
work later in a point of time when my data volume or distribution changes . So
trying to understand what/how exactly this OPT_ESTIMATE does it stuff.
Hi Marko - Thanks for your reply.
https://blog.dbi-services.com/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly/
I went thro' this blog already. Looks like this is the only article that
explains this stuff :-) Sso , what I understand is : histograms (on skewed
columns) + estimate_percent
100% (though not practically possible everytime) or AUTO is going to give a
better insight to optimizer. right ? Looks like , that's what tuning advisor
does most of
the time ... gives better detailed abt data and it's distribution to the CBO.
Please correct me , if I am wrong.
Regards,
Prem
________________________________
From: Marko Sutic <marko.sutic@xxxxxxxxx>
Sent: Friday, February 17, 2017 4:29 AM
To: christopherdtaylor1994@xxxxxxxxx
Cc: jprem@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: understanding OPT_ESTIMATE and SCALE_ROWS !!
Prem,
check out these great from Franck Pachot:
https://blog.dbi-services.com/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly/
https://blog.dbi-services.com/oracle-sql-profile-why-multiple-optestimate/
Oracle SQL Profile: why multiple OPT_ESTIMATE? - Blog dbi
services<
https://blog.dbi-services.com/oracle-sql-profile-why-multiple-optestimate/>
blog.dbi-services.com
Why, in my opinion, SQL Profiles are just like hints: a short term workaround
that must be documented and re-evaluated at each upgrade.
Very nice explanation on the subject.
Regards,
Marko
On Thu, Feb 16, 2017 at 5:44 PM, Chris Taylor
<christopherdtaylor1994@xxxxxxxxx<
mailto:christopherdtaylor1994@xxxxxxxxx>>
wrote:
Personally, I *love* SQL Profiles - because plan stability is a DBAs best
friend.
The other thing you can do without a SQL profile is take the hint text and add
it directly to the SQL instead but that makes for some long SQL text and isn't
exactly "pretty" but works just as well.
Chris
On Thu, Feb 16, 2017 at 10:28 AM, Prem Khanna J
<jprem@xxxxxxxxxxx<
mailto:jprem@xxxxxxxxxxx>> wrote:
Hi Friends ,
My customer is on oracle R12.1. I am in the mid of tuning a SELECT SQL
statement . Used SQL Tuning advisor and it says accepting a
profile will result in 90% improvement. Before even I accept the profile, I
want to understand what it does and want to do the
same - if possible - without profiles .
I am trying to understand the OPT_ESTIMATE part of the advisor
(dba_advisor_rationale) . Hard though , as I could not find much about
it on internet. Looks like SCALE_ROWS part of it does the magic . Can anyone
explain or point me to a doc/URL which helps how to read
and understand the OPT_ESTIMATE line .
How can one give optimizer the info' ... which OPT_ESTIMATE can give ? How do
you guys approach this .
Thanks and Regards,
Prem
Other related posts:
