Re: How Oracle finds a data block address?

Thak you very much for your detailed explanation. The point 2 is the thing that was confusing me, until you explained that if the segment header was in the data dictionary cache, then no recursive sql was needed. I just didn't know that. I flushed the shared pool in order to delete the dictionary cache, but wasn't able to make the conclusions myself.

Thanks again
Dusan Miloradovic

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<lcgsp0lejgj5dtl4p4891pen7bb1egoddd_at_4ax.com>...
> 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
Received on Sun Nov 21 2004 - 06:59:11 CST