OraFAQ Forum: Performance Tuning » Wait event 'db file sequential read' on TABLE segment

Home » RDBMS Server » Performance Tuning » Wait event 'db file sequential read' on TABLE segment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wait event 'db file sequential read' on TABLE segment [message #161930]

Tue, 07 March 2006 23:21

tphan_au
Messages: 3
Registered: March 2006
Location: Sydney

Junior Member

Hi

Can someone please explain why my DML below has a wait event 'db file sequential read' on a partition table, instead of the index segment.

My OS/DB Infor.
==============
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /applic/oracle/product/9.2.0
System name: HP-UX
Node name: chasrp3
Release: B.11.11
Version: U
Machine: 9000/800

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PARSING IN CURSOR #1 len=443 dep=0 uid=54 oct=6 lid=54 tim=17623392084636 hv=1104730794 ad='57cfd908'
update ww.dc_soh_pt a
set a.tot_sugo = nvl((select SUM(RR.FULFILLMENT_QUANTITY * nvl(qty_in_each, 1))
from WW_RP.I_REQUESTED_REPLENISHMENTS RR, WW_RP.I_TIME_PARAMETERS TP, RP_APPLIC.PACK_BREAKDOWN PB
WHERE A.ITEM_ID = RR.ITEM_ID
AND A.location_id = RR.FROM_LOCATION_ID
AND A.PARTITION_ID = RR.PARTITION_ID
AND TP.RP_CURRENT = RR.ORDER_DATE
AND PB.LOCATION_ID(+) = RR.LOCATION_ID
AND PB.ITEM_ID(+) = RR.ITEM_ID
AND PB.PARENT_ID(+) IS NULL), 0)
END OF STMT
PARSE #1:c=0,e=4345,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=17623392084623
BINDS #1:
WAIT #1: nam='db file scattered read' ela= 46854 p1=9 p2=1472 p3=5
WAIT #1: nam='db file sequential read' ela= 8642 p1=12 p2=380452 p3=1
WAIT #1: nam='db file sequential read' ela= 12343 p1=10 p2=5136 p3=1
WAIT #1: nam='db file sequential read' ela= 11452 p1=14 p2=892459 p3=1
WAIT #1: nam='db file sequential read' ela= 9935 p1=14 p2=893288 p3=1
WAIT #1: nam='db file sequential read' ela= 9657 p1=14 p2=892522 p3=1
WAIT #1: nam='db file sequential read' ela= 14641 p1=12 p2=380725 p3=1
WAIT #1: nam='db file sequential read' ela= 13019 p1=11 p2=1023820 p3=1
WAIT #1: nam='db file sequential read' ela= 5746 p1=14 p2=893271 p3=1

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
UTPNG@SRPR61PR >@fileints part_indx

FILE_ID FILE_NAME MB
---------- -------------------------------------------------------------------------------- ----------
15 /u011/oradata/SRPR61PR/part_indx_01.dbf 38000
16 /u012/oradata/SRPR61PR/part_indx_02.dbf 38000
17 /u013/oradata/SRPR61PR/part_indx_03.dbf 38000
18 /u014/oradata/SRPR61PR/part_indx_04.dbf 38000

UTPNG@SRPR61PR >@fileints part_data

FILE_ID FILE_NAME MB
---------- -------------------------------------------------------------------------------- ----------
11 /u007/oradata/SRPR61PR/part_data_01.dbf 40000
12 /u008/oradata/SRPR61PR/part_data_02.dbf 40000
13 /u009/oradata/SRPR61PR/part_data_03.dbf 40000
14 /u010/oradata/SRPR61PR/part_data_04.dbf 40000

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
UTPNG@SRPR61PR >@fileblk 11 1023014

OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS
-------- ------------------------------ ------------------ ---------- ----------
WW_RP I_REQUESTED_REPLENISHMENTS TABLE PARTITION 1022725 1280

UTPNG@SRPR61PR >@fileblk 12 380452

OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS
-------- ------------------------------ ------------------ ---------- ----------
WW_RP I_REQUESTED_REPLENISHMENTS TABLE PARTITION 380165 1280

UTPNG@SRPR61PR >UTPNG@SRPR61PR >@fileblk 10 5136

OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS
-------- ------------------------------ ------------------ ---------- ----------
RP_APPLI PK_PKBDN INDEX 5133 8
C

UTPNG@SRPR61PR >@fileblk 14 892459

OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS
-------- ------------------------------ ------------------ ---------- ----------
WW_RP I_REQUESTED_REPLENISHMENTS TABLE PARTITION 892165 1280

UTPNG@SRPR61PR >