|
Undo
|
In this issue, I continue to talk about the behavior and size of Undo space
when I execute a large number of update processes. I use 1MB of Undo tablespace
and 1000MB of Undo tablespace to compare performance and throughput.
First, I use the transaction table (TRN TBL) to view the status before and after
1,000,000 of update processes are executed. Transaction table contains information regarding
transaction process and is located in the rollback segment header. The information
is updated before Undo data is actually stored in the rollback segment.
Transaction table can be referred to in the block dump. Check Vol.41 to 49
for further details.
I execute the following SQL statement while executing the 1,000,000 of update processes,
and find the rollback segment used for this operation.
SQL> SELECT N.USN, N.NAME, S.STATUS, S.EXTENTS, S.RSSIZE, S.HWMSIZE,
S.XACTS
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
(Result)
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS
0 SYSTEM ONLINE 8 407552 407552 0
21 _SYSSMU21$ ONLINE 2 129024 129024 0
22 _SYSSMU22$ ONLINE 23 8386560 8386560 1
23 _SYSSMU23$ ONLINE 2 129024 129024 0
24 _SYSSMU24$ ONLINE 2 129024 129024 0
25 _SYSSMU25$ ONLINE 2 129024 129024 0
26 _SYSSMU26$ ONLINE 2 129024 129024 0
27 _SYSSMU27$ ONLINE 2 129024 129024 0
28 _SYSSMU28$ ONLINE 2 129024 129024 0
29 _SYSSMU29$ ONLINE 2 129024 129024 0
30 _SYSSMU30$ ONLINE 2 129024 129024 0
|
The result above indicates that rollback segment (_SYSSMU22$) is used
for that operation. I get the block dump of this rollback segment.
SQL> SELECT SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '_SYSSMU22$';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------- ----------- ------------
_SYSSMU22$ 8 97
SQL> ALTER SYSTEM DUMP DATAFILE 8 BLOCK 97;
|
I check the contents of the block dump such as transaction table,
Extent Control Header, Extent Map, and Retention Table but
I can't find any remarkable differences. So I take a look at the contents of the block
dump after the update processes are executed.
(10000MB of Undo tablespace)
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
----------------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0004 0xffff 0x0000.000e6357 0x03c08fcb 0x0000.000.00000000 0x00008b83 0x00000000
0x01 9 0x00 0x000b 0x0002 0x0000.000e62ec 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x000b 0x0003 0x0000.000e6315 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000
0x03 9 0x00 0x000b 0x0004 0x0000.000e632d 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000
0x04 9 0x00 0x000b 0x0000 0x0000.000e6345 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000
0x05 9 0x00 0x000a 0x0006 0x0000.000e6230 0x03c078a6 0x0000.000.00000000 0x00000001 0x00000000
0x06 9 0x00 0x000a 0x0007 0x0000.000e6249 0x03c078a6 0x0000.000.00000000 0x00000001 0x00000000
0x07 9 0x00 0x000a 0x0008 0x0000.000e6261 0x03c07df0 0x0000.000.00000000 0x00000001 0x00000000
0x08 9 0x00 0x000a 0x0009 0x0000.000e6296 0x03c08095 0x0000.000.00000000 0x00000001 0x00000000
0x09 9 0x00 0x000a 0x0001 0x0000.000e62b1 0x03c08095 0x0000.000.00000000 0x00000001 0x00000000
|
(1MB of Undo tablespace)
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
----------------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0044 0x0002 0x0000.000e5802 0x02008097 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x003d 0xffff 0x0000.000e58ce 0x02008fcc 0x0000.000.00000000 0x00008b83 0x00000000
0x02 9 0x00 0x0044 0x0003 0x0000.000e581e 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000
0x03 9 0x00 0x0044 0x0004 0x0000.000e5852 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000
0x04 9 0x00 0x0044 0x0005 0x0000.000e586a 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000
0x05 9 0x00 0x0044 0x0001 0x0000.000e5882 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000
0x06 9 0x00 0x0043 0x0007 0x0000.000e5756 0x020078a7 0x0000.000.00000000 0x00000001 0x00000000
0x07 9 0x00 0x0043 0x0008 0x0000.000e5789 0x020078a7 0x0000.000.00000000 0x00000001 0x00000000
0x08 9 0x00 0x0043 0x0009 0x0000.000e57a3 0x02007df1 0x0000.000.00000000 0x00000001 0x00000000
0x09 9 0x00 0x0043 0x0000 0x0000.000e57bd 0x02008097 0x0000.000.00000000 0x00000001 0x00000000
|
SCN (System Change Number) and undo block dba (data
block address) are different between two Undo tablespaces
but I find a category that is not changed at all. Check the underlined
rows above. nub of dba (0x03c08fcb) is 0x00008b83 in
1000MB of Undo tablespace. nub of dba (0x02008fcc)
is also 0x00008b83 in 1MB of Undo tablespace.
nub represents the number of used rollback segments.
As nub is the information of currenly used rollback segment,
it is assumed that file extension affects performance after all.
I make sure if a record is an active rollback segment that I have found
in V$ROLLSTAT.
I convert hexadecimal dba to decimal and execute the following SQL statement.
(1) 0x02008fcc -> 33591244
(2) 0x03c08fcb -> 62951371
SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID= DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(33591244)
AND DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(33591244) BETWEEN
BLOCK_ID AND BLOCK_ID+BLOCKS ;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
_SYSSMU22$ TYPE2 UNDO
|
I also execute the same SQL statement with the second one (62951371) and
find out that it is the active rollback segment captured in V$ROLLSTAT.
Now, I view the file information in a dynamic performance view V$FILESTAT.
Make sure that the initialization parameter TIMED_STATISTICS is set to TRUE.
If FALSE, some parameters may result in 0.
Following is the result abstracted from V$FILESTAT. I create UNDOTBS_04
to 1000MB and UNDOTBS_03 to 1MB.
FILE_ID FILE_NAME TABLESPACE_NAME MBYTES PHYRDS PHYWRTS WRITETIM LSTIOTIM MAXIORTM MAXIOWTM
15 undo04.dbf UNDOTBS_04 1000 19 332405 141029609 1 5 8554
8 undo03.dbf UNDOTBS_03 74.1875 26 72227 245472056 4153 723 14184
|
Description
PHYRDS: Number of physical reads done
PHYWRTS: Number of physical writes done
WRITETIM: Time (in milliseconds) spent doing writes if the TIMED_STATISTICS parameter
is TRUE; 0 if FALSE.
LSTIOTIM: Time (in milliseconds) spent doing I/O
MAXIORTM: The maximum time (in milliseconds) spent doing a single read if the TIMED_STATISTICS
parameter is TRUE; 0 if FALSE.
MAXIOWTM: The maximum time (in milliseconds) spent doing a single write if the TIMED_STATISTICS
parameter is TRUE; 0 if FALSE.
Take a look at PHYWRTS and WRITETIM.
PHYWRTS of UNDOTBS_03 is 72227. PHYWRTS of UNDOTBS_04 is 33240 which
is less than half of UNDOTBS_03. This proves that the smaller Undo tablespace
executes more than twice number of physical writes than the larger Undo tablespace.
Furthermore, WRITETIM of smaller Undo tablespace is 245472056 that is
1.5 times more than the larger Undo tablespace 141029609
Even though the number of extents, blocks and size are the same before and after the
heavy update processes are executed, Undo tablespace size does affect performance.
In othe words, if the Undo tablespace size is smaller than the size required for transaction,
datafile needs its extension. As a result, performance slows down.
If you want to execute heavy update process, it is recommended that you
use the larger Undo tablespace (i.e. large rollback segment).
If you want to have Automatic Undo Management, consider the following:
1. System limitation, Undo space usage, transaction type, load time, number of sessions
2. If file system does not have any limitation, create different sizes of Undo tablespace.
3. Check the transaction type, and change the Undo tablespace (initialization parameter:
UNDO_TABLESPACE) dynamically
4. Monitor system at regular intervals and check performance
This is the final issue covering Undo. I will start a new topic next week.
That's it for today.
Yoshihiro Iida
|
|
Ora! Ora! Oracle