Re: understanding OPT_ESTIMATE and SCALE_ROWS !!

Hey Prem,
just in addition to what Chris said. Oracle does these things all the time (on-the-fly) - keyword "Cardinality / Statistics Feedback".

Just have a look into view v$sql_reoptimization_hints.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals  

> Prem Khanna J <jprem_at_outlook.com> hat am 17. Februar 2017 um 02:40 geschrieben:
>
> 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