Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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)