improving performance of update statement

[anandkl]

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

[psychofun]

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.

[psychofun]

Previous post should not read

/* APPEND PARALLEL ...

it should be

/*+ APPEND PARALLEL ...

[tamilselvan]

Do it in a single shot (SQL).
See below:

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

[anandkl]

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

[tamilselvan]

May be BULK COLLECT will help you.

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

Tamil