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