Re: v$sql - executions vs loads vs invalidations

From: Gerry Miller <gerry_at_millerandbowman.com>
Date: Tue, 01 May 2012 21:49:41 +1000
Message-ID: <4F9FCDD5.4030700_at_millerandbowman.com>



Hi GG

The DB version is 10.2.0.4. I have been looking at Document 1088239.1 and have found possible causes of the ORA-04031 including "Imbalanced Subpool Allocation". I have yet to try the suggested fix for that but in the meantime I have robbed the buffer cache of 1Gb to give to the shared pool and this has significantly reduced the reload rate. However, I think that this is just addressing the symptoms, not the problem. There was such a high level of SQL statements using literals that I was sure that setting CURSOR_SHARING=FORCE (again addressing the symptoms, not the problem) would relieve the situation, but not so. What I really want to know is why a statement would be invalidated and loaded so many time without being executed. Most of these statements have system-generated bind variables, that is, they originally had literal values, and this makes me wonder if this is an offshoot of CURSOR_SHARING=FORCE and would not be happening if the developers had used bind variables.

Thanks

Gerry     

GG wrote:
> W dniu 2012-05-01 11:41, Gerry Miller pisze:
>
>> LOADS: 2794
>> INVALIDATIONS: 2793
>> EXECUTIONS: 0
>
> You've never mentioned DB version :) but basically looks like
> statements are only parsed and never executed (application issue ?).
> You got invalidation because some dependent objects are 'manipulated'
> via (grants, ddls, stats gathering and so) ,
> loads are because of flushes which could be caused by shared pool free
> space pressure .
> For better understanding we need some info about sga size and
> allocations and ora-4031 related dumps .
> Meantime You can check
>
> *Master Note for Diagnosing ORA-4031 [ID 1088239.1]*
>
> Regards
> GregG
>
>
>
>
>
>

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

Original text of this message