Secret of oracle logic IO: Full Table Scan: Part 3
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2009-11-06 06:34:48
Share to
Full Table Scan --- Large Table
In further, let look into a full table scan on a large table.
SQL代码
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace stat
- HELLODBA.COM>select * from bigtab;
- 529517 rows selected.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 42118 consistent gets
- 7301 physical reads
- 116 redo size
- 27039382 bytes sent via SQL*Net to client
- 388696 bytes received via SQL*Net from client
- 35303 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 529517 rows processed
Check the pins first,
SQL代码
- C:\oracle\product\10.2.0\admin\hellodba.com>set /a a=0
- 0
- C:\oracle\product\10.2.0\admin\hellodba.com>for /f "tokens=1 delims=:" %i in ('findstr /C:"pin" C:\oracle\product\10.2.0\admin\edgar\udump\LIO_large_table.trc') do @set /a a+=1 > NUL
- C:\oracle\product\10.2.0\admin\hellodba.com>echo %a%
- 42118
The pin count matched the Logical reads number. With the table size increased, what different can be found in the Logical reads? Again, it's the segments header. We can find more extent map reading in the pin trace.
SQL代码
- pin ktewh25: kteinicnt dba 140c8c3:4 time 3971468258
- pin ktewh26: kteinpscan dba 140c8c3:4 time 3971468321
- pin ktewh27: kteinmap dba 140c8c3:4 time 3971468367
- pin ktewh27: kteinmap dba 140c8c3:4 time 3971697103
- pin ktewh27: kteinmap dba 140c8c3:4 time 3973270748
- pin ktewh27: kteinmap dba 140c8c3:4 time 3976857792
- pin ktewh27: kteinmap dba 140c8c3:4 time 3980580862
- pin ktewh27: kteinmap dba 140c8c3:4 time 3984468743
- pin ktewh27: kteinmap dba 140c8c3:4 time 3988398058
- pin ktewh27: kteinmap dba 140c8c3:4 time 3992112294
Is there any rules for the extent map reading? check the extent number first,
SQL代码
- HELLODBA.COM>select count(1) from dba_extents where segment_name='BIGTAB' and owner='DEMO';
- COUNT(1)
- ----------
- 73
73 extents, while the reading extent map times is 8, means it will read the extent map for each 10 extents, 8 = ceil(73/10). Is this conclusion right?
Let's dump the header block first, what we interst is the extent map:SQL代码
- ...
- Extent Map
- -----------------------------------------------------------------
- 0x0140c8c1 length: 8
- 0x0140c8c9 length: 8
- 0x0140c8d1 length: 8
- ...
- 0x01401b89 length: 128
- 0x01401c09 length: 128
- 0x01401c89 length: 128
- ...
- Auxillary Map
- --------------------------------------------------------
- Extent 0 : L1 dba: 0x0140c8c1 Data dba: 0x0140c8c4
- Extent 1 : L1 dba: 0x0140c8c1 Data dba: 0x0140c8c9
- ...
- Extent 9 : L1 dba: 0x0140c901 Data dba: 0x01400009
- Extent 10 : L1 dba: 0x01400011 Data dba: 0x01400012
- ...
- Extent 19 : L1 dba: 0x01400209 Data dba: 0x0140020b
- Extent 20 : L1 dba: 0x01400289 Data dba: 0x0140028b
- ..
- Extent 71 : L1 dba: 0x01401c09 Data dba: 0x01401c0b
- Extent 72 : L1 dba: 0x01401c89 Data dba: 0x01401c8b
- ...
And then look back which block it read right after each extent map reading.
SQL代码
- ...
- pin kdswh01: kdstgr dba 140c8c4:1 time 3971469137
- ...
- pin ktewh27: kteinmap dba 140c8c3:4 time 3971697103
- pin kdswh01: kdstgr dba 1400012:1 time 3971697982
- ...
- pin ktewh27: kteinmap dba 140c8c3:4 time 3973270748
- pin kdswh01: kdstgr dba 140028b:1 time 3973291945
- ...
It's exactly the 1st data block of the 10s'th extent. Finally, calculate the fetch IOs,
SQL代码
- HELLODBA.COM>set serveroutput on
- HELLODBA.COM>declare
- 2 cursor vc is select t2.extent_id, t1.block_add, t1.cnt
- 3 from (select to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(ROWID),
- 4 dbms_rowid.rowid_block_number(ROWID)),
- 5 'XXXXXXXX') block_add,
- 6 dbms_rowid.rowid_relative_fno(ROWID) relative_fno,
- 7 dbms_rowid.rowid_block_number(ROWID) block_number,
- 8 count(1) cnt
- 9 from &&owner..&&tabname
- 10 group by dbms_rowid.rowid_relative_fno(ROWID) ,
- 11 dbms_rowid.rowid_block_number(ROWID)
- 12 ) t1,
- 13 demo.tmp_extents t2
- 14 where t1.relative_fno = t2.relative_fno
- 15 and t1.block_number >= t2.block_id and t1.block_number < t2.block_id + t2.blocks
- 16 order by t2.extent_id, block_add;
- 17 comp_cnt pls_integer:=-1;
- 18 array_size pls_integer:=15;
- 19 total_io pls_integer:=1;
- 20 begin
- 21 dbms_output.enable(1000000);
- 22 delete from demo.tmp_extents;
- 23 insert into demo.tmp_extents select * from dba_extents where owner='&&owner' and segment_name='&&tabname';
- 24 for rec in vc loop
- 25 --dbms_output.put_line(rec.block_add||' reads:'||(ceil((rec.cnt+comp_cnt)/array_size)));
- 26 total_io := total_io + ceil((rec.cnt+comp_cnt)/array_size);
- 27 comp_cnt := rec.cnt+comp_cnt - (floor((rec.cnt+comp_cnt)/array_size))*array_size;
- 28 end loop;
- 29 dbms_output.put_line('total reads:'||total_io);
- 30 show_space('&&tabname','&&owner');
- 31 rollback;
- 32 end;
- 33 /
- Enter value for owner: DEMO
- Enter value for tabname: BIGTAB
- old 9: from &&owner..&&tabname
- new 9: from DEMO.BIGTAB
- old 23: insert into demo.tmp_extents select * from dba_extents where owner='&&owner' and segment_name='&&tabname';
- new 23: insert into demo.tmp_extents select * from dba_extents where owner='DEMO' and segment_name='BIGTAB';
- old 30: show_space('&&tabname','&&owner');
- new 30: show_space('BIGTAB','DEMO');1409414 reads:3
- total reads:42107
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ..................... 0
- FS2 Blocks (25-50) ..................... 1
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 1
- Full Blocks ..................... 7,298
- Total Blocks............................ 7,424
- Total Bytes............................. 60,817,408
- Total MBytes............................ 58
- Unused Blocks........................... 0
- Unused Bytes............................ 0
- Last Used Ext FileId.................... 5
- Last Used Ext BlockId................... 7,305
- Last Used Block......................... 128
- total reads:42107
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ..................... 0
- FS2 Blocks (25-50) ..................... 1
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 1
- Full Blocks ..................... 7,298
- Total Blocks............................ 7,424
- Total Bytes............................. 60,817,408
- Total MBytes............................ 58
- Unused Blocks........................... 0
- Unused Bytes............................ 0
- Last Used Ext FileId.................... 5
- Last Used Ext BlockId................... 7,305
- Last Used Block......................... 128
- PL/SQL procedure successfully completed.
Total number is 42107, plus with 10 segment header reads, come to 42117. Where is the last one? Pls note the output of the show_space, there is 1 FS4 block under hwm, if it's 100% free, it's an empty block, no data be fetched from it. Look into the trace file, the last lines:
SQL代码
- ...
- pin kdswh01: kdstgr dba 1401d07:1 time 3993156221
- WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993156299
- FETCH #1:c=0,e=204,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=3993156412
- WAIT #1: nam='SQL*Net message from client' ela= 333 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993156890
- pin kdswh01: kdstgr dba 1401d07:1 time 3993157019
- WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993157074
- pin kdswh01: kdstgr dba 1401d08:1 time 3993157128
- FETCH #1:c=0,e=171,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=3993157177
There is at least 1 records left in 1401d07 after server fetching, and need read the next block to fullfil the arraysize to return data. However, you can see this fetching just returned 1 record, means the block 1401d08 is an empty block.
SQL代码
- HELLODBA.COM>select 1 from bigtab
- 2 where to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(ROWID),
- 3 dbms_rowid.rowid_block_number(ROWID)),
- 4 'XXXXXXXX') = ' 1401D08';
- no rows selected
At last, count in the empty blocks read, we got the right Logical reads number, 42118.