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?
There are a zillion questions packed on the initial posting, let me address this particular bit...
DBWn always writes to disk - "checkpoint queue" is a list of dirty buffers.
Originally Posted by gasbie
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width