Re: One for the Oracle Gods - db buffers questions

chris adams a écrit dans le message <35F7E46C.C7CC9BE1_at_sphinxcst.co.uk>...
>All,
>

I 've suppressed those questions I can't anwwer cause it would be too long and you have first to have a look to Oracle Doc.

But for those remainding questions, you're thinking wrong.

>How does a user process check to see if a db block is in the buffered
>cache?
>(All I know is it uses some kind of hash calculation to find the
>(memory?) address of the buffer.)
>

A user process doesn't check anything in the buffers ! The process is just blowing some SQL text thru a connection and it's the server process which is attached that searchs for data. The user process just waits for the naswer. So, very simply it's Oracle that knows in a kind of buffer map if asked data is already in buffers or not.

>Also, how does a process work out if the buffer has been modified by
>another process or is clean?
>(Some kind of flag? Can we see these from any of the dynamic or X$
>tables?)
>

It's the same idea : user process doesn't check anything in the buffers. If the buffer has not changed, Oracle reads it. If the buffer has changed and has not been commited Oracle reads the corresponding before image on RBS.
If the buffer has changed and has been commited and the timestamp of the user request is newer than the timestamp of buffer change, Oracle reads the buffer.
If the buffer has changed and has been commited and the timestamp of the user request is older than the timestamp of buffer change, Oracle reads the before page in the RBS for the user have a consisten read (*).

>After modifying a block, how does a process find a free block in the
>redo buffers cache?
>(Is it a cylic list that gets flushed?)
>

Oracle is writing in the redo.log not the user process. Redo.log are sequential files, so changes are writen as soon as possible at the currrent position in that sequential file.
When a Redo.log is full, Oracle switches on the other one. You ahve to choose to archive those freeed Redo.log. Or not and Oracle will rewrite it.

>When checking the LRU list for free buffers, if it comes across a dirty
>one it will supposedly moves it to the dirty list.
>Does it actually move the block or just a pointer?
>

A buffer is dirty, and known as well by Oracle, as soon as one byte has been changed in that buffer. It's dirty because buffer differs from block. Oracle will write it later, at shutdown or when it needs some place and the buffer is not really used. When Oracle needs buffer place, it flushes the not recently used ones which are not dirty and writes the dirty ones.

Don't think technical Input/Output. Think transactionnal ACIDidity. Atomicity of transactionnal work : the whole work is done or not done. Half done work is impossible
Consistency : (*) that 's why Oracle keeps before image as long as it is necessary for long running jobs (DB2 locks for read). Isolation : of users from other
Durability : logging against crashes

Regards,
Jean-Philippe
>
>
Received on Thu Sep 10 1998 - 00:00:00 CDT