RE: Automatic Shared Memory question
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Aug 2006 13:17:07 +0800
> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
