Re: Logical IO

  • From: Dion Cho <ukja.dion@xxxxxxxxx>
  • To: john.o.golden@xxxxxxxxx
  • Date: Wed, 18 Nov 2009 17:39:54 +0900

Wouldn't it be better to trace it yourself with 10200 event and/or 10046
event?

- 10200 event would tell you which blocks are logically read.
- 10046 event would tell you which blocks are physicall read(when the buffer
cache was clean).

The number of blocks being read would also be depedent on how the segment
space is managed(auto vs. manual) , which would be easily verifed with 10046
and/or 10200 event.


================================
Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================


2009/11/18 John Glohahi <john.o.golden@xxxxxxxxx>

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