Re: How Oracle finds a data block address?

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:

SQL> create table t as select * from all_objects   2 /

Table created.

SQL> set autotrace traceonly
SQL> select * from t
  2 /

3306 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'T' Statistics

          0  recursive calls
         12  db block gets
        261  consistent gets
         40  physical reads
          0  redo size
     347051  bytes sent via SQL*Net to client
      24845  bytes received via SQL*Net from client
        222  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3306  rows processed

SQL> / 3306 rows selected.

SQL> select num_rows,blocks,empty_blocks from user_tables where table_name='T'
  2 /

  NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------

      3306 40 0

So, I understand the 40 physical reads, but what about 12 db block gets and 261 consistent gets, since there were no recursive calls?

Regards,
Dusan Miloradovic

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<mjspp01vh3aqh39l79jq0bohdlocg91a6u_at_4ax.com>...
> On 18 Nov 2004 10:19:01 -0800, dusanmiloradovic_at_hotmail.com
> (dusanmiloradovic_at_hotmail.com) wrote:
>
> >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?
>
> In the segmentheader.
> Please read the concepts manual.
Received on Fri Nov 19 2004 - 01:36:32 CST