RE: Automatic Shared Memory question

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 22 Aug 2006 19:36:09 +0800
Message-id: <002e01c6c5df$2a004f00$6501a8c0@windows01>

> 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') + ksmchsiz

 37 )
 38 and s.ksmchcom = 'KGH: NO ACCESS'
 39 order by s.ksmchptr, b.ba;

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-l

Received on Tue Aug 22 2006 - 06:36:09 CDT