How do I insert, update and delete works (internally) in Oracle database?

5 pts.
Tags:
Oracle Database
Can you explain the states of DB buffer cache, redo log buffer, undo table space etc, when DML statements are executing?


Software/Hardware used:
oracle
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi

this is not a simple question, as there are many activities involved in a DML.
I’ll try to answer, there will be lack of details..probably errors…I studied such things long time ago with 8i version,
and now probably some behaviour has changed.

Let imagine an UPDATE which, as far I remember, is the most complicated, because a “before image” is needed.
For simplicity take in count a very simple update which modifies a single row, like “update table set column=value where column_pk=xx;’
(column_pk==> Primary Key)

Any modification against a data block, at first happens in Database Buffer Cache (DBF)
and only after it is moved to datafiles (if committed).

– The server process (which is the one that works at server side) scans the DBC
to see if the required data block is already cached. Suppose it is not.
– The server process scans the DBC to find a free block where to copy contents from datafile.
– Once a free block is found, the data is copied and the buffer state is changed to PINNED.
– A slot in rollback segment is acquired, and a block of rollback it is
copied to DBC and filled with the present copy of data (the before image => before the update).
– The update modification is applied to the block in DBC.
– The entries related with the operations are written in redo log buffer. I used “operations”, plural,
because both data and rollback changes are written in the redo log, .

At this point it is ALL in memory, nothing (but this is not always true, because of DBWR and LGWR algorithm)
has been written on disk yet.

– the user issues a commit.
– Oracle assigns a unique SCN (System Change Number) to the transaction both in rollback segments
and in the redo log buffer.
– LGWR actually writes the content in the redo log buffer to the redo log files on disk subsystem.
– Once the operating system confirms that the write has been done, Oracle notifies the server process
that the transaction has been committed.

This is a very simplified view around what really happens. You have to take in count that
DBC is not a simple array but there are other structures that make it usable by server process and
backgroud process.

Here some details:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/process.htm#CNCPT9840
http://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT1222

More than this, there are surely other things to take care about:
Locks, Latchs, Read Consistency and probably many other that
I’m forgetting now.

Hope this help
Regards
M.

Its well explained. Check out this. 

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Subhendu Sen
    Assuming you are in a learning process. It is better to learn from your study materials. while online resource obviously help, but as well as you can study external reference also. You can link here for Oracle tutorials: http://www.tutorialspoint.com/listtutorials/oracle/1

    And also learn from this book, 'Oracle Database 11g A Beginner's Guide 1st Edition', by Ian Abramson, Michael Abbey & Michael J Corey

    139,020 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: