update taking so long
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: update taking so long

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    I have the table and it had 5 columns
    CREATE TABLE LIS.LD_EXHIBIT_DESC
    (
    AGMT number (10) NOT NULL,
    EX VARCHAR2(4000) NOT NULL,
    SYSGEN VARCHAR2(1) NULL,
    UPDT_DATE DATE NULL,
    UP VARCHAR2(12) NULL,
    LINE_NO NUMBER(3) NULL
    )


    I try to update the column LINE_NO=5 and it takes forever,
    the table has about 40K now.

    What do I need to to do to speed up this process ???

  2. #2
    Join Date
    Jan 2002
    Posts
    474
    Please help , it's urgent


  3. #3
    Join Date
    Feb 2001
    Posts
    389
    run a trace with explain plan and check what is it waiting for and doing.

  4. #4
    Join Date
    Feb 2001
    Posts
    119
    use bot of these and check how many records are used and
    How much more time it will take ..

    check whether you have space for rollback space ...


    select l.sid sid,p.spid PID,NVL ( s.username, 'NO TRANSACTION' ) USERNAME,
    NVL ( s.osuser, '----' ) osuser,
    substr(r.name,1,10)"RBS NAME",
    s.terminal TERMINAL ,
    USED_UREC,
    START_TIME
    FROM v$lock l, v$process p, v$session s, v$rollname r ,V$TRANSACTION T
    WHERE l.sid = s.sid(+) AND TRUNC (l.id1(+)/65536) = r.usn
    AND l.type(+) = 'TX' AND l.lmode(+) = 6 AND s.paddr = p.addr
    and s.taddr=t.addr ORDER BY r.name

    select SID,SERIAL#,TARGET,SOFAR,TOTALWORK,TIME_REMAINING,
    to_char(START_TIME,'DD-MON-YYYY HH:MI:SS PM')"START_TIME",
    to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS PM')"SYSDATE",message
    from v$session_longops where TIME_REMAINING>0

  5. #5
    Join Date
    Jan 2002
    Posts
    474
    Thanks for your reply



    gpsingh,

    I did run the explain plan and here is the result:

    table access full, obviously it will scan the full table since we update everyhting on there



    Subha


    I did run your query and here is the result

    I didn't see anything on the longop, any other advise ????




  6. #6
    Join Date
    Feb 2001
    Posts
    389
    i meant to say run dbms_system.set_ev(sid,serial#,10046,8,'')
    and check waits and then run tkprof to get the
    i/o and cpu usage.

  7. #7
    Join Date
    Sep 2000
    Posts
    384
    First if You update a big table you should assign a big rollback segment with a high initial and next extent

    Ensure there is proper log switching ...
    If checkpoint problem is there your update will take longer(check in the alert log)
    If there is a problem add one or two more log membets ...
    if possible while doing mass update do that as nightly job

    Radhakrishnan.M

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