RE: Automatic Shared Memory question

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Wed, 23 Aug 2006 16:02:02 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1D7A@NT15.oneneck.corp>

Thanks again Tanel. Here are my query results, which appear to confirm the hypothesis that v$sgastat is double-reporting those shared pool granules that are being used for the buffer cache. I also included the contents of my single row in x$ksmsp_dsnew.

SQL> select grantype, count(*) GRANULES, sum(gransize)/1048576 MB from x$ksmge group by grantype;

GRANTYPE GRANULES MB
---------- ---------- ----------

         6         30        480
         1        116       1856
         3          1         16
         2          4         64

SQL> select sum(bytes)/1048576 MB from v$sgastat where pool = 'shared pool';

1856.27834

SQL> select bytes / 1048576 MB from v$sgastat where name = 'buffer_cache';

1392

SQL> select sum(bytes)/1048576 MB from v$sgastat where pool = 'shared pool' and name != 'KGH: NO ACCESS';

961.987656

SQL> select bytes/1048576 from v$sgainfo where name = 'Shared Pool Size';

BYTES/1048576

944

SQL> select * from x$ksmsp_dsnew;

ADDR                   INDX    INST_ID DSCNT_KGHDSNEW CURSIZ_KGHDSNEW

TARSIZ_KGHDSNEW TMSTMP_KGHDSNEW EXAM_KGHDSNEW
---------------- ---------- ---------- -------------- ---------------

--------------- ---------------- 0700000010000058 0 1 3 110 0 0 070000004C000000

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder Sent: Tuesday, August 22, 2006 10:17 PM
To: 'ORACLE-L'
Subject: RE: Automatic Shared Memory question

Btw, I'm interested, how many lines do you see in x$ksmsp_dsnew ?

Tanel.

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



Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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

Received on Wed Aug 23 2006 - 18:02:02 CDT