Re: Logical IO

  • From: John Glohahi <john.o.golden@xxxxxxxxx>
  • To: Fuyuncat <fuyuncat@xxxxxxxxx>
  • Date: Wed, 18 Nov 2009 16:21:44 +0800

Hi Fuyuncat,

Thanks! Good stuff!

I'm also confused by another question, which may be a tooooo old question.
There are 7 logical reads in 10g when full table scan on the small table
with 1 record, while there are just 3 logical reads for such operation
on the same size table in 9i. Is it 10g's degradation?

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        133  bytes sent via SQL*Net to client
        228  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
John.9205>set autot off
John.9205>insert into iotest values(1);
1 row created.
John.9205>commit;
Commit complete.
John.9205>set autot trace stat
John.9205>select * from iotest;

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

Best Rgds,
John
On Wed, Nov 18, 2009 at 3:25 PM, Fuyuncat <fuyuncat@xxxxxxxxx> wrote:

>  Hi John,
>
> For table scan on the small table that only 1 extent, there are 2 read on
> segment header, 1 for extent count(guess), 1 for hwm. The rest reads for the
> data blocks under hwm. Here is an article for your reference.
> http://www.hellodba.com/Doc/logic_io_secret_FTS_1.html
>
> If it contains more than 1 extent, an additional read on segment header for
> extent map, and it will repeat for every 10 extents.
> Not only the block number affects the logical IO, but also the arraysize of
> the client. If row number in a block larger than the arraysize, it will
> fetch the rows more than once. Pls refer these papers.
>
> http://www.hellodba.com/Doc/logic_io_secret_FTS_2.html
> http://www.hellodba.com/Doc/logic_io_secret_FTS_3.html
>
> Logical io of other operations could also be refered.
> http://www.hellodba.com/Doc/logic_io_secret_CR.html
> http://www.hellodba.com/Doc/logic_io_secret_Current_Mode.html
> http://www.hellodba.com/Doc/logic_io_secret_sorting.html
> http://www.hellodba.com/Doc/logic_io_secret_index_scan.html
>
>
>  2009/11/18 John Glohahi <john.o.golden@xxxxxxxxx>
>
>>
>>
>>     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
>>
>>
>
>
>
> --
> Sr. DBA Fuyuncat
> www.HelloDBA.com <http://www.hellodba.com/>
>
>

Other related posts: