Re: Operations that perform multiblock I/O and cluster factor
- From: Riku Räsänen <riku.rasanen@xxxxxxxxxxxxxxxx>
- To: alberto.dellera@xxxxxxxxx
- Date: Tue, 11 Dec 2007 19:49:20 +0200 (EET)
I had deleted the tracefile, but I did have a script to reproduce the results.
This trace was so unusual, so that I made notes about it, in case if I happened
to run into this again. I re-run it today and the results match to those I saw
earlier.
This is Oracle 9.2.0.4 on Linux, table (~80MB) placed in small (4MB)
recycle pool, index should fit in memory in default pool.
The test query is run from inside a anonymous PL/SQL block using bulk fetch.
This is a snippet from the raw trace. To me the interesting thing is the
p3-parameter, as DB_FILE_MULTIBLOCK_READ_COUNT is actually set to 16 and
normal FTS reads 16 blocks at a time. The "db file scattered read"s are
all for the table, the index should be cached.
PARSING IN CURSOR #6 len=74 dep=1 uid=0 oct=3 lid=0 tim=1169333298070551
hv=668808299 ad='5ba07778'
SELECT /*+INDEX (c idx_clf_scattered) */ c.*
FROM DEMO.clusfac_demo c
END OF STMT
PARSE #6:c=1000,e=1510,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1169333298070527
BINDS #6:
EXEC #6:c=0,e=137,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1169333298070916
WAIT #6: nam='db file scattered read' ela= 2659 p1=6 p2=1034 p3=40
WAIT #6: nam='db file scattered read' ela= 2241 p1=6 p2=1074 p3=40
WAIT #6: nam='db file scattered read' ela= 2254 p1=6 p2=1114 p3=40
WAIT #6: nam='db file parallel read' ela= 1598 p1=1 p2=40 p3=40
WAIT #6: nam='db file scattered read' ela= 2280 p1=6 p2=1322 p3=40
WAIT #6: nam='db file scattered read' ela= 2281 p1=6 p2=1362 p3=40
WAIT #6: nam='db file scattered read' ela= 2281 p1=6 p2=1402 p3=40
WAIT #6: nam='db file scattered read' ela= 2253 p1=6 p2=1442 p3=40
WAIT #6: nam='db file scattered read' ela= 2258 p1=6 p2=1482 p3=40
WAIT #6: nam='db file scattered read' ela= 2257 p1=6 p2=1522 p3=40
WAIT #6: nam='db file scattered read' ela= 2246 p1=6 p2=1562 p3=40
WAIT #6: nam='db file scattered read' ela= 2257 p1=6 p2=1602 p3=40
WAIT #6: nam='db file scattered read' ela= 2246 p1=6 p2=1642 p3=40
WAIT #6: nam='db file scattered read' ela= 2248 p1=6 p2=1682 p3=40
WAIT #6: nam='db file scattered read' ela= 2247 p1=6 p2=1722 p3=40
WAIT #6: nam='db file scattered read' ela= 2278 p1=6 p2=1762 p3=40
WAIT #6: nam='db file scattered read' ela= 2275 p1=6 p2=1802 p3=40
WAIT #6: nam='db file scattered read' ela= 2258 p1=6 p2=1842 p3=40
WAIT #6: nam='db file scattered read' ela= 2225 p1=6 p2=1882 p3=40
WAIT #6: nam='db file scattered read' ela= 2217 p1=6 p2=1922 p3=40
WAIT #6: nam='db file scattered read' ela= 2230 p1=6 p2=1962 p3=40
WAIT #6: nam='db file scattered read' ela= 2234 p1=6 p2=2002 p3=40
WAIT #6: nam='db file scattered read' ela= 2212 p1=6 p2=2042 p3=40
WAIT #6: nam='db file scattered read' ela= 2229 p1=6 p2=2082 p3=40
--
Riku Räsänen
Kantamestarit OY
www.kantamestarit.fi
--
//www.freelists.org/webpage/oracle-l
Other related posts:
