Re: v$sql - executions vs loads vs invalidations

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

Other related posts: