|
X$BH
|
In this issue, I will further analyze why CR block is created only by UPDATE queries
at full scan.
I assumed last time that CR block was created in order to create UNDO information
not to refer to CR block. I determined the number of UNDO entries created by this process.
Preconditions are as follows.
*************************************************************
(Environment)
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0
(Table configuration)
SQL> desc test
Name Type
--------- ------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(2000)
*1 Grant INDEX (TEST_IDX) to ID1.
*2 Table contains 10000 of data.
*************************************************************
|
First, I view V$TRANSACTION to check how many UNDO entries are created by
UPDATE queries at full scan.
*************************************************************
[UPDATE queries at full scan]
*I add a hint to let full scan be performed.
SQL> update /*+ FULL(test) */ test set id1=1 where id1 > 0;
10000 rows updated
[Check the number of UNDO entries created]
SQL> select USED_UBLK ,USED_UREC
from v$transaction;
XIDUSN USED_UBLK USED_UREC
---------- ---------- ----------
8 329 30011 <-Number of UNDO entries
Column Description
---------- -------------------
XIDUSN UNDO segment number
USED_UBLK Number of blocks used
USED_UREC Number of UNDO entries
*************************************************************
|
The result above indicates that approximately 30000 of UNDO entries
are created. This number is appropriate because there are three types
of information: pre-updated table information, pre-updated index information,
and post-updated index information.
10000 * 3 = 30000 (UNDO entries)
Next, I perform UPDATE queries at index scan.
*************************************************************
[Update queries at index scan]
* Index in id1 is used.
SQL> update test set id1=1 where id1>0;
10000 rows updated
XIDUSN UBAFIL UBABLK USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ----------
3 2 3834 154 10148 <-here
*************************************************************
|
Take a look at 'here' section. Number of UNDO entries
decreases by a factor of 3. (i.e. 10000). Also, only
a half of UNDO entries is used at index scan.
I have found a remarkable difference by obtaining UNDO block dump
and examining information about UNDO entries.
*************************************************************
(Information before record is updated)
*-----------------------------
* Rec #0x10 slt: 0x15 objn: 33468(0x000082bc) objd: 33498 tblspc: 0(0x00000000)
KDO undo record:
col 0: [ 3] c2 64 64
(Information before index is updated)
*-----------------------------
* Rec #0x11 slt: 0x15 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000)
index undo for leaf key operations
restore leaf row (clear leaf delete flags)
key :(11): 03 c2 64 64 06 00 40 f9 73 00 61
(Information after index is updated)
*-----------------------------
* Rec #0x12 slt: 0x15 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000)
index undo for leaf key operations
purge leaf row
key :(10): 02 c1 02 06 00 40 f9 73 00 61
Loop
*************************************************************
|
Following three entries are repeated 10000 times. (i.e. 30000)
[UNDO entry before table is updated]
[UNDO entry before index is updated]
[UNDO entry after index is updated]
(Entries are looped 10000 times)
A breakdown of UNDO entries is as follows.
*************************************************************
(10000 of UNDO entries before table is updated)
(70 of UNDO entries before index is updated)
(70 of UNDO entries after index is updated)
*************************************************************
[Excerpts of UNDO entry before index is updated]
Multiple keys (255 in this case) are included in a single entry
*-----------------------------
* Rec #0x2 slt: 0x17 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
restore leaf row (clear leaf delete flags) <-Restore delete flag
number of keys: 255 <-Number of keys updated
key sizes:
11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
key :(2802):
03 c2 43 39 06 00 40 f9 5a 00 bc 03 c2 43 3a 06 00 40 f9 5a 00 bd 03 c2 43
.
.
[Excerpts of UNDO entry after index is updated]
Multiple keys (255 in this case) are included in a single entry
*-----------------------------
* Rec #0x3 slt: 0x17 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000)
index undo for leaf key operations
purge leaf row <-Purge a leaf value
number of keys: 255 <-Number of keys updated
key sizes:
10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
.
.
key :(2550):
02 c1 02 06 00 40 f9 43 00 11 02 c1 02 06 00 40 f9 43 00 12 02 c1 02 06 00
.
.
*************************************************************
|
UNDO entries are created in following order.
(1)Create all UNDO entries for a table
(2)Create all UNDO entries for pre-updated index information
(3)Create all UNDO entries for post-updated index information
First, all UNDO entries for a table are created. Next, UNDO entries
are created before and after an index is updated. In regard to the index,
only a single entry is created for hundreds of leaf values, which
indicates that index scan is more efficient than full scan.
I assume that when UPDATE queries are performed at full scan, CR block
is once created as an image for transaction only, and then UPDATE query
is performed to each row of the current block. Index is updated every time
UPDATE query is performed.
When you tune INSERT and UPDATE statements, be sure to consider UNDO information.
You may not use such SQL statement used in this issue in production environment,
but I have actually determined the execution time and have found out that UPDATE query
at index scan is two times faster than that at full scan.
Yoshihiro Uratsuji
|
|
