Re: High shared pool usage
- From: Ram Raman <veeeraman@xxxxxxxxx>
- To: mm@xxxxxxxxxxxxxxxx
- Date: Tue, 27 Sep 2011 16:55:16 -0500
Thanks Marcus:
POOL NAME BYTES
------------ ---------------------------------------- --------------------
shared pool KGH: NO ACCESS 5,131,392,192
shared pool sql area 4,664,912,584
shared pool free memory 4,503,302,200
shared pool CCursor 913,006,592
shared pool PCursor 717,546,600
shared pool library cache 531,817,048
shared pool gcs resources 256,661,792
shared pool kglsim object batch 151,151,952
Not very many child cursors:
SQL_ID CHILDCOUNT
------------- ----------
5js8dhamyt3cw 6
96g93hntrzjtr 6
6wh0q2tfwvssz 6
cqv52axncuvzv 6
fuscam04hhdqr 5
grwydz59pu6mc 5
7ng34ruy5awxq 5
260w39duxtz9u 5
15zytu14qzw6p 5
83taa7kaw59c1 5
2q93zsrvbdw48 5
011bkqb0xaxpp 5
at3ryvuft2f40 5
db78fxqxwxt7r 5
4yvsj6a508pgf 4
09vwa8mu0zfqa 4
...
There are about 1300 cursors for which the child count is greater than 1.
On Tue, Sep 27, 2011 at 2:24 AM, Marcus Mönnig <mm@xxxxxxxxxxxxxxxx> wrote:
> Hello Ram!
>
> What is allocating the memory in the shared pool?
>
> SELECT *
> FROM V$SGASTAT
> WHERE pool='shared pool' ORDER BY bytes DESC;
>
> If allocations for "KGH: NO ACCESS" are very high, you are seeing an ASMM
> bug.
>
> If "sql area" is very high, check if the same SQL is parsed again and
> again and thus lots of child cursors exists for the same SQL (another
> bug available in 10.2):
>
>
> SELECT sql_id,count(*) childcount
> FROM V_$SQL_SHARED_CURSOR
> group by sql_id
> order by childcount desc;
>
> HTH,
> Marcus
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
