RE: Operations that perform multiblock I/O and cluster factor
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "Josh Collier" <Josh.Collier@xxxxxxxxxxxx>
- Date: Tue, 11 Dec 2007 16:58:56 +0100
Hi Josh
To improve the performance of nested loops prefetching is available.
- With prefetching of index blocks multi-block reads are performed
during index range and unique scans.
- With prefetching of data blocks multi-block reads are performed during
ROWID accesses.
I have a test case with which I can reproduce it at will. Here a trace
file snip:
=====================
PARSING IN CURSOR #3 len=99 dep=0 uid=27 oct=3 lid=27
tim=1169311377812522 hv=1565697230 ad='30915040'
SELECT /*+ ordered use_nl(b) index(a) index(b) */ a.*, b.*
FROM a, b
WHERE a.a1 = b.b2 AND a.a1 > 0
END OF STMT
PARSE
#3:c=4000,e=4181,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1169311377812496
EXEC
#3:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1169311377825710
WAIT #3: nam='SQL*Net message to client' ela= 0 driver id=1413697536
#bytes=1 p3=0 obj#=269 tim=1169311377825712
WAIT #3: nam='db file scattered read' ela= 11 file#=8 block#=49 blocks=8
obj#=10378 tim=1169311377826333
WAIT #3: nam='db file sequential read' ela= 2 file#=8 block#=34 blocks=1
obj#=10376 tim=1169311377826349
WAIT #3: nam='db file scattered read' ela= 0 file#=8 block#=73 blocks=8
obj#=10381 tim=1169311377826352
WAIT #3: nam='db file sequential read' ela= 18 file#=8 block#=42
blocks=1 obj#=10377 tim=1169311377826558
FETCH
#3:c=1000,e=532,p=18,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1169311377826688
WAIT #3: nam='SQL*Net message from client' ela= 476 driver id=1413697536
#bytes=1 p3=0 obj#=10377 tim=1169311377827408
WAIT #3: nam='SQL*Net message to client' ela= 9 driver id=1413697536
#bytes=1 p3=0 obj#=10377 tim=1169311377828791
WAIT #3: nam='db file scattered read' ela= 0 file#=8 block#=35 blocks=6
obj#=10376 tim=1169311377833310
WAIT #3: nam='db file scattered read' ela= 35 file#=8 block#=43 blocks=6
obj#=10377 tim=1169311377833702
FETCH
#3:c=5999,e=6379,p=12,cr=19,cu=0,mis=0,r=15,dep=0,og=1,tim=1169311377834
168
WAIT #3: nam='SQL*Net message from client' ela= 3325 driver
id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377837515
WAIT #3: nam='SQL*Net message to client' ela= 36 driver id=1413697536
#bytes=1 p3=0 obj#=10377 tim=1169311377838572
FETCH
#3:c=6999,e=6670,p=0,cr=18,cu=0,mis=0,r=14,dep=0,og=1,tim=11693113778443
19
WAIT #3: nam='SQL*Net message from client' ela= 5661 driver
id=1413697536 #bytes=1 p3=0 obj#=10377 tim=1169311377850609
STAT #3 id=1 cnt=30 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=41 pr=30
pw=0 time=573 us)'
STAT #3 id=2 cnt=30 pid=1 pos=1 obj=10376 op='TABLE ACCESS BY INDEX
ROWID A (cr=8 pr=15 pw=0 time=2790 us)'
STAT #3 id=3 cnt=30 pid=2 pos=1 obj=10378 op='INDEX RANGE SCAN A1_I
(cr=3 pr=8 pw=0 time=1550 us)'
STAT #3 id=4 cnt=30 pid=1 pos=2 obj=10377 op='TABLE ACCESS BY INDEX
ROWID B (cr=33 pr=15 pw=0 time=8619 us)'
STAT #3 id=5 cnt=30 pid=4 pos=1 obj=10381 op='INDEX UNIQUE SCAN B2_I
(cr=3 pr=8 pw=0 time=4043 us)'
WAIT #0: nam='SQL*Net message to client' ela= 10 driver id=1413697536
#bytes=1 p3=0 obj#=10377 tim=1169311377863627
WAIT #0: nam='SQL*Net message from client' ela= 532 driver id=1413697536
#bytes=1 p3=0 obj#=10377 tim=1169311377864596
=====================
The object IDs are the following:
SQL> SELECT object_id, object_name
2 FROM user_objects
3 WHERE object_name IN ('A','B','A1_I','B2_I');
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------
10376 A
10378 A1_I
10377 B
10381 B2_I
If you check the IDs you can see that even if the execution plan
contains only ROWID accesses and INDEX RANGE/UNIQUE SCAN only scattered
reads are performed. The two single-block reads are used for the header
of the tables.
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
