Re: understanding OPT_ESTIMATE and SCALE_ROWS !!

From: JP . <jprem_at_outlook.com>
Date: Sun, 19 Feb 2017 12:36:40 +0000
Message-ID: <TY1PR01MB0538D1A211D03932AB6DEACEBC5F0_at_TY1PR01MB0538.jpnprd01.prod.outlook.com>

Many Thanks Chris & Stefan [😊]

So if I want to give the best possible info' to optimizer (without using sql profiles) - it would be a combination of :

• histograms (on skewed columns)
• estimate_percent 100% (though not practically possible everytime) or AUTO
• io calibration ?! have not did this . Curious to know if this is being done as best practice ? any thoughts !
• anything else ?!

Regards,

~ JP ~

---

From: Christian Antognini <Christian.Antognini_at_trivadis.com> Sent: Friday, February 17, 2017 6:20 PM To: jprem_at_outlook.com; oracle-l_at_freelists.org Subject: RE: understanding OPT_ESTIMATE and SCALE_ROWS !!

Hi Prem

The key thing to realize is that the query optimizer is not always able to correctly estimate the number of rows that are returned by a specific operation. And that, even though all available object statistics are available and up-to-date. The typical case to mention is the join between two tables... based on object statistics the query optimizer can only *guess* what the number of rows resulting from a join is.

With the OPT_ESTIMATE hint, it's possible to inform the query optimizer that some of its estimations are wrong, and by how much (SCALE_ROWS) or what to use instead (ROWS).

For example:

• the following tells the query optimizer to scale up the estimation of the operation that accesses the T1 table through an index by about 478 times

OPT_ESTIMATE(_at_"SEL$1", INDEX_SCAN, "T1"_at_"SEL$1", "T1_COL1_COL2_I", SCALE_ROWS=477.9096254)

• the following tells the query optimizer to use 20 instead of the actual estimation for the access to the KOKBF$0 table

OPT_ESTIMATE (_at_"SEL$2" TABLE "KOKBF$0"_at_"SEL$2" ROWS=20.000000 )

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008/2014 http://top.antognini.ch

---

From: Prem Khanna J <jprem_at_outlook.com> Subject: Re: understanding OPT_ESTIMATE and SCALE_ROWS !! 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

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




image/png attachment: OutlookEmoji-😊.png

Received on Sun Feb 19 2017 - 13:36:40 CET