Finally, let's calculate the physicalreads. By counting the "db file sequential read" & "db filescattered read" waits blocks, we get 63 PIO. Then count the "directpath read temp", we get the other 138 PIO.
SQL代码
C:\oracle\product\10.2.0\admin\SQL>set/a a=0
0
C:\oracle\product\10.2.0\admin\SQL>for/f "tokens=1 delims=:" %i in ('findstr /C:"direct path readtemp" C:\oracle\product\10.2.0\admin\edgar\udump\LIO_Sort.trc') do @set /aa+=1 > NUL
C:\oracle\product\10.2.0\admin\SQL>echo%a%
138
Btw, in other test cases with same process,I noted sometimes the "direct path read temp" number is more thanexcepted, I still not found the reason, just guess it may caused by therecursive calls.
Secret of oracle logic IO: Index Scan
=====================================
Let study the index scan case.
SQL代码
SQL>conndemo/demo
Connected.
SQL>set linesize 300
SQL>alter system flushbuffer_cache;
System altered.
SQL>ALTER SESSION SETEVENTS '10046 trace name context forever, level 8';
Session altered.
SQL>set autot trace
SQL>select * from t_test2where owner = 'OUTLN';
Execution Plan
----------------------------------------------------------
Plan hash value:1900296288
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 630 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST2 | 3 | 630 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN |T_TEST2_IDX1 | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - access("OWNER"='OUTLN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
5 physical reads
0 redo size
3622 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)
3 rows processed
There are 6 Logical reads. Check the traceoutput, just find 5 pins.
SQL代码
WAIT #2: nam='db filesequential read' ela= 15454 file#=5 block#=66164 blocks=1 obj#=100897tim=999288447
WAIT #2: nam='db filesequential read' ela= 35067 file#=5 block#=66176 blocks=1 obj#=100897tim=999323645
pin kdiwh09: kdiixs dba1410280:1 time 999323748
WAIT #2: nam='db filesequential read' ela= 14164 file#=5 block#=37922 blocks=1 obj#=97820tim=999337989
pin kdswh05: kdsgrp dba1409422:1 time 999338144
FETCH#2:c=0,e=65288,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=999338229
WAIT #2: nam='SQL*Net messagefrom client' ela= 718 driver id=1111838976 #bytes=1 p3=0 obj#=97820tim=999339051
pin kdiwh16: kdifxs dba1410280:1 time 999339159
WAIT #2: nam='db filesequential read' ela= 3066 file#=5 block#=37944 blocks=1 obj#=97820tim=999342291
pin kdswh05: kdsgrp dba1409438:1 time 999342355
WAIT #2: nam='SQL*Net messageto client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=97820 tim=999342411
WAIT #2: nam='db filesequential read' ela= 1935 file#=5 block#=37958 blocks=1 obj#=97820tim=999344410
pin kdswh05: kdsgrp dba1409446:1 time 999344463
FETCH#2:c=0,e=5388,p=2,cr=3,cu=0,mis=0,r=2,dep=0,og=4,tim=99