improving performance of update statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: improving performance of update statement

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    improving performance of update statement

    Hi All,
    We use oracle 8.1.7.4 and solaris 2.8.
    We have have 8 CPU each 750 Mhz. We are doing a bulk update using Pro*C. Each batch does an update of 50K. This is taking close to 8.5 Min. We are passing the where condition values using an Array.
    The table is very big, and has some old data. We are planing to purge all the old data. Apart from this is it possible to improve the performance of the update statement. Something like parallel update or direct update which would improve the update speed.

    Can u please guide to any documentation on this or provide some help would be greatly appreciated.

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Jan 2002
    Posts
    113
    You can play with parallel dml to see if it makes a difference. I use it like this:

    1)alter session enable parallel dml;
    2)run procedure with hint in dml statement:

    insert /* APPEND PARALLEL(voters_dupkey, 2) */ into voters_temp ....

    (update statement would not use APPEND its used for direct inserts)

    There are restrictions. Look at metalink notes 201457.1 and 201978.1

    I don't think there is a way to do direct updates, only direct inserts. If I'm wrong then someone please correct me.
    Arm yourself with knowledge

  3. #3
    Join Date
    Jan 2002
    Posts
    113
    Previous post should not read

    /* APPEND PARALLEL ...

    it should be

    /*+ APPEND PARALLEL ...
    Arm yourself with knowledge

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do it in a single shot (SQL).
    See below:
    PHP Code:
    set time on timing on
    spool upd_my_table_2
    .log

    drop index scott
    .my_table_m3 ;

    --- 
    1 option
    alter table scott
    .my_table parallel 8;
    alter index scott.my_table_p1 parallel 8;
    alter table scott.tamil_temp_con_addr parallel 8;
    alter index scott.tamil_temp_con_addr_idx parallel 8;

    commit;
    alter session enable parallel dml ;
    alter session enable parallel query ;

    update /*+ parallel(a,8) */ scott.my_table a
       set a
    .pr_addr_id = ( select addr_per_id
                              from scott
    .tamil_temp_con_addr c
                             where c
    .accnt_id a.row_id)
    where exists select null
                    from scott
    .tamil_temp_con_addr c
                    where c
    .accnt_id a.row_id) ;
    commit;
    alter session disable parallel dml ;
    alter session disable parallel query ;
    alter table scott.my_table    noparallel ;
    alter index scott.my_table_p1 noparallel ;

    spool off 
    Tamil

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi All,
    Thanks for the response. I forgot to mention one thing, the column that gets updated gets its values from an external source, its not stored in a table. Its a daily update and this values is passed using an array, using bind variable.
    I will try what tamilselvan suggested.

    Thanks once again.

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    May be BULK COLLECT will help you.

    Also, increase in INITRANS value may speed up update process.

    Tamil

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