OraFAQ Forum: Server Administration » DELETE and Buffer Cache

Home » RDBMS Server » Server Administration » DELETE and Buffer Cache (10g,xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DELETE and Buffer Cache [message #472305]

Thu, 19 August 2010 15:51

zahidbashir
Messages: 34
Registered: September 2009

Member

Hi,
1)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?

This is a basic concept but i am still confused about it. Please help
Regards,

Report message to a moderator

Re: DELETE and Buffer Cache [message #472315 is a reply to message #472305]

Thu, 19 August 2010 17:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How is this change then applied to the data in datafiles after commit?

the change may be applied to the datafile BEFORE the COMMIT.

Report message to a moderator

Re: DELETE and Buffer Cache [message #472341 is a reply to message #472315]

Fri, 20 August 2010 01:19

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

will this statement drag any data from the datafile to database buffer?

Yes.

Quote:

How is the change made by a DELETE statement recorded in buffer cache?

Block is loaded from buffer cache to PGA, it is modified there and put back into the buffer cache.

Quote:

How is this change then applied to the data in datafiles after commit?

There is no relation between commit time and write of the block into the file.
DBWR who writes the buffer from cache to file is asynchronous from your own process. It can write the buffer/block before or after your commit a it wants/needs.

Regards
Michel

Report message to a moderator

Re: DELETE and Buffer Cache [message #472527 is a reply to message #472341]

Sat, 21 August 2010 10:47

zahidbashir
Messages: 34
Registered: September 2009

Member

so what data will it bring in buffer cache if i want to delete a specific employee from emp table? What about INSERT? And if i am not wrong when another user want to retrieve the deleted row (in case the first user has not commited), the row is returned to the second user by constructing the before image of data using UNDO.
I searched for documentation to explain the status/picture of data in Buffer cache in response to statements like INSERT, DELETE but couldn't find one. Can you please provide me with a link.
Regards,

Report message to a moderator

Re: DELETE and Buffer Cache [message #472529 is a reply to message #472527]

Sat, 21 August 2010 10:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.developerbay.net/Threads/Oracle/dml-effects-on-redo-log-buffer-undo-segment-db-buffer-cache-db-writer-log-writer-05398484. html

Report message to a moderator

Re: DELETE and Buffer Cache [message #472531 is a reply to message #472529]

Sat, 21 August 2010 11:22

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I searched for documentation to explain the status/picture of data in Buffer cache in response to statements like INSERT, DELETE but couldn't find one.

Because it is complex and the exact picture depends on many things that happen concurrently and the current of cache and disk.

Regards
Michel

Report message to a moderator

Re: DELETE and Buffer Cache [message #472532 is a reply to message #472531]

Sat, 21 August 2010 11:58

zahidbashir
Messages: 34
Registered: September 2009

Member

ok, but back to the first question.. what data will it bring in buffer cache if i want to delete a specific employee from emp table?

Report message to a moderator

Re: DELETE and Buffer Cache [message #472533 is a reply to message #472532]

Sat, 21 August 2010 12:06

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Block is loaded from buffer cache to PGA, it is modified there and put back into the buffer cache.

Regards
Michel

Report message to a moderator

Previous Topic:	Error at Setup of Archive log.
Next Topic:	Finding Failed login attempts in database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 21:57:57 CDT 2024