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).
Charles Hooper's Oracle Notes 
It appears Donald K. B. has prevented anyone but himself from commenting or replying to the thread.
(Sorry, comment was initially marked as spam – problem fixed. CH Sep 18, 2012)
David,
Sorry for the delay in your comment appearing – certain words seem to trigger WordPress’ spam filter.
I just tried replying to the thread and received the message “Sorry, you do not have permission to reply to that topic.”, so it does appear that replying to that thread is restricted.
The one reply that the OP received in the thread is incomplete. That reply seems to suggest that it is the SELECT that receives the “ORA-30036: unable to extend segment by n in undo tablespace” in the undo tablespaces’ files are unable to expand to the size of the tables that are queried. The OP in the thread might have been thinking about “ORA-01555: snapshot too old”, however, that error would not apply in the OP’s test case.
—
Off subject humor (unrelated to David’s comment) 1- what was the guy on the purple mule attempting to accomplish?:
* He is a Windows 1.0 beta tester, still reporting bugs found in the field, his report: 1 moth dead due to heat exhaustion, 3 lady bugs, and a frog.
* He is a Windows 8.0 beta tester trying to get the hang of the touch screen interface.
* He found that Windows 8.0 requires new hardware, and found that a tire iron is the perfect accessory for disabling the Windows Metro interface.
* He decided to re-tire, but forget where he parked the car.
* He is a trademark infringement lawyer for Microsoft, researching a lawsuit against the hole in the wall companies that sell unrelated products under the Windows label.
* He is looking for the cliff-jumping little red riding horse.
* He was checking to see if the Window violated any of Sun’s patents, such as those covering OpenWindows (http://en.wikipedia.org/wiki/OpenWindows).
* He was interested in checking out the scene at the police bars that he had heard so much about.
And he appears to understand as much about UNDO as the OP. It is a downside to the internet that bad information is as freely available as good information and sadly sometimes the bad information is more popular and returns search results in the top entries.
Charles, are you wanting answers to the questions posed? My small understanding of UNDO is that a select does not create undo unless a transaction is outstanding that has not commited changes to the data you are looking at. So,
Question 1) Undo isn’t used here to get a read consistent view of the data. The data is already in a consistent state.
Question 2) I’m sure the difference in consistent gets is the hard parse of the select statement indicated by the 11 recursive calls dropping to zero the second time.
Question 3)The delete’s will create undo records so that this transaction can roll back. That’s where the extend has originated. Deleting 300,000 records will result in an attempt to create 300,000 undo records one for each row deleted. This requirement broke the 1M limit. The transaction rolled back, so 1M of undo was now available. Deleting 30 rows required less than 1M so this was successful. I’m not sure as to the value of 8, but I would hazard a guess it is the smallest automatic extent size in blocks?
I am now at my limit of undo knowledge, so those more knowledgeable than my self can pick up the baton!
I will guess, that RETENTION GUARANTEE according to the documentation
http://docs.oracle.com/cd/B12037_01/server.101/b10739/undo.htm#BJFJCHCB
‘By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations.’
So, the delete of 30 records failed due to the first deletes that filled up the tablespace not being reused as they hadn’t expired. When you ran the next set, the undo records had expired and so were re-used.
I’m not sure compression is used, however, encoding will be, so rather than contain an INSERT for every DELETE it will be encoded in the UNDO record.
How have I done?
Tony,
Yes, I am interested in seeing appropriate answers for the questions – might help people who are searching for answers to similar questions. For the record, some of these questions appeared on the OTN forums in the past, using slightly different wording. There are certainly experienced DBAs reading this blog, and there is a good chance that the answers provided by one or more readers will be better than my answers (a consensus on how things work is usually better than one man’s opinion).
How have you done? I like your answers to questions 1 and 2.
1) As mentioned before the data is already in a consistent state so no undo is generated.
2) I agree that the difference in consistent gets is the hard parse.
3) The 8 is the number of blocks that can’t be allocated for the larger deletes; the smaller deletes have sufficient space for the undo records.
4) The retention guarantee, I believe, creates this issue — it’s the only difference between
the two undo tablespaces. If the undo_retention parameter isn’t changed from its default value (mine is set to 5 in my 11.2.0.3 database) then it’s likely that Oracle considers the current undo (whatever it may be) as unexpired and refuses to overwrite it while the retention guarantee is in place. Once 5 seconds have elapsed (easily the time between the first delete attempt and the second) the undo is available. So, to my knowledge, it’s not the second delete that frees the undo space it’s the elapsed time between the delete attempts that allows the second and subsequent deletes (that aren’t too large) to succeed. As there is no retention guarantee in place for the second undo tablespace Oracle need not preserve unexpired undo should it need the space for further transactions.
David,
Sorry for the delay in responding. WordPress has apparently decided to return the formatting of my blog as a narrow column of text, rather than the full screen width that I configured when I bought the custom CSS option nearly 3 years ago. I am still trying to find a work around for that issue. Between that and fighting with a couple of high-definition security camera that refuse to work correctly, I have not had sufficient free time to check for comments posted to the blog. My review of the high-definition security camera is now live on Amazon’s site:
—
Overall, nice answers. Just a few comments:
1) 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? That is as well worded as anything I could have written.
2) Why did the first select of the table require 81 more consistent gets than the second select from that table? That is as well worded as anything I could have written.
3) Why is Oracle attempting to extend a segment when the session is deleting rows; also, what does the number 8 signify? Good answer – the Oracle error could have better explained the meaning of 8. Regarding your answer, it would have been slightly more clear to mention that Oracle Database was attempting to allocate another undo extent (64KB in size due to the use of ASSM autoallocate) — I believe that is your implied answer.
4) 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? David, I am not sure that your answer is completely correct. The undo from a session cannot be overwritten until that session issues a commit, and the session executing the deletes never executed a commit (sorry if the commit frequency was not very clear). You do make good points regarding how undo retention works. If I recall correctly, when I wrote the question, I noticed that the documentation for 11.2 mentioned that there were certain default behaviors when UNDO tablespaces are created.
5) What type of compression is used in undo logs? I don’t think that anyone has attempted to answer this question yet. Odd, the comment about compression of “undo logs” seems to have been removed from the forum thread. Of course, one might question what is an undo log?
Well, that took a while! I was finally able to fix the formatting of this blog so that the pages are again almost (99%) as wide as the browser window, rather than having the entire page being confined to a width of 780 pixels.