Archive for the ‘Research’ Category
Buffer Cache In Shared Pool?
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….
Create Table Making Buffers Dirty….
I always thought that the creation of table is a mere data dictionary update. I mean to say that the data dictionary , dba_tables, OBJ$ and other similar ones would get updated when we create a table. The logic was that its just a definition so where else it would be going? Its correct too that its just a definition but still for couple of things, Oracle does access DataBuffer Cache too when it creates a table and (here is what I didnt even think to see) not just accesses it but also makes couple of its block dirty too. Doesn’t make a sense,well ok let the numbers,bits, flags speak then. Have a look,
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 22 00:24:26 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t (a number);
Table created.
SQL> select object_id ,object_name from user_objects where object_name='T';
OBJECT_ID OBJECT_NAME
---------- ----------
72217 T
Here comes the interesting part:
SQL> SELECT b.block#,b.class#,b.status, object_name, object_type, dirty "Dirty" FROM v$bh b,
2 dba_objects o WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';
BLOCK# CLASS# STATUS OBJECT_NAME OBJECT_TYPE D
---------- ---------- ---------- -------------------- ------------------- -
3746 9 xcur T TABLE Y
3745 8 xcur T TABLE Y
3747 4 xcur T TABLE Y
SQL>
The buffers from the table T are marked Dirty and are accessed in the XCUR mode which means the blocks are accessed in the Exclusive Current mode and is representing that these blocks are going to be modified. The description of the buffer status which is shown from V$BH(or X$BH.status) from 11gr1 docs is given here,
STATUS |
VARCHAR2(6) |
Status of the buffer:
|
Here is the link for the same in docs.http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_1059.htm#REFRN30029
A much better description of the same is mentioned in the 9iR2 docs,in the RAC guide. The link is mentioned below and also the description,
http://download.oracle.com/docs/cd/B10501_01/rac.920/a96597/pslkgdtl.htm#19780
Block Access Modes and Buffer States
An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance. The buffer state of a block relates to the access mode of the block. For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.
To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view. The V$BH view provides information about the block access mode and their buffer state names as follows:
- With a block access mode of
NULLthe buffer state name isCR–An instance can perform a consistent read of the block. That is, if the instance holds an older version of the data. - With a block access mode of
Sthe buffer state name isSCUR–An instance has shared access to the block and can only perform reads. - With a block access mode of
Xthe buffer state name isXCUR–An instance has exclusive access to the block and can modify it. - With a block access mode of
NULLthe buffer state name isPI–An instance has made changes to the block but retains copies of it as past images to record its state before changes.
Only the SCUR and PI buffer states are Real Application Clusters-specific. There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time. To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.
For example, if another instance requests read access to the most current version of the same block, then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance, and keeps a PI buffer if the buffer contained a dirty block.
At this point, the first instance has the current block and the requesting instance also has the current block in shared mode. Therefore, the role of the resource becomes global. There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.
So the blocks are meant for the modification and hence they are marked dirty after being modified. The same is confirmed from the query written by Jonathan Lewis,
SQL> select
decode(bitand(flag,power(2,00)),0,'No','Yes' ) buffer_dirty,
decode(bitand(flag,power(2,01)),0,'No','Yes' ) about_to_modify,
decode(bitand(flag,power(2,02)),0,'No','Yes' ) mod_started,
decode(bitand(flag,power(2,03)),0,'No','Yes' ) block_has_been_logged,
decode(bitand(flag,power(2,04)),0,'No','Yes' ) temp_data,
decode(bitand(flag,power(2,05)),0,'No','Yes' ) being_written,
decode(bitand(flag,power(2,06)),0,'No','Yes' ) waiting_for_write,
decode(bitand(flag,power(2,07)),0,'No','Yes' ) checkpoint_wanted,
decode(bitand(flag,power(2,08)),0,'No','Yes' ) recovery_reading,
decode(bitand(flag,power(2,09)),0,'No','Yes' ) unlink_from_lock,
decode(bitand(flag,power(2,10)),0,'No','Yes' ) down_grade_lock,
decode(bitand(flag,power(2,11)),0,'No','Yes' ) cross_instance_write,
decode(bitand(flag,power(2,12)),0,'No','Yes' ) reading_as_CR,
decode(bitand(flag,power(2,13)),0,'No','Yes' ) gotten_in_current_mode,
decode(bitand(flag,power(2,14)),0,'No','Yes' ) stale,
decode(bitand(flag,power(2,15)),0,'No','Yes' ) deferred_ping,
decode(bitand(flag,power(2,16)),0,'No','Yes' ) direct_access,
decode(bitand(flag,power(2,17)),0,'No','Yes' ) moved_to_lru_tail,
decode(bitand(flag,power(2,18)),0,'No','Yes' ) ignore_redo,
decode(bitand(flag,power(2,19)),0,'No','Yes' ) only_sequential_access,
decode(bitand(flag,power(2,20)),0,'No','Yes' ) unused_0x100000,
decode(bitand(flag,power(2,21)),0,'No','Yes' ) re_write_needed,
decode(bitand(flag,power(2,22)),0,'No','Yes' ) flushed,
decode(bitand(flag,power(2,23)),0,'No','Yes' ) resilvered_already,
decode(bitand(flag,power(2,24)),0,'No','Yes' ) ckpt_writing,
decode(bitand(flag,power(2,25)),0,'No','Yes' ) redo_since_read,
decode(bitand(flag,power(2,26)),0,'No','Yes' ) unused_0x4000000,
decode(bitand(flag,power(2,27)),0,'No','Yes' ) unused_0x8000000,
decode(bitand(flag,power(2,28)),0,'No','Yes' ) unused_0x10000000,
decode(bitand(flag,power(2,29)),0,'No','Yes' ) unused_0x20000000,
decode(bitand(flag,power(2,30)),0,'No','Yes' ) unused_0x40000000,
decode(bitand(flag,power(2,31)),0,'No','Yes' ) unused_0x80000000
from x$bh where obj=72217;
BUF ABO MOD BLO TEM BEI WAI CHE REC UNL DOW CRO REA GOT STA DEF DIR MOV IGN ONL UNU RE_ FLU RES CKP RED UNU UNU UNU UNU UNU UNU
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
Yes No No No No No No No No No No No No No No No No No No No No No No No No Yes No No No No No No
Yes No No No No No No No No No No No No No No No No No No No No No No No No Yes No No No No No No
Yes No No No No No No No No No No No No No No No No No No No No No No No No Yes No No No No No No
So indeed the buffers are marked are as dirty only. But the buffers are supposed to be marked dirty when they are changed and with the create table,what’s going on which has made them change. Each buffer belongs to a particular classLets try to find out the class description of these buffers using their numbers.The block classes are described here*,
| Block Class | Description |
| 1 | Data block |
| 2 | Sort block |
| 3 | Save undo block |
| 4 | Segment header |
| 5 | Save undo header |
| 6 | Free list |
| 7 | Extent map |
| 8 | 1st level bitmap block |
| 9 | 2nd level bitmap block |
| 10 | 3rd level bitmap block |
| 11 | Bitmap block |
| 12 | Bitmap index block |
| 13 | File header block |
| 14 | Unused |
| 15 | System undo block |
| 16 | System undo block |
| 17 | Undo header |
| 18 | Undo block |
So according to the result that we have got, the blocks belong to the classes 4,8,9 and these numbers represent the classes(in the same order ),Header Block,1st level Bitmap Block,2nd Level Bitmap Block.
When oracle creates a table, it has to allocate a segment header block. This is done by picking up a block from a cache,formatting it,writing into it the new description of the data segment.As it is written so it is marked dirty and subsequently is written to the datafile.
The Automatic Segment Space Management allocates 3 levels of the blocks for example, Level1 , Level2 , Level3. The level 3 is the bitmap header block which resides in the segment header. This is linked to the Level 2 block. This block is used to search Level 1 which is always the first block in the extent. Level 1 marks the blocks for their respective status. So it appears , Oracle does the same what it does for the Segment header block to the Bitmap Blocks i.e. acquires two blocks from the cache , marks as Bitmap blocks, writes into them and marks them dirty. So this is the reason that we get the blocks marked as dirty even when we have not done anything by ourselves to change them. For me it was a new concept as I was not aware about it before. I would still try to dig upon more and will try to post more as and when I shall find it out. Heartiest thanks to Jonathan Lewis for helping me to understand it.
References,
http://www.juliandyke.com/Internals/BlockClasses.html
http://www.jlcomp.demon.co.uk/buf_flag.html
Aman….