Well Oracle is changing and I believe that there are alot changes which are happening “under-the-hood”. I just got a question over Forums that can Shared Pool shrink if we are using ASMM?Well the docs say no that wont be possible. Well the answer as per docs is No,its not possible that oracle will shrink the shared pool. Another fellow poster over forums, gave this link where Tanel Poder revealed one more “hidden” thing that Oracle from 10.2 onwards , is keeping Database buffer cache chunks in the Shared Pool heap. Now my first reponse what WTH! Why they would want to do that? But Tanel explains it well. I am posting the entire entry here with the reference. Things are not changing in a “big time”.
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.
And the link is,
http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm
My head is spinning :-S.
Aman….
Hi Aman,
This is Sunil from Chennai, Thanks for your information on dbc in shared pool.
Regards,
Snil