| Undo Why [message #172893] |
Thu, 18 May 2006 09:17  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Dear All: Here is my question: Is it true that if we run inserts, it generates less undo than the same numbers of delete or update done on the same table. I have done a small mad science experiment which involved creating a simple 2 column tables and loaded around 2 million rows. The table size was around 29M or some thing like that. Then I inserted the same number of rows and check the undo size (measure the byte size of undo datafile). It showed very little undo growth. Now I rollback, no rows added and then update a column for the entire table. The Undo growth was huge. Now last time to bore you, I did the delete for entire table and the undo size even bigger than the table size. If the number rows remain same, why then undo generation different. Is the update and delete are involved with the existing table data, so more undo and insert is just adding the data rows so no change in parent table yet so less undo generation.
Any help, adivice, suggestion, response would be gladly accepted and rememberd for long time.
|
|
|
|
|
|
| Re: Undo Why [message #172903 is a reply to message #172893] |
Thu, 18 May 2006 09:55  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
You did a good experiment and the results you found are in line with what should be expected. Just think logically about what it would take for a human to undo an insert: just forget the insert happene. But to undo a delete, you have to have saved away all the data that used to be there.
|
|
|
|
| Re: Undo Why [message #172905 is a reply to message #172903] |
Thu, 18 May 2006 10:02 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
thanks and it make sense. But I failed to understand in case of delete, Why the undo amount is even bigger than the size of the tables itself. In my experiment, it was multiple size of the parent table.
Thanks.
|
|
|
|
| Re: Undo Why [message #172909 is a reply to message #172893] |
Thu, 18 May 2006 10:12 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
The size is also dependent on how many transactions you are covering, because each transaction has overhead. So if you delete 2 million rows in one commit, then that will be less undo in total than if you delete them one row at a time with a commit between each row. Try it and see.
Can you show your actual test here and prove that the undo size is in fact bigger than the table itself?
|
|
|
|
|
|
| Re: Undo Why [message #172927 is a reply to message #172912] |
Thu, 18 May 2006 11:44 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Here is the Mad science experiment:
As the sql statements are pretty clear, I am not explaining it further. Here we already created the table TEST1011 which has around 3Million rows and checking its size in MB:
SQL> select segment_name, bytes/1024/1024 as "Size in MB" from user_segments where segment_name='TEST1011';
SEGMENT_NAME Size in MB
---------------------------
TEST1011 37
We made an undo_delete tablespace and making it as our undo for experiment.
SQL> alter system set undo_tablespace=undo_delete scope=memory;
System altered.
Since it is just made and no transaction it registered, we just checked its size to be sure: nothing goes nowhere.
SQL> Select t.tablespace_name "Tablespace",
2 ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "Used MB"
3 FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
4 WHERE t.tablespace_name = d.tablespace_name and t.tablespace_name ='UNDO_DELETE'
5 AND f.tablespace_name(+) = d.tablespace_name
6 AND f.file_id(+) = d.file_id
7 GROUP BY t.tablespace_name;
Tablespace Used MB
------------------------------ ----------
UNDO_DELETE 1.31
Now we Delete the rows:
SQL> delete test1011;
3000000 rows deleted.
Finally Checking the undo space used:
SQL> Select t.tablespace_name "Tablespace",
2 ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "Used MB"
3 FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
4 WHERE t.tablespace_name = d.tablespace_name and t.tablespace_name ='UNDO_DELETE'
5 AND f.tablespace_name(+) = d.tablespace_name
6 AND f.file_id(+) = d.file_id
7 GROUP BY t.tablespace_name;
Tablespace Used MB
------------------------------ ----------
UNDO_DELETE 321.19
We dont have an index on the parent table. Seems the undo generation is around 10 times of the parent table.
Thanks.
|
|
|
|
| Re: Undo Why [message #172943 is a reply to message #172927] |
Thu, 18 May 2006 13:20 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
As i already said,
First , insert/update and delete are different mechanisms. Both acquire different path and resources.
And in this case, you are updating a SINGLE column
and comparing it with an delete (on ALL COLUMNS)?
Scott already stated| Quote: |
Just think logically about what it would take for a human to undo an insert: just forget the insert happene. But to undo a delete, you have to have saved away all the data that used to be there.
|
Let me rephrase the same.
Insert generates less undo because it needs less information to rollback (rollback will remove all data). Delete generates more undo because (if required to rollback) it needs much more information. It needs information on all rows to reconstruct whole row back.
Please try this.
Instead of delete, truncate the table.
This will create no undo for the table ( But this DDL will create some UNDO).
>>Seems the undo generation is around 10 times of the parent table
Depends on how things are configured here.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
