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

What's the solution (or workarounds) for "bind mismatch" problem (plans changing due to bind variable length)?

Thanks
BA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Koehler Sent: Tuesday, July 18, 2017 9:38 AM
To: ORACLE-L; christopherdtaylor1994_at_gmail.com Subject: Re: Looking for suggestions - how find cause of sql invalidations

Hey Chris,
just use a script by grandmaster Tanel Poder.

SQL> _at_http://blog.tanelpoder.com/files/scripts/nonshared2.sql PRINT 49s16x6k5ubwc

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Chris Taylor <christopherdtaylor1994_at_gmail.com> hat am 18. Juli 2017 um 17:37 geschrieben:
>
> 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

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Jul 19 2017 - 00:28:06 CEST