RE: sequential read on full-table scan?

Hi everbody in this thread!

I see a long discussion here about db file sequential read in a FTS.
I have discovered an interesting opposite case - a multi block read
(db file scattered read) for INDEX UNIQUE SCAN operation that is
typically a single block read operation.

I turned on event 10299 as well to trace prefetched blocks and below one
can see the excerpt from event 10046+10299 trace file. Obviously in =
version
10.1.0.3 Oracle is capable to do such optimization. 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 by primary key.


Row Source Operation
---------------------------------------------------------------------
 UPDATE  (cr=3D175 r=3D159 w=3D0 time=3D4253117 us)
  INDEX UNIQUE SCAN S_EMP_ID_PK (cr=3D164 r=3D78 w=3D0 time=3D1259138 =
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=3D0,e=3D9361,p=3D0,cr=3D0,cu=3D1,mis=3D0,r=3D0,dep=3D1,og=3D1,tim=3D=
1068988965560
EXEC =
#26:c=3D0,e=3D8354,p=3D0,cr=3D2,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D1,tim=3D=
1068988976606
EXEC =
#25:c=3D0,e=3D2345,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D1,tim=3D=
1068988981230
Prefetching 6 blocks
WAIT #28: nam=3D'db file scattered read' ela=3D 65100 p1=3D8 p2=3D5813 =
p3=3D7
Prefetching 12 blocks
WAIT #28: nam=3D'db file scattered read' ela=3D 451681 p1=3D8 p2=3D1800 =
p3=3D13
EXEC =
#27:c=3D0,e=3D2687,p=3D0,cr=3D0,cu=3D2,mis=3D0,r=3D1,dep=3D2,og=3D1,tim=3D=
1068989563505
EXEC =
#28:c=3D10014,e=3D589352,p=3D20,cr=3D2,cu=3D8,mis=3D0,r=3D1,dep=3D1,og=3D=
1,tim=3D1068989574339

Regards,
Joze

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Lex de Haan
Sent: Sunday, May 22, 2005 1:17 PM
To: 'Oracle-L Freelists'
Subject: RE: sequential read on full-table scan?


sorry folks -- I was just reading my own post, and I see the text is =
confusing.
"This obviously also includes blocks containing overflow row pieces"
refers to all blocks below the HWM being read into the buffer cache by a =
full
table scan.
kind regards,

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

Other related posts: