Re: How Oracle finds a data block address?

On 18 Nov 2004 23:36:32 -0800, dusanmiloradovic_at_hotmail.com (dusanmiloradovic_at_hotmail.com) wrote:

>Thank you Sybrand, I couldn't find that in the concepts manual.
>
>So, if the info about adressess of used data blocks is stored in the
>segment header, this is what happened according to my understanding:
>
>1.create table t as select from all_objects;
>Oracle reserves blocks for the table t, and stores that info in the
>segment header of table t. Segment header info is then cached in the
>data dictionary cache.
>2. select * from t
>This generates no recursive sql. Does it mean that accessing the data
>dictionary cache needs no recursive sql?
>Anyway, Oracle parses the sql, and after finding the row source, it
>looks for the address of the first and last block of table t in data
>dictionary cache. Then, it generates the list of block addresses that
>needs to be found , and existence of each block in the data buffer
>cache is checked. After that, all that blocks are fetched.
>3.alter system flush shared_pool
>select * from t
>After that, lot of recursive sql occured. Segment header(among other
>things) needs to be read and cached again in data dictionary cache.
>
>Now, if that is as easy as I have written, I don't understrand the
>aritmetics:

Obviously you are grossly oversimplifying everything, in such a fashion it is just plain incorrect.

1 Oracle doesn't reserve blocks for the complete table, it just allocates one extent. Obviously in a dictionary managed tablespace it would need to query sys.fet$
2 No recursive sql is necessary when the requested info actually *is* in the dictionary cache. If the info is not in the dictionary cache, recursive sql is necessary. In a well tuned shared pool however recursive sql is seldom, and in the past it mainly occurred to access fet$ and uet$ (see 1)
'looks for the address of the first and last block' This is definitely incorrect. During full table scan Oracle tries to read one extent in memory, not the complete table. So it reads the segment header, finds the rba for the first segment, and it reads n continguous blocks as indicated by the segment. However, in doing so, a normal read operation is attempted, so for each block Oracle verifies whether or not the block is already in cache. This may force Oracle to issue a consistent get (if the data is in cache and the block is newer compared to the start of the select * from t, Oracle will need to visit the rollback segment associated to the transaction, to get the before image).
After reading this extent Oracle *revisits* the segment header to retrieve information for the next extent. As the segment header may have been removed from the buffer cache this might also result in a consistent get.
Your second phrase is also definitely incorrect, the existence check is not being executed once, but per read. If you would do it once you wouldn't have correctly functioning multiversioning. If you want to verify what is going on during the operation, set event 10046 in your session on level 8, and you will see *exactly* which blocks are being fetched.
How to do that is well documented everywhere (including this newsgroup archives at Google) so I am not going to repeat it here.

3 The alter system flush shared pool only flushes the shared pool, which includes the dictionary cache, it doesn't flush the buffer cache. It is completely unnecessary and destructive.

Regards

--
Sybrand Bakker, Senior Oracle DBA