v$sql - executions vs loads vs invalidations

From: Gerry Miller <gerry_at_millerandbowman.com>
Date: Tue, 01 May 2012 19:41:33 +1000
Message-ID: <4F9FAFCD.3030304_at_millerandbowman.com>



Colleagues,

I am currently working on a system that is experiencing frequent ORA-04031 errors. Investigation found that they have a LOT of SQL with literal values and while I suggested that they fix the code to use bind variables I meantime set CURSOR_SHARING=FORCE in an attempt to avoid these errors. This had virtually no effect and while further investigating I found a number of rows in v$sql with that were similar to this:
LOADS: 2794
INVALIDATIONS: 2793
EXECUTIONS: 0 How I read this is that this statement was loaded 2794 times, invalidated 2793 but never executed, but I think this opinion may stem from my ignorance. This is one of hundreds of such statements.

Can anyone enlighten me?

Regards

Gerry Miller

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2012 - 04:41:33 CDT

Original text of this message