Re: Looking for suggestions - how find cause of sql invalidations

One thing that is interesting - I created a sql_profile (for giggles) for this SQL, and it's not had a single invalidation since. *scratches head*

Chris

On Tue, Jul 18, 2017 at 12:05 PM, Mark W. Farnham <mwf_at_rsiz.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_
> 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
>

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