RE: db file parallel read for prefetching?

From: Joe Senegačnik <Joze.Senegacnik_at_snt.si>
Date: Sat, 26 Nov 2005 02:19:13 +0100
Message-ID: <4A18399C13358544BB9A383EA097749ED609D1@simail01.adriatic.snt.eu>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of zhu chao Sent: Monday, November 21, 2005 10:05 AM To: oracle-l_at_freelists.org
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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 25 2005 - 19:20:36 CST