Ok, no AMM in use and result cache involved. Thanks for the input.
Going through those 2 sql scripts that were sent to see what's what.
Thanks guys. I'll post an update shortly.
Chris
On Tue, Jul 18, 2017 at 12:05 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Two things (from probably a lot of possibilities) come immediately to mind:
1) If automatic memory allocation is shuttling a granule between
shared pool and the buffer cache back and forth and your sql is in that
granule, bad luck. (If you do have a chronic back and forth between shared
and the buffer cache of a small number of granules, perhaps you’ve found
your sweet spot, should bump them each a tad and turn off auto.)
2) If you have any results cache (dbms server) components of the
query, that might do something.
I’m not sure if anyone has compiled a consolidated list of all the
possible reasons for invalidation (and it would surely be an evolving list
across added features and patches), but if anyone has something like that
it deserves publication of the url.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Chris Taylor
*Sent:* Tuesday, July 18, 2017 11:37 AM
*To:* ORACLE-L
*Subject:* Looking for suggestions - how find cause of sql invalidations
DB: 12.1.0.2
OS: Red Hat Enterprise Linux Server release 6.8 (x86_64)
TL;DR: How to find cause of high sql invalidations when nothing obvious
is happening (such as stats or table ddl).
We're having some library cache/shared pool thrashing and I ran an AWR for
a 2 hour period this morning.
In the library cache activity, for SQL AREA, I see:
sql_id = 49s16x6k5ubwc
Invalidations_Total = 2065
Invalidations_Delta = 1287
This SQL_ID is executed many, many times every hour and I'm trying to
figure out why it's getting invalidated.
So, far I've checked v$active_session_history and
dba_hist_active_sess_history to get the full history of the sessions that
have executed that SQL_ID.
Nothing in those sessions appear to be doing any type of DDL (stats or
anything like that) so it appears to be something outside those sessions
causing the cursor invalidations.
There is one table involved in the query - and it has 1444 partitions.
I checked LAST_DDL_TIME from dba_objects and I don't see any DDL
timestamps that would be related.
I've checked last_analyzed from DBA_TAB_PARTITIONS and that the table
hasn't been analyzed during the period of invalidations.
Much of the SQL being executed against this table is "EXECUTE IMMEDIATE"
from within pl/sql.
One of the code blocks does have an IF statement that would do a "LOCK
TABLE" if the condition is met (I can't tell if that condition is being met
however). Would a LOCK TABLE cause invalidations?
Any suggestions on how to crack this particular egg?
Chris
