Read Consistency question

Hi All,

I am new to the Oracle world(and to this forum), so bear with my question =)

How does a server process know that when executing a query it needs to search the datafiles(even if there was a partial cache hit)? Meaning, lets say you ran a query and half the rows were already in the buffer cache but the other half are still in the datafiles.....how does the server process know "I already have these rows in the buffer cache, but to complete my query I need to grab these from the datafiles and move them to the cache also"?

I guess my thinking is, lets say you have a table with 10 rows and they have not been modified for awhile(none are in the buffer cache). Then a user adds a row, and that dirty block is in the buffer cache. Then a subsequent user request like "select * from ....." to grab all the rows is ran. Is there something that the server process "looks at" to know that there are now 11 rows, 1 row(block) is in cache and the other are in the datafiles still? How does the server process know that it has successfully gathered all rows from cache and the datafiles?

 

answer:

Oracle has to keep it's promise of providing Read Consistency.

At the time a SELECT statement is initiated Oracle attaches to it the current SCN meaning, all data returned from that query will be consistent to the specific point in time defined by such SCN.

Since datablocks already sitting on buffer_cache have their own SCN Oracle would know if those are or are not good to answer the current query - here is when redo logs come into play.

Picture this... target table a.k.a. segment has 10 datablocks and no indexes. Oracle knows which segment has to be read 'cause FROM clause is telling... also Oracle knows which rows query wants 'cause WHERE clause is telling.

To serve this particular query - no indexes, remember? - Oracle knows it has to retrieve a.k.a. move to buffer_cache all 10 datablocks of target segment then apply the predicate. If Oracle finds out some datablocks are already on buffer_cache SCN will tell when they are good or not to serve this query.

Hope this helps.
__________________
Pablo (Paul) Berzukov

 

personal opinion:

每个数据块上都有一个scn 这个scn用来标识该块被修改的时间，当发生一致读的时候为了避免读脏块，就

在运行查询语句的时候记录一个时间点，与对于检索出来的数据块的scn进行对比 如果数据块的scn大于那个时间点的话 那么认为该块

被修改了，那么如果读取这个块的话就是脏读了，所以需要构造cr块就是根据回滚表空间来构造之前查询语句运行的那个时间点的数据，然后读这种构造好的cr块 来防止脏读