RE: Automatic Shared Memory question

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 23 Aug 2006 13:17:07 +0800
Message-id: <008401c6c673$6122b100$6501a8c0@windows01>

> I wonder if those buffers stored in the shared pool are being
> reported twice in v$sgastat - once under "KGH: NO ACCESS" and
> again under "buffer_cache"? That would explain why
> sum(bytes) from v$sgastat is so much larger than my
> sga_target & sga_max_size. I'll see if I can find a way to
> confirm this is what's happening . . .

Check this:

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

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

         1 30 120 <<<--- 120 MB of granules have been marked as shared pool ones

         6 14 56 <<<--- 56 MB of granules have been marked as buffer cache ones

         2          1          4
         3          1          4

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

        MB

---

        60 <<<--- however v$sgastat says buffer cache is really 60 MB

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

        MB

---

 120.00457 <<<--- shared pool total is 120MB

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

        MB

---

116.448845 <<<--- but about 3,6MB of it is not usable for shared pool objects

• the rest 400kB is still used by shared pool objects in this granule

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

BYTES/1048576

---

          116 <<<--- v$sgainfo has somewhat better idea what's really going on in shared pool

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

Tanel.

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

Received on Wed Aug 23 2006 - 00:17:07 CDT