REDO INTERNALS AND TUNING BY REDO

REDO INTERNALS AND TUNING BY REDO

REDUCTION
Introduction
This paper is to explore internals of redo generation and then analyze the effect of excessive redo
generation. We will substantiate common issues that increases redo size and techniques to reduce it.
We will also explore few techniques to detect excessive redo generation and how to find the root
cause of excessive redo generation.
This paper is NOT designed as a step by step approach, rather as a guideline. Every effort has been
taken to reproduce the test results. It is possible for the test results to differ slightly due to
version/platform. differences.
Redo generation is absolutely essential for recovery. This paper does not subscribe to the notion that
the redo generation must be disabled. We explore the options to reduce excessive redo without
affecting the functional behavior. of application or recovery. We can also substantiate that reducing
redo improves application scalability and reduces MTTR (Mean time to recover).
Redo generation in Oracle
Oracle maintains ACID properties (Atomicity, Consistency, Isolation and Durability) of relational
database theory. Oracle database’s redo logging mechanism plays pivotal role in implementing these
properties.
Combination of physical and logical change logging, physiological logging, implements few of these
ACID properties:
(a) Atomic change to a database block generates a change vector. This change
vector is for a database block and physical in nature.
(b) Multiple change vectors grouped together to create a redo record. This
implements logical change.
Change vectors
Change vectors transitions a database block from one state to another state. These change
vectors applies an atomic change to a database block and prescribes the specific version of a
block that this change vector can be applied to.
Every valid database block has version information in the block header. Pair
identifies the version of the block.
For example, following change vector, inserts one row in to a block with DBA 0x100000a at
a slot #4. This change vector can only be applied to this block with a version :
<0x0000.00060335, 1>.
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x0100000a SCN:0x0000.00060335 SEQ: 1 OP:11.2
KTB Redo
op: 0x02 ver: 0x01 op: C uba: 0x008000ab.0021.0f
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100000a hdba: 0x01000009
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) size/delt: 30
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 1] 32
col 1: [24]
53 65 63 6f 6e 64 20 72 6f 77 20 66 6f 72 20 72 65 64 6f 20 64 75 6d 70
Redo records
Multiple change vectors grouped together to create a redo record. Redo records transitions
the database from one state to another state. All or none of the change vectors from a redo
record will be applied. This property guarantees that all or none of the changes associated
with a logical change is applied to the database.
In a sense, this is a logical change.
Redo application
Short description of the redo application process follows. Processes intent to modify a database
block, must perform. the following, before applying the change vectors to the database blocks.
1. Create change vectors describing the change.
2. Changes are protected by undo segments and transaction rollback is implemented using
undo segments. Hence, undo records describing ‘how to undo the changes’ must be
created in the undo blocks. This change to undo block generates change vectors for the
undo segment.1
3. Many change vectors are grouped together to create a redo record. This redo record is
created in the PGA of the process.
4. Redo record copied to the log buffer 2.
5. Changes are applied to the database blocks in the buffer cache.
There are few exceptions such as direct mode sqlloader, direct mode insert, nologging operations
such as creating index etc, does not adhere to this behavior. Redo generation behavior. is different
while the tablespaces are in hot backup mode.
(1)Not every change is protected by undo.
(2) Copying to the log buffer is different for private redo threads.
Redo ordering
Redo records are generated and applied in a strict order to preserve database sanity. Every redo
record has an SCN (System Change Number) and SUBSCN fields in the redo record header. Redo
records in the redo stream are ordered by these fields. A redo stream will have redo records ordered
in ascending SCN and SUBSCN order.
This sequencing of redo records is guaranteed in multi-instance database too. Nearly simultaneous
changes from two different redo threads, to a block, generates two redo records, but these redo
records ordering is maintained by SCN,SUBSCN pair.
If the redo records have same SCN, then the SUBSCN will be different for those redo records.
Following example illustrates that.
REDO RECORD - Thread:1 RBA: 0x0001a6.0000004e.0108 LEN: 0x0060 VLD:
0x01
SCN: 0x0000.0056c320 SUBSCN: 1 02/02/2006 08:23:39
REDO RECORD - Thread:1 RBA: 0x0001a6.0000004e.0168 LEN: 0x01f0 VLD:
0x01
SCN: 0x0000.0056c320 SUBSCN: 2 02/02/2006 08:23:39
REDO RECORD - Thread:1 RBA: 0x0001a6.0000004f.019c LEN: 0x0160 VLD:
0x01
SCN: 0x0000.0056c320 SUBSCN: 3 02/02/2006 08:23:39
CHANGE #1 TYP:0 CLS:18 AFN:2 DBA:0x008005de SCN:0x0000.0056c320 SEQ: 1
OP:5.1
Internals of redo
We will explore various types of operations and explain how physiological logging mechanism works
for those statements.
General structure of redo record
We will consider conventional single row insert statement to explain attributes of a redo
record. Following shows a listing of one redo record generated by one single row insert
statement.
SQL: Insert into redo_dump values ('2', 'Second row for redo dump');
REDO RECORD - Thread:1 RBA: 0x00000c.00000002.0010 LEN: 0x013c VLD: 0x05
..1
SCN: 0x0000.00060338 SUBSCN: 1 11/28/2005 12:03:02
CHANGE #1 TYP:0 CLS:26 AFN:2 DBA:0x008000ab SCN:0x0000.00060335 SEQ: 1
OP:5.1..2
ktudb redo: siz: 68 spc: 6482 flg: 0x0022 seq: 0x0021 rec: 0x0f
xid: 0x0005.011.00000020
ktubu redo: slt: 17 rci: 14 opc: 11.1 objn: 9913 objd: 9913 tsn: 4
Undo type: Regular undo Undo type: Last buffer split: No ..3
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008000ab.0021.0e
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100000a hdba: 0x01000009
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4)
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x0100000a SCN:0x0000.00060335 SEQ: 1
OP:11.2..4
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008000ab.0021.0f
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100000a hdba: 0x01000009
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) size/delt: 30
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 1] 32 ....5
col 1: [24]
53 65 63 6f 6e 64 20 72 6f 77 20 66 6f 72 20 72 65 64 6f 20 64 75 6d 70 ....6
Let’s explore various lines in this redo record.
(1) Indicates that this is a new redo record. Each redo record has a header. Various fields in
the header are:
RBA: Redo Byte Address. RBA donates a position in the redo log file.
Format is ...
LEN: Length of redo record in Hexadecimal 13C, converting this to decimal yields
316 bytes. Size of this redo record is 316 bytes.
SCN: System Change Number at this RBA.
SUBSCN: Sequence at this SCN.
(2) This is a first change vector within this redo record. This change vector is for an undo
block.
TYP: Type of the block. 0-Normal, 1-New block 2-Delayed logging etc.
CLS: Class of the block. This field is same as x$bh.class column except for undo
blocks/undo header.
DBA: Data Block Address (0080000ab) that this change vector can be applied to.
: Block version of the block that this change vector can be applied to.
OP: 5.1: Opcode indicates what internal calls to make for this change vector.
Structure of the change vector is different for each opcode. Format of opcode is
Layer.opcode. In this case, Layer 5 is for transaction undo and 5.1 indicates either
an undo block or undo segment header change.
xid: specifies the transaction id of the current transaction. XID is pointing to a
transaction table slot in an undo segment header.
(3) This change vector is for undo changes. Lines under the ‘KDO undo record’, specifies
that “to undo the changes drop the row at slot #4 in DBA 0x0100000a”.
(4) CHANGE #2: New change vector within the same redo record. This change vector
applies to a block with DBA 0x0100000a, which is a table segment block.
<0x0000.00060335 seq:>: Specifies the version of the block that this change vector
can be applied to.
OP: 11.2: Opcode 11.2 is for row piece insert.
(5) Specifies value for column A in redo_dump table
(6) Specifies value for column B in redo_dump table
Scripts :
Redo_internals_insert_00.sql – Script. to insert single row and generate redo log dump.
Redo record for single row insert
Single row inserts are discussed in previous section. It is listed again for consistency. For
single row insert, Oracle must generate, at least, two change vectors:
1. Change vector to modify the table segment block to add a row.
2. Change vector to modify the undo block, to add an undo record.
These two change vectors are grouped together as a redo record.
SQL: insert into redo_internals_tbl values ('A2','SECOND ROW');
REDO RECORD - Thread:1 RBA: 0x0000e4.00000002.0010 LEN: 0x0130 VLD: 0x05
SCN: 0x0000.003f8f60 SUBSCN: 1 01/12/2006 10:32:37
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x008003b6 SCN:0x0000.003f8f5e SEQ: 1
OP:5.1
..(1)
ktudb redo: siz: 68 spc: 4670 flg: 0x0022 seq: 0x03d6 rec: 0x1f
xid: 0x0004.021.00001113
ktubu redo: slt: 33 rci: 30 opc: 11.1 objn: 14036 objd: 14036 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: 0x008003b6.03d6.1e
KDO Op code: DRP row dependencies Disabled ..(2)
xtype: XA flags: 0x00000000 bdba: 0x0100cb0a hdba: 0x0100cb09 ..(3)
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) ..(4)
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x0100cb0a SCN:0x0000.003f8f5e SEQ: 5
OP:11.2
..(5)
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008003b6.03d6.1f
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100cb0a hdba: 0x0100cb09 ..(6)
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) size/delt: 17 ..(7)
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] 41 32 ..(8)
col 1: [10] 53 45 43 4f 4e 44 20 52 4f 57 ..(9)
(1) First change vector modifies undo block. Specifies DBA to which this change vector can
be applied. This block is an undo segment block.
(2) For an insert operation, undo operation is to drop the row piece. ‘DRP ’ is the opcode to
drop the row piece.
(3) Undo record describes the block dba (0x0100cb0a) that this undo record applies to. This
block is the table segment block for redo_internals_tbl.
(4) Points to a specific slot in the row directory. Slot # is 1(0x1). Second row as this
numbering starts from 0. Above undo record prescribes that to undo the changes
“Delete the row from the directory at slot #1 in the block with DBA 0x0100cb0a”.
(5) Second change vector modifies table segment block.
(6) Block DBA is 0x0100cb0a, this block belongs to redo_internals_tbl table segment. Hdba:
0x0100cb09 denotes the segment header.
(7) Describes the slot number in the row directory of the block to insert this row.
(8) Value for char_column. ASCII character for value 41 is A and chr(31) is 2. Value is A2.
(9) Value for varchar2 column.
ASCII -> 53 45 43 4f 4e 44 20 52 4f 57
CHAR -> S E C O N D R O W
Scripts :
Redo_internals_insert.sql – Script. for single row insert and generate redo log dump.
Redo record for single row /single column update
For single row update, Oracle generates two change vectors:
1. Change to the table segment block updating column value.
2. Change to the undo segment block adding undo record. Undo operation for an update
is another update of the same column(s) with old value. Old values of the column(s)
are preserved in the undo record.
SQL :
insert into redo_internals_tbl values ('A1','FIRST ROW');
update redo_internals_tbl set varchar2_column='FIRST ROW UPD' where char_column
='A1';
We will discuss only redo records specific to the update statement:
REDO RECORD - Thread:1 RBA: 0x0000e8.00000002.0010 LEN: 0x0140 VLD: 0x05
SCN: 0x0000.003f96f5 SUBSCN: 1 01/12/2006 11:46:07
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x008003b6 SCN:0x0000.003f96f1 SEQ: 2 OP:5.1
ktudb redo: siz: 100 spc: 3792 flg: 0x0022 seq: 0x03d6 rec: 0x28
xid: 0x0004.025.00001113
ktubu redo: slt: 37 rci: 39 opc: 11.1 objn: 14038 objd: 14038 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: 0x008003b6.03d6.27
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0101868a hdba: 0x01018689
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 1
ncol: 2 nnew: 1 size: -4 ..1
col 1: [ 9] 46 49 52 53 54 20 52 4f 57 ..2
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x0101868a SCN:0x0000.003f96f1 SEQ: 6 OP:11.5
..3
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008003b6.03d6.28
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0101868a hdba: 0x01018689
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 1
ncol: 2 nnew: 1 size: 4 ..4
col 1: [13] 46 49 52 53 54 20 52 4f 57 20 55 50 44 ..5
(1) Describes that the undo record will reduce the column size by 4 bytes. URP is the KDO
opcode, since undo for an update is another update with older value.
(2) Describes the pre-update image of row piece (i.e. column value)
Old value of the column is stored in this undo record.
46 49 52 53 54 20 52 4f 57
F I R S T R O W
(3) Opcode 11.5 is for update row piece.
(4) Describes that this change increases the column size by 4 bytes.
(5) New value of that column for the row in slot #0.
46 49 52 53 54 20 52 4f 57 20 55 50 44
F I R S T R O W U P D
Scripts :
Redo_internals_update.sql – Script. for single row /single column update and to generate redo
log dump.
Redo record for single row delete
For single row delete, Oracle generates, at least, two change vectors:
(1) Change vector for table segment block to mark the row as deleted.
(2) Change vector for undo block to add an undo record. Undo for delete is Row insert.
Pre-image of the row is stored in the undo record to facilitate undo.
SQL :
delete from redo_internals_tbl where char_column ='A1';
REDO RECORD - Thread:1 RBA: 0x0000ea.00000002.0010 LEN: 0x0130 VLD: 0x05
SCN: 0x0000.003f97b3 SUBSCN: 1 01/12/2006 11:51:44
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x008003b6 SCN:0x0000.003f97af SEQ: 2 OP:5.1
..1
ktudb redo: siz: 120 spc: 3376 flg: 0x0022 seq: 0x03d6 rec: 0x2c
xid: 0x0004.027.00001113
ktubu redo: slt: 39 rci: 43 opc: 11.1 objn: 14039 objd: 14039 tsn: 4 0x0000.00060335>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-622667/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20542911/viewspace-622667/