Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sequential read on full-table scan?

RE: sequential read on full-table scan?

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Fri, 20 May 2005 09:36:19 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B90A5C2C@wafedixm10.corp.weyer.pri>

You're not alone, I saw this weirdness in a recent 10046 trace, observe:

WAIT #40: nam=3D'db file sequential read' ela=3D 53 p1=3D90 p2=3D145793 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 50 p1=3D90 p2=3D145798 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 55 p1=3D90 p2=3D145800 = p3=3D1
WAIT #40: nam=3D'db file scattered read' ela=3D 74 p1=3D90 p2=3D145805 = p3=3D2
WAIT #40: nam=3D'db file scattered read' ela=3D 88 p1=3D90 p2=3D145832 = p3=3D2
WAIT #40: nam=3D'db file sequential read' ela=3D 85 p1=3D92 p2=3D121800 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 44 p1=3D92 p2=3D121802 = p3=3D1

My MBRC is 8. Also why would it switch from sequential to scattered and back again in the middle of the trace?

Hmmm, thinking about this, could this be because of parallel? If so, you can ignore my message...

Thanks!=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paul Baumgartel Sent: Friday, May 20, 2005 9:24 AM
To: Oracle-L
Subject: sequential read on full-table scan?

I am tracking a process that's exhibiting what I consider odd behavior. This is 9.2.0.4.0 on Windows.

A stored procedure is executing a loop in which it performs a cursor fetch, then updates the same table based on data in the fetch:

SELECT SUM(OPTIONS_VESTED) SUM_VESTED,SUM(ACTUAL_FORFEITURE) SUM_FORFEITED FROM OE_LINEITEM_RPT WHERE GRANT_DT =3D3D :b1 AND PLAN_ID =3D3D :b2 AND PLAN_SORT =3D3D:b3 AND OPT_PRC =3D3D :b4 AND EXPIRATION_DT =3D3D :b5 AND FAS123_QUAL_CODE =3D3D :b6 AND RUNTIME_ID =
=3D3D

:b7=3D
.

followed by=3D20

UPDATE /*+ rule */OE_LINEITEM_RPT R SET FAS123_TURNOVER=3D3DROUND(:b1 / DECODE(:b2,0,1,:b2) * 100 ,4) WHERE R.GRANT_DT =3D3D :b4 AND R.PLAN_ID
=3D3D

:b5 AND R.PLAN_SORT =3D3D :b6 AND R.OPT_PRC =3D3D :b7 AND = R.EXPIRATION_DT
=3D
=3D3D

:b8 AND R.FAS123_QUAL_CODE =3D3D :b9 AND R.RUNTIME_ID =3D3D :b10

I don't know the reason for the RULE hint. There are NO indexes on the table OE_LINEITEM_RPT. Now here's the odd part: v$session_wait (and statspack reports) show that the process is spending 80% of its time waiting for db file sequential read of 1 block from the table.=3D20 Why in the world would Oracle be reading a block at a time rather than performing full-table scans?

--=3D20
Paul Baumgartel
paul.baumgartel_at_gmail.com

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


--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 20 2005 - 12:41:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US