Home » RDBMS Server » Performance Tuning » What is the most expensive dml transaction, insert, update, delete? (Oracle)
What is the most expensive dml transaction, insert, update, delete? [message #580401] Sun, 24 March 2013 03:37 Go to next message
steves
Messages: 4
Registered: March 2013
Junior Member
Hi

Does anyone know what is the most expensive dml transaction, insert, update, delete?

Thanks
Steve
Re: What is the most expensive dml transaction, insert, update, delete? [message #580406 is a reply to message #580401] Sun, 24 March 2013 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None or all, it depends on what you do with them.

Regards
Michel
Re: What is the most expensive dml transaction, insert, update, delete? [message #580407 is a reply to message #580406] Sun, 24 March 2013 04:27 Go to previous messageGo to next message
steves
Messages: 4
Registered: March 2013
Junior Member
Thanks Michael,

Good answer. Thanks for coming back to me.

I was looking at this link:

www.dba-oracle.com/t_dml_statements.html

It discusses old vals and new vals that are stored before and after DML processes.

Lets say there are 4 rows that you do for each. This would be the matrix of old vals and new vals

UPDATE INSERT DELETE
store oldvals yes no yes
store newval yes yes no

num rows stored 8 4 4

I know that is a very simple example but in this case, the update would be the most expensive.

S
Re: What is the most expensive dml transaction, insert, update, delete? [message #580420 is a reply to message #580407] Sun, 24 March 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forget FOREVER this site, it is b...

Regards
Michel
Re: What is the most expensive dml transaction, insert, update, delete? [message #580427 is a reply to message #580420] Sun, 24 March 2013 06:16 Go to previous messageGo to next message
steves
Messages: 4
Registered: March 2013
Junior Member
oh ok
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 Go to previous messageGo to next message
rleishman
Messages: 3700
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 #580588 is a reply to message #580568] Tue, 26 March 2013 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... without speaking about triggers...

Regards
Michel
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 Go to previous messageGo to next message
youngryand
Messages: 8
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 #609967 is a reply to message #609965] Fri, 14 March 2014 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 22711
Registered: January 2009
Senior 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!

assume I initiate an INSERT & the amount of new data exceeds total RAM plus combined size of all REDO log files.
where does all this changed data reside prior to when I issue a single COMMIT?
Re: What is the most expensive dml transaction, insert, update, delete? [message #609968 is a reply to message #609965] Fri, 14 March 2014 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 26 March 2013 07:24
... without speaking about triggers...

Regards
Michel


... and indexes.

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 Go to previous messageGo to next message
youngryand
Messages: 8
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 Go to previous messageGo to next message
John Watson
Messages: 4508
Registered: January 2010
Location: Global Village
Senior Member
As the author of the book you mention, Ryan, I suppose I have to say something Smile
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 Go to previous message
youngryand
Messages: 8
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
Previous Topic: Sql Tuning Advisor - Privileges (sqltrpt.sql)
Next Topic: SQL Execution Runtime Measure
Goto Forum:
  


Current Time: Wed Aug 27 20:44:22 CDT 2014

Total time taken to generate the page: 0.06439 seconds