Re: High shared pool usage

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 27 Sep 2011 16:55:16 -0500
Message-ID: <CAHSa0M0tLBmS1nqqhyfkLQEH24HvAEF7dSsumTmR_zk8R8_fgg_at_mail.gmail.com>

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_at_marcusmoennig.de> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 27 2011 - 16:55:16 CDT