| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Automatic Shared Memory question
> Yeah, I heard that too (on this list, I think). In my case,
> "KGH: NO ACCESS" consistently hogs about 800MB-1GB of my
> 2.4GB SGA though. I think maybe it just keeps it aside just
> in case it might need to add it to the buffer cache some day.
> Here is the exact definition I got from Oracle Support:
>
Well, believe or not, in addition to keeping private undo and redo buffers in shared pool, Oracle can nowadays hold some of the buffer cache there as well.
Sounds crazy? Check this!
SQL> select
2 s.ksmchptr SP_CHUNK, 3 s.ksmchsiz CH_SIZE, 4 b.obj DATAOBJ#, 5 b.ba BLOCKADDR, 6 b.blsiz BLKSIZE, 7 decode(b.class, 8 1,'data block', 9 2,'sort block', 10 3,'save undo block', 11 4,'segment header', 12 5,'save undo header', 13 6,'free list', 14 7,'extent map', 15 8,'1st level bmb', 16 9,'2nd level bmb', 17 10,'3rd level bmb', 18 11,'bitmap block', 19 12,'bitmap index block', 20 13,'file header block', 21 14,'unused', 22 15,'system undo header', 23 16,'system undo block', 24 17,'undo header', 25 18,'undo block', 26 class) BLKTYPE, 27 decode (b.state, 28 0,'free',1,'xcur',2,'scur',3,'cr', 4,'read', 29 5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory', 30 10,'mwrite',11,'donated',b.state) BLKSTATE 31 from 32 x$bh b, 33 x$ksmsp s 34 where ( 35 b.ba >= s.ksmchptr 36 and to_number(b.ba, 'XXXXXXXXXXXXXXXX') + b.blsiz < to_number(ksmchptr, 'XXXXXXXXXXXXXXXX') + ksmchsiz37 )
SP_CHUNK CH_SIZE DATAOBJ# BLOCKADDR BLKSIZE BLKTYPE BLKSTATE
---------------- ---------- ---------- ---------------- -------
-------------------- ----------
0000000387C01FE0 1269792 9001 0000000387C26000 8192 data block
xcur
9001 0000000387C28000 8192 data block
xcur
9001 0000000387C2A000 8192 data block
xcur
2 0000000387C2C000 8192 data block
xcur
9001 0000000387C2E000 8192 1st level
bmb xcur
9001 0000000387C30000 8192 2nd level
bmb xcur
9001 0000000387C32000 8192 segment
header xcur
4294967295 0000000387C34000 8192 36
xcur
4294967295 0000000387C36000 8192 36
xcur
51673 0000000387C38000 8192 data block
xcur
4294967295 0000000387C3A000 8192 36
xcur
4294967295 0000000387C3C000 8192 22
xcur
4294967295 0000000387C3E000 8192 22
xcur
37 0000000387C40000 8192 data block
xcur
4294967295 0000000387C42000 8192 22
xcur
4294967295 0000000387C44000 8192 30
xcur
4294967295 0000000387C46000 8192 30
xcur
4294967295 0000000387C48000 8192 30
xcur
573 0000000387C4A000 8192 data block
xcur
From matching SP_CHUNK and BLOCKADDR values you see that there are cache buffers which actually reside in shared pool heap.
When MMAN tries to get rid of a shared pool granule it obviously can't just flush and throw away all the object in it. As long as anybody references chunks in this granule, it cannot be completely deallocated.
Oracle has faced a decision, what to do in this case:
1) wait until all chunks aren't in use anymore - this might never happen
2) suspend the instance, relocate chunks somewhere else and update all
SGA/PGA/UGA/CGA structures for all processes accordingly - this would get
very complex
3) flush as many chunks from this shared pool granule as possible, mark them
as "KGH: NO ACCESS" that nobody else would touch them, mark corresponding
entry to DEFERRED in V$SGA_RESIZE_OPS and notify buffer cache manager, about
the new memory locations being available for use.
Oracle has gone with option 3 as option 1 wouldn't satisfy us and 2 would be very complex to implement, and it would mean a complete instance hang for seconds to minutes.
So, Oracle can share a granule between shared pool and buffer cache data. This sounds like a mess, but there is not really a better way to do it (if leaving the question, why the heck do you want to continuously reduce your shared pool size anyway, out).
This was tested on Oracle 10.2.0.2 on Solaris 10/x64
Tanel.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 22 2006 - 06:36:09 CDT
 |
 |