Home » RDBMS Server » Performance Tuning » What is the most expensive dml transaction, insert, update, delete? (Oracle)
|
|
|
|
|
|
|
|
|
|
| Re: What is the most expensive dml transaction, insert, update, delete? [message #580568 is a reply to message #580427] |
Mon, 25 March 2013 21:40   |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK, I'll play.
Let's assume we have a table with some indexed non-null columns.
- A single row INSERT, UPDATE and DELETE will always rewrite a single table block. So they're the same.
- An UPDATE might overfill a block causing it to chain, thereby updating two table blocks. So update is slower.
- INSERT and DELETE always rewrite index leaf blocks, but UPDATE only rewrites index leaf block if the indexed column changes, so UPDATE is faster.
- If an indexed column DOES change, then UPDATE will rewrite TWO index leaf blocks, so UPDATE is slower
- Statistically, some INSERTs will cause a leaf-block split, which rewrites two leaf blocks and a branch block, so INSERT is slower.
- Statistically, INSERTs will find space in recently used (cached) blocks, so they only need to rewrite the block without reading it, so INSERTs are faster.
Hope that makes it nice and clear.
Ross Leishman
|
|
|
|
|
|
| Re: What is the most expensive dml transaction, insert, update, delete? [message #609965 is a reply to message #580401] |
Fri, 14 March 2014 12:48 |
 |
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
|
Junior Member |
|
|
I can't find anyone who answered this correctly except maybe rleishman. The accepted answer is DELETE.
All three DML operations required that data blocks are read, changed, and subsequently re-written. All of the data file writes however, will go into the buffer cache, and are generally not synchronized to data files until after the commit. Sometimes much later. ONLY the online redo logs have to be updated from the log buffer before a successful message can be returned from a commit. However, all operations have to create comprehensive UNDO of any block they change before they can change that block. This is so that a rollback can occur in the case of something bad happening like a session terminating, or in the simple case of a ROLLBACK being explicitly issued.
UNDO is stored in tablespace data file(s) and has to be written to disk. The amount of UNDO that is generated by various DML statements varies greatly.
During an INSERT, only the ROWIDs of the inserted rows need to be stored to UNDO. This is because to "UNDO" the transaction, only the ROWIDs need be known to delete the inserted rows.
During an UPDATE, slightly more information about what was updated has to be logged to UNDO.
During DELETE, the entire row of every row deleted has to be completely recorded into UNDO before the DELETE can even begin. This is because to ROLLBACK, the rows have to be completely reconstructed from UNDO down to the last byte of data.
[Updated on: Fri, 14 March 2014 12:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: What is the most expensive dml transaction, insert, update, delete? [message #609970 is a reply to message #609967] |
Fri, 14 March 2014 13:30 |
|
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
|
Junior Member |
|
|
>>All of the data file writes however, will go into the buffer cache, and are generally are not synchronized to data files until after the commit.
>WRONG!
I don't disagree with you. Things aren't black and white, right or WRONG as you say. You are correct that data can be synchronize to the data files at any time, even before a commit. But it's a lazy algorithm. Yes, it can be a bottleneck on the DML transaction.
I'm just speaking from a purely textbook answer. One example is chapter 10 of the Oracle Press book for preparation for the Database Administration 1 exam guide. ISBN 978-0-07-159104-1. Although this chapter may not come out and say DELETE is the most expensive, I know it's in the OCP material somewhere. Below is an example of Tom Kyte mentioning that DELETE is the most expensive DML operation. Of course it depends, but if you were to get the question on an exam, the correct answer is DELETE.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30403834755164
"DELETE is the single most expensive DML statement"
[Updated on: Fri, 14 March 2014 13:56] Report message to a moderator |
|
|
|
| Re: What is the most expensive dml transaction, insert, update, delete? [message #609971 is a reply to message #609970] |
Fri, 14 March 2014 15:25 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
As the author of the book you mention, Ryan, I suppose I have to say something 
Re-visiting the chapter now, so long after writing it, I think you are right: it does imply that the different DML operations may generate different volumes of undo and redo, but never says that one operation is more "expensive". I would hope that any exam question would be worded in a way that meant one could work out what the answer should be, given the knowledge that you clearly have of how DML is executed.
And by the way, just to do the moderator thing:
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
I hope you'll keep contributing.
|
|
|
|
| Re: What is the most expensive dml transaction, insert, update, delete? [message #609976 is a reply to message #609971] |
Fri, 14 March 2014 17:49 |
|
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
|
Junior Member |
|
|
Wow,
Hi John. Great book. Really helped me get a handle on the Oracle architecture when I was starting out as a DBA. Did well on the exam too. Nothing worse than an exam prep book that doesn't give you what you need to pass the exam.
I'm pretty sure I read that in one of the two books, but it's possible I "added" to the content, because I've been told at least twice by mentor DBAs that DELETE is the most expensive. I never understood why completely and remembered thinking today that one of the OCP prep books shed light on that.
I'll read up on the postingbest practices as you suggest, but am not too sure how much I really have to contribute.
Ryan
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 22:04:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
