PERFOMANCE ISSUE

[miraora]

How I can i increase the perfomance of the follwoing procedure.
Please help me. IT is taking long time
I used BULK COLLECT to improve. it is executing in half time but experts says not good idea to update raws in not a good idea.
Expert say as under.
If you do update in chunks and the process fails, how do you know which customers have been given a rebate and which customers not? How do you restart this business transaction that you chose to implements as multiple database transactions?

What happens during the time of these serial batch chunk runs when new customers are added? Or existing customers are deleted (and the rebate already subtracted from the company's coffers are deleted with it)?

The problem here is not locking. Or concurrency. The problem is plain and simple - a damn poor and very flawed database design. Why?

For debiting and crediting you use a TRANSACTION table. You do not directly updates the balances of customers. You insert rows as debits and credits against a customer account.

Any Suggestion please

PROCEDURE SP_930_End_Of_Week_OH_up (
            pSKU            In 
            SS_SKU_Store_Week.SKU%Type,
            pStore           In 
        SS_SKU_Store_Week.Store_Num%Type) Is
DECLARE
  Cursor OpportunityRating_Cursor Is
      SELECT  /*+ index (SS_SKU_Store_Week     
      SS_SKU_STR_WK_PK  )*/ Opportunity_Rating,
      End_Of_Week_On_Hand,
      Decode(PSW_Flag, 0, 0, 
      Distributed_Planned_Sales)    
      DistributedPlannedSales
   FROM     SS_SKU_Store_Week
   WHERE    SKU                  = pSKU AND
               Store_Num            = pStore
   ORDER BY Year_Week_Key Desc     FOR UPDATE;
BEGIN
   
       vForwardSales := 0;
   For rWeek In OpportunityRating_Cursor Loop
      If rWeek.Opportunity_Rating Is Not Null Then
         UPDATE   SS_SKU_Store_Week
         SET      Opportunity_Rating   = Decode 
     (vForwardSales, 0, 1, Round 
      reek.End_Of_Week_On_Hand / vForwardSales, 4))  
      WHERE CURRENT OF OpportunityRating_Cursor;
         vForwardSales := 0;
      End If;
      vForwardSales := vForwardSales + 
      rWeek.DistributedPlannedSales;
 End Loop;

   Commit;

Exception
   When OTHERS Then
      Null;
    
END;

 

[LKBrwnDBA]

Your experts are wrong, a simple answer to their concerns is that the BULK COLLECT will behave like any SELECT, Its just that it selects multiple rows.

Try coding your procedure something like this:

PROCEDURE SP_930_End_Of_Week_OH_up (
            pSKU            In 
            SS_SKU_Store_Week.SKU%Type,
            pStore           In 
        SS_SKU_Store_Week.Store_Num%Type) Is
DECLARE
  Cursor OpportunityRating_Cursor Is
      SELECT  /*+ index (SS_SKU_Store_Week SS_SKU_STR_WK_PK  )*/ 
             Opportunity_Rating,
             End_Of_Week_On_Hand,
             Decode(PSW_Flag, 0, 0, 
             Distributed_Planned_Sales)    
             DistributedPlannedSales,
             ROWID Row_Id
        FROM    SS_SKU_Store_Week
       WHERE    SKU = pSKU AND Store_Num = pStore
       ORDER BY Year_Week_Key Desc FOR UPDATE;

  Type Num_Typ Is Table Of Number Index By Binary Integer;
  Type Row_Typ Is Table Of ROWID Index By Binary Integer;
  V_Opportunity_Rating		Num_Typ;
  V_End_Of_Week_On_Hand		Num_Typ;
  V_DistributedPlannedSales	Num_Typ;
  V_Row_Id                      Row_Typ;
  I Pls_Integer; 
  vForwardSales 		Number:= 0;
   
BEGIN
   Open OpportunityRating_Cursor;
   Fetch OpportunityRating_Cursor BULK COLLECT
           Into V_Opportunity_Rating
              , V_End_Of_Week_On_Hand
              , V_DistributedPlannedSales
              , V_Row_Id;
   For I In 1..V_Row_Id.Count
   Loop
     If V_Opportunity_Rating(I) Is Not Null Then
       Select Decode (vForwardSales, 0, 1
            , Round(V_End_Of_Week_On_Hand(I) / vForwardSales, 4))  
         Into V_Opportunity_Rating(I)
         From Dual;
       vForwardSales := 0;
     End If;
     vForwardSales := vForwardSales + V_DistributedPlannedSales(I);
   End Loop;
   ForALL I In 1..V_Row_Id.Count
     UPDATE   SS_SKU_Store_Week
        SET   Opportunity_Rating = V_OpportunityRating(I)
      WHERE   ROWID = V_Row_Id(I);           

   Commit;

Exception
   When OTHERS Then
      Null;
    
END;

PS: If the procedure fails, everything will roll-back.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[miraora]

Thank you very very much LKBrwnDBA (MIS) for your help.

The code really looks great. I did the same logic but instead of rowid i stored all sku,store and yearweekkey in pl/sql table. and then did FORALL update.
I just have on dought in above code is that I am not storing any ROWID, as not all V_oppertunityrating(i) will have data in the if statement. Don't we need to store corrosponding ROWID. so probebly it will give me ann error of no_data_found. I am not sure. Am right.

Also in cursor for update will lock all the rows fetched in the bulk collect. am I right or wrong. Do we need that clause in for update in cursor.

Also please read the follwoing forum. which really confused me so that I was scaptical using bulk collect.

http://forums.oracle.com/forums/thread.jspa?threadID=459738&tstart=0

Please please clear mu dougths. Thank tyou so much for help in advance.

 

[miraora]

Thank you LKBrwnDBA

Here is my updated procedure which i did with bulk before your suggestion. please have look at it. i will chage it with rowid now

Procedure SP_930_End_Of_Week_OH_up (
            pSKU                       In SS_SKU_Store_Week.SKU%Type,
            pStore                     In SS_SKU_Store_Week.Store_Num%Type) Is

Type  tsku                  is table of  SS_SKU_Store_Week.sku%Type;
Type  tstore                is table of  SS_SKU_Store_Week.store_num%Type;
Type  tYearWeekKey          is table of           SS_SKU_Store_Week.Year_Week_Key%Type;
Type  tOnHand               is table of           SS_SKU_Store_Week.End_Of_Week_On_Hand%Type;
Type  tPlannedReceipts      is table of           SS_SKU_Store_Week.Planned_Receipts%Type;
Type  tShipmentQuantity     is table of          SS_SKU_Store_Week.Shipment_Quantity%Type;
Type  tWarehouseAllocation  is table of          SS_SKU_Store_Week.Warehouse_Allocation%Type;
Type  tPlannedSalesTW       is table of          SS_SKU_Store_Week.Distributed_Planned_Sales%Type;
Type  tPlannedWeekFlag      is table of          SS_SKU_Store_Week.Planned_Week_Flag%Type;
Type  tOpertunityRating     is table of         SS_SKU_Store_Week.OPPORTUNITY_RATING%type;
Type  tLastWeekEndOfWeekOnHand  is table of     number index by binary_integer;
Type  tEndOfWeekOnHand      is table of         number index by binary_integer; 
   vsku                       tsku;
   vsku_1                     tsku;
   vstore                     tstore;
   vstore_1                   tstore;
   vYearWeekKey               tYearWeekKey;
   vYearWeekKey_1             tYearWeekKey; 
   vOnHand                    tOnHand;     
   vPlannedReceipts           tPlannedReceipts; 
   vShipmentQuantity          tShipmentQuantity ; 
   vWarehouseAllocation       tWarehouseAllocation; 
   vPlannedSalesTW            tPlannedSalesTW;  
   vPlannedWeekFlag           tPlannedWeekFlag;
   vLastWeekEndOfWeekOnHand   tLastWeekEndOfWeekOnHand; 
   v_total_EndOfWeekOnHand     tEndOfWeekOnHand; 
   vOpertunityRating           tOpertunityRating;
   vdistributedSales           tPlannedSalesTW;  -- distributed planned sales
   vOpRat                      tEndOfWeekOnHand; 
   vEndOfWeekOnHand             Number;        
   vFirstWeek                   Boolean;
   vForwardSales                Number;
   v_idx pls_integer:=1;
  Cursor /*+ index (SS_SKU_Store_Week SS_SKU_STR_WK_PK  )*/OpportunityRating_Cursor Is
      SELECT   sku,store_num,year_week_key,Opportunity_Rating,
               End_Of_Week_On_Hand,
               Decode(PSW_Flag, 0, 0, Distributed_Planned_Sales)    DistributedPlannedSales
      FROM     SS_SKU_Store_Week
      WHERE    SKU                  = pSKU AND
               Store_Num            = pStore
      ORDER BY Year_Week_Key Desc;
      --FOR UPDATE;
  Cursor EOWOH_Cursor is 
      SELECT /*+ ordered all_rows use_nl(SS_SKU SS_SKU_STORE) index( SS_SKU SS_SKU_PK) 
               index( SS_SKU_STORE SS_SKU_ST_PK) use_nl( SS_SKU_STORE  SS_SKU_Store_Week )
                index( SS_SKU_Store_Week SS_SKU_STR_WK_PK  ) */ Year_Week_Key,SS_SKU_store_week.SKU,
                SS_SKU_store_week.Store_Num,
              Floor(Starting_On_Hand * On_Hand_Percent) + Contributing_On_Hand        OnHand,
              (Planned_Receipts * On_Order_Flag) + Contributing_On_Order     PlannedReceipts,
              Nvl(Shipment_Quantity, 0)                                     ShipmentQuantity,
              Decode(PSW_Flag, 0, 0, Distributed_Planned_Sales)      DistributedPlannedSales,
              Warehouse_Allocation
      FROM    SS_SKU,
              SS_SKU_Store,
              SS_SKU_Store_Week
      WHERE   SS_SKU.SKU                 = pSKU AND
              SS_SKU.SKU                 = SS_SKU_store.SKU AND
              SS_SKU_Store.Store_Num     = pStore  AND
              SS_SKU_Store.SKU           = SS_SKU_Store_Week.SKU AND
              SS_SKU_Store.Store_Num     = SS_SKU_Store_Week.Store_Num
      ORDER BY Year_Week_Key;

Begin
   vFirstWeek := True;
    Open EOWOH_Cursor;
     Loop
         FETCH    EOWOH_Cursor bulk collect 
          INTO     vYearWeekKey,
                   vsku,
                   vstore, 
                   vOnHand,
                   vPlannedReceipts,
                   vShipmentQuantity,
                   vPlannedSalesTW,
                   vWarehouseAllocation limit 200;
                
        for i in 1..vYearWeekKey.count loop
                  
              begin   
                   If vFirstWeek Then
                          vFirstWeek := False;
                          vLastWeekEndOfWeekOnHand(i) := vOnHand(i);
                   ElsIf   vEndOfWeekOnHand >  0 Then
                       vLastWeekEndOfWeekOnHand
        (i) :=vEndOfWeekOnHand;
                   Else
                             vLastWeekEndOfWeekOnHand
         (i) := 0;
                   End If;
                          vEndOfWeekOnHand :=      vLastWeekEndOfWeekOnHand(i)
                                                +  vPlannedReceipts(i)
                                                +  vShipmentQuantity(i)
                                                +  vWarehouseAllocation(i)
                                                -  vPlannedSalesTW(i);
                          v_total_EndOfWeekOnHand(i):=vEndOfWeekOnHand;                   
              exception
                       when others then
                        null;
              end;   

        End loop;
           
         forall i in 1..vYearWeekKey.count
                     
                       UPDATE   /*+ index (SS_SKU_Store_Week SS_SKU_STR_WK_PK  )*/SS_SKU_Store_Week
                       SET      End_Of_Week_On_Hand     = v_total_EndOfWeekOnHand(i)
                       WHERE    SKU                   = vSKU(i) AND
                               Store_Num               = vStore(i) AND
                               Year_Week_Key           = vYearWeekKey(i);          
             
           Exit When EOWOH_Cursor%NotFound;    
   End Loop; 

    close EOWOH_Cursor;
    Commit;
    
   vForwardSales := 0;
   v_idx :=1;
-- using delete() reusing same variable again
   vsku.delete(); 
   vstore.delete();
   vyearweekkey.delete();
   vstore_1 := tstore();
   vsku_1:=tsku();
   vyearweekkey_1 :=tyearweekkey();
   vOnHand.delete;
   OPEN OpportunityRating_Cursor;
   LOOP 
        FETCH OpportunityRating_Cursor bulk collect into
                  vsku,vstore,vyearweekkey,
                  vOpertunityRating,vOnHand, vdistributedSales limit 200 ;
           FOR i in    vsku.first..vsku.last  LOOP               
            
                 IF  vOpertunityRating(i) Is Not Null Then
                                          
                       vOpRat(v_idx):=case  vforwardsales
                                 when 0 then 1
                                 else  Round(vOnHand(i)/ vForwardSales, 4)
                                 end;  
                          vsku_1.extend;
                          vstore_1.extend;
                          vyearweekkey_1.extend;
                          vsku_1(v_idx)        :=vsku(i);
                          vstore_1(v_idx)      :=vstore(i);
                          vyearweekkey_1(v_idx):=vyearweekkey(i);         
                          v_idx                :=v_idx+1;
                          vForwardSales := 0;                       
                  END IF;
                        vForwardSales := vForwardSales + vdistributedSales(i);
           END LOOP;;
                       
           FORALL i in 1..vOpRat.count
                 update  /*+ index (SS_SKU_Store_Week SS_SKU_STR_WK_PK  )*/SS_SKU_Store_Week
                  SET  Opportunity_Rating      = vOpRat(i)
                  Where           sku          =vsku_1(i) and
                                  store_num    =vstore_1(i) and 
                                  year_week_key=vyearweekkey_1(i);

            EXIT WHEN OpportunityRating_Cursor%NOTFOUND;                   
   END LOOP;
      CLOSE OpportunityRating_Cursor;
     COMMITt;
 Exception
   When OTHERS Then
   dbms_output.put_line('sql eror  :' ||sqlerrm);
      Null;
End SP_930_End_Of_Week_OH_up;

 

[LKBrwnDBA]

You may have problems with limit 200.

It appears you are partitioning the result cursor by Year_Week_Key, therefore if you limit to 200 one of the partitions may fall between two 200 limits.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[miraora]

Thank you for your help and Patience.
If i take out limit then i will fetch all the rows and may be thousands of rows. It will require huge PGA and will degrade the prfomance. Am I right ?
Please let me know.

Also How about this two questions I have.

1. I just have on dought in above code is that I am not storing any ROWID, as not all V_oppertunityrating(i) will have data in the if statement. Don't we need to store corrosponding ROWID. so probebly it will give me ann error of no_data_found. I am not sure. Am right.

2. Also in cursor for update will lock all the rows fetched in the bulk collect. am I right or wrong. Do we need that clause in for update in cursor.

 

[miraora]

UPDATE:

The above procedure is running 1 sec faster then with ROWID, I thought with ROWID would run faster. Just for your consideration.
Please let me know what could be the reason.

 

[miraora]

Please reply to my last two postings.
Thank you in advance

 

[LKBrwnDBA]

1) Use rowid, it's faster.

2) I've never had a problem not using 'FOR UPDATE'

How many 'thousands' of rows? I have tested for 500k+ with no problem (depends on the amount of data retrieved), for larger tables I use LIMIT 10000 (and 50000). Trick is only to query the minimum data necesary.

Test it without the limit to check if it works.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[miraora]

Thank you LKBrwnDBA for you reply.
It is working fine with ROWID. It is faster now.

But If use FOR UPDATE it becomes slower by two seconds for 1000 rows. If i don't use this clause should not effect my update(data integrity) as I am not using where CURRENT of CLAUSE in cursor. Am i right ?. Sorry to bother you but you are providing the best Help.
Thank you so Much again.

 

[LKBrwnDBA]

I've never had update(data integrity) problems not using 'FOR UPDATE' clause combined with 'bulk collect'.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[miraora]

Thank you so much.
I really got a great reply from you. All my dought are clear
You are the best