Re: understanding OPT_ESTIMATE and SCALE_ROWS !!

On 02/16/2017 11:28 AM, Prem Khanna J 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

I replied to a similar question, not so long ago. SCALE_ROWS is an option to OPT_ESTIMATE hint. That hint is explained on the Pythian page:

https://www.pythian.com/blog/oracles-opt_estimate-hint-usage-guide/

Another good article, with examples is here:

http://www.oaktable.net/content/oracles-sql-tuning-pack-part-2

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


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