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@xxxxxxxxxxxx>
Sent: Friday, February 17, 2017 6:20 PM
To: jprem@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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(@"SEL$1", INDEX_SCAN, "T1"@"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 (@"SEL$2" TABLE "KOKBF$0"@"SEL$2" ROWS=20.000000 )
HTH
Chris Antognini
Troubleshooting Oracle Performance, Apress 2008/2014
http://top.antognini.ch
------------------------------
From: Prem Khanna J <jprem@xxxxxxxxxxx>
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 ...
https://blog.dbi-services.com/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly/