Secret of oracle logic IO: Sorting

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-11-10 01:14:31

Share to 

In this case, I will involve the sorting. 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 t_test2 order by table_name;       2072 rows selected.       Statistics   ----------------------------------------------------------              7  recursive calls             21  db block gets             65  consistent gets            201  physical reads              0  redo size        131049  bytes sent via SQL*Net to client           1903  bytes received via SQL*Net from client            140  SQL*Net roundtrips to/from client              0  sorts (memory)              1  sorts (disk)           2072  rows processed       Review the trace file, we can get the Logical reads number: 62 data block rows fetching + 3 segment header reads = 65.       Check the file in detail, you will found the FETCH came after all the sorting operation completed, that means the arraysize will not affect the data block reads, so, each of the data block was just read once in this case! compare to the previous test case that query the same table without sorting, its Logical reads is lower.     Since this case involved the sorting, we can find below new entries, SQL代码 pin stswh00: stsswr dba 402f3f:2 time 857155584    pin release        43 stswh00: stsswr dba 402f3f:2    WAIT #2: nam='direct path write temp' ela= 0 file number=201 first dba=12080 block cnt=1 obj#=97820 tim=857155720       It's not hard to guess the stsswr is the operation to write sorting data in temp segments. Since it's to write data, it's counted as db block gets. There are 21 db block gets. 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 stswh00" C:\oracle\product\10.2.0\admin\edgar\udump\LIO_Sort.trc') do @set /a a+=1 > NUL       C:\oracle\product\10.2.0\admin\hellodba.com>echo %a%    21       Read all of the sorting operation entries, we can find all of them wrote into the same block, so there was just 1 sorts in disk.   SQL代码 pin stswh00: stsswr dba 402f3f:2 time 857147806    ...    pin stswh00: stsswr dba 402f3f:2 time 857255971    pin stswh00: stsswr dba 402f3f:2 time 857256077       Finally, let's calculate the physical reads. By counting the "db file sequential read" & "db file scattered read" waits blocks, we get 63 PIO. Then count the "direct path read temp", we get the other 138 PIO. 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:"direct path read temp" C:\oracle\product\10.2.0\admin\edgar\udump\LIO_Sort.trc') do @set /a a+=1 > NUL       C:\oracle\product\10.2.0\admin\hellodba.com>echo %a%    138       Btw, in other test cases with same process, I noted sometimes the "direct path read temp" number is more than excepted, I still not found the reason, just guess it may caused by the recursive calls.     --- Fuyuncat TBC ---