Logical IO

  Hi Forks,

I ever read a ppt (http://www.juliandyke.com/Presentations/LogicalIO.ppt) about
logical IO, it mentioned how to calculate the IO. But I'm confused with my
test result and the conclusion in this parper. It said in Full Table Scan,
it will first read the segment header for 3 times. While in my test, things
are different.

For an emty table, it's undoubted 3 IO.

John.10g>create table iotest ( x number);
Table created.
John.10g>set autot trace stat
John.10g>select * from iotest;
no rows selected

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

However, after insert 1 record, the logical IO increased to 7.

John.10g>insert into iotest values (1);
1 row created.
John.10g>commit;
Commit complete.
John.10g>alter system flush buffer_cache;
System altered.
John.10g>select * from iotest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          6  physical reads
          0  redo size
        404  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

By 10046 event trace the waits, I saw it read the segment header physically,
and 5 data blocks in the 1st extent. According to the ppt, it
should additional read the header for 2 times (totally 3 times with the
physical read), the final IO should be 8. While the trace just reported 7.

In another test, I increased the table records number, found the logical
reads is much larger than its block number.

John.10g>insert into iotest select rownum from dba_objects;
57008 rows created.
John.10g>commit;
Commit complete.
John.10g>set autot off
John.10g>set serveroutput on
John.10g>exec show_space('IOTEST');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               1
Full Blocks        .....................              85
Total Blocks............................              96
Total Bytes.............................         786,432
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          60,441
Last Used Block.........................               8
PL/SQL procedure successfully completed.
John.10g>set autot trace stat
John.10g>select * from iotest;
57009 rows selected.

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3893  consistent gets
          0  physical reads
          0  redo size
     825870  bytes sent via SQL*Net to client
      42185  bytes received via SQL*Net from client
       3802  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      57009  rows processed

There are just 96 blocks under hwm, while I got 3893 logical reads.

Anybody can help me to understand it? Thanks in adv.

Rds,
John

Other related posts: