RE: db file parallel read for prefetching?
- From: Jože Senegačnik <Joze.Senegacnik@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 26 Nov 2005 02:19:13 +0100
Additionaly to Tanels respond I would like to add my two cents. One year ago I
found in one of my trace files that Oracle performs a 'db file scattered read'
- multiblock read for INDEX UNIQUE SCAN operation that is typically a single
block read operation. I looked a little bit closer to this problem and found
some interesting details.
I turned on event 10299 as well to trace prefetched blocks and below you can
see the excerpt from event 10046+10299 trace file. At that time I was using
10.1.0.3. Instead of reading 1 block at a time Oracle reads 13 blocks in one
multiblock read and 12 of them are prefetched. Of course there are also some
single block reads.
Below are the details. The statement is a simple update statement that updates
isung primary key primary key.
Row Source Operation
---------------------------------------------------------------------
UPDATE (cr=175 r=159 w=0 time=4253117 us)
INDEX UNIQUE SCAN S_EMP_ID_PK (cr=164 r=78 w=0 time=1259138 us)
Response Time Component Time ela. Pct
---------------------------------------- ----------- --------
db file scattered read 1.56s 62.82%
db file sequential read 0.70s 28.32%
CPU service 0.22s 8.86%
---------------------------------------- ----------- --------
Response time: 2.49s 100.00%
Service time: 0.22s 8.86%
Wait time: 2.27s 91.14%
Statement Read Statistics
Blks/Read Count Num of blk
--------- ---------- ----------
1 19 19
2 1 2
3 3 9
5 2 10
6 2 12
7 2 14
8 1 8
10 1 10
11 2 22
12 1 12
13 2 26
15 1 15
--------- ---------- ----------
SUM 37 159
Event 10046 + EVENT 10299 Trace Excerpt:
EXEC #24:c=0,e=9361,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=1,tim=1068988965560
EXEC #26:c=0,e=8354,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=1,tim=1068988976606
EXEC #25:c=0,e=2345,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1068988981230
Prefetching 6 blocks
WAIT #28: nam='db file scattered read' ela= 65100 p1=8 p2=5813 p3=7
Prefetching 12 blocks
WAIT #28: nam='db file scattered read' ela= 451681 p1=8 p2=1800 p3=13 EXEC
#27:c=0,e=2687,p=0,cr=0,cu=2,mis=0,r=1,dep=2,og=1,tim=1068989563505
EXEC #28:c=10014,e=589352,p=20,cr=2,cu=8,mis=0,r=1,dep=1,og=1,tim=1068989574339
Regards,
Joze
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of zhu chao
Sent: Monday, November 21, 2005 10:05 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: db file parallel read for prefetching?
hi, all,
Does anyone has document/note talking about the index prefetching?
I saw a few doc talking about oracle 9i introduced this feature, but no doc
really talked, how it works, and how to enable/hint the prefetch for the
optimizer, and how to verify it is working.
I have two database with identical hardware/schema, and some SQL with
identical plan (and nearly same data volume), SQL in one database always run at
1/2 time of the other database.
I checked the 10046 trace and found the database faster has pretty much "db
file parallel read" and the other instance is all "db file sequential read". I
guess it is related to the prefetch, but do not have much detail about it.
If someone has some experience it, can you share?
--
Regards
Zhu Chao
www.cnoug.org
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
