Re: Logical IO
Date: Thu, 19 Nov 2009 20:00:22 -0800 (PST)
Message-ID: <352275.12472.qm_at_web32004.mail.mud.yahoo.com>
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_at_poderc.com> wrote:
From: Tanel Poder <tanel_at_poderc.com>
Subject: Re: Logical IO
To: john.o.golden_at_gmail.com
Cc: oracle-l_at_freelists.org
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_at_gmail.com> wrote: Hi Forks,Received on Thu Nov 19 2009 - 22:00:22 CST
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 -- http://www.freelists.org/webpage/oracle-l