RE: Automatic Shared Memory question

> 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

Other related posts: