How Oracle finds a data block address?

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 Received on Thu Nov 18 2004 - 04:52:52 CST