1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

performance issue in pl/sql program

Discussion in 'SQL PL/SQL' started by r.uma, Nov 11, 2009.

  1. r.uma

    r.uma Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hai all this query i am facing performance issue its taking nearly 6 hrs to run ....can any one modify the query.....because the table which i am updating is having millon row ....
    for 5000 rows itz taking nearly 3 min .....help me out ....
    DECLARE
    Rec_count_for_commit NUMBER := 0;
    Rec_count_for_commit1 NUMBER := 0;
    BEGIN
    for cur_oos_var in (
    SELECT item_upc,avail_to_sell,loc_nbr
    from ft_curr_ent_bal1
    where item_upc>1 and
    LOC_NBR in (975,525,526,1385,3831,4421) group by

    item_upc,avail_to_sell,loc_nbr)
    loop
    Rec_count_for_commit := Rec_count_for_commit + 1;
    --BEGIN THE oos_loc_date UPDATION
    BEGIN
    if(cur_oos_var.avail_to_sell<1) then
    UPDATE ft_ceb_onhand1 SET OOS_LOC_DATE=sysdate
    WHERE oos_loc_date is null and item_upc=cur_oos_var.item_upc and

    loc_nbr=cur_oos_var.loc_nbr;
    else
    UPDATE ft_ceb_onhand1
    SET oos_loc_date=null
    WHERE oos_loc_date is not null and item_upc=cur_oos_var.item_upc and

    loc_nbr=cur_oos_var.loc_nbr;
    end if;
    If Rec_count_for_commit = 5000 Then
    Rec_count_for_commit := 0;
    Commit;
    End If;
    end;
    end loop;
    --BEGIN THE oos_network_date UPDATION
    for cur_OOS_net_var in ( select item_upc from ft_curr_ent_bal1 where

    item_upc>1 and avail_to_sell<1 and
    LOC_NBR in (975,525,526,1385,3831,4421) group by item_upc

    having count(loc_nbr)=6)
    loop
    Rec_count_for_commit1 := Rec_count_for_commit1 + 1;
    UPDATE ft_ceb_onhand1
    SET OOS_NETWORK_DATE=sysdate
    WHERE item_upc =cur_OOS_net_var.item_upc;
    update ft_ceb_onhand1
    set oos_network_date = Null
    where avail_to_sell >0 AND LOC_NBR IN (975,525,526,1385,3831,4421);
    If Rec_count_for_commit1 = 5000 Then
    Rec_count_for_commit1 := 0;
    Commit;
    End If;
    end loop;
    end;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is in the wrong place; it needs to be posted in the SQL forum.
     
  3. vatsjai

    vatsjai Guest

    Hi r.uma

    Just paste like this will not help. Try to paste TKPROF output of this program. Then only we can find out why query is taking so long.

    Thanks
    Jai