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: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
Prem Khanna J <jprem@xxxxxxxxxxx> 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
