HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

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  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

Full Table Scan --- Large Table

    In further, let look into a full table scan on a large table.

SQL代码
  1. HELLODBA.COM>conn demo/demo   
  2. Connected.   
  3. HELLODBA.COM>alter system flush buffer_cache;   
  4.   
  5. System altered.   
  6.   
  7. HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';   
  8.   
  9. Session altered.   
  10.   
  11. HELLODBA.COM>set autot trace stat   
  12. HELLODBA.COM>select * from bigtab;   
  13.   
  14. 529517 rows selected.   
  15.   
  16.   
  17. Statistics  
  18. ----------------------------------------------------------   
  19.           0  recursive calls   
  20.           0  db block gets   
  21.       42118  consistent gets   
  22.        7301  physical reads   
  23.         116  redo size  
  24.    27039382  bytes sent via SQL*Net to client   
  25.      388696  bytes received via SQL*Net from client   
  26.       35303  SQL*Net roundtrips to/from client   
  27.           0  sorts (memory)   
  28.           0  sorts (disk)   
  29.      529517  rows processed  

    Check the pins first,
 

SQL代码
  1. C:\oracle\product\10.2.0\admin\hellodba.com>set /a a=0   
  2. 0   
  3. 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   
  4.   
  5. C:\oracle\product\10.2.0\admin\hellodba.com>echo %a%   
  6. 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代码
  1. pin ktewh25: kteinicnt dba 140c8c3:4 time 3971468258   
  2. pin ktewh26: kteinpscan dba 140c8c3:4 time 3971468321   
  3. pin ktewh27: kteinmap dba 140c8c3:4 time 3971468367   
  4. pin ktewh27: kteinmap dba 140c8c3:4 time 3971697103   
  5. pin ktewh27: kteinmap dba 140c8c3:4 time 3973270748   
  6. pin ktewh27: kteinmap dba 140c8c3:4 time 3976857792   
  7. pin ktewh27: kteinmap dba 140c8c3:4 time 3980580862   
  8. pin ktewh27: kteinmap dba 140c8c3:4 time 3984468743   
  9. pin ktewh27: kteinmap dba 140c8c3:4 time 3988398058   
  10. pin ktewh27: kteinmap dba 140c8c3:4 time 3992112294  

    Is there any rules for the extent map reading? check the extent number first,
 

SQL代码
  1. HELLODBA.COM>select count(1) from dba_extents where segment_name='BIGTAB' and owner='DEMO';   
  2.   
  3.   COUNT(1)   
  4. ----------   
  5.         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代码
  1. ...   
  2.   Extent Map   
  3.   -----------------------------------------------------------------   
  4.    0x0140c8c1  length: 8       
  5.    0x0140c8c9  length: 8       
  6.    0x0140c8d1  length: 8       
  7.    ...   
  8.    0x01401b89  length: 128     
  9.    0x01401c09  length: 128     
  10.    0x01401c89  length: 128     
  11. ...   
  12.   Auxillary Map   
  13.   --------------------------------------------------------   
  14.    Extent 0     :  L1 dba:  0x0140c8c1 Data dba:  0x0140c8c4   
  15.    Extent 1     :  L1 dba:  0x0140c8c1 Data dba:  0x0140c8c9   
  16.    ...   
  17.    Extent 9     :  L1 dba:  0x0140c901 Data dba:  0x01400009   
  18.    Extent 10    :  L1 dba:  0x01400011 Data dba:  0x01400012   
  19.    ...   
  20.    Extent 19    :  L1 dba:  0x01400209 Data dba:  0x0140020b   
  21.    Extent 20    :  L1 dba:  0x01400289 Data dba:  0x0140028b   
  22.    ..   
  23.    Extent 71    :  L1 dba:  0x01401c09 Data dba:  0x01401c0b   
  24.    Extent 72    :  L1 dba:  0x01401c89 Data dba:  0x01401c8b   
  25. ...  

    And then look back which block it read right after each extent map reading.

SQL代码
  1. ...   
  2. pin kdswh01: kdstgr dba 140c8c4:1 time 3971469137   
  3. ...   
  4. pin ktewh27: kteinmap dba 140c8c3:4 time 3971697103   
  5. pin kdswh01: kdstgr dba 1400012:1 time 3971697982   
  6. ...   
  7. pin ktewh27: kteinmap dba 140c8c3:4 time 3973270748   
  8. pin kdswh01: kdstgr dba 140028b:1 time 3973291945   
  9. ...  

    It's exactly the 1st data block of the 10s'th extent. Finally, calculate the fetch IOs,

SQL代码
  1. HELLODBA.COM>set serveroutput on  
  2. HELLODBA.COM>declare  
  3.   2    cursor vc is select t2.extent_id, t1.block_add, t1.cnt   
  4.   3                  from (select to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(ROWID),   
  5.   4                                                                            dbms_rowid.rowid_block_number(ROWID)),   
  6.   5                                      'XXXXXXXX') block_add,   
  7.   6                               dbms_rowid.rowid_relative_fno(ROWID) relative_fno,   
  8.   7                               dbms_rowid.rowid_block_number(ROWID) block_number,   
  9.   8                               count(1) cnt   
  10.   9                        from &&owner..&&tabname   
  11.  10                   group by dbms_rowid.rowid_relative_fno(ROWID) ,   
  12.  11                                 dbms_rowid.rowid_block_number(ROWID)   
  13.  12                   ) t1,   
  14.  13                   demo.tmp_extents t2   
  15.  14                  where t1.relative_fno = t2.relative_fno   
  16.  15                  and t1.block_number >= t2.block_id and t1.block_number < t2.block_id + t2.blocks   
  17.  16                  order by t2.extent_id, block_add;   
  18.  17    comp_cnt pls_integer:=-1;   
  19.  18    array_size pls_integer:=15;   
  20.  19    total_io pls_integer:=1;   
  21.  20  begin  
  22.  21    dbms_output.enable(1000000);   
  23.  22    delete from demo.tmp_extents;   
  24.  23    insert into demo.tmp_extents select * from dba_extents where owner='&&owner' and segment_name='&&tabname';   
  25.  24    for rec in vc loop   
  26.  25      --dbms_output.put_line(rec.block_add||' reads:'||(ceil((rec.cnt+comp_cnt)/array_size)));   
  27.  26      total_io := total_io + ceil((rec.cnt+comp_cnt)/array_size);   
  28.  27      comp_cnt := rec.cnt+comp_cnt - (floor((rec.cnt+comp_cnt)/array_size))*array_size;   
  29.  28    end loop;   
  30.  29    dbms_output.put_line('total reads:'||total_io);   
  31.  30    show_space('&&tabname','&&owner');   
  32.  31    rollback;   
  33.  32  end;   
  34.  33  /   
  35. Enter value for owner: DEMO   
  36. Enter value for tabname: BIGTAB   
  37. old   9:                       from &&owner..&&tabname   
  38. new   9:                       from DEMO.BIGTAB   
  39. old  23:   insert into demo.tmp_extents select * from dba_extents where owner='&&owner' and segment_name='&&tabname';   
  40. new  23:   insert into demo.tmp_extents select * from dba_extents where owner='DEMO' and segment_name='BIGTAB';   
  41. old  30:   show_space('&&tabname','&&owner');   
  42. new  30:   show_space('BIGTAB','DEMO');1409414 reads:3   
  43. total reads:42107   
  44. Unformatted Blocks .....................               0   
  45. FS1 Blocks (0-25)  .....................               0   
  46. FS2 Blocks (25-50) .....................               1   
  47. FS3 Blocks (50-75) .....................               0   
  48. FS4 Blocks (75-100).....................               1   
  49. Full Blocks        .....................           7,298   
  50. Total Blocks............................           7,424   
  51. Total Bytes.............................      60,817,408   
  52. Total MBytes............................              58   
  53. Unused Blocks...........................               0   
  54. Unused Bytes............................               0   
  55. Last Used Ext FileId....................               5   
  56. Last Used Ext BlockId...................           7,305   
  57. Last Used Block.........................             128   
  58. total reads:42107   
  59. Unformatted Blocks .....................               0   
  60. FS1 Blocks (0-25)  .....................               0   
  61. FS2 Blocks (25-50) .....................               1   
  62. FS3 Blocks (50-75) .....................               0   
  63. FS4 Blocks (75-100).....................               1   
  64. Full Blocks        .....................           7,298   
  65. Total Blocks............................           7,424   
  66. Total Bytes.............................      60,817,408   
  67. Total MBytes............................              58   
  68. Unused Blocks...........................               0   
  69. Unused Bytes............................               0   
  70. Last Used Ext FileId....................               5   
  71. Last Used Ext BlockId...................           7,305   
  72. Last Used Block.........................             128   
  73.   
  74. 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代码
  1. ...   
  2. pin kdswh01: kdstgr dba 1401d07:1 time 3993156221   
  3. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993156299   
  4. FETCH #1:c=0,e=204,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=3993156412   
  5. WAIT #1: nam='SQL*Net message from client' ela= 333 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993156890   
  6. pin kdswh01: kdstgr dba 1401d07:1 time 3993157019   
  7. WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=12976 tim=3993157074   
  8. pin kdswh01: kdstgr dba 1401d08:1 time 3993157128   
  9. 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代码
  1. HELLODBA.COM>select 1 from bigtab   
  2.   2  where to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(ROWID),   
  3.   3                                                     dbms_rowid.rowid_block_number(ROWID)),   
  4.   4               'XXXXXXXX') = ' 1401D08';   
  5.   
  6. no rows selected  

    At last, count in the empty blocks read, we got the right Logical reads number, 42118.

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat