Re: Logical IO

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: john.o.golden@xxxxxxxxx, tanel@xxxxxxxxxx
  • Date: Thu, 19 Nov 2009 20:00:22 -0800 (PST)

Finally!  Someone who agrees with me on ASSM and logical I/O waits and more so, 
has more ammo than I ever had to back it up, (OK, so I was backing mine up a 
bit with DBA intuition! :))
 
Thanks Tanel, this is great stuff!


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 
"Go away before I replace you with a very small and efficient shell script..."

--- On Thu, 11/19/09, Tanel Poder <tanel@xxxxxxxxxx> wrote:


From: Tanel Poder <tanel@xxxxxxxxxx>
Subject: Re: Logical IO
To: john.o.golden@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Thursday, November 19, 2009, 10:21 AM


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: