Skip to Main Content
  • Questions
  • Why Oracle Copies the data into Buffer for Insertion

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Chandan.

Asked: March 31, 2014 - 8:12 am UTC

Last updated: March 31, 2014 - 5:59 pm UTC

Version: 10.1.0

Viewed 1000+ times

You Asked

HI Tom,
My question is that why Oracle Copies the data into Buffer for Insertion? I mean it could use direct insertion path for copying the data.

Thanks
Chandan

and Tom said...

Direct path inserts always have to write above the high water mark for a table.

Therefore, if an insert of a single row were to use direct path operations

a) the table would always grow, you could never reuse space
b) there would be at most one row per block
c) there would be at most one transaction inserting into the table at a time (direct path operations lock the segment)
d) there would be no ability for a transaction to read the table it just inserted into (you cannot read a segment that you've direct pathed into until you commit)
e) there would be no ability to insert more than a single table into a table in a given transaction (you get one shot, then you have to finish your transaction)

so, in general, it would be a really bad idea to direct path data in general. It is reserved for large bulk operations like a create table as select, alter table t move, insert /*+ APPEND */


Also, you have to WAIT for the physical IO's to complete in real time. Your dedicated/shared server process would be writing to the data files directly. And you would have to write an entire block at a time (8k typically). Say you insert a 100 byte row - would you rather wait for 100 bytes of redo to be written or 8k of a database block? Additionally - LGWR is operating constantly in the background. There is a very very very good chance that when you go to commit - LGWR has already written your 100bytes to disk and you don't really have to wait for it at all - whereas if you direct pathed, you'd constantly be waiting for IO's to complete.


Now, in a large bulk operation - where you are inserting 10's of thousands, millions, billions, trillions of rows - the physics all change. You don't want to use the buffer cache for something like that (the things won't fit in there, you don't want to reuse space - you just want to slam this new data in there).


So, for OLTP - buffer cache makes total sense and would be the only way to go.

But for extract/transform/load - ETL - processing, direct path is the way to go.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Chandan Rattan, March 31, 2014 - 5:38 pm UTC

Thanks a lot but hv one doubt. You said that
d) there would be no ability for a transaction to read the table it just inserted into (you cannot read a segment that you've direct pathed into until you commit)

Why this happens? I mean in Direct Path, its writing into data files, so while doing select * from table , why its not selecting from table. why it throw error ? Why only after doing the commit, it will return the rows from table.
Tom Kyte
March 31, 2014 - 5:59 pm UTC

because there is no undo generated, there is quite simply no way to provide a consistent read - which is the only way we read.

We can provide consistent reads for every other transaction out there - except for the one that direct pathed the data. They just ignore the newly added data (they cannot see it, it does not exist). But the transaction that created would see it (if they could query it), but without the undo information - there is no chance of a consistent read being supported - so no go.

after they commit- the entire world can see the new data and all operations on the existing data will always generate undo, permitting a fully consistent read for every one.

if consistent read is not a term you are familiar with, see: http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60asktom-176254.html

Great explanation!

Tom, April 01, 2014 - 12:04 pm UTC


More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions