| DB FILE SEQUENTIAL READ IN Full table scan [message #560793] |
Tue, 17 July 2012 03:11  |
 |
vattigunta
Messages: 32
Registered: August 2011
Location: India
|
Member |
|
|
Hi, Could someone explain me why db file sequential wait event is appearing continously while table is accessing through full table scan method.
I know sometimes in full table scan method also, oracle fetch single blocks in certain situations(like undo blocks, segment blocks) but in my case there is only one wait event that is db file sequential nothing else is showing up..i have traced the session also but i couldn't find any other wait event.
Please help me out by your valuable suggestions.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560936 is a reply to message #560882] |
Tue, 17 July 2012 20:57  |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
In the following example the NFLPROD USERNAME is doing "direct path read"
but since the seconds of wait time (SEC_WAIT) is zero, these are multiple
reads that are occuring but are taking fractions of a second to execute.
It is normal for Physical reads and physical writes to take longer to
perform than reads from blocks that are cached in memory.
SID SERIAL# MACHINE LOGIN DB SEC_WAIT USERNAME EVENT
------- ------- ------------------ --------------- ---------- -------- ----------- ----------------
227 41817 proddoc02 17-JUL-12 18:31 NDOCP2 2 NFLPROD direct path write temp
ENDOCP1P > /
SID SERIAL# MACHINE LOGIN DB SEC_WAIT USERNAME EVENT
------- ------- ------------------ --------------- ---------- -------- ----------- ----------------
227 41817 proddoc02 17-JUL-12 18:31 NDOCP2 0 NFLPROD direct path read
ENDOCP1P > /
SID SERIAL# MACHINE LOGIN DB SEC_WAIT USERNAME EVENT
------- ------- ------------------ --------------- ---------- -------- ----------- ----------------
227 41817 proddoc02 17-JUL-12 18:31 NDOCP2 0 NFLPROD direct path read
ENDOCP1P > list
1 SELECT sid,
2 serial#,
3 machine,
4 To_char(s.logon_time, 'DD-MON-RR HH24:MI') login,
5 i.instance_name db,
6 s.seconds_in_wait sec_wait,
7 s.username,
8 s.event,
9 s.status,
10 s.program,
11 s.machine,
12 s.MODULE,
13 s.terminal
14 FROM gv$session s,
15 gv$instance i
16 WHERE i.inst_id = s.inst_id
17 AND s.status = 'ACTIVE'
18 AND s.username IS NOT NULL
19 AND s.wait_class <> 'Idle'
20* ORDER BY seconds_in_wait
|
|
|
|
| Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560998 is a reply to message #560803] |
Wed, 18 July 2012 03:43 |
|
vattigunta
Messages: 32
Registered: August 2011
Location: India
|
Member |
|
|
Here is the sql stmt
Version :- 11.1.0.7.0
OS :- AIX.
i have removed parallel hint and i have run it.
Query :-
SELECT ACCT_NBR,UPB_SOURCE,UPB_TARGET,FPB_SOURCE,FPB_TARGET
FROM
(
SELECT A.ACCT_NBR,
DECODE(A.UPB, B.UPB,0,1) UPB_MISMATCH,
DECODE(A.FPB, B.FPB,0,1) FPB_MISMATCH,
A.UPB UPB_SOURCE,B.UPB UPB_TARGET,
A.FPB FPB_SOURCE,B.FPB FPB_TARGET
FROM
(
SELECT ACCT_NBR, FIRST_PRINCIPAL_BALANCE FPB, UPB
FROM COAMGR.PR_TBL_M_LR_RESTATE
WHERE RUN_YR_MO = 200907
) A
JOIN
(
SELECT ACCT_NBR, FIRST_PRINCIPAL_BALANCE AS FPB, UPB
FROM COAMGR.CED_TBL_M_L_RESTATE_LM_IND2 a
WHERE HERITAGE = 'CHASE' and RUN_YR_MO = 200907
and PRODUCT='PRIME' AND SERVICING_ENTITY = 'CHASE'
) B
ON A.ACCT_NBR = B.ACCT_NBR
)
WHERE UPB_MISMATCH=1 OR FPB_MISMATCH = 1
For your understanding:-
COAMGR.PR_TBL_M_LR_RESTATE is reading first and we don't have any issues with this.
COAMGR.CED_TBL_M_L_RESTATE_LM_IND2 is reading second and this is the one which is creating the so called issue.
Trace file contents:-
<< first table wait events >>
WAIT #2: nam='direct path read' ela= 4 file number=7 first dba=3294067 block cnt=10 obj#=5638234 tim=53353057394314
WAIT #2: nam='direct path read' ela= 83687 file number=7 first dba=3294078 block cnt=9 obj#=5638234 tim=53353057479112
WAIT #2: nam='direct path read' ela= 5 file number=7 first dba=3294093 block cnt=6 obj#=5638234 tim=53353057480097
WAIT #2: nam='direct path read' ela= 104457 file number=7 first dba=3266535 block cnt=32 obj#=5638234 tim=53353057585101
.
.
.
i am not pasting whole copy pertaining to the first table bcz the the above mentioned wait event is the only one which is reflecting and most importantly we don't have any issues with this.
<< second table wait event snippet >>
WAIT #2: nam='db file sequential read' ela= 59073 file#=7 block#=4429003 blocks=1 obj#=6056418 tim=53353116506401
WAIT #2: nam='i/o slave wait' ela= 44 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353116506434
*** 2012-07-18 01:49:13.384
WAIT #2: nam='db file sequential read' ela= 56304 file#=7 block#=4698456 blocks=1 obj#=6056418 tim=53353117879399
WAIT #2: nam='i/o slave wait' ela= 148 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353117879524
WAIT #2: nam='db file sequential read' ela= 8746 file#=7 block#=45028084 blocks=1 obj#=6056418 tim=53353117888318
WAIT #2: nam='i/o slave wait' ela= 35 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353117888344
WAIT #2: nam='db file sequential read' ela= 11263 file#=7 block#=4712389 blocks=1 obj#=6056418 tim=53353117899664
WAIT #2: nam='i/o slave wait' ela= 28 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353117899685
WAIT #2: nam='db file sequential read' ela= 12297 file#=7 block#=4713390 blocks=1 obj#=6056418 tim=53353117912023
WAIT #2: nam='i/o slave wait' ela= 50 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353117912066
WAIT #2: nam='db file sequential read' ela= 12147 file#=7 block#=41747087 blocks=1 obj#=6056418 tim=53353117924271
WAIT #2: nam='i/o slave wait' ela= 50 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353117924310
WAIT #2: nam='db file sequential read' ela= 107887 file#=7 block#=29260467 blocks=1 obj#=6056418 tim=53353118032251
WAIT #2: nam='i/o slave wait' ela= 76 msg ptr=0 p2=0 p3=0 obj#=6056418 tim=53353118032319
..
...
..
and so on.
there is no other wait event listed in the trace file content
i have killed the sql bcz it's been running more than 1 hour and bcz of cpu limits i had to kill it.
object_id = 6056418 belongs to COAMGR.CED_TBL_M_L_RESTATE_LM_IND2(second table)
explain plan :- we have taken with the help of explain plan utility which is same when we compared with dbms_xplan.display_cursor output so there is no change in plan
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time Pstart Pstop
--------------------------------------------------------------------------------
0 SELECT STATEMENT 79149 5178K 74070 (3) 00:14:49
* 1 HASH JOIN 79149 5178K 172M 74070 (3) 00:14:49
2 PARTITION RANGE SINGLE 5036K 115M 23064 (5) 00:04:37 55 55
* 3 TABLE ACCESS FULL PR_TBL_M_LR_RESTATE 5036K 115M 23064 (5) 00:04:37 55 55
4 PARTITION RANGE SINGLE 3973K 162M 32029 (3) 00:06:25 31 31
5 PARTITION LIST SINGLE 3973K 162M 32029 (3) 00:06:25 KEY KEY
*| 6 | TABLE ACCESS FULL | CED_TBL_M_L_RESTATE_LM_IND2 | 3973K| 162M| | 32029 (3)| 00:06:25 | 91 | 91 |**
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
1 - access("ACCT_NBR"="ACCT_NBR")
filter(DECODE("UPB","UPB",0,1)=1 OR DECODE("FIRST_PRINCIPAL_BALANCE","FIRST_PRINCIPAL_BALANCE",0,1)=1)
3 - filter("RUN_YR_MO"=200907)
6 - filter("PRODUCT"='PRIME' AND "RUN_YR_MO"=200907 AND "SERVICING_ENTITY"='CHASE')
21 rows selected.
Anaysis we have done :-
When we don't select FIRST_PRINCIPAL_BALANCE and SERVICING_ENTITY columns then it's going for scattered read and i don't know it's bit weird.
And no dml operations on tables.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
