Update taking time because of db file sequential read.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Update taking time because of db file sequential read.

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245

    Update taking time because of db file sequential read.

    One of my users is running following query to update.

    HTML Code:
    update BM_ANUSALES_OCT2005 bm set totalmou=
    (select sum(rated_volume)/60 from tnpostpaid.rtx_jul_aug 
    where r_p_contract_id=bm.co_id and 
    r_p_customer_id=bm.customer_id and
    rtx_ent_date>=bm.act_date and 
    rtx_ent_date<=bm.end_date and 
    sncode=1 and call_type=1 and xfile_ind='H') 
    where act_date < '01-Aug-2005'
    he said its running very slow so i traced his session and found that trace file contains ony foll nothing else...


    HTML Code:
    Redo thread mounted by this instance: 1
    Oracle process number: 24
    Unix process pid: 19270, image: oracle@cbemis (TNS V1-V3)
    
    *** 2005-10-07 11:00:39.230
    *** SESSION ID:(38.46742) 2005-10-07 11:00:39.173
    WAIT #1: nam='db file sequential read' ela= 22 p1=90 p2=110268 p3=1
    WAIT #1: nam='db file sequential read' ela= 26 p1=90 p2=71954 p3=1
    WAIT #1: nam='db file sequential read' ela= 157 p1=90 p2=71955 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71956 p3=1
    WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71957 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71958 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71959 p3=1
    WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71960 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71961 p3=1
    WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71962 p3=1
    WAIT #1: nam='db file sequential read' ela= 17 p1=90 p2=110269 p3=1
    WAIT #1: nam='db file sequential read' ela= 18 p1=90 p2=71963 p3=1
    WAIT #1: nam='db file sequential read' ela= 139 p1=90 p2=71964 p3=1
    WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71965 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71966 p3=1
    WAIT #1: nam='db file sequential read' ela= 18 p1=90 p2=71967 p3=1
    WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71968 p3=1
    WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71969 p3=1
    WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71970 p3=1
    WAIT #1: nam='db file sequential read' ela= 17 p1=90 p2=110270 p3=1
    WAIT #1: nam='db file sequential read' ela= 15 p1=90 p2=71971 p3=1
    and it looks like session is waiting for indefinite time...
    what need s to be done. From asktom i got to know that it is bcoz of sinle block read index scans but i am not sure what to do.

    Oracle 9.2.0.6.0
    HP-UX-11i
    Any thoughts....

    Rgds
    Parag

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    "db file sequential read"s are definitely single block reads due to index usage. In about 80% of the cases, it means the wrong index is being used.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Quote Originally Posted by marist89
    "db file sequential read"s are definitely single block reads due to index usage. In about 80% of the cases, it means the wrong index is being used.
    or rather an appropriately selective enough index has not been created?
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This leaps out at me:
    Code:
    act_date < '01-Aug-2005'
    I think you ought to be using
    Code:
    act_date < to_date('01-Aug-2005','DD-Mon-YYYY')
    Also, how many rows are you updating here? Might it be possible to run the update "indirectly" by creating a new temporary data set based on a join of BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug, then either replacing the data in the target table or updating it through an in-line view that joins it with the temporary table? Or maybe you can even update against an in-line view between BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug right now (although it looks tricky).
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    yes actually developer was using that to_date. While trying to see explain plan i removed it. sorry for that.
    I have asked user to change the logic .
    My thinking is since he is using sub-query it might be the cause. I have asked him to make use of cursor. I have aksed him to test with bulk collect.. Do you think that will help.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    From your trace p1=90

    Do you know the object stored in the data file 90?

    Tamil

  7. #7
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Yes file is part of tablespace which contains all rtx partition tables for JUL,AUG,SEP,OCT. and rtx_jul_aug is view for these base tables..

    Rgds
    Parag

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by paragp1981
    Yes file is part of tablespace which contains all rtx partition tables for JUL,AUG,SEP,OCT. and rtx_jul_aug is view for these base tables..

    Rgds
    Parag
    I suspect the cause may be in the view definition.
    Do one thing:
    First create a temp table and measure run time:

    Code:
    create table temp_rtx  NOLOGGING 
    as
    select  r_p_contract_id, r_p_customer_id, 
              rtx_ent_date
              sum(rated_volume)/60 
    from tnpostpaid.rtx_jul_aug 
    where sncode=1 and call_type=1 and xfile_ind='H'
    group by  r_p_contract_id, r_p_customer_id, rtx_ent_date
    
    And also check how many rows in the temp file.
    Tamil

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The way that this update will be processed is that the subquery will be executed for every individual row that needs to be updated ... so you are potentially executing that subquery (and accessing the same index blocks) 100,000 times if you have that many rows to update.

    Hence my suggestions to try and join the two tables directly together to get the result calculated more efficiently ... it could be that there is a more effective join strategy than the nested loops you are currently probably seeing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Well size of these each tables is around 15-16 GB. So i cant really create
    these temp table that will require huge space .
    but if you run

    select count(1) from tnpostpaid.rtx_jul_aug
    where sncode=1 and call_type=1 and xfile_ind='H'
    group by r_p_contract_id, r_p_customer_id, rtx_ent_date
    it gives

    52128446

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width