Home » SQL & PL/SQL » SQL & PL/SQL » Frequent COMMIT is Culprit or anything else?
Frequent COMMIT is Culprit or anything else? [message #184489] Wed, 26 July 2006 15:10 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hello everyone!

I Need help,please.

I am assigned to rectify a slow performance procedure in a project.

This procedure is a part of a package and it is a trunc and load process 17,000,000 rows.

The problem is that it takes 20 hours to insert 17,000,000 rows to the target (two) tables.

So far I have figured out that the procedure is

using COMMIT AT EVERY 10 RECORD - WHICH IS VERY VERY BAD IDEA

as I have read a lots of information about COMMIT on this site.

But I am afraid if I remove this FREQUENT COMMIT what will be

the effect with one COMMIT.

will it be successfully insert all the rows with one COMMIT?

OR there could be other reasons also for slow performance
like SQL statement in the cursor etc...?

And more over there is no ROLLBACK also.

Here is the code please advise me what are the areas
I have to look for to improve the code performance.

Your assistance is highly appreciated.
PROCEDURE p_ClaimSplit (v_LastDay IN  DATE, 
                               v_Rc OUT NUMBER)
  IS
    v_ThisFiscalMonth  tblClaimDetMonthly_S_part.FiscalMonth%TYPE; --'200402';
    v_PendingLastDay   tblClaimDetMonthly_S_part.FromDate%TYPE;
                                                --Last day of next fisical month.
    v_Ct               Number :=0;
    v_FMPart           Number;
    v_SvcFiscalMonth   tblClaimDetMonthly_S_part.FiscalMonth%TYPE;
    v_ProvAmount       tblClaimDetMonthly_S_part.ProvAmount%TYPE;
    v_PlanAmount       tblClaimDetMonthly_S_part.PlanAmount%TYPE;  --LJK 06/27/2005  New field Added.
    v_CTX              Number := 0;
    v_Date             tblClaimDetMonthly_S_part.Fromdate%TYPE := NULL;
    v_FiscalDate       tblClaimDetMonthly_S_part.Fromdate%TYPE := NULL;
    v_TOFiscalMonth    tblClaimDetMonthly_S_part.FiscalMonth%TYPE := NULL;
    v_AmtUsed          tblClaimDetMonthly_S_part.ProvAmount%TYPE := 0;
    v_PlanAmtUsed      tblClaimDetMonthly_S_part.ProvAmount%TYPE := 0;  --LJK 06/27/2005  New field Added.
    v_ClaimID          tblClaimDetail.ClaimID%TYPE := NULL;
    v_ClaimLineNumb    tblClaimDetail.ClaimLineNumb%TYPE := NULL;
    error_number       number;
    error_message      varchar2(200);
    v_SeqNumb          NUMBER := 0;
    v_EmailTo          EMAIL.AddressList_Tab;
    v_EmailFrom        VARCHAR2(50) := USER || '@GNT.COM';
    v_EmailSubject     VARCHAR2(50) := 'MONTH-END CLAIM SPLIT';
    v_EmailText        VARCHAR2(2000) := NULL;
    x                  number := 0;
    y                  number := 5000;
    z                  number := 0;
    v_FromFiscalInfo   tFiscalMonthRec;
    v_ToFiscalInfo     tFiscalMonthRec;
    --LJK 06/27/2005  Added PlanAmount to be pulled back from the partitioned table.
    CURSOR curCD is
      SELECT O.ClaimID, O.ClaimLIneNumb, O.RegionalNetworkCenter, O.PaymentFiscalMonth,
DECODE(O.AccountingDepartmentRptCD,'1','1','2','2','3','3','4','4','5','3','6','3','7','3','9','9','1') FeeForService,
        O.ServiceCatID, O.FromDate, O.ToDate, O.FromDateFiscalMOnth, O.ProvAMount, O.PlanAmount,
        NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD, NVL(O.ClaimDetailHistSeqNumb,0) ClaimDetailHistSeqNumb,
    (O.TODate - O.FromDate) + 1 TotalServiceDays, CarID, O.ProvPaymentType ProvType
      FROM tblClaimDetMonthly_s_part O
      WHERE FiscalDate = v_FiscalDate
      AND   (O.ProvAmount <> 0 OR O.PlanAmount <> 0)  --LJK 08/19/2005.  Added OR O.PlanAmount <> 0.  Some claims might have one > 0 and the other value = 0.
      AND   O.claimid BETWEEN x AND y;
    
      recCD           curCD%ROWTYPE;
      v_CurrentInfo   tFiscalMonthRec;
      
      
BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE('MONTH-END','CLAIM SPLIT');
    v_EmailTo   := f_BuildEmail('MONTHENDCLAIMS');
    v_EmailText := 'PROCESS STARTED: ['  || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '].';
    EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);

    --Clear Out work table.

TRUNCATE_TABLE('W_CLAIMSPLIT');

    --LK... Since I keep on messing up the pending last day, I've changed it to where you do not have to pass it in, but will instead be calculated for you.
    
    v_CurrentInfo     := f_GetFiscalInfo(v_LastDay);
    v_PendingLastDay  := v_CurrentInfo.LastDay;
    v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
    
    DBMS_OUTPUT.PUT_LINE('THIS FISCALMONTH: [' || TO_DATE(SUBSTR(v_ThisFiscalMonth,5) || '/01/' || SUBSTR(v_ThisFiscalMonth,1,4),'mm/dd/yyyy') || ']');
    
    v_FiscalDate := TO_DATE(SUBSTR(v_ThisFiscalMonth,5) || '/01/' || SUBSTR(v_ThisFiscalMonth,1,4),'mm/dd/yyyy');
    
    SELECT MAX(ClaimId) INTO z
          FROM   Tblclaimdetmonthly_s_part
          WHERE  FiscalDate = v_FiscalDate;
    
    WHILE x < Z LOOP

      v_SeqNumb := 0;

      FOR recCD in curCD LOOP

        v_ClaimID         := recCD.ClaimID;
        v_ClaimLineNumb   := recCD.ClaimLIneNumb;
        v_SvcFiscalMonth  := recCD.FromDateFiscalMonth;
        v_FromFiscalInfo  := f_GetFiscalInfo(recCD.FromDate);
        v_ToFiscalInfo    := f_GetFiscalInfo(recCD.ToDate);
        
        IF  v_ToFiscalInfo.LastDay IS NULL THEN  --lk 10/04/2004 If they enter a dos beyond the tblFiscalMonth, then this row was not being populated.
            v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
        END IF;
        IF  v_ToFiscalInfo.LastDay > v_PendingLastDay THEN
            v_ToFiscalInfo.FiscalMonth := 'PEND';
        END IF;
        IF  v_FromFiscalInfo.LastDay > v_PendingLastDay --lk 10/04/2004.
            OR v_FromFiscalInfo.FiscalMonth = 'PEND'
            OR recCD.TotalServiceDays <= 0 THEN
            v_ProvAmount     := recCD.ProvAmount;
            v_PlanAmount     := recCD.PlanAmount;    --LJK 06/27/2005  Added for new field.
            v_SvcFiscalMonth := 'PEND';  --lk 10/04/2004
            v_Ct             := v_Ct + 1;
            v_SeqNumb        := v_SeqNumb + 1;
            
          recCD.ClaimDetailHistSeqNumb := v_SeqNumb;
          --LJK 06/27/2005  Added PlanAMount to Insert.
       
 INSERT INTO   w_ClaimSplit(ClaimId, ClaimLineNumb, ClaimDetailHistSeqNumb,ClaimDetailHistRecTypeCd,
               OperationCenterCode,FeeForService, ServiceCatId, SvcFiscalMonth, PaymentFiscalMonth, ProvAmount, CarId, ProvTypeCd, PlanAmount)
        VALUES (recCD.ClaimId, recCD.ClaimLineNumb, recCD.ClaimDetailHistSeqNumb,recCD.ClaimDetailHistRecTypeCd,
               recCD.RegionalNetworkCenter,recCD.FeeForService, recCD.ServiceCatId, v_SvcFiscalMonth, recCD.PaymentFiscalMonth, recCD.ProvAmount,
               recCD.CarId, recCD.ProvType, recCD.PlanAmount);
         
         IF v_ct >= 10 THEN
            v_ct := 0;
            COMMIT;
         END IF;
          v_FromFiscalInfo.FiscalMonth := 'PEND';
        ELSE
          v_AmtUsed     := 0;
          v_PlanAmtUsed := 0;    --LJK 06/27/2005  Added to initialize new field.
          v_Date        := recCD.FromDate;
          
     WHILE v_FromFiscalInfo.LastDay <= NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1)) LOOP
           
           v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; 
           
         IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN  --LK 10/04/2004
                 -- Into the Pending month
              v_ProvAmount     := recCD.ProvAmount - v_AmtUsed;
              v_PlanAmount     := recCD.PlanAmount - v_PlanAmtUsed;  --LJK 08/23/2005.  Set the PlanAmount not the Used Amount.
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
            ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
              v_ProvAmount     := recCD.ProvAmount - v_AmtUsed;
              v_PlanAmount     := recCD.PlanAmount - v_PlanAmtUsed;
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_FromFiscalInfo.LastDay     := v_ToFiscalInfo.LastDay + 1;
            ELSE
              v_FMPart         := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD.TotalServiceDays;
              v_ProvAmount     := Round(recCD.ProvAmount * v_FMPart,2);
              v_PlanAmount     := Round(recCD.PlanAmount * v_FMPart,2);
              v_AmtUsed        := v_AmtUsed + v_ProvAmount;
              v_PlanAmtUsed    := v_PlanAmtUsed + v_PlanAmount;
              v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
              v_Date           := v_FromFiscalInfo.FirstDay;
            END IF;
            v_SeqNumb := v_SeqNumb + 1;
            recCD.ClaimDetailHistSeqNumb := v_SeqNumb;
            
 INSERT INTO   w_ClaimSplit(ClaimId, ClaimLineNumb, ClaimDetailHistSeqNumb,ClaimDetailHistRecTypeCd,
               OperationCenterCode,FeeForService, ServiceCatId, SvcFiscalMonth, PaymentFiscalMonth, ProvAmount, CarId, ProvTypeCd, PlanAmount)
        VALUES (recCD.ClaimId, recCD.ClaimLineNumb, recCD.ClaimDetailHistSeqNumb,recCD.ClaimDetailHistRecTypeCd,
               recCD.RegionalNetworkCenter,recCD.FeeForService, recCD.ServiceCatId, v_SvcFiscalMonth, recCD.PaymentFiscalMonth, v_ProvAmount,
               recCD.CarId, recCD.ProvType, v_PlanAmount);
               v_Ct := v_Ct + 1;
IF v_ct >= 10 THEN
   v_ct := 0;
   COMMIT;
END IF;
           IF v_SvcFiscalMonth = 'PEND' THEN
              EXIT;
            END IF;
          END LOOP;
        END IF;
      END LOOP;
      x := y + 1;
      y := y + 5000;
    END LOOP;
    COMMIT;
    v_EmailText := 'PROCESS COMPLETED: [' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '].';
    EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
  EXCEPTION
    WHEN OTHERS THEN
      error_number  := SQLCODE;
      error_message := substr(SQLERRM, 1, 200);
      dbms_output.put_line('error: ' || error_number || ' ' || error_message);
      DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
      v_EmailText := 'CLAIMID: ' || v_ClaimID || ' #' || v_ClaimLineNumb || '. ' || SQLCODE || ' - ' || SQLERRM;
      EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
      v_EmailText := 'PROCESS COMPLETED: [' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '] WITH ERRORS. ' || SQLCODE || ' - ' || SQLERRM;
      EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
  END p_ClaimSplit;
  



Re: Frequent COMMIT is Culprit or anything else? [message #184504 is a reply to message #184489] Wed, 26 July 2006 17:29 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I would post an explain plan for your main cursor. Also what version of Oracle are you using? What columns are indexed on Tblclaimdetmonthly_s_part?
Re: Frequent COMMIT is Culprit or anything else? [message #184506 is a reply to message #184489] Wed, 26 July 2006 17:59 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Another thing to point out here is you have a function f_getfiscalinfo. It might be a good idea to profile the PL/SQL code, so you can understand where the issue is.

Start here:

http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_profil.htm#sthref5502

Re: Frequent COMMIT is Culprit or anything else? [message #184507 is a reply to message #184506] Wed, 26 July 2006 18:01 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks a lots for your reply.

We have oracle v 9.2

yes, tblClaimDetMonthly_S_Part have following indexes:
ClaimID, ClaimLineNumb, ClaimDetailHistSeqNumb,
ClaimDetailHistRecTypeCD,FiscalDate.

[Updated on: Wed, 26 July 2006 18:07]

Report message to a moderator

Re: Frequent COMMIT is Culprit or anything else? [message #184527 is a reply to message #184507] Wed, 26 July 2006 22:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Benchmark your procedure:
- Take out the INSERT statements and the COMMIT and run it for 100,000 rows. How long does it take?
- Extrapolate this to 17M rows. Now you have a baseline. Your best possible performance will be this time PLUS however long it takes to perform and COMMIT the INSERTs.

If your baseline is (say) 17 hours, then no amount of COMMIT tuning is going to help. However, if the baseline is 3 hours, then I think you can get the INSERT done more cleverly in around 1 hour more (depends).

So here's what you do:

- Have you done the baseline? Or are you skipping ahead. Go back and do it. It will save you a heap of time. If you don't do it, don't even bother replying to this post because I wont help you, and if the others are smart, they wont either.

- Create a nested table of w_ClaimSplit%ROWTYPE, and load the rows into that collection instead of INSERTing. Every 50,000 rows, perform a FORALL loop (NOT a FOR loop!!!) which loops through the 50,000 rows and inserts them, then commits at the end. FORALL is faster performing individual inserts inside a conventional loop; just trust me.

- That will have helped a (fair) bit. You can make it faster still by dropping indexes and disabling foreign keys and then rebuilding them at the end.

- Another possibility (but more complex) is to write the results out to a file (on the Oracle Server) and then loading it with SQL*Loader. Don't listen to the idiots who say its too slow - it's not - providing it all happens on the one machine.

- Best of all, if you can write a single SQL to SELECT the results that you want to go into the table, then INSERT /*+APPEND */ INTO tab SELECT .... will give the best performance yet (providing there are no indexes, triggers, or foreign keys).

Ross Leishman.
Re: Frequent COMMIT is Culprit or anything else? [message #184561 is a reply to message #184489] Thu, 27 July 2006 01:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I am agreeing with rleishman's suggestion .

If you are having Oracle 9i , why are you experimenting with Oracle 8 methodology .

Plus ,

Just check with you DBA and ensure thay your database parameters are capable of handling these bulk processes .

Use BULK COLLECT for collecting the records to collections and process (If needed) those using normal loop.

use FORALL instead of conventional for loop with SAVE EXCEPTION option to handle the exception.

Then Your code looks like ...

PROCEDURE p_ClaimSplit (v_LastDay IN  DATE, 
                               v_Rc OUT NUMBER)
  IS
   .............

   ..................

  CURSOR curCD is ........... ;

  TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
 
  rec  curCD_type ;
 
  TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;

  ClaimSplit ClaimSplit_type;

BEGIN

  OPEN curCD ;

  LOOP 

    FETCH curCD BULK COLLECT INTO rec LIMIT 5000; --> It depends

    FOR i in 1 .. curCD
    LOOP
     --> Processing and inserting into Collction
     --> If Not much processing is needed this Can be avoided 
     
     ClaimSplit.EXTEND;

       ClaimSplit(ClaimSplit.LAST)..... = .....;
       ClaimSplit(ClaimSplit.LAST)..... = .....;
       ClaimSplit(ClaimSplit.LAST)..... = .....;
       ClaimSplit(ClaimSplit.LAST)..... = .....;
       ClaimSplit(ClaimSplit.LAST)..... = .....;

    END LOOP;

      BEGIN
         FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
         INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
      EXCEPTION
         ............--> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
      END;

 COMMIT;

 EXIT WHEN curCD%NOTFOUND;

END LOOP;
CLOSE curCD;

END;



Thumbs Up
Rajuvan




[Updated on: Thu, 27 July 2006 01:22]

Report message to a moderator

Re: Frequent COMMIT is Culprit or anything else? [message #184674 is a reply to message #184561] Thu, 27 July 2006 07:36 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thanks Ross and Rajuvan for your reply.
Certainly your advice is immportant and valuable.

I would certainly like to re factor the code with Nested Table and bulk collect as shown by both of you.


- Have you done the baseline? Or are you skipping ahead. Go back and do it.


Ross, earlier the procedure used to finish in three to four hours as per lot of events and but suddenly in the month of Jun-2006 process it really ran slow down to 20 hours.

What else I have to check to know the baseline.

Please elaborate further on baseline too.

Again thank you for your guideline.







[Updated on: Thu, 27 July 2006 07:45]

Report message to a moderator

Re: Frequent COMMIT is Culprit or anything else? [message #184678 is a reply to message #184674] Thu, 27 July 2006 07:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Go back and read Ross' reply.
Read it carefully, particularly the first 3 lines.
The ones that say
Quote:

Benchmark your procedure:
- Take out the INSERT statements and the COMMIT and run it for 100,000 rows. How long does it take?
- Extrapolate this to 17M rows. Now you have a baseline. Your best possible performance will be this time PLUS however long it takes to perform and COMMIT the INSERTs.



This is (as line 3 says) your baseline.

Now got and calculate it, and that will let you know the absolute minimum time we can got your code to run in.
Re: Frequent COMMIT is Culprit or anything else? [message #184680 is a reply to message #184678] Thu, 27 July 2006 07:48 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member


Thanks JRowbottom for clearing that.

I will do it and bring the result on the board.

Thankig you again.
























Re: Frequent COMMIT is Culprit or anything else? [message #185828 is a reply to message #184680] Thu, 03 August 2006 15:36 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
back to my promise , sorry i could not bring the
result, i could not test baseline as per Ross advise,
but meanwhile i have to change the code to bring the result so i made demo script to check wether collection works
but to me I think something fundamentaly wrong with my code
as it took 2 hours for 5000 rows,
please help.
here is the code:
  1  DECLARE
  2  CURSOR curCD is     SELECT O.ClaimID, O.ClaimLIneNumb, O.RegionalNetworkCenter, O.PaymentFiscalMon
  3   DECODE(O.AccountingDepartmentRptCD,'1','1','2','2','3','3','4','4','5','3','6','3','7','3','9'
  4       O.ServiceCatID, O.FromDate, O.ToDate, O.FromDateFiscalMOnth, O.ProvAMount, O.PlanAmount,
  5       NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD, NVL(O.ClaimDetailHistSeqNumb,0) 
  6          (O.TODate - O.FromDate) + 1 TotalServiceDays, CarID, O.ProvPaymentType ProvType
  7      FROM tblClaimDetMonthly_s_part O
  8          WHERE ROWNUM < 5000;
  9  TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
 10    recCD  curCD_type;
 11    TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;
 12    ClaimSplit ClaimSplit_typE := ClaimSplit_typE();
 13    bulk_errors   EXCEPTION;
 14    PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
 15      x                  number := 0;
 16      y                  number := 1000;
 17      z                  number := 0;
 18  BEGIN
 19  select max(claimid)
 20      into z
 21       from  tblClaimDetMonthly_s_part;
 22  while x < z
 23  loop
 24      OPEN curCD;
 25      LOOP  ----loop 1st
 26            FETCH curCD BULK COLLECT INTO recCD LIMIT 5000; --> It depends
 27            EXIT WHEN recCD.COUNT = 0;
 28             FOR i in 1 ..recCD.Last
 29            LOOP  -----loop 2nd
 30       ClaimSplit.EXTEND;
 31       ClaimSplit(ClaimSplit.LAST).ClaimID                   := recCD(i).ClaimId;
 32       ClaimSplit(ClaimSplit.LAST).ClaimLineNumb             := recCD(i).ClaimLineNumb;
 33       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
 34       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
 35         ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter;
 36         ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
 37         ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
 38         ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := recCD(i).fromdatefiscalmonth;
 39         ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
 40         ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
 41         ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
 42         ClaimSplit(ClaimSplit.LAST).ProvTypeCD           := recCD(i).ProvType;
 43         ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount;
 44           BEGIN
 45             FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
 46             INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
 47           EXCEPTION    --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
 48              WHEN bulk_errors
 49           THEN
 50               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
 51               LOOP
 52                DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
 53                TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
 54                SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
 55              END LOOP;
 56     COMMIT;
 57             END;
 58        END LOOP;
 59     end loop;
 60             x := y + 1;
 61             y := y + 1000;
 62      EXIT WHEN curCD%NOTFOUND;
 63  COMMIT;
 64  END LOOP;
 65*  END;
 66  /

PL/SQL procedure successfully completed.

Elapsed: 02:06:07.76
SQL> 




Re: Frequent COMMIT is Culprit or anything else? [message #185831 is a reply to message #184489] Thu, 03 August 2006 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
use SQL_TRACE=TRUE & TKPROF to actually see where the time is being spent.
anything else is blindly shooting in the dark & hoping to get lucky & hitting the unknown target.
Re: Frequent COMMIT is Culprit or anything else? [message #185836 is a reply to message #185831] Thu, 03 August 2006 16:30 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
alright, here is DBMS_PROFILER RESULTS
with 1000 record insert it took ---> Elapsed: 00:04:37.16
please guide
Elapsed: 00:00:00.10
SQL> create or replace procedure test2
  2  as
  3    CURSOR curCD is
  4       SELECT O.ClaimID, O.ClaimLIneNumb, O.RegionalNetworkCenter, O.PaymentFiscalMonth,
  5       DECODE(O.AccountingDepartmentRptCD,'1','1','2','2','3','3','4','4','5','3','6','3','7','3','9','9','1') FeeForService,
  6       O.ServiceCatID, O.FromDate, O.ToDate, O.FromDateFiscalMOnth, O.ProvAMount, O.PlanAmount,
  7       NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD, NVL(O.ClaimDetailHistSeqNumb,0) ClaimDetailHistSeqNumb,
  8          (O.TODate - O.FromDate) + 1 TotalServiceDays, CarID, O.ProvPaymentType ProvType
  9      FROM tblClaimDetMonthly_s_part O
 10           WHERE ROWNUM < 1000;
 11    TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
 12     recCD  curCD_type;
 13      TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;
 14      ClaimSplit ClaimSplit_typE := ClaimSplit_typE();
 15      bulk_errors   EXCEPTION;
 16      PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
 17        x                  number := 0;
 18        y                  number := 1000;
 19        z                  number := 0;
 20    BEGIN
 21    select max(claimid)
 22        into z
 23         from  tblClaimDetMonthly_s_part;
 24    while x < z
 25    loop
 26        OPEN curCD;
 27        LOOP  ----loop 1st
 28              FETCH curCD BULK COLLECT INTO recCD LIMIT 5000; --> It depends
 29              EXIT WHEN recCD.COUNT = 0;
 30               FOR i in 1 ..recCD.Last
 31              LOOP  -----loop 2nd
 32         ClaimSplit.EXTEND;
 33         ClaimSplit(ClaimSplit.LAST).ClaimID                   := recCD(i).ClaimId;
 34         ClaimSplit(ClaimSplit.LAST).ClaimLineNumb             := recCD(i).ClaimLineNumb;
 35         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
 36         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
 37           ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter;
 38           ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
 39           ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
 40          ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := recCD(i).fromdatefiscalmonth;
 41           ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
 42           ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
 43           ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
 44           ClaimSplit(ClaimSplit.LAST).ProvTypeCD           := recCD(i).ProvType;
 45          ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount;
 46             BEGIN
 47               FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
 48               INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
 49             EXCEPTION    --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
 50                WHEN bulk_errors
 51             THEN
 52                 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
 53                 LOOP
 54                  DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
 55                  TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
 56                  SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
 57                END LOOP;
 58       COMMIT;
 59         END;
 60        END LOOP;
 61     end loop;
 62        x := y + 1;
 63        y := y + 1000;
 64      EXIT WHEN curCD%NOTFOUND;
 65  COMMIT;
 66  END LOOP;
 67   END;
 68  /

Procedure created.

Elapsed: 00:00:00.15
SQL> @c:\dbms_profiler\call_profiler.sql
Profiler started

Elapsed: 00:00:00.09

PL/SQL procedure successfully completed.

Elapsed: 00:04:37.16
Profiler stopped

Elapsed: 00:00:00.17
Profiler flushed

Elapsed: 00:00:00.10
runid:3

Elapsed: 00:00:00.09
SQL> @c:\dbms_profiler\evaluate_profiler_results.sql
Enter value for runid: 3
Enter value for name: test2
Enter value for owner: akdadhan

      Line      Occur        Sec Text
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1                       procedure test2
         2                       as
         3                         CURSOR curCD is
         4          1 .001335583      SELECT O.ClaimID, O.ClaimLIneNumb, O.RegionalNetworkCenter, O.PaymentFiscalMonth,
         5                            DECODE(O.AccountingDepartmentRptCD,'1','1','2','2','3','3','4','4','5','3','6','3','7','3','9','9','1') FeeForService,
         6                            O.ServiceCatID, O.FromDate, O.ToDate, O.FromDateFiscalMOnth, O.ProvAMount, O.PlanAmount,
         7                            NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD, NVL(O.ClaimDetailHistSeqNumb,0) ClaimDetailHistSeqNumb,
         8                               (O.TODate - O.FromDate) + 1 TotalServiceDays, CarID, O.ProvPaymentType ProvType
         9                           FROM tblClaimDetMonthly_s_part O
        10                                WHERE ROWNUM < 1000;
        11                         TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
        12                          recCD  curCD_type;
        13                           TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;
        14          1 .000003617     ClaimSplit ClaimSplit_typE := ClaimSplit_typE();
        15                           bulk_errors   EXCEPTION;
        16                           PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
        17          1 .000002294       x                  number := 0;
        18          1 .000000951       y                  number := 1000;
        19          1 .000000323       z                  number := 0;
        20                         BEGIN
        21          1 .000205422   select max(claimid)
        22                             into z
        23                              from  tblClaimDetMonthly_s_part;
        24          1 .000001289   while x < z
        25                         loop
        26          1 .000000555       OPEN curCD;
        27                             LOOP  ----loop 1st
        28          2 .067001859             FETCH curCD BULK COLLECT INTO recCD LIMIT 5000; --> It depends
        29          2 .000046655             EXIT WHEN recCD.COUNT = 0;
        30       1000 .001220559              FOR i in 1 ..recCD.Last
        31                                   LOOP  -----loop 2nd
        32        999 .016349294        ClaimSplit.EXTEND;
        33        999 .008379379        ClaimSplit(ClaimSplit.LAST).ClaimID                   := recCD(i).ClaimId;
        34        999 .002511957        ClaimSplit(ClaimSplit.LAST).ClaimLineNumb             := recCD(i).ClaimLineNumb;
        35        999 .002840272        ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
        36        999 .003840409        ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
        37        999 .002505958          ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter;
        38        999 .002541618          ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
        39        999  .00261955          ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
        40        999 .002468846         ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := recCD(i).fromdatefiscalmonth;
        41        999 .002914451          ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
        42        999 .003806701          ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
        43        999 .002475571          ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
        44        999 .005495675          ClaimSplit(ClaimSplit.LAST).ProvTypeCD           := recCD(i).ProvType;
        45        999  .00239806         ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount;
        46                                  BEGIN
        47        999 272.854495              FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
        48                                    INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
        49                                  EXCEPTION    --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
        50                                     WHEN bulk_errors
        51                                  THEN
        52     499499 .352105498                FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        53                                      LOOP
        54     997002 2.16208714                 DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
        55                                       TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
        56                                       SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
        57                                     END LOOP;
        58        998  .16841848      COMMIT;
        59                              END;
        60                             END LOOP;
        61                          end loop;
        62          1 .000004341       x := y + 1;
        63          1 .000001043       y := y + 1000;
        64          1 .000001681     EXIT WHEN curCD%NOTFOUND;
        65          2 .000186141 COMMIT;
        66                       END LOOP;
        67                        END;

67 rows selected.

Elapsed: 00:00:00.43

Code% coverage
--------------
           100

Elapsed: 00:00:00.09
SQL>


Re: Frequent COMMIT is Culprit or anything else? [message #185841 is a reply to message #185836] Thu, 03 August 2006 16:57 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
If you look there at lines 47 & 48, it looks like it spent 98% of the time doing a bulk insert. I would take a look at the w_ClaimSplit table. Perhaps the disks where your online redo is stored is the bottleneck here, I see many cases where online redo is duplexed on the same physical disk (you might as well not bother at all). How many rows did you say it was inserting? Also, is this a new table? Perhaps Oracle is spending alot of time AUTOEXTENDING? Are you running DICTIONARY or LOCAL managed? I thought I read earlier on it was a temp table created on the fly, if that is the case you may want to create it with a large initial extent.

So basically you know it is spending 98% of the time doing a bulk insert, but now you don't know why (so you narrowed it down -- good!). At this time you may want to break open sql_trace=true, timed_statistics=true, and generate a trace file and run it through tkprof as others have suggested. I wouldn't have done it until I had something to focus on. Also, I would try to make a simple case to run through tkprof (perhaps you can simplify the test case to be just the bulk insert?).

Good luck.
Re: Frequent COMMIT is Culprit or anything else? [message #185842 is a reply to message #185836] Thu, 03 August 2006 17:00 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Something else I noticed is that you are getting alot (499 maybe?) of exceptions for each bulk insert. Why is there so many exceptions?

I would think constraint violations would definately slow you down.
Re: Frequent COMMIT is Culprit or anything else? [message #185847 is a reply to message #185842] Thu, 03 August 2006 17:27 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
I think you are right when
I remove EXCEPTION IT GIVES ERROR :
So how to deal with this error?
 create or replace procedure test2
  as
    CURSOR curCD is
       SELECT O.ClaimID, O.ClaimLIneNumb, O.RegionalNetworkCenter, O.PaymentFiscalMonth,
       DECODE(O.AccountingDepartmentRptCD,'1','1','2','2','3','3','4','4','5','3','6','3','7','3','9','9','1') FeeForService,
       O.ServiceCatID, O.FromDate, O.ToDate, O.FromDateFiscalMOnth, O.ProvAMount, O.PlanAmount,
       NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD, NVL(O.ClaimDetailHistSeqNumb,0) ClaimDetailHistSeqNumb,
          (O.TODate - O.FromDate) + 1 TotalServiceDays, CarID, O.ProvPaymentType ProvType
      FROM tblClaimDetMonthly_s_part O
           WHERE ROWNUM < 100;
    TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
    recCD  curCD_type;
      TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;
      ClaimSplit ClaimSplit_typE := ClaimSplit_typE();
      bulk_errors   EXCEPTION;
      PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
       x                  number := 0;
       y                  number := 1000;
       z                  number := 0;
    BEGIN
    select max(claimid)
        into z
         from  tblClaimDetMonthly_s_part;
    while x < z
    loop
        OPEN curCD;
        LOOP  ----loop 1st
              FETCH curCD BULK COLLECT INTO recCD LIMIT 5000; --> It depends
              EXIT WHEN recCD.COUNT = 0;
               FOR i in 1 ..recCD.Last
              LOOP  -----loop 2nd
         ClaimSplit.EXTEND;
         ClaimSplit(ClaimSplit.LAST).ClaimID                   := recCD(i).ClaimId;
         ClaimSplit(ClaimSplit.LAST).ClaimLineNumb             := recCD(i).ClaimLineNumb;
         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
           ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter;
           ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
           ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
          ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := recCD(i).fromdatefiscalmonth;
           ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
           ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
          ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
           ClaimSplit(ClaimSplit.LAST).ProvTypeCD           := recCD(i).ProvType;
          ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount;
     --   BEGIN
               FORALL i IN 1 ..  ClaimSplit.COUNT  ----SAVE EXCEPTIONS
               INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
     ---       EXCEPTION    --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
     ---         WHEN bulk_errors
     ---  THEN
     ---     FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     ---  LOOP
     ---  DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
     ---  TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
     ---  SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
     ---  END LOOP;
     ---  COMMIT;
     ---  END;
       END LOOP;
     end loop;
       x := y + 1;
        y := y + 1000;
     EXIT WHEN curCD%NOTFOUND;
  COMMIT;
  END LOOP;
 END;


Procedure created.


SQL> @c:\dbms_profiler\call_profiler.sql
Profiler started

Elapsed: 00:00:00.15
begin
*
ERROR at line 1:
ORA-00001: unique constraint (AKDADHAN.PK_W_CLAIMSPLIT) violated
ORA-06512: at "AKDADHAN.TEST2", line 47
ORA-06512: at line 2


Elapsed: 00:00:00.15
Profiler stopped

Elapsed: 00:00:00.20
Profiler flushed

Elapsed: 00:00:00.17
runid:5

Elapsed: 00:00:00.14
SQL>

Re: Frequent COMMIT is Culprit or anything else? [message #185857 is a reply to message #185847] Thu, 03 August 2006 18:26 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Well you really have to ask yourself what is the intention. If the intention is to update or insert, then I would recommend using MERGE.

But I think your problem is the code is wrong, I will outline it below:

1. You open a cursor, curCD.
2. You fetch up to 5K rows into recCD collection.
3. You loop through recCD collection, transferring records from the recCD collection into the ClaimSplit collection.
4. Inside the recCD loop where you are populating ClaimSplit you are doing the BULK INSERT (FORALL), so for each record you add to ClaimSplit you are not only inserting the latest record -- but also the previous records. Move the end loop on line 60 to just after line 45.
5. Before line 61 (where the END LOOP was that moved to line 45), you should flush the ClaimSplit collection (ClaimSplit.DELETE) -- otherwise you will end up growing the ClaimSplit collection with the next set of records and try inserting the first set again.
6. Line 64 should be replaced with a CLOSE curCD, otherwise you will leak a cursor. Not sure you need the EXIT here. If you leave this in then it should be move after line 60 (where the ClaimSplit.DELETE would be).
7. You may also want to flush the ClaimSplit collection after line 65, it shouldn't be necessary but it is probably a good idea.

CREATE OR REPLACE PROCEDURE test2
AS
   CURSOR curcd
   IS
      SELECT o.claimid
            ,o.claimlinenumb
            ,o.regionalnetworkcenter
            ,o.paymentfiscalmonth
            ,DECODE (o.accountingdepartmentrptcd
                    ,'1', '1'
                    ,'2', '2'
                    ,'3', '3'
                    ,'4', '4'
                    ,'5', '3'
                    ,'6', '3'
                    ,'7', '3'
                    ,'9', '9'
                    ,'1'
                    ) feeforservice
            ,o.servicecatid
            ,o.fromdate
            ,o.todate
            ,o.fromdatefiscalmonth
            ,o.provamount
            ,o.planamount
            ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
            ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
            , (o.todate - o.fromdate) + 1 totalservicedays
            ,carid
            ,o.provpaymenttype provtype
        FROM tblclaimdetmonthly_s_part o
       WHERE ROWNUM < 100;

   TYPE curcd_type IS TABLE OF curcd%ROWTYPE
      INDEX BY BINARY_INTEGER;

   reccd         curcd_type;

   TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;

   claimsplit    claimsplit_type := claimsplit_type ();
   bulk_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   x             NUMBER          := 0;
   y             NUMBER          := 1000;
   z             NUMBER          := 0;
BEGIN
   SELECT MAX (claimid)
     INTO z
     FROM tblclaimdetmonthly_s_part;

   WHILE x < z
   LOOP
      OPEN curcd;

      LOOP                                                       ----loop 1st
         FETCH curcd
         BULK COLLECT INTO reccd LIMIT 5000;                    --> It depends

         EXIT WHEN reccd.COUNT = 0;

         FOR i IN 1 .. reccd.LAST
         LOOP                                                    -----loop 2nd
            claimsplit.EXTEND;
            claimsplit (claimsplit.LAST).claimid := reccd (i).claimid;
            claimsplit (claimsplit.LAST).claimlinenumb :=
                                                      reccd (i).claimlinenumb;
            claimsplit (claimsplit.LAST).claimdetailhistseqnumb :=
                                             reccd (i).claimdetailhistseqnumb;
            claimsplit (claimsplit.LAST).claimdetailhistrectypecd :=
                                           reccd (i).claimdetailhistrectypecd;
            claimsplit (claimsplit.LAST).operationcentercode :=
                                              reccd (i).regionalnetworkcenter;
            claimsplit (claimsplit.LAST).feeforservice :=
                                                      reccd (i).feeforservice;
            claimsplit (claimsplit.LAST).servicecatid :=
                                                       reccd (i).servicecatid;
            claimsplit (claimsplit.LAST).svcfiscalmonth :=
                                                reccd (i).fromdatefiscalmonth;
            claimsplit (claimsplit.LAST).paymentfiscalmonth :=
                                                 reccd (i).paymentfiscalmonth;
            claimsplit (claimsplit.LAST).provamount := reccd (i).provamount;
            claimsplit (claimsplit.LAST).carid := reccd (i).carid;
            claimsplit (claimsplit.LAST).provtypecd := reccd (i).provtype;
            claimsplit (claimsplit.LAST).planamount := reccd (i).planamount;
         END LOOP;

         BEGIN
            FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
               INSERT INTO w_claimsplit
                    VALUES claimsplit (i);
         EXCEPTION
            --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
            WHEN bulk_errors
            THEN
               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                            (   'Error from element #'
                             || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
                             || ': '
                             || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
                            );
               END LOOP;

               COMMIT;
         END;

         claimsplit.DELETE;
         EXIT WHEN curcd%NOTFOUND;
      END LOOP;

      x := y + 1;
      y := y + 1000;
      COMMIT;
      claimsplit.DELETE;
   END LOOP;
END;

Re: Frequent COMMIT is Culprit or anything else? [message #185860 is a reply to message #185857] Thu, 03 August 2006 20:40 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Oh Man! You Rescued me!

I made few change on your modified code
and it just churned out 1824249 Million rows
in only --> TIME : 00:03:30.75.

This is actualy demo only i have to
worked out the real code on this basis.

If anybody has any suggestion on what
to keep in mind while converting
demo to real code, please do so.


Here is the demo code with time results.


CREATE OR REPLACE PROCEDURE test2
AS
   CURSOR curcd
   IS
      SELECT o.claimid
            ,o.claimlinenumb
            ,o.regionalnetworkcenter
            ,o.paymentfiscalmonth
            ,DECODE (o.accountingdepartmentrptcd
                    ,'1', '1'
                    ,'2', '2'
                    ,'3', '3'
                    ,'4', '4'
                    ,'5', '3'
                    ,'6', '3'
                    ,'7', '3'
                    ,'9', '9'
                    ,'1'
                    ) feeforservice
            ,o.servicecatid
            ,o.fromdate
            ,o.todate
            ,o.fromdatefiscalmonth
            ,o.provamount
            ,o.planamount
            ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
            ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
            , (o.todate - o.fromdate) + 1 totalservicedays
            ,carid
            ,o.provpaymenttype provtype
        FROM tblclaimdetmonthly_s_part o;

   TYPE curcd_type IS TABLE OF curcd%ROWTYPE
      INDEX BY BINARY_INTEGER;

   reccd         curcd_type;

   TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;

   claimsplit    claimsplit_type := claimsplit_type ();
   bulk_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   x             NUMBER          := 0;
   y             NUMBER          := 100000;
   z             NUMBER          := 0;
BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';

   SELECT MAX (claimid)
     INTO z
     FROM tblclaimdetmonthly_s_part;

   WHILE x < z
   LOOP
      OPEN curcd;

      LOOP                                                       ----loop 1st
         FETCH curcd
         BULK COLLECT INTO reccd LIMIT 100000;                    --> It depends

         EXIT WHEN reccd.COUNT = 0;

         FOR i IN 1 .. reccd.LAST
         LOOP                                                    -----loop 2nd
            claimsplit.EXTEND;
            claimsplit (claimsplit.LAST).claimid := reccd (i).claimid;
            claimsplit (claimsplit.LAST).claimlinenumb :=
                                                      reccd (i).claimlinenumb;
            claimsplit (claimsplit.LAST).claimdetailhistseqnumb :=
                                             reccd (i).claimdetailhistseqnumb;
            claimsplit (claimsplit.LAST).claimdetailhistrectypecd :=
                                           reccd (i).claimdetailhistrectypecd;
            claimsplit (claimsplit.LAST).operationcentercode :=
                                              reccd (i).regionalnetworkcenter;
            claimsplit (claimsplit.LAST).feeforservice :=
                                                      reccd (i).feeforservice;
            claimsplit (claimsplit.LAST).servicecatid :=
                                                       reccd (i).servicecatid;
            claimsplit (claimsplit.LAST).svcfiscalmonth :=
                                                reccd (i).fromdatefiscalmonth;
            claimsplit (claimsplit.LAST).paymentfiscalmonth :=
                                                 reccd (i).paymentfiscalmonth;
            claimsplit (claimsplit.LAST).provamount := reccd (i).provamount;
            claimsplit (claimsplit.LAST).carid := reccd (i).carid;
            claimsplit (claimsplit.LAST).provtypecd := reccd (i).provtype;
            claimsplit (claimsplit.LAST).planamount := reccd (i).planamount;
         END LOOP;

         BEGIN
            FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
               INSERT INTO w_claimsplit
                    VALUES claimsplit (i);
         EXCEPTION
            --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
            WHEN bulk_errors
            THEN
               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                            (   'Error from element #'
                             || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
                             || ': '
                             || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
                            );
               END LOOP;

               COMMIT;
         END;

         claimsplit.DELETE;
         
      END LOOP;

      x := y + 1;
      y := y + 100000;
      COMMIT;
      claimsplit.DELETE;
EXIT WHEN curcd%NOTFOUND;
      CLOSE curCD;
   END LOOP;
   
END;
/


SQL> @c:\dbms_profiler\demo\testorafaq.txt
124  /

Procedure created.

Elapsed: 00:00:00.26


SQL> @c:\dbms_profiler\call_profiler.sql
Profiler started

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:03:30.74
Profiler stopped

Elapsed: 00:00:00.23
Profiler flushed

Elapsed: 00:00:00.14
runid:15


SQL> select count(*) from w_ClaimSplit;

  COUNT(*)
----------
   1824249

Elapsed: 00:00:00.72
SQL> 





Re: Frequent COMMIT is Culprit or anything else? [message #185870 is a reply to message #185860] Thu, 03 August 2006 23:29 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
now here is my real code if anybody help me
what i need i have mention in the script
itself.

I have two set of different argument on each insert
first argument process follow by first end loop and
then insert (this work fine) and i have another
set of argument to process on the same collection
to insert in the same table.

so how do i do to process the 2nd argument set
like the first argument set with the same collection?

Please guide.
code like....
CURSOR curCD 
  IS
        SELECT O.ClaimID
             , O.ClaimLIneNumb
             , O.RegionalNetworkCenter
             , O.PaymentFiscalMonth
             , DECODE(O.AccountingDepartmentRptCD
                     ,'1','1'
                     ,'2','2'
                     ,'3','3'
                     ,'4','4'
                     ,'5','3'
                     ,'6','3'
                     ,'7','3'
                     ,'9','9'
                     ,'1') FeeForService
             ,O.ServiceCatID
             , O.FromDate
             , O.ToDate
             , O.FromDateFiscalMOnth
             , O.ProvAMount
             , O.PlanAmount
             ,NVL(O.ClaimDetailHistRecTypeCD,'A') ClaimDetailHistRecTypeCD
             ,NVL(O.ClaimDetailHistSeqNumb,0) ClaimDetailHistSeqNumb
             ,   (O.TODate - O.FromDate) + 1 TotalServiceDays
             , CarID
             , O.ProvPaymentType ProvType
        FROM tblClaimDetMonthly_s_part O
        WHERE FiscalDate = v_fiscaldate
        AND   (O.ProvAmount <> 0 OR O.PlanAmount <> 0)  --LJK 08/19/2005.  Added OR O.PlanAmount <> 0.  Some claims might have one > 0 and the other value = 0.
        AND    O.claimid BETWEEN x AND y;
	
  TYPE curCD_type IS TABLE OF curCD%ROWTYPE INDEX BY BINARY_INTEGER;
  
  TYPE ClaimSplit_type is TABLE OF w_ClaimSplit%ROWTYPE ;
  
  recCD         curCD_type;
    
  ClaimSplit    ClaimSplit_typE;
  
  bulk_errors   EXCEPTION;
  
  PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
  
BEGIN
   /*
     DBMS_APPLICATION_INFO.SET_MODULE('MONTH-END','CLAIM SPLIT');
     v_EmailTo := f_BuildEmail('MONTHENDCLAIMS');
     v_EmailText := 'PROCESS STARTED: ['  || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '].';
     EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
   */
 
 EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';     --Clear Out work table.
   
   /*
     LK... Since I keep on messing up the pending last day,
     I've changed it to where you do not have to pass it in, but will instead be calculated for you.
    v_CurrentInfo     := f_GetFiscalInfo(v_LastDay);
    v_PendingLastDay  := v_CurrentInfo.LastDay;
    v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
    DBMS_OUTPUT.PUT_LINE('THIS FISCALMONTH:
    [' ||TO_DATE(SUBSTR(v_ThisFiscalMonth,5) ||'/01/' || SUBSTR(v_ThisFiscalMonth,1,4),'mm/dd/yyyy') || ']');
    v_FiscalDate := TO_DATE(SUBSTR(v_ThisFiscalMonth,5) || '/01/' || SUBSTR(v_ThisFiscalMonth,1,4),'mm/dd/yyyy');
  */ 
    
  
SELECT  MAX (claimid)
      INTO  z
      FROM  tblclaimdetmonthly_s_part;

WHILE x < z
LOOP                                                 -------LOOP ONE
   OPEN curCD;
   LOOP                                              -------LOOP TWO
      FETCH curCD 
      BULK COLLECT INTO recCD LIMIT 100000; 
      
      EXIT WHEN recCD.COUNT = 0;
 
       FOR i in 1..recCD.Last
       LOOP                                         -------LOOP THREE
       v_SeqNumb := 0;
                                        --- Processing and inserting into Collection
                                        ---v_ClaimId        := recCD(i).ClaimID;
                                        ---v_ClaimLineNumb  := recCD(i).ClaimLineNumb;
       v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth;
       v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate);
       v_ToFiscalInfo   := f_GetFiscalInfo(recCD(i).ToDate);
       
       ClaimSplit.EXTEND;
       ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
       ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
     
   ---lk 10/04/2004 If they enter a dos beyond the tblFiscalMonth, then this row was not being populated.
           
       CASE 
          WHEN v_ToFiscalInfo.LastDay IS NULL THEN v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
       END CASE;
           
       CASE 
          WHEN v_ToFiscalInfo.LastDay > v_PendingLastDay THEN v_ToFiscalInfo.FiscalMonth := 'PEND';
       END CASE;
       
                        --lk 10/04/2004.
  |------->IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR 
  |           v_FromFiscalInfo.FiscalMonth = 'PEND'       OR 
  |           recCD(i).TotalServiceDays <= 0              
  |
  |------>THEN  
  | 
  |            v_ProvAmount            := recCD(i).ProvAmount;
  |            v_PlanAmount            := recCD(i)CD.PlanAmount;    --LJK 06/27/2005  Added for new field.
  |             v_SvcFiscalMonth        := 'PEND';                   --lk 10/04/2004
  |             v_Ct                    := v_Ct + 1;
  |             v_SeqNumb               := v_SeqNumb + 1;
  |     recCD(i).ClaimDetailHistSeqNumb := v_SeqNumb;
  |     
  |     ClaimSplit.EXTEND;
  |     ClaimSplit(ClaimSplit.LAST).OperationCenterCode   := recCD(i).RegionalNetworkCenter;
  |     ClaimSplit(ClaimSplit.LAST).FeeForService         := recCD(i).FeeForService;
  |     ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
  |     ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := v_SvcFiscalMonth;
  |     ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
  |     ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
  |     ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
  |     ClaimSplit(ClaimSplit.LAST).ProvTypeCD            := recCD(i).ProvType;
  |     ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount;
  |
  |---->END IF; --->CAN I END THIS IF HERE AND START NEW IF WITH SAME ARGUMENT IN THE NEXT SLOT?      
  |
  |     END LOOP;  
  |     
  |------->/* if I insert after loop then it gives me a rocket speed for 18 million rows,
              fine,but i have another set of processing argument on the same collection
              under the above mentioned 'if' statement and after the following first insert which is here <--|
  ----------------------------------------------------------------------------------------------------------|      
  |         BEGIN
  |           FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
  |           INSERT  /*+ append */  INTO   w_ClaimSplit 
  |                                  VALUES ClaimSplit(i);
  |          EXCEPTION    
  |          -- Use SQL%bulk_exceptions.COUNT to get the count of exceptions
  |            WHEN bulk_errors
  |            THEN
  |               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
  |               LOOP
  |                  DBMS_OUTPUT.PUT_LINE 
  |                              (  'Error from element #' 
  |                               || TO_CHAR (SQL%BULK_EXCEPTIONS(j).error_index) 
  |                               || ': ' 
  |                               || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code)
  |                              );
  |               END LOOP;
  |	       
  |	       COMMIT;
  |         END;
  |         
  |         
  |    COMMIT;
  |    
  |    
  |---> so how do i do this insert on the same collection after first loop end and insert it done on the collection?
  |     basically the following few argument change values to the insert column.
  |     I have tried to do it but how to put loop is confusing me, so please help me.
  |
  |---> Another set of Argument begin here---i am confused how to include this argument on the same collection to insert 
              as i tried to do this but the above loop is closed -----basicaly two record have been changed in the following
              argument i marked it with star ******** - Can I use update statement instead insert ?

         v_FromFiscalInfo.FiscalMonth := 'PEND';
  ELSE
          v_AmtUsed     := 0;
	  v_PlanAmtUsed := 0;    --LJK 06/27/2005  Added to initialize new field.
          v_Date        := recCD(i).FromDate;
  WHILE v_FromFiscalInfo.LastDay <= NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1))
  LOOP  ---loop 3rd.
         v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
           IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN  --LK 10/04/2004
              v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month
              v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;  --LJK 08/23/2005.  Set the PlanAmount not the Used Amount.
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
            ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
              v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed;
              v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_FromFiscalInfo.LastDay     := v_ToFiscalInfo.LastDay + 1;
            ELSE
              v_FMPart         := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays;
              v_ProvAmount     := Round(recCD(i).ProvAmount * v_FMPart,2);
              v_PlanAmount     := Round(recCD(i).PlanAmount * v_FMPart,2);
              v_AmtUsed        := v_AmtUsed + v_ProvAmount;
              v_PlanAmtUsed    := v_PlanAmtUsed + v_PlanAmount;
              v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
              v_Date           := v_FromFiscalInfo.FirstDay;
            END IF;
            v_SeqNumb := v_SeqNumb + 1;
            recCD(i).ClaimDetailHistSeqNumb := v_SeqNumb;
       ClaimSplit.EXTEND;
       ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
       ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;         
       ClaimSplit(ClaimSplit.LAST).OperationCenterCode      := recCD(i).RegionalNetworkCenter;
       ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
       ClaimSplit(ClaimSplit.LAST).ServiceCatId             := recCD(i).ServiceCatId;
       ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth           := v_SvcFiscalMonth;
       ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth       := recCD(i).PaymentFiscalMonth;
       ClaimSplit(ClaimSplit.LAST).ProvAmount               := v_ProvAmount;*****************************************
       ClaimSplit(ClaimSplit.LAST).CarId                    := recCD(i).CarId;
       ClaimSplit(ClaimSplit.LAST).ProvTypeCD               := recCD(i).ProvType;
       ClaimSplit(ClaimSplit.LAST).PlanAmount               := v_PlanAmount;*****************************************
     BEGIN
           FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
           INSERT INTO w_ClaimSplit VALUES ClaimSplit(i);
        EXCEPTION    --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
            WHEN bulk_errors
         THEN
             FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
             LOOP
              DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
              TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
              SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
             END LOOP;
			 COMMIT;
          END;
        IF v_SvcFiscalMonth = 'PEND' THEN
              EXIT;
            END IF;
    END LOOP; ---- loop 4rd
       END IF;
    END LOOP; ----loop 3nd
          
COMMIT;
  END LOOP; ----loop 2st.
   x := y + 1;
   y := y + 100000;
 
 COMMIT;
 END LOOP; -----loop 1st
 CLOSE curCD;
---    v_EmailText := 'PROCESS COMPLETED: [' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '].';
 ---   EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
EXCEPTION
    WHEN OTHERS THEN
      error_number  := SQLCODE;
      error_message := substr(SQLERRM, 1, 200);
      dbms_output.put_line('error: ' || error_number || ' ' || error_message);
      DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
    --  v_EmailText := 'CLAIMID: ' || v_ClaimID || ' #' || v_ClaimLineNumb || '. ' || SQLCODE || ' - ' || SQLERRM;
    --  EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
    --  v_EmailText := 'PROCESS COMPLETED: [' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || '] WITH ERRORS. ' || SQLCODE || ' - ' || SQLERRM;
    ---  EMAIL.SP_SENDMAIL(MSG_TO => v_EmailTo, MSG_FROM => v_EmailFrom,MSG_SUBJECT => v_EmailSubject,MSG => v_EmailText);
  END p_ClaimSplit;




Re: Frequent COMMIT is Culprit or anything else? [message #185893 is a reply to message #185870] Fri, 04 August 2006 02:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Basically What you have to do is (If I am correct)to do like ..
If you want to have 2 processes for a single set of insert, and you have to have only One Bulk insert.. Its format should be like ...


   OPEN curCD;
   LOOP                                              -------LOOP TWO
      FETCH curCD 
      BULK COLLECT INTO recCD LIMIT 100000; 
      
      EXIT WHEN recCD.COUNT = 0;
 
       FOR i in 1..recCD.Last
       LOOP                                         -------LOOP THREE
  
         --- some fields may be the sme Irrespective of these 
         --- Two processes like ClaimSplit(ClaimSplit.LAST).ClaimID
         --- They should come here ..

       ClaimSplit.EXTEND;
       ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
       ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;

      --- Start the processing No. 1 ..
       IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR 
        |           v_FromFiscalInfo.FiscalMonth = 'PEND'       OR 
        |           recCD(i).TotalServiceDays <= 0  THEN 

                  ---> Prscesss No:1  -- Without ClaimSplit.EXTEND;
        |     ClaimSplit(ClaimSplit.LAST).OperationCenterCode   := recCD(i).RegionalNetworkCenter;
        |     ClaimSplit(ClaimSplit.LAST).FeeForService         := recCD(i).FeeForService;
        |     ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
        |     ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := v_SvcFiscalMonth;
        |     ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
        |     ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
        |     ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
        |     ClaimSplit(ClaimSplit.LAST).ProvTypeCD            := recCD(i).ProvType;
        |     ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount

      ELSE ---- Start the processing No. 2 ..

               ---> Prscesss No:1  -- Without ClaimSplit.EXTEND;
        |     ClaimSplit(ClaimSplit.LAST).OperationCenterCode   := recCD(i).RegionalNetworkCenter;
        |     ClaimSplit(ClaimSplit.LAST).FeeForService         := recCD(i).FeeForService;
        |     ClaimSplit(ClaimSplit.LAST).ServiceCatId          := recCD(i).ServiceCatId;
        |     ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth        := v_SvcFiscalMonth;
        |     ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth    := recCD(i).PaymentFiscalMonth;
        |     ClaimSplit(ClaimSplit.LAST).ProvAmount            := recCD(i).ProvAmount;
        |     ClaimSplit(ClaimSplit.LAST).CarId                 := recCD(i).CarId;
        |     ClaimSplit(ClaimSplit.LAST).ProvTypeCD            := recCD(i).ProvType;
        |     ClaimSplit(ClaimSplit.LAST).PlanAmount            := recCD(i).PlanAmount

      END LOOP ;                                        -------LOOP THREE

           BEGIN
                   |           FORALL i IN 1 ..  ClaimSplit.COUNT SAVE EXCEPTIONS
                   |           INSERT  /*+ append */  INTO   w_ClaimSplit 
                   |                                  VALUES ClaimSplit(i);
                   |          EXCEPTION    
                   |          -- Use SQL%bulk_exceptions.COUNT to get the count of exceptions
                   |            WHEN bulk_errors
                   |            THEN
                   |               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
                   |               LOOP
                   |                  DBMS_OUTPUT.PUT_LINE 
                   |                              (  'Error from element #' 
                   |                               || TO_CHAR (SQL%BULK_EXCEPTIONS(j).error_index) 
                   |                               || ': ' 
                   |                               || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code)
                   |                              );
                   |               END LOOP;
      COMMIT ;
                 END LOOP; ----loop 2st.



Thumbs Up
Rajuvan



Re: Frequent COMMIT is Culprit or anything else? [message #186104 is a reply to message #185893] Sat, 05 August 2006 20:08 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi Rajuvan,

Thanks a lot for you email and the above reply,
did accordingly.

procedure run successfully but data is not inserted
what could be the problem?

I am using TOAD, DBMS_OUTPUT find only
fetched record : 1

Here is code :

PROCEDURE p_ClaimSplit ----(v_LastDay IN DATE)  -----,v_Rc OUT NUMBER)
  IS
  
   /* VARIABLES */
    
       v_ThisFiscalMonth  tblClaimDetMonthly_S_part.FiscalMonth%TYPE; --'200402';
       v_PendingLastDay   tblClaimDetMonthly_S_part.FromDate%TYPE;  --Current fiscal month.
       v_SvcFiscalMonth   tblClaimDetMonthly_S_part.FiscalMonth%TYPE;
       v_ProvAmount       tblClaimDetMonthly_S_part.ProvAmount%TYPE;
       v_PlanAmount       tblClaimDetMonthly_S_part.PlanAmount%TYPE;  --LJK 06/27/2005  New field Added.
       v_Date             tblClaimDetMonthly_S_part.Fromdate%TYPE := NULL;
       v_FiscalDate       tblClaimDetMonthly_S_part.Fromdate%TYPE := NULL;
      v_TOFiscalMonth    tblClaimDetMonthly_S_part.FiscalMonth%TYPE := NULL;
      v_AmtUsed          tblClaimDetMonthly_S_part.ProvAmount%TYPE := 0;
      v_PlanAmtUsed      tblClaimDetMonthly_S_part.ProvAmount%TYPE := 0;  --LJK 06/27/2005  New field Added.
      v_ClaimID          tblClaimDetail.ClaimID%TYPE := NULL;
      v_ClaimLineNumb    tblClaimDetail.ClaimLineNumb%TYPE := NULL;
      v_Ct               PLS_INTEGER :=0;
      v_FMPart           PLS_INTEGER;
      v_CTX              PLS_INTEGER := 0;
      error_number       number;
      error_message      varchar2(200);
      v_SeqNumb          PLS_INTEGER := 0;
      ---v_EmailTo          EMAIL.AddressList_Tab;
      ---v_EmailFrom        VARCHAR2(50) := USER || '@GENTIVA.COM';
      ---v_EmailSubject     VARCHAR2(50) := 'MONTH-END CLAIM SPLIT';
      ---v_EmailText        VARCHAR2(2000) := NULL;
      x                  PLS_INTEGER := 0;
      y                  PLS_INTEGER := 100000;
      z                  PLS_INTEGER := 0;
      v_FromFiscalInfo   tFiscalMonthRec;
      v_ToFiscalInfo     tFiscalMonthRec;
      v_CurrentInfo      tFiscalMonthRec;
      --LJK 06/27/2005  Added PlanAmount to be pulled back from the partitioned table.
   
  CURSOR curcd
   IS
      SELECT o.claimid
            ,o.claimlinenumb
            ,o.regionalnetworkcenter
            ,o.paymentfiscalmonth
            ,DECODE (o.accountingdepartmentrptcd
                    ,'1', '1'
                    ,'2', '2'
                    ,'3', '3'
                    ,'4', '4'
                    ,'5', '3'
                    ,'6', '3'
                    ,'7', '3'
                    ,'9', '9'
                    ,'1'
                    ) feeforservice
            ,o.servicecatid
            ,o.fromdate
            ,o.todate
            ,o.fromdatefiscalmonth
            ,o.provamount
            ,o.planamount
            ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
            ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
            , (o.todate - o.fromdate) + 1 totalservicedays
            ,carid
            ,o.provpaymenttype provtype
        FROM tblclaimdetmonthly_s_part o
        WHERE FISCALDATE = '01-AUG-05'
        AND   (O.ProvAmount <> 0 OR O.PlanAmount <> 0)
        AND    O.claimid BETWEEN x AND y;    

   TYPE curcd_type IS TABLE OF curcd%ROWTYPE
      INDEX BY BINARY_INTEGER;

   reccd         curcd_type;

   TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;

   claimsplit    claimsplit_type := claimsplit_type ();
   bulk_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';

    v_CurrentInfo     := f_GetFiscalInfo(TO_DATE('28-AUG-05'));
    v_PendingLastDay  := v_CurrentInfo.LastDay;
    v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
    v_FiscalDate      := '01-AUG-05';

	SELECT MAX (claimid)
     INTO z
     FROM tblclaimdetmonthly_s_part;
DBMS_OUTPUT.put_line('This is x  : '||x ||' : '||z);
      OPEN curcd;
     LOOP                                                       ----loop 1st
         FETCH curcd
         BULK COLLECT INTO reccd LIMIT 100000;                    --> It depends
         DBMS_OUTPUT.put_line('Fetch record : '||SQL%ROWCOUNT);
        EXIT WHEN reccd.COUNT = 0;
          DBMS_OUTPUT.PUT_LINE('RECORD COUNT : '||reccd.COUNT);
  
 WHILE x < z
 LOOP
      FOR i IN  recCD.FIRST.. reccd.LAST
      LOOP                                                    -----loop 2nd

            v_SeqNumb := 0;
            
DBMS_OUTPUT.PUT_LINE ('v_SeqNumb : '|| v_Seqnumb);

            v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth;
            v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate);
            v_ToFiscalInfo   := f_GetFiscalInfo(recCD(i).ToDate);              
        
       CASE 
          WHEN v_ToFiscalInfo.LastDay IS NULL THEN v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
       END CASE;
           
       CASE 
          WHEN v_ToFiscalInfo.LastDay > v_PendingLastDay THEN v_ToFiscalInfo.FiscalMonth := 'PEND';
       END CASE;


      IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR 
                  v_FromFiscalInfo.FiscalMonth = 'PEND'       OR 
                   recCD(i).TotalServiceDays <= 0              
      THEN  
                     v_ProvAmount            := recCD(i).ProvAmount;
                     v_PlanAmount            := recCD(i).PlanAmount;     --LJK 06/27/2005  Added for new field.
                     v_SvcFiscalMonth        := 'PEND';                   --lk 10/04/2004
                     v_Ct                    := v_Ct + 1;
                     v_SeqNumb               := v_SeqNumb + 1;
             recCD(i).ClaimDetailHistSeqNumb := v_SeqNumb;
      
            claimsplit.EXTEND;
            claimsplit (claimsplit.LAST).claimid := reccd (i).claimid;
            claimsplit (claimsplit.LAST).claimlinenumb :=
                                                      reccd (i).claimlinenumb;
            claimsplit (claimsplit.LAST).claimdetailhistseqnumb :=
                                             reccd (i).claimdetailhistseqnumb;
            claimsplit (claimsplit.LAST).claimdetailhistrectypecd :=
                                           reccd (i).claimdetailhistrectypecd;
            claimsplit (claimsplit.LAST).operationcentercode :=
                                              reccd (i).regionalnetworkcenter;
            claimsplit (claimsplit.LAST).feeforservice :=
                                                      reccd (i).feeforservice;
            claimsplit (claimsplit.LAST).servicecatid :=
                                                       reccd (i).servicecatid;
            claimsplit (claimsplit.LAST).svcfiscalmonth :=
                                                reccd (i).fromdatefiscalmonth;
            claimsplit (claimsplit.LAST).paymentfiscalmonth :=
                                                 reccd (i).paymentfiscalmonth;
            claimsplit (claimsplit.LAST).provamount := reccd (i).provamount;
            claimsplit (claimsplit.LAST).carid := reccd (i).carid;
            claimsplit (claimsplit.LAST).provtypecd := reccd (i).provtype;
            claimsplit (claimsplit.LAST).planamount := reccd (i).planamount;

       v_FromFiscalInfo.FiscalMonth := 'PEND';
     ELSE
                  v_AmtUsed     := 0;
         	  v_PlanAmtUsed := 0;    --LJK 06/27/2005  Added to initialize new field.
                  v_Date        := recCD(i).FromDate;
      WHILE v_FromFiscalInfo.LastDay <= NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1))
      LOOP  
         
                v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;                       
                           
           IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN  --LK 10/04/2004
              v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month
              v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;  --LJK 08/23/2005.  Set the PlanAmount not the Used Amount.
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
            ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
              v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed;
              v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
              v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_FromFiscalInfo.LastDay     := v_ToFiscalInfo.LastDay + 1;
            ELSE
              v_FMPart         := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays;
              v_ProvAmount     := Round(recCD(i).ProvAmount * v_FMPart,2);
              v_PlanAmount     := Round(recCD(i).PlanAmount * v_FMPart,2);
              v_AmtUsed        := v_AmtUsed + v_ProvAmount;
              v_PlanAmtUsed    := v_PlanAmtUsed + v_PlanAmount;
              v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
              v_Date           := v_FromFiscalInfo.FirstDay;
            END IF;
            v_SeqNumb := v_SeqNumb + 1;
         ---   recCD(i).ClaimDetailHistSeqNumb := v_SeqNumb;

       ClaimSplit.EXTEND;
       ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
       ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := recCD(i).ClaimDetailHistSeqNumb;
       ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;         
       ClaimSplit(ClaimSplit.LAST).OperationCenterCode      := recCD(i).RegionalNetworkCenter;
       ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
       ClaimSplit(ClaimSplit.LAST).ServiceCatId             := recCD(i).ServiceCatId;
       ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth           := v_SvcFiscalMonth;
       ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth       := recCD(i).PaymentFiscalMonth;
       ClaimSplit(ClaimSplit.LAST).ProvAmount               := v_ProvAmount;
       ClaimSplit(ClaimSplit.LAST).CarId                    := recCD(i).CarId;
       ClaimSplit(ClaimSplit.LAST).ProvTypeCD               := recCD(i).ProvType;
       ClaimSplit(ClaimSplit.LAST).PlanAmount               := v_PlanAmount;

       END LOOP;

       END IF;

   END LOOP;          

   DBMS_OUTPUT.put_line('Fetch record : '||SQL%ROWCOUNT);
         BEGIN
            FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
               INSERT INTO w_claimsplit
                    VALUES claimsplit (i);
					DBMS_OUTPUT.put_line('cLAIMSPLIT : '|| CLAIMSPLIT.COUNT);
         EXCEPTION
            --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
            WHEN bulk_errors
            THEN
               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                            (   'Error from element #'
                             || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
                             || ': '
                             || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
                            );
               END LOOP;
            
               COMMIT;
         END;
         claimsplit.DELETE;
   x := y + 1;
   y := y + 100000;
      COMMIT;
      claimsplit.DELETE;
      
   END LOOP;
IF v_SvcFiscalMonth = 'PEND' THEN
              EXIT;
            END IF;
     CLOSE curCD;
         
END LOOP;
   
EXCEPTION 
    WHEN NO_DATA_FOUND THEN
	    DBMS_OUTPUT.PUT_LINE('There is no data');
     
	  WHEN OTHERS THEN
  
      error_number  := SQLCODE;
      error_message := substr(SQLERRM, 1, 200);
      dbms_output.put_line('error: ' || error_number || ' ' || error_message);
      DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
END;

[Updated on: Sat, 05 August 2006 20:30]

Report message to a moderator

Re: Frequent COMMIT is Culprit or anything else? [message #186346 is a reply to message #186104] Mon, 07 August 2006 10:25 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
IN ADDITION TO above , the DBMS OUTPUT results is
SQL> begin

  2     pk_cdtable_part_test.p_claimsplit;

  3  end;

  4  /

This is x  : 0 : 7304836

Fetch record : 1

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.26

SQL>

 





Re: Frequent COMMIT is Culprit or anything else? [message #187361 is a reply to message #186346] Sat, 12 August 2006 15:19 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys, i have figured it out.

I have removed WHILE LOOP
AND one of table was not having data
hence the 2nd set of collection was not populating.

now everything is fine, except performance issue
which i have raised in new topic.

Thanks for all you support.





Previous Topic: Slow response
Next Topic: After executing Dbms_pipe package session get blocked
Goto Forum:
  


Current Time: Mon Dec 05 13:21:40 CST 2016

Total time taken to generate the page: 0.11037 seconds