Oracle Internals
JulianDyke.com

Welcome

Consultancy

Seminars

Blog

Presentations

Diagnostics

Internals

 

Block Classes

Every Oracle block belongs to a block class. Block classes are used to identify undo segment within redo records. Block classes are also used to produce more granular output in the V$WAITSTAT dynamic performance view.

The following table shows the block classes in Oracle 10.2

Block ClassDescription
1Data block
2Sort block
3Save undo block
4Segment header
5Save undo header
6Free list
7Extent map
81st level bitmap block
92nd level bitmap block
103rd level bitmap block
11Bitmap block
12Bitmap index block
13File header block
14Unused
15System undo block
16System undo block
17Undo header
18Undo block

Block classes above 16 are reserved for undo segments. The block class is dependent on the undo segment number. Each undo segment has two block classes; one for the undo segment header and the other for undo segment blocks. The following table shows the first few block classes.

Undo SegmentUndo HeaderUndo Block
11718
21920
32122
42324
52526
62728
72930
83132
93334
103536
...............

The block class is not stored on individual blocks within the database. However, it is used in a couple of diagnostic areas

V$WAITSTAT

This dynamic performance view returns wait statistics by block class. V$WAITSTAT is defined as follows

Column NameData Type
CLASSVARCHAR2(18)
COUNTNUMBER
TIMENUMBER

V$WAITSTAT is derived from X$KCBWAIT as follows


SELECT view_definition FROM v$waitstat
WHERE view_name = 'GV$WAITSTAT';

SELECT 
  inst_id,
  DECODE (indx,
    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 bmb',
    9,'2nd level bmb',
    10,'3rd level bmb',
    11,'bitmap block',
    12,'bitmap index block',
    13,'file header block',
    14,'unused',
    15,'system undo header',
    16,'system undo block', 
    17,'undo header',
    18,'undo block'
  ),
  count,
  time
FROM x$kcbwait
WHERE indx != 0;

X$KCBWAIT has the following columns:

Column NameData Type
ADDRRAW(4)
INDXNUMBER
INST_IDNUMBER
COUNTNUMBER
TIMENUMBER

The INDX column of X$KCBWAIT is the block class

Redo Log Dump Files

The block class is stored in the change record header. The block class also appears in redo log dump files

For example, for the start of a transaction the redo operation is 5.2. This operation updates the undo segment header. An unused slot is selected and updated to indicate that it is in use. The following is an example from a redo log dump


CHANGE #11 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x0000.0026762c SEQ:  1 OP:5.2
ktudh redo: slt: 0x001e sqn: 0x000003bd flg: 0x0012 siz: 104 fbi: 0
            uba: 0x008001ae.02ee.23    pxid:  0x0000.000.00000000

In the above example the class (CLS) is 23. From the table above, we can determine that this transaction is using undo segment number is 4. We can also see that the slot number (slt) is 0x001e (30 in decimal) and the sequence number (sqn) is 0x3bd (957 in decimal).

For this transaction, the XID will be

        xid: 0x0004.01e.000003bd

Each subsequent change within the same transaction will specify the transaction ID


CHANGE #15 TYP:0 CLS:24 AFN:2 DBA:0x008001ae OBJ:4294967295 SCN:0x0000.00267707 SEQ:  1 OP:5.1
ktudb redo: siz: 64 spc: 4092 flg: 0x0022 seq: 0x02ee rec: 0x24
            xid:  0x0004.01e.000003bd  
ktubu redo: slt: 30 rci: 35 opc: 11.1 objn: 16916 objd: 16916 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
op: C  uba: 0x008001ae.02ee.23
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x010018ad  hdba: 0x010018ab
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)

In the redo log dump file, the XID is stored in the ktudb structure. In the above example the XID is 0x0004.01e.000003bd

Every transaction ends with a COMMIT operation which is represented by a 5.4 redo operation. In the event of a ROLLBACK operation for the transaction being requested, the changes are rolled back in reverse order. The final operation of the rollback segment is a 5.4 redo operation which effectively performs a commit.


CHANGE #13 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x0000.00267707 SEQ:  1 OP:5.4
ktucm redo: slt: 0x001e sqn: 0x000003bd srt: 0 sta: 9 flg: 0x2 
ktucf redo: uba: 0x008001ae.02ee.24 ext: 2 spc: 4026 fbi: 0 

In the redo for the 5.4 operation, the transaction ID is specified by the block class (CLS - described above), the slot (slt) and the sequence number (sqn). This information can be used to identify the undo segment header and the changes to which this commit statement applies.