Re: Logical IO

  • From: Tanel Poder <tanel@xxxxxxxxxx>
  • To: john.o.golden@xxxxxxxxx
  • Date: Fri, 20 Nov 2009 01:21:21 +0800

John,

In the first test case of yours you experienced the effect of ASSM
tablespace. ASSM formats multiple blocks at a time (thus moves high water
mark up by multiple blocks at a time) even if you have one block actually in
use.

The second test case is due low arraysize. If a block has 100 rows in it,
but you fetch only 15 at a time then subsequent fetches will do more buffer
gets agains the same datablock (as you can not keep buffers pinned across
database calls).

Depending on your database version and whether you can test in an
experimental database you can see the reasons for buffer gets yourself. I
couldn't help it and wrote a blog entry about it :)

http://blog.tanelpoder.com/2009/11/19/finding-the-reasons-for-excessive-logical-ios/


--
Tanel Poder
http://blog.tanelpoder.com


On Wed, Nov 18, 2009 at 1:20 PM, John Glohahi <john.o.golden@xxxxxxxxx>wrote:

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



-- 
Tanel Poder
http://blog.tanelpoder.com

Other related posts: