Re: How Oracle finds a data block address?

Unfortunately, the thing that bothers me here is not written in the docs.
That is: where does Oracle find the fileid# and blockid# for each block,after issuing the full table scan, if not in the data dictionary?
Also, if the data dictionary is cached, how do you retreive necessary info without recursive sql?
I thought that recursive sql is needed for finding the file#id and block#id of blocks that were to be retreived with full table scan, but obviouslly I was wrong.

Dusan Miloradovic

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<VuWdnfa-hKA7HAHcRVn-uA_at_comcast.com>...
> <dusanmiloradovic_at_hotmail.com> wrote in message
> news:2bfc209f.0411180252.30939b81_at_posting.google.com...
> | In order to find a data block in the buffer, file_id and block_id must
> | be provided. But where from Oracle gets them?
> | I presumed that Oracle creates row source when it hard parse the
> | query, use some recursive sql from the data dictionary,and then search
> | for the block in the buffer. I supposed that this inforamtion was
> | included with the parsed sql.
> | But:
> |
> | SQL> create table t as select * from all_objects;
> |
> | Table created.
> |
> | SQL> set autotrace traceonly
> | SQL> select * from t
> | 2 /
> |
> | 32446 rows selected.
> |
> |
> | Execution Plan
> | ----------------------------------------------------------
> | 0 SELECT STATEMENT Optimizer=CHOOSE
> | 1 0 TABLE ACCESS (FULL) OF 'T'
> |
> |
> |
> |
> | Statistics
> | ----------------------------------------------------------
> | 0 recursive calls
> | 0 db block gets
> | 2587 consistent gets
> | 442 physical reads
> | 0 redo size
> | 2294399 bytes sent via SQL*Net to client
> | 24297 bytes received via SQL*Net from client
> | 2165 SQL*Net roundtrips to/from client
> | 0 sorts (memory)
> | 0 sorts (disk)
> | 32446 rows processed
> |
> | Altough this was the first time this query was executed, it had 0
> | recursive calls.
> |
> | After that i typed this:
> |
> | SQL> alter system flush shared_pool;
> |
> | System altered.
> |
> | SQL> select * from t
> | 2 /
> |
> | 32446 rows selected.
> |
> |
> | Execution Plan
> | ----------------------------------------------------------
> | 0 SELECT STATEMENT Optimizer=CHOOSE
> | 1 0 TABLE ACCESS (FULL) OF 'T'
> |
> |
> |
> |
> | Statistics
> | ----------------------------------------------------------
> | 238 recursive calls
> | 0 db block gets
> | 2634 consistent gets
> | 419 physical reads
> | 0 redo size
> | 2294399 bytes sent via SQL*Net to client
> | 24297 bytes received via SQL*Net from client
> | 2165 SQL*Net roundtrips to/from client
> | 2 sorts (memory)
> | 0 sorts (disk)
> | 32446 rows processed
> |
> | Now it has 238 recursive calls!
> | So, where Oracle keeps this info?
> |
> | Dusan Miloradovic
>
> data dictionary tables are owned by SYS, you can see some of the information
> in data dictionary views
>
> the USER_SEGMENTS and USER_EXTENTS views expose some of the physical storage
> characteristics of tables, indexes, clusters, LOB storage, etc.
>
> recursive calls are only needed if the necessary data dictionary information
> is not cached
>
> immediately after creating the table, there is no need to retrieve its
> definition for the data dictionary since the just-used info is already
> cached
>
> take a look at the concepts and dba manuals for more details
>
> ++ mcs
Received on Thu Nov 18 2004 - 12:19:01 CST