| Database buffer cache [message #155970] |
Fri, 20 January 2006 01:18  |
aseshu70
Messages: 3
Registered: January 2006
|
Junior Member |
|
|
Hi,
I am new to oracle dba . To understand about db buffer cache, i went through oracle architecture books ,websites and documentation, but all explained in similar fashion like below :
"When an oracle porcess accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are conitnually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list"
I got confused with two things :
MRU end of the LRU list and
LRU end of the LRU list.
Can anybody explain me with better examples.
Is there any other website which explains examples in animated form ?
Pls help.
|
|
|
|
|
|
| Re: Database buffer cache [message #155979 is a reply to message #155977] |
Fri, 20 January 2006 02:14  |
aseshu70
Messages: 3
Registered: January 2006
|
Junior Member |
|
|
Hi,
Thanks a lot. You explained me in layman terms which is very good. I have got 2 more question.
1) When you say 'out of the queue' means the DBWR flushes the buffers to the disk . Can i say is this a dirty buffer ?
In that case,what happens during COMMIT, is DBWR plays any crucial role here ?
2)I understand that the DBWr process moves the dirty buffers from the write list to the disk.
Let's assume that there are some free buffers in the LRU, then there is no need to move the dirty buffers to the write list. Whenever there is a need for room in the LRU, then oracle process moves the dirty buffers. Am i right ?
|
|
|
|
| Re: Database buffer cache [message #155985 is a reply to message #155979] |
Fri, 20 January 2006 02:54 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
-->1) When you say 'out of the queue' means the DBWR flushes the buffers to the disk . Can i say is this a dirty buffer ?
No, its not flushed to the disk from the LRU list but its just overwritten if not dirty and if its a dirty buffer then it will be written to the dirty list or write list.
-->In that case,what happens during COMMIT, is DBWR plays any crucial role here ?
LGWR comes into play after a COMMIT not DBWR
-->I understand that the DBWr process moves the dirty buffers from the write list to the disk.
And you are right in the understanding that dirty buffers are moved from the write list to the disk.
-->Let's assume that there are some free buffers in the LRU, then there is no need to move the dirty buffers to the write list. Whenever there is a need for room in the LRU, then oracle process moves the dirty buffers. Am i right ?
If there are free buffers then no need to move to dirty list but Dirty buffers are always written to dirty list before DBWR writes to disk whether the LRU is full or not.
For this point you can better read the manuals again.
|
|
|
|
|
|
| Re: Database buffer cache [message #156042 is a reply to message #155970] |
Fri, 20 January 2006 07:46 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not sure I'm totally comfortable with the ticket analogy unless you add a bit more to it to clarify. Such as, you start at the MRU end, away from the ticket counter. But you don't always move towards the ticket counter over time. Every time you are accessed, you get moved backwards away from the ticket and you stay at the MRU end. Here is where I lose the analogy.
Not that I have a better one at the moment.
But conceptually, think of it like this. Buffers go in. If nobody uses the buffer then eventually it gets old, and moves to the LRU (least recently used) end. But every time it is used, it gets moved back to the MRU (most recently used) end.
Although, from what I understand, Oracle doesn't actually move the buffers anywhere, because that would be too slow. Instead it just uses counters which keep track of which buffers are being used.
And depending on how the data is read by Oracle, it may not go onto the list at all (bypass the buffer as in a direct path read). Or it may be read and start off in the middle of the list to begin with, not at the MRU end.
Regarding dirty buffers, that is different concept than being in the buffer list in the first place. You can be on the list and not be dirty. Dirty just means you have been modified, and therefore need to be written to disk at some point. But just because you are written to disk doesn't mean you leave the LRU buffer list. You can stay in the buffer for later use.
|
|
|
|
| Re: Database buffer cache [message #156118 is a reply to message #156042] |
Sun, 22 January 2006 11:41 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi,
You may want to read "All About Oracle's Touch-Count Data Block Buffer Algorithm (Jan 2004)" written by Craig A Shallahamer. You can download this excellent white paper here
http://resources.orapub.com/category_s/2.htm
Since release 8.1 Oracle has introduced a touch count algorithm for it' s buffer cache management. Every time a block is accessed / touched in the buffer cache it' s counter is incremented. A block should be touched more than 1 time to be considered as really popular.
Regards
Guy Lambregts
|
|
|
|
| Re: Database buffer cache [message #156219 is a reply to message #155970] |
Mon, 23 January 2006 13:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yeah I think I've read this papeer, if it is the one I was thinking about. It is why I mentioned the counters rather than actually moving blocks. You are right, excellent paper indeed, thanks for providing the link to it as I didn't have a copy (or the author or source).
|
|
|
|
|
|
| Re: Database buffer cache [message #156571 is a reply to message #155979] |
Wed, 25 January 2006 20:41 |
chj733
Messages: 11
Registered: October 2005
Location: CHINA
|
Junior Member |
|
|
| aseshu70 wrote on Fri, 20 January 2006 16:14 |
Hi,
Thanks a lot. You explained me in layman terms which is very good. I have got 2 more question.
1) When you say 'out of the queue' means the DBWR flushes the buffers to the disk . Can i say is this a dirty buffer ?
In that case,what happens during COMMIT, is DBWR plays any crucial role here ?
2)I understand that the DBWr process moves the dirty buffers from the write list to the disk.
Let's assume that there are some free buffers in the LRU, then there is no need to move the dirty buffers to the write list. Whenever there is a need for room in the LRU, then oracle process moves the dirty buffers. Am i right ?
|
Hi
When Oracle process search the LRU list without finding free buffers or exceeding the limmit NUMBER,he will flush the buffers near to the END OF LRU,or send a message to dbwr to write dirty buffers to disk.during the searching,if Oracel meet dirty buffer,he will move it to the LRUW list.
When you offer a commit,the lgwr will work,he will flush log buffer to disk,and mark commit to the current transaction,and return the success info to user,after your commit,the locks hold by the trans will have been releaseed,and the redo of block change,undo block will have been writen to logfile,and the ITL list of relevant blocks will have been refreshed.But the dbwr won't work immediatly,it will "be waked up" by the next checkpoint
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
