Re: How Oracle finds a data block address?

dusanmiloradovic_at_hotmail.com (dusanmiloradovic_at_hotmail.com) wrote in message news:<2bfc209f.0411182336.7e52edb_at_posting.google.com>...
> 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?

What does recursive have to do with it? Remember that Oracle is doing more than just getting the data, it has to get information about where things are, and a physical read can load the buffers for a consistent get; look at the definitions again:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/apc2.htm#26159

The numbers may change if you have multiple sessions looking at your table, and if you repeat your test. Oracle is optimized for the more complicated situations.

>
> 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.

jg

--
@home.com is bogus.
"Orwell, you are way behind !" - Peter H.