Re: v$sql - executions vs loads vs invalidations
- From: Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>
- To: GG <grzegorzof@xxxxxxxxxx>
- Date: Tue, 01 May 2012 21:49:41 +1000
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
>
>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
