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

Scratch that last comment/email. SQL was buggy that I was using to check the latest snapshot.

On Tue, Jul 18, 2017 at 12:30 PM, Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> 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_freeli
>> sts.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