1748246 Members
3330 Online
108760 Solutions
New Discussion юеВ

Re: Bulk Update - Urgent

 
Prabhu_7
Frequent Advisor

Bulk Update - Urgent

I have attached a procedure for reference.

"cur_monthBalance" Cursor will return
22523 records when grouped by
company_code,bus_group,pctr_num,bustype,dealer_num,fund_num,segment_code (For fetching into cursor itself it takes arround 20 mins)

In tPctr_Hanover_Hist i got millions of records.

So for updating one record it takes 2 mins.
and i have 22523 records in total.

Can any one help me modifying this procedure to execute in quicker time.

Thanks in advance.
3 REPLIES 3
Brian Crabtree
Honored Contributor

Re: Bulk Update - Urgent

Please post an explain plan for this.

To do this, run the $ORACLE_HOME/rdbms/admin/utlxplan.sql file from the user running the SQL statement, then do:
explain plan for
Select company_code,bus_group,pctr_num,bustype,dealer_num,fund_num,
segment_code,sum(Nvl(daily_balance,0)) Monthly_Bal,sum(Nvl(daily_accts,0)) Monthly_Accts
From tPctr_Hanover_Hist
Where as_of_date>=to_date(vFirst_Day) and as_of_date<=to_date(pi_last_day)
Group by company_code,bus_group,pctr_num,bustype,dealer_num,fund_num, segment_code;

Once this is complete, run the following command and post the results:

set linesize 150
set pagesize 1000
column operation format a50
column optimizer format a15
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, optimizer,options, object_name,
position, cost from plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id;

(You will not need the "set" and "column" lines if you are using svrmgrl)

Thanks,

Brian
Brian Crabtree
Honored Contributor

Re: Bulk Update - Urgent

Moving along with the assumption that you are missing an index on a column, verify the columns that are indexed on this table. The following should help:

select table_name,index_name,column_name from dba_ind_columns where table_name in ('tPctr_Hanover_Hist','tPctr_Hanover_eom')

Verify that as_of_date, company_code, bus_group, pctr_num, bustype, dealer_num, fund_num, and segment_code are all listed (preferably in the same index).

Brian
Yogeeraj_1
Honored Contributor

Re: Bulk Update - Urgent

hi,

Have you considered running doing a Bulk collect?

Pseudocode:
=======================================================
cursor c is select ....;

loop
fetch c bulk collect into ... LIMIT ;

loop N times, processing N records

for all i in 1 .. N update ...
end loop
=======================================================

where
FOR ALL is a single statement executed atomically.

hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)