Re: v$sql - executions vs loads vs invalidations
Date: Tue, 01 May 2012 21:49:41 +1000
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.
> 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]*