Undo Quiz – Bringing the Right Tools for the Job

17 09 2012

September 17, 2012

Sometimes humor is a good recipe for undo.  A mid-50s man was driving down the road, riding a purple mule, when he saw a house with a new window.  Uninvited, the man decided to take a closer look using an obvious tool of choice, a tire iron (YPDNTI).  Elapsed time, roughly 60 seconds.  The man is obviously familiar with Oracle Database’s concept of transactions and issuing a ROLLBACK, but did not account for video recording in several autonomous transactions.  Even through a COMMIT was not issued, that does not imply that nothing happened in those 60 seconds. 

Riding a purple mule… strange.

On to the quiz.  Assume that there are three and only three users connected to an Oracle database (preferrably a test database of sorts).  The first session is connected as the SYS user, and the other two sessions are connected as a normal database user (the same user ID, but with access permissions for DBA_SOURCE).  Assume that session 1 creates a new undo tablespace that cannot grow beyond 1MB in size:

CREATE UNDO TABLESPACE SMALL_UNDO2 DATAFILE 'C:\Oracle\OraData\SAMPLE\small_undo2.dbf' SIZE 1M AUTOEXTEND OFF; 

And then that tablespace is set as the system default tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE=SMALL_UNDO2;

In session 2, table T10 is created based on DBA_SOURCE:

CREATE TABLE T10 AS
SELECT
  *
FROM
  DBA_SOURCE;

In my case, I used the Oracle sample schema in Oracle Database 11.2.0.3 (tablespace for T10 is ASSM AUTOALLOCATE).  Let’s check the amount of space consumed (not necessarily all used for data strorage yet) for the T10 table:

SELECT
  ROUND(SUM(BYTES)/1024/1024,2) SIZE_MB
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T10';

   SIZE_MB
----------
        88 

88MB – a nice even number.  With a 1MB maximum size for the undo tablespace, let’s see if the user is able to select all of the rows from the T10 table:

SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS

SELECT
  *
FROM
  T10;

633054 rows selected.

Statistics
---------------------------------------------------
         11  recursive calls
          0  db block gets
      11352  consistent gets
      10635  physical reads
          0  redo size
   66576339  bytes sent via SQL*Net to client
       7322  bytes received via SQL*Net from client
        635  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     633054  rows processed 

That worked, but why?  Why didn’t the session receive an error when performing the 11,352 consistent gets to provide a consistent view of the data?

Maybe on the second execution we will see an error?

SELECT
  *
FROM
  T10;

633054 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11271  consistent gets
          0  physical reads
          0  redo size
   66576339  bytes sent via SQL*Net to client
       7322  bytes received via SQL*Net from client
        635  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     633054  rows processed

—–

The first quiz question is simple: How it is possible for Oracle Database to provide a read consistent view of a table that requires approximately 88MB of storage space when the file supporting the undo tablespace cannot exceed 1MB?  Maybe someone will be kind enough to share the answer with the OP in this message thread:
dbaforums.org/oracle/index.php?s=439a689327b6fbd09ba0018b320041d8&showtopic=21972

The second quiz question is also simple: Why did the first select of the table require 81 more consistent gets than the second select from that table?

—–

Continuing with the test case, this time working in session 3.  Let’s delete some rows from the table created in session 2:

DELETE FROM
  T10
WHERE
  ROWNUM<=300000; 

Oracle Database responses with the following message:

SQL> DELETE FROM
  2    T10
  3  WHERE
  4    ROWNUM<=300000;
  T10
  *
ERROR at line 2:
ORA-30036: unable to extend segment by 8 in undo tablespace 'SMALL_UNDO2'

The third question is slightly more challenging: Why is Oracle attempting to extend a segment when the session is deleting rows; also, what does the number 8 signify?

Let’s try again with a smaller number of rows:

DELETE FROM
  T10
WHERE
  ROWNUM<=30;

30 rows deleted. 

That worked.  Interestingly, the first time I tried the test case, I named the tablespace SMALL_UNDO and specified “RETENTION GUARANTEE” when creating the undo tablespace, and the following message appeared when executing the above command:

SQL> DELETE FROM
  2    T10
  3  WHERE
  4    ROWNUM<=30;
  T10
  *
ERROR at line 2:
ORA-30036: unable to extend segment by 8 in undo tablespace 'SMALL_UNDO'

After the above message appeared, session 3 successfully deleted 3 rows, 30 rows, and then 300 rows from table T10 without receiving an error:

SQL> DELETE FROM
  2    T10
  3  WHERE
  4    ROWNUM<=3;

3 rows deleted.

SQL> DELETE FROM
  2    T10
  3  WHERE
  4    ROWNUM<=30;

30 rows deleted.

SQL> DELETE FROM
  2    T10
  3  WHERE
  4    ROWNUM<=300;

300 rows deleted. 

The fourth question is again slightly more challenging: why was the 30 row delete from table T10 successful when the SMALL_UNDO2 undo tablespace was the default undo tablespace, yet that delete failed when the SMALL_UNDO tablespace was set as the default tablespace – until 3 rows were first deleted from the table?

Going back to the forum thread, another interesting question arises.  The fifth question is again challenging: What type of compression is used in undo logs?

I was curious about the last question.  In session 1 I executed the following:

SELECT
  R.NAME,
  T.XIDUSN,
  T.XIDSLOT,
  T.XIDSQN
FROM
  V$TRANSACTION T,
  V$ROLLNAME R
WHERE
  T.XIDUSN=R.USN; 

NAME                               XIDUSN    XIDSLOT     XIDSQN
------------------------------ ---------- ---------- ----------
_SYSSMU15_897375467$                   15          1          8

ALTER SESSION SET TRACEFILE_IDENTIFIER='UNDO_BLOCKS';
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU15_897375467$' XID 15 1 8;

A portion of the generated trace file follows:

********************************************************************************
Undo Segment:  _SYSSMU15_897375467$ (15)
xid: 0x000f.001.00000008
Low Blk   :   (0, 0) 
High Blk  :   (2, 7) 
Object Id :   ALL 
Layer     :   ALL 
Opcode    :   ALL 
Level     :   2 

********************************************************************************
UNDO BLK:  Extent: 1   Block: 1   dba (file#, block#): 6,0x00000071
xid: 0x000f.001.00000008  seq: 0x4   cnt: 0x2c  irb: 0xa   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f24     0x02 0x1e64     0x03 0x1dbc     0x04 0x1d34     0x05 0x1c68     
0x06 0x1b9c     0x07 0x1ae4     0x08 0x1a5c     0x09 0x1998     0x0a 0x18d8     
0x0b 0x1830     0x0c 0x17a8     0x0d 0x16f0     0x0e 0x162c     0x0f 0x15a4     
0x10 0x14ec     0x11 0x1424     0x12 0x1378     0x13 0x12f0     0x14 0x1234     
0x15 0x1168     0x16 0x10b8     0x17 0x1030     0x18 0x0f74     0x19 0x0ecc     
0x1a 0x0e44     0x1b 0x0d88     0x1c 0x0cbc     0x1d 0x0be8     0x1e 0x0b44     
0x1f 0x0a88     0x20 0x09bc     0x21 0x08e8     0x22 0x0840     0x23 0x0784     
0x24 0x06b8     0x25 0x05e4     0x26 0x0540     0x27 0x0484     0x28 0x03b8     
0x29 0x02e4     0x2a 0x023c     0x2b 0x0180     0x2c 0x00b4     

*-----------------------------
* Rec #0xa  slt: 0x01  objn: 79928(0x00013838)  objd: 79928  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x09   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x01800071.0004.09
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01091a2e  hdba: 0x01091a2a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 16(0x10) size/delt: 89
fb: --H-FL-- lb: 0x0  cc: 5
null: -----
col  0: [ 3]  53 59 53
col  1: [ 8]  53 54 41 4e 44 41 52 44
col  2: [ 7]  50 41 43 4b 41 47 45
col  3: [ 3]  c2 04 1f
col  4: [60]
 20 20 20 20 70 72 61 67 6d 61 20 42 55 49 4c 54 49 4e 28 27 58 4f 52 27 2c
 38 2c 20 33 2c 20 39 29 3b 20 2d 2d 20 50 45 4d 53 5f 49 4e 54 45 47 45 52
 2c 20 49 4e 54 5f 58 4f 52 0a

*-----------------------------
* Rec #0x9  slt: 0x01  objn: 79928(0x00013838)  objd: 79928  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x08   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x01800071.0004.08
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01091a2e  hdba: 0x01091a2a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 15(0xf) size/delt: 90
fb: --H-FL-- lb: 0x0  cc: 5
null: -----
col  0: [ 3]  53 59 53
col  1: [ 8]  53 54 41 4e 44 41 52 44
col  2: [ 7]  50 41 43 4b 41 47 45
col  3: [ 3]  c2 04 1e
col  4: [61]
 20 20 66 75 6e 63 74 69 6f 6e 20 58 4f 52 20 28 4c 45 46 54 20 42 4f 4f 4c
 45 41 4e 2c 20 52 49 47 48 54 20 42 4f 4f 4c 45 41 4e 29 20 72 65 74 75 72
 6e 20 42 4f 4f 4c 45 41 4e 3b 0a

*-----------------------------
* Rec #0x8  slt: 0x01  objn: 79928(0x00013838)  objd: 79928  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x07   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x01800071.0004.07
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01091a2e  hdba: 0x01091a2a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 14(0xe) size/delt: 30
fb: --H-FL-- lb: 0x0  cc: 5
null: -----
col  0: [ 3]  53 59 53
col  1: [ 8]  53 54 41 4e 44 41 52 44
col  2: [ 7]  50 41 43 4b 41 47 45
col  3: [ 3]  c2 04 1d
col  4: [ 1]  0a

*-----------------------------
* Rec #0x7  slt: 0x01  objn: 79928(0x00013838)  objd: 79928  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x06   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x01800071.0004.06
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01091a2e  hdba: 0x01091a2a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 13(0xd) size/delt: 78
fb: --H-FL-- lb: 0x0  cc: 5
null: -----
col  0: [ 3]  53 59 53
col  1: [ 8]  53 54 41 4e 44 41 52 44
col  2: [ 7]  50 41 43 4b 41 47 45
col  3: [ 3]  c2 04 1c
col  4: [49]
 20 20 2d 2d 20 20 77 68 65 6e 20 74 68 65 79 20 6f 63 63 75 72 20 69 6e 20
 63 6f 6e 64 69 74 69 6f 6e 61 6c 20 73 74 61 74 65 6d 65 6e 74 73 2e 0a 

It would not surprise me too much if a guide existed to assist in decompressing the undo logs (whatever that term implies).








Follow

Get every new post delivered to your Inbox.

Join 137 other followers