Home » RDBMS Server » Performance Tuning » DB FILE SEQUENTIAL READ IN Full table scan (Oracle,11.1.0.7.0,Aix)
DB FILE SEQUENTIAL READ IN Full table scan [message #560793] Tue, 17 July 2012 03:11 Go to next message
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 #560794 is a reply to message #560793] Tue, 17 July 2012 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what you saw, did and got.
Post the trace file (only the relevant part).

Regards
Michel
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560795 is a reply to message #560794] Tue, 17 July 2012 03:30 Go to previous messageGo to next message
vattigunta
Messages: 32
Registered: August 2011
Location: India
Member
hi..Thanks for your immediate reply. unfortunately i don't have trace file but with the help of owi views i can provide below information
---------------------------------------------------------------- ------------- ---------- ---------- ----------
SQL*Net message to client 2d8rh2f07x97u 1 0 1413697536
PX Deq: Execute Reply 73cfvs6axp77a 15 0 200
db file sequential read 73cfvs6axp77a 52021521 1 7
db file sequential read 73cfvs6axp77a 42363789 1 7

I am executing my query with parallel hint of degree 2 but still it consider db file sequential read wait event even though plan is showing full table scan method.
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560803 is a reply to message #560795] Tue, 17 July 2012 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the columns are?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560879 is a reply to message #560795] Tue, 17 July 2012 10:41 Go to previous messageGo to next message
LNossov
Messages: 283
Registered: July 2011
Location: Germany
Senior Member
Do you run a DML?
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560882 is a reply to message #560879] Tue, 17 July 2012 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94465

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #560936 is a reply to message #560882] Tue, 17 July 2012 20:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.



Re: DB FILE SEQUENTIAL READ IN Full table scan [message #561082 is a reply to message #560998] Wed, 18 July 2012 14:01 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Because I am in an environment where we do 99.99% selects and only 0.01% inserts,
I usually eliminate the full table scans by adding compostite indexes (indexes on
more than one column) on PR_TBL_M_LR_RESTATE and CED_TBL_M_L_RESTATE_LM_IND2.
Indexes are smaller and are kept in memory longer so they perform better.
I usually create an index on the columns that are in the where clause, this way the
exeuction plan can eliminate the majority of rows by the index joins, then only read
the table for the columns to be displayed.

Creating indexes WILL make inserts and deletes run slower
and WILL make some (but not all) updates run slower. There is
also the possibility of more locking when inserting/updating.
Re: DB FILE SEQUENTIAL READ IN Full table scan [message #561083 is a reply to message #560998] Wed, 18 July 2012 14:05 Go to previous message
LNossov
Messages: 283
Registered: July 2011
Location: Germany
Senior Member
What is about IOT? Do you have any? Could you please upload your tkprof output.
Previous Topic: how can i know host name using sql id or sid
Next Topic: Sentence tuning
Goto Forum:
  


Current Time: Sun Apr 20 11:37:31 CDT 2014

Total time taken to generate the page: 0.07914 seconds