Hey Andrew,
what do you mean with a SQL profile can become invalid?
A classic SQL profile has OPT_ESTIMATE() hints which may be resolved and used
or not but the SQL profile itself is valid all the time. Depending on
the amount of SQL profiles and SQLs you may want to "EXPLAIN PLAN FOR" them and
check the hints with event 10132. You can also detect errors in case
of custom SQL profiles.
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
Andrew Kerber <andrew.kerber@xxxxxxxxx> hat am 26. Mai 2017 um 21:13
geschrieben:
As we all know, sql profiles can become invalid if the stats on a table
change, or the layout of a table changes. Has anyone found a way to
identify when a profile is no longer valid, without actually running the
query in question?
I would like to run a daily report to identify any sql profiles that have
become invalid. Sometimes an application provider will implement a change
and a large query that we had fixed suddenly goes crazy again.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
