Home » RDBMS Server » Performance Tuning » Oracle Architecture
Oracle Architecture [message #608725] Sun, 23 February 2014 20:37 Go to next message
Messages: 1
Registered: February 2014
Junior Member
Hello everyone,
I need someone to put me out of mystery.

An oracle data-block is fetched from disk and placed in the middle of the db buffer cache (midpoint insertion). In the documentation, it states that if the touch count is greater 2, the buffer will be move to the MRU of the LRU list. Is that right? the touch can be incremented at most once every three second. if that is case, then that means on a busy OLTP database, a lot of the buffer will be in the hot zone region of the LRU, is that correct? Furthermore, is it correct if I say the hot zone is only use to cache pinned or dirty buffer, or are free buffer present in the hot zone?

Lets assume user A manipulated a data block, therefore it is considered dirty and the transaction made by user A was committed. can other session issue a select statement on the dirty block before it gets written to the LRUW or disk? if that is possible, then can I say a dirty block can stay in the buffer cache for a long period of time if it is frequently assessed?

If the server process or DBW0 moves the dirty buffer from the LRU list to the dirty list, does that mean that the dirty buffer will no longer be present in the buffer cache?

When a user manipulate a datablock in the cache with committing the transaction, oracle utilizes undo segment for read consistency so that other sessions can only see the old data.Is that correct? Furthermore, let's assume that the user issue commits which means that the changes made by the user is now permanent, other users will be able to see new changes. How? I know that sometimes the manipulated buffer might still be present in the cache, so does that means that other session will be using the dirty block to view the update version of the buffer?

Server process or DBW0 begin scanning for free buffer from the bottom (LRU or cold) of the LRU list. If DBW0 or server process detect any dirty buffer, it will either be written to checkpoint queue or they will written to disk depending on the circumstances. Now, I've read several discussion on google stating that it is not mandatory for DBW0 to write dirty blocks to the checkpoint queue, it can write directly from the LRU list. Won't this mess up the order of SCN# for this blocks? How does oracle ensure that data blocks are written to disk in the order of SCN# if the DBW0 can bypass the checkpoint queue?
Re: Oracle Architecture [message #608726 is a reply to message #608725] Sun, 23 February 2014 20:55 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Newbies like you should learn about the parts of Oracle that you can control & avoid worrying needlessly about the parts of Oracle that you can not control.

How will you behave differently once all the question is the post above are answered & understood?

>I've read several discussion on google stating that it is not mandatory for DBW0 to write dirty blocks to the checkpoint queue,
There is NO "checkpoint queue".

Assume a single session starts a VERY, Very, very big UPDATE; such that the amount of data exceeds the combined size of RAM and all REDO log file.
What happens to all the modified blocks which now exist PRIOR to issuing the single COMMIT?
Re: Oracle Architecture [message #608732 is a reply to message #608725] Mon, 24 February 2014 00:49 Go to previous message
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
Senior Member
There are too many mistakes here to correct. What have you been reading? You need to start with this,

Previous Topic: AWR top events:: enq: TX - row lock contention
Next Topic: Oracle contain clause performance issue
Goto Forum:

Current Time: Wed Mar 22 22:47:38 CDT 2023