61643

WAIT #2: nam='db filesequential read' ela= 6579 file#=2 block#=73 blocks=1 obj#=0tim=4975935594

pin ktuwh01: ktugus dba800049:25 time 680968375

pin ktuwh03: ktugnb dba8012cb:26 time 680968434

pin release 141 ktuwh01: ktugus dba 800049:25

pin release 123 ktuwh03: ktugnb dba 8012cb:26

pin release 6954 kduwh01: kdusru dba 140e64f:1

pin kdswh01: kdstgr dba140e650:1 time 680968657

pin kduwh01: kdusru dba140e650:1 time 680968719

pin kcbwh5: kcbchg1 dba8012cb:26 time 680968776

pin release 49 kcbwh5: kcbchg1 dba 8012cb:26

pin release 146 kduwh01: kdusru dba 140e650:1

EXEC #2:c=0,e=7657,p=1,cr=7,cu=4,mis=0,r=2,dep=0,og=4,tim=4975936219

...

While in the 2nd UPDATE of the 2ndtransaction, the UNDO block is be reused, so it just has 2 db block gets.

SQL代码

...

pin kduwh01: kdusru dba140e64f:1 time 680977322

pin kcbwh5: kcbchg1 dba8012cb:26 time 680977384

pin release 63 kcbwh5: kcbchg1 dba 8012cb:26

pin release 166 kduwh01: kdusru dba 140e64f:1

pin kdswh01: kdstgr dba140e650:1 time 680977538

pin kduwh01: kdusru dba140e650:1 time 680977595

pin kcbwh5: kcbchg1 dba8012cb:26 time 680977642

pin release 48 kcbwh5: kcbchg1 dba 8012cb:26

pin release 136 kduwh01: kdusru dba 140e650:1

EXEC#2:c=0,e=829,p=0,cr=7,cu=2,mis=0,r=2,dep=0,og=4,tim=4975945080

...

Secret of oracle logic IO: Sorting

==================================

In this case, I will involve the sorting.

SQL代码

SQL>conn demo/demo

Connected.

SQL>alter system flushbuffer_cache;

System altered.

SQL>ALTER SESSION SETEVENTS '10046 trace name context forever, level 8';

Session altered.

SQL>set autot tracestat

SQL>select * from t_test2order 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 theLogical reads number: 62 data block rows fetching + 3 segment header reads =65.

Check the file in detail, you will foundthe FETCH came after all the sorting operation completed, that means thearraysize will not affect the data block reads, so, each of the data block wasjust read once in this case! compare to the previous test case that query thesame table without sorting, its Logical reads is lower.

Since this case involved the sorting, wecan find below new entries,

SQL代码

pin stswh00: stsswr dba402f3f:2 time 857155584

pin release 43 stswh00: stsswr dba 402f3f:2

WAIT #2: nam='direct pathwrite temp' ela= 0 file number=201 first dba=12080 block cnt=1 obj#=97820tim=857155720

It's not hard to guess thestsswr is the operationto write sorting data in temp segments. Since it's to write data, it's countedas db block gets. There are 21 db block gets.

SQL代码

C:\oracle\product\10.2.0\admin\SQL>set/a a=0

C:\oracle\product\10.2.0\admin\SQL>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\SQL>echo%a%

Read all of the sorting operation entries,we can find all of them wrote into the same block, so there was just 1 sorts indisk.

SQL代码

pin stswh00: stsswr dba402f3f:2 time 857147806

...

pin stswh00: stsswr dba402f3f:2 time 857255971

pin stswh00: stsswr dba402f3f:2 tim

allaboutoraclelogicIO(五)

Secret of oracle logic IO: Sorting