Re: Logical IO

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

>
>
> 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.freelists.org/webpage/oracle-l