1 2 Previous Next 15 Replies Latest reply: Jul 19, 2012 10:20 AM by 950046 RSS

    db file sequential read appears in full table scan mode

    950046
      Hi..could some one explain why db file sequential wait event appears continually while table is accessing through full table scan method.

      i know sometime even in full table scan mode oracle fetch single blocks if it's segment block.undo block but not contiguously but in my case i haven't seen any other wait event except db file sequential wait event why it is so?.

      Addition to this i have used parallel hint also but unfortunately all parallel servers current event is db file sequential event that means even parallel servers are reading single blocks.

      Any information would be deeply appreciated
        • 1. Re: db file sequential read appears in full table scan mode
          Dom Brooks
          Further analysis required.

          More information needed, amongst which
          - sample of information from raw 10046 trace file, including from the parallel sessions
          - actual execution plans
          - version.
          • 2. Re: db file sequential read appears in full table scan mode
            Hoek
            Welcome to the forum.
            Hi..could some one explain why db file sequential wait event appears continually while table is accessing through full table scan method.
            Appears exactly where? Can you show us an execution plan or trace/tkprof output?
            Database version?
            In short: read {message:id=9360002} and {message:id=9360003} so you know what information to provide.

            Any information would be deeply appreciated
            You could search http://asktom.oracle.com and ofcourse the docs @ http://www.oracle.com/pls/db102/homepage or http://www.oracle.com/pls/db112/homepage
            to get more clues.
            • 3. Re: db file sequential read appears in full table scan mode
              Nikolay Savvinov
              Hi,

              I've seen that a few times, but in my case only a comparatively small fraction of data was read that way, so the explanation about sequential reads being used when just 1 block was left in an extent seemed plausible. If your case is different, more details are needed to figure out what exactly is going on (as others have already pointed out).

              Best regards,
              Nikolay
              • 4. Re: db file sequential read appears in full table scan mode
                950046
                Thanks for all of your answers..some of you asked trace file contents so i will provide them as well as execution plan in next post.
                • 5. Re: db file sequential read appears in full table scan mode
                  Hoek
                  Not 'some of us', all of us ;)
                  And the database version is also important to know.

                  If you can provide trace/tkprof output (which would be great), then make sure to trace with wait events (level 10046). You can find examples in the link regarding tuning/performance from my previous post.
                  An execution plan will be included in trace output 'automagically', by the way...
                  • 6. Re: db file sequential read appears in full table scan mode
                    Nikolay Savvinov
                    Hi,

                    somehow I have a feeling that once you get a good look at the trace file, the solution will become apparent, since the trace file can tell you where the reads are coming from (in 11g it will tell you the object_id directly, in earlier versions you'll have to do some manual work, but it's easy, e.g. see http://dioncho.wordpress.com/2009/07/06/object-name-from-file-and-block/). One likely possibility is that these reads may be coming from the UNDO tablespace (to maintain read consistency)...

                    Best regards,
                    Nikolay
                    • 7. Re: db file sequential read appears in full table scan mode
                      950046
                      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.
                      • 8. Re: db file sequential read appears in full table scan mode
                        950046
                        Following to the previous post.. why selecting particular column changes the multiblock(db file scattered read) to single block read(db file sequential read)?

                        Any update on this would be deeply appreciated..

                        Edited by: shany on Jul 18, 2012 11:26 PM
                        • 9. Re: db file sequential read appears in full table scan mode
                          Hemant K Chitale
                          How many columns does the table have ? Does it have more than 254 columns ?
                          Is Row Chaining present in the table ?

                          Hemant K Chitale
                          • 10. Re: db file sequential read appears in full table scan mode
                            950046
                            Hi hemanth,

                            Yeah table is having more than 254 columns and exact count is 351
                            It's seem's there is lot of chained rows are there..i have below mentioned query to get the details..if the below approach is right then it's having huge number of chained rows...

                            select sid,value,name from v$sesstat s,v$statname s1 where s.statistic#=s1.statistic# and sid=4210 and name like 'table fetch contin%'

                            Could you please explain me why chained rows would create this issue if it is the one which happers performance.
                            • 11. Re: db file sequential read appears in full table scan mode
                              Hemant K Chitale
                              Although Oracle allows you to define a table with 1000 columns, data is actually stored in terms of "row-pieces", each piece being 254 columns. Thus, fetching from the second row-piece requires another single block read even if it happens to be in the same block.

                              See http://hemantoracledba.blogspot.sg/2009/10/some-more-testing-on-intra-block-row.html


                              Hemant K Chitale
                              • 12. Re: db file sequential read appears in full table scan mode
                                Hemant K Chitale
                                Although Oracle allows you to define a table with 1000 columns, data is actually stored in terms of "row-pieces", each piece being 254 columns. Thus, fetching from the second row-piece requires another single block read even if it happens to be in the same block.

                                See http://hemantoracledba.blogspot.sg/2009/10/some-more-testing-on-intra-block-row.html


                                Hemant K Chitale
                                • 13. Re: db file sequential read appears in full table scan mode
                                  Hemant K Chitale
                                  Although Oracle allows you to define a table with 1000 columns, data is actually stored in terms of "row-pieces", each piece being 254 columns. Thus, fetching from the second row-piece requires another single block read even if it happens to be in the same block.

                                  See http://hemantoracledba.blogspot.sg/2009/10/some-more-testing-on-intra-block-row.html


                                  Hemant K Chitale
                                  • 14. Re: db file sequential read appears in full table scan mode
                                    Hemant K Chitale
                                    Although Oracle allows you to define a table with 1000 columns, data is actually stored in terms of "row-pieces", each piece being 254 columns. Thus, fetching from the second row-piece requires another single block read even if it happens to be in the same block.

                                    See http://hemantoracledba.blogspot.sg/2009/10/some-more-testing-on-intra-block-row.html


                                    Hemant K Chitale
                                    1 2 Previous Next