RE: sequential read on full-table scan?

From: Joze Senegacnik <Joze.Senegacnik_at_snt.si>
Date: Tue, 24 May 2005 00:38:31 +0200
Message-ID: <DA4AE15355CD6A4A9EC163E0C5BCE633032980@simail01.adriatic.snt.eu>

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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]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

Received on Mon May 23 2005 - 18:42:19 CDT