Are you elaborate by what you mean when you say "Bind Mismatch problem" ?
Bind mismatches are perfectly normal unless caused by a bug.
or... are there mismatches caused by the application where one or more
queries has large numbers of varchar2 bind variables that will vary in
length causing bind graduation? If it is that very specific use case, it
is causing issues and that wasting of extra memory for all of your child
cursors is not an issue, you can set event 10503 where the level is the
bind length. e.g. alter system set events '10503 trace name context
forever, level 4000';
On Wed, Jul 19, 2017 at 8:28 AM, Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
wrote:
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stefan Koehler
Sent: Tuesday, July 18, 2017 9:38 AM
To: ORACLE-L; christopherdtaylor1994@xxxxxxxxx
Subject: Re: Looking for suggestions - how find cause of sql invalidations
Hey Chris,
just use a script by grandmaster Tanel Poder.
SQL> @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: @OracleSK
Chris Taylor <christopherdtaylor1994@xxxxxxxxx> hat am 18. Juli 2017 um17:37 geschrieben:
is happening (such as stats or table ddl).
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
for a 2 hour period this morning.
We're having some library cache/shared pool thrashing and I ran an AWR
figure out why it's getting invalidated.
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
dba_hist_active_sess_history to get the full history of the sessions that
So, far I've checked v$active_session_history and
have executed that SQL_ID.
anything like that) so it appears to be something outside those sessions
Nothing in those sessions appear to be doing any type of DDL (stats or
causing the cursor invalidations.
timestamps that would be related.
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
hasn't been analyzed during the period of invalidations.
I've checked last_analyzed from DBA_TAB_PARTITIONS and that the table
from within pl/sql.
Much of the SQL being executed against this table is "EXECUTE IMMEDIATE"
TABLE" if the condition is met (I can't tell if that condition is being met
One of the code blocks does have an IF statement that would do a "LOCK
however). Would a LOCK TABLE cause invalidations?
--
Any suggestions on how to crack this particular egg?
Chris
//www.freelists.org/webpage/oracle-l