Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_PROFILER Results
DBMS_PROFILER Results [message #187071] Thu, 10 August 2006 14:52 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi everyone!

Any body has any idea why line 689 is taking so much resouces?

Please let me know if you need any further info on the following.

Please suggests me How can i improve the performance?

SQL> prompt Most popular lines (more than 1%), summarize across all runs 


SQL> SELECT p1.runid AS runid, p1.total_time/10000000 AS Hsecs,
  2  p1.total_time/p4.grand_total*100 AS pct,
  3  SUBSTR(p2.unit_owner, 1, 20) AS owner,
  4  DECODE(p2.unit_name, '', '<anonymous>',
  5  SUBSTR(p2.unit_name,1, 20)) AS unit_name, p1.line#, (
  6    SELECT p3.text
  7    FROM all_source p3
  8    WHERE p3.owner = p2.unit_owner
  9    AND p3.line = p1.line#
 10    AND p3.name=p2.unit_name
 11    AND p3.type NOT IN ('PACKAGE', 'TYPE')) text
 12  FROM plsql_profiler_data p1, plsql_profiler_units p2,
 13       plsql_profiler_grand_total p4
 14  WHERE (p1.total_time >= p4.grand_total/100)
 15  AND p1.runID = p2.runid
 16  AND p2.unit_number=p1.unit_number
 17  and p1.runid = 28
 18* ORDER BY p1.total_time desc
SQL> /

     RUNID      HSECS        PCT OWNER                UNIT_NAME                 LINE# TEXT
---------- ---------- ---------- -------------------- -------------------- ---------- --------------
        28 47010.8718 27.4094401 AKDADHAN             PK_CDTABLE_PART_TEST        689   SELECT BeginDate, EndDate,.....--------> Why  ? can't understand?
        28 7096.52888 4.13759363 AKDADHAN             PK_CDTABLE_PART_TEST        816   FETCH curcd                    -------->bearable result
        28 5481.77693 3.19612104 AKDADHAN             PK_CDTABLE_PART_TEST        975   FORALL i IN 1 .. claimspli     -------->bearable result

Elapsed: 00:00:00.25


TEXT
--------------
SELECT BeginDate, EndDate,.....--------> Why takes so much HSECS AND PCT ?  can't understand.
FETCH curcd                    -------->bearable result
FORALL i IN 1 .. claimspli     -------->bearable result



HERE IS WHAT IN THE PACKAGE BODY contains where line  689# :

line
------
      FUNCTION f_GetFiscalInfo (v_Date IN DATE)
      RETURN tFiscalMonthRec
      IS
      v_FiscalInfo             tFiscalMonthRec;
      BEGIN
689   SELECT BeginDate, EndDate, FiscalMonth
      INTO   v_FiscalInfo
      FROM   tblFiscalMonth
      WHERE  BeginDate <= v_Date
      AND    EndDate >= v_Date;
      RETURN (v_FiscalInfo);
      EXCEPTION
      WHEN OTHERS THEN
      v_FiscalInfo.FiscalMonth := 'PEND';
      RETURN (v_FiscalInfo);
     END f_GetFiscalInfo;
 
----------------over loaded  

   FUNCTION f_GetFiscalInfo (v_FiscalMonth IN tblFiscalMonth.FiscalMonth%TYPE)
  RETURN tFiscalMonthRec
  IS
    v_FiscalInfo             tFiscalMonthRec;
  BEGIN
    SELECT BeginDate, EndDate, FiscalMonth
    INTO   v_FiscalInfo
    FROM   tblFiscalMonth
    WHERE  FiscalMonth = v_FiscalMonth;
    RETURN (v_FiscalInfo);
  EXCEPTION
    WHEN OTHERS THEN
      v_FiscalInfo.FiscalMonth := 'PEND';
      RETURN (v_FiscalInfo);
  END f_GetFiscalInfo;



Re: DBMS_PROFILER Results [message #187074 is a reply to message #187071] Thu, 10 August 2006 15:22 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Akshar wrote on Thu, 10 August 2006 15:52

SELECT BeginDate, EndDate, FiscalMonth
INTO v_FiscalInfo
FROM tblFiscalMonth
WHERE BeginDate <= v_Date
AND EndDate >= v_Date;



What does the explain plan say when you do:

explain plan
set statement_id = 'L689' 
for
SELECT BeginDate, EndDate, FiscalMonth
      FROM   tblFiscalMonth
      WHERE  BeginDate <= :1
      AND    EndDate >= :1;

select *
from table (dbms_xplan.display ('PLAN_TABLE', 'L689'));

delete from plan_table
where statement_id = 'L689';
commit;


I would guess that it is doing a full scan on a rather large table, especially if your only expecting one row back.
Re: DBMS_PROFILER Results [message #187078 is a reply to message #187074] Thu, 10 August 2006 15:51 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks a lot for your reply and suggestion.

Your guess is correct, it is scanning full table,
so what is solution now?

here PLAN_TABLE results :

SQL>  explain plan
  2   set statement_id = 'L689' 
  3   for
  4   SELECT BeginDate, EndDate, FiscalMonth
  5         FROM   tblFiscalMonth
  6         WHERE  BeginDate <= :1
  7         AND    EndDate >= :1;

Explained.

Elapsed: 00:00:00.12
SQL> select *
  2  from table (dbms_xplan.display ('PLAN_TABLE', 'L689'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

------------------------------------------------------------------------
| Id  | Operation            |  Name           | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |    23 |     2 |
|   1 |  TABLE ACCESS FULL   | TBLFISCALMONTH  |     1 |    23 |     2 |
------------------------------------------------------------------------

Re: DBMS_PROFILER Results [message #187082 is a reply to message #187071] Thu, 10 August 2006 16:30 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
How many rows are actually in the table TBLFISCALMONTH? Is this a temporary table of some sorts? How long does it actually take when you execute the query with parameters? Is there any indexes on the begindate/enddate column? Or are the statistics way skewed here?

If it is a temporary table, then collecting statistics would obviously be difficult -- so you may have to hint it or collect statistics when the table has data. It may not be unreasonable for the database to believe full scanning is the best plan.

47010 hsecs is 47010/100 seconds?, I am assuming. So we are talking over 5 minutes here?

Another thing you should probably pull back is the total_occur column, perhaps this is a function that is called inside a loop?
Re: DBMS_PROFILER Results [message #187089 is a reply to message #187082] Thu, 10 August 2006 20:41 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
The table is realy small,it has only 132 ROWS, its kind of lookup table for week,month,year information only.

WHEN I QUERY


SQL> ed
Wrote file afiedt.buf

  1  select fiscalmonth,begindate,enddate,numberofweeks from tblfiscalmonth
  2* where fiscalmonth = 200508
  3  /

FISCAL BEGINDATE ENDDATE   NUMBEROFWEEKS
------ --------- --------- -------------
200508 01-AUG-05 28-AUG-05             4

Elapsed: 00:00:00.59
SQL> 


When i use the same function :

SQL> ed
Wrote file afiedt.buf

  1  declare
  2     v_fiscalinfo  pk_cdtable_part_test.tfiscalmonthrec;
  3  begin
  4       v_fiscalinfo := pk_cdtable_part_test.f_getfiscalinfo('28-AUG-05');
  5  DBMS_OUTPUT.PUT_LINE (v_fiscalinfo.fiscalmonth);
  6* end;
SQL> /
PEND

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40



Yes we are talking about 5 minitues,
because if you see the 'FETCH CURCD ' statement
and 'FORALL ..' statement , these statement are
processing 2 million rows in 0.70 minitues and 0.54 respectively,why 'SELECT BEGINDATE..' statment 5 minitues?

entire 2 million rows process takes 13 minitues and out that
5 minitues is taken by 'SELECT BEGINDATE..' statement.

why i am concern about that minitues because this same
process will process 20 million rows and that time
what would be time for 'SELECT BEGINDATE...' statement?



SQL> desc tblfiscalmonth
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FISCALMONTH                               NOT NULL VARCHAR2(6)
 BEGINDATE                                          DATE
 ENDDATE                                            DATE
 NUMBEROFWEEKS                                      NUMBER(10)
 NUMBEROFDAYS                                       NUMBER(10)
 TABLENAME                                          VARCHAR2(30)
 CREATEDBY                                          VARCHAR2(30)
 CREATEDDATETIME                                    DATE
 UPDATEDBY                                          VARCHAR2(30)
 UPDATEDDATETIME                                    DATE
 AUTHORIZATIONID                                    NUMBER(10)




there are two index : FISCALMONTH AND AUTORIZATIONID


Re: DBMS_PROFILER Results [message #187090 is a reply to message #187089] Thu, 10 August 2006 20:51 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I don't think that query is your problem, then. Unless you are calling it 600 or so times?
Re: DBMS_PROFILER Results [message #187102 is a reply to message #187090] Thu, 10 August 2006 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry if this isn't useful - I only skimmed the details:

So you are calling a SQL inside a loop that executes 2 million times? SQLs inside loops are a no-no. A table with this few rows would probably fit inside a single block, so I doubt I/O is the problem.

Can you join the table into the main cursor so that you don't need to do the lookup?

Ross Leishman
Re: DBMS_PROFILER Results [message #187217 is a reply to message #187102] Fri, 11 August 2006 06:08 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I agree with Ross here.

If you cannot join it into the query and it is always going to be a small table then perhaps consider preloading it into a collection.

Another alternative is to try it out as a subquery in the SELECT clause in the main query.

I recall reading with 10g that you can use "associative" (hash) collections, so you can reference the record based on a key -- rather than a integer index.

The only thing that seems to make sense to me is it is being called inside the loop.

Since you have gotten handy with the profiler, I am sure you can try out a few of these ideas and figure out the most optimal solution.
Re: DBMS_PROFILER Results [message #187262 is a reply to message #187217] Fri, 11 August 2006 09:26 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks you for putting so much effort for me
i have to show you new results where i can
bring you more clear VIEW.

Please suggest how can i bypass or improve use of that
function ( from line no : 682 to 699 ) being call from
my p_ClaimSplit procedure ,
This call is realy killing the performance.

Actual rows process is : 2,070,829


SQL> spool c:\results.txt
SQL> @c:\dbms_profiler\evaluate_profiler_results.sql
Enter value for runid: 29
Enter value for name: pk_cdtable_part_test
Enter value for owner: akdadhan

      Line      Occur        Sec Text
---------- ---------- ---------- --------------------------------------------------------------------------------------------
       671                           v_FiscalMonth   tblFiscalMonth.FiscalMonth%TYPE;
       672                           v_EndDate       tblFiscalMonth.EndDate%TYPE;
       673                         BEGIN
       674          0          0     SELECT EndDate INTO v_EndDate
       675                           FROM tblFiscalMonth
       676                           WHERE FiscalMonth = p_FiscalMonth; --v_FiscalMonth;
       677          0          0     RETURN v_EndDate;
       678                         EXCEPTION
       679                           WHEN NO_DATA_FOUND THEN
       680          0          0       DBMS_OUTPUT.PUT_LINE('FISCALMONTH [' || v_FiscalMonth || '] WAS NOT FOUND IN TABLE TBLFISCALMONTH.');
       681          0          0       RAISE;
       682    3663843 11.3198125   END f_GetLastPendDay;
       683
       684                           FUNCTION f_GetFiscalInfo (v_Date IN DATE)
       685                         RETURN tFiscalMonthRec
       686                         IS
       687                           v_FiscalInfo             tFiscalMonthRec;
       688                         BEGIN
       689    3663843 468.861021     SELECT BeginDate, EndDate, FiscalMonth
       690                           INTO   v_FiscalInfo
       691                           FROM   tblFiscalMonth
       692                           WHERE  BeginDate <= v_Date
       693                           AND    EndDate >= v_Date;
       694    3663745 5.68674985     RETURN (v_FiscalInfo);
       695                         EXCEPTION
       696                           WHEN OTHERS THEN
       697         98 .000083135       v_FiscalInfo.FiscalMonth := 'PEND';
       698         98 .010264864       RETURN (v_FiscalInfo);
       699    3663843 5.07679844   END f_GetFiscalInfo;
       700                         FUNCTION f_GetFiscalInfo (v_FiscalMonth IN tblFiscalMonth.FiscalMonth%TYPE)
       701                         RETURN tFiscalMonthRec
       702                         IS
       703                           v_FiscalInfo             tFiscalMonthRec;
       704                         BEGIN
       705          0          0     SELECT BeginDate, EndDate, FiscalMonth
       706                           INTO   v_FiscalInfo
       707                           FROM   tblFiscalMonth
       708                           WHERE  FiscalMonth = v_FiscalMonth;
       709          0          0     RETURN (v_FiscalInfo);
       710                         EXCEPTION
       711                           WHEN OTHERS THEN
       712          0          0       v_FiscalInfo.FiscalMonth := 'PEND';
       713          0          0       RETURN (v_FiscalInfo);
       714          1 .000143008   END f_GetFiscalInfo;
       715
       716

-----The procedure which i have run under DBMS_PROFILER START HERE-------*****************************************

       717                       PROCEDURE p_ClaimSplit (v_LastDay IN DATE)  -----,v_Rc OUT NUMBER)
       718                         IS
       719
       720                       /* VARIABLES */
       721                              v_ThisFiscalMonth  tblclaimdetmonthly_s_part.FiscalMonth%TYPE; --'200402';
       722                              v_PendingLastDay   tblclaimdetmonthly_s_part.FromDate%TYPE;  --Current fiscal month.
       723                              v_SvcFiscalMonth   tblclaimdetmonthly_s_part.FiscalMonth%TYPE;
       724                              v_ProvAmount       tblclaimdetmonthly_s_part.ProvAmount%TYPE;
       725                              v_PlanAmount       tblclaimdetmonthly_s_part.PlanAmount%TYPE;  --LJK 06/27/2005  New field Added.
       726          1 .000000352        v_Date             tblclaimdetmonthly_s_part.Fromdate%TYPE := NULL;
       727          1 .000000242        v_FiscalDate       tblclaimdetmonthly_s_part.Fromdate%TYPE := NULL;
       728          1 .000000488        v_TOFiscalMonth    tblclaimdetmonthly_s_part.FiscalMonth%TYPE := NULL;
       729          1 .000001715        v_AmtUsed          tblclaimdetmonthly_s_part.ProvAmount%TYPE := 0;
       730          1 .000000555        v_PlanAmtUsed      tblclaimdetmonthly_s_part.ProvAmount%TYPE := 0;  --LJK 06/27/2005  New field Added.
       731          1 .000000294        v_ClaimID          tblClaimDetail.ClaimID%TYPE := NULL;
       732          1  .00000029        v_ClaimLineNumb    tblClaimDetail.ClaimLineNumb%TYPE := NULL;
       733          1   .0000003        v_Ct               PLS_INTEGER :=0;
       734                              v_FMPart           PLS_INTEGER;
       735          1 .000000289        v_CTX              PLS_INTEGER := 0;
       736                              error_number       number;
       737                              error_message      varchar2(200);
       738          1 .000000367        v_SeqNumb          NUMBER := 0;
       739                              --v_EmailTo          EMAIL.AddressList_Tab;
       740                              ---v_EmailFrom        VARCHAR2(50) := USER || '@GENTIVA.COM';
       741                              ---v_EmailSubject     VARCHAR2(50) := 'MONTH-END CLAIM SPLIT';
       742                             --- v_EmailText        VARCHAR2(2000) := NULL;
       743          1 .000000294        x                  PLS_INTEGER := 0;
       744          1 .000000468        y                  PLS_INTEGER := 100000;
       745          1 .000000275        z                  PLS_INTEGER := 0;
       746                              v_FromFiscalInfo   tFiscalMonthRec;
       747                              v_ToFiscalInfo     tFiscalMonthRec;
       748                              v_CurrentInfo      tFiscalMonthRec;
       749
       750
       751                          CURSOR curcd
       752                          IS
       753          1 .034344102       SELECT o.claimid
       754                                   ,o.claimlinenumb
       755                                   ,o.regionalnetworkcenter
       756                                   ,o.paymentfiscalmonth
       757                                   ,DECODE (o.accountingdepartmentrptcd
       758                                           ,'1', '1'
       759                                           ,'2', '2'
       760                                           ,'3', '3'
       761                                           ,'4', '4'
       762                                           ,'5', '3'
       763                                           ,'6', '3'
       764                                           ,'7', '3'
       765                                           ,'9', '9'
       766                                           ,'1'
       767                                           ) feeforservice
       768                                   ,o.servicecatid
       769                                   ,o.fromdate
       770                                   ,o.todate
       771                                   ,o.fromdatefiscalmonth
       772                                   ,o.provamount
       773                                   ,o.planamount
       774                                   ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
       775                                   ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
       776                                   , (o.todate - o.fromdate) + 1 totalservicedays
       777                                   ,carid
       778                                   ,o.provpaymenttype provtype
       779                               FROM tblclaimdetmonthly_s_part o
       780                               WHERE FISCALDATE = v_fiscaldate
       781                               AND   (O.ProvAmount <> 0 OR O.PlanAmount <> 0);
       782
       783                          TYPE curcd_type IS TABLE OF curcd%ROWTYPE
       784                             INDEX BY PLS_INTEGER;
       785
       786                          reccd         curcd_type;
       787
       788                          TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;
       789
       790          1 .000003294    claimsplit    claimsplit_type := claimsplit_type ();
       791                          bulk_errors   EXCEPTION;
       792                          PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
       793
       794                       BEGIN
       795
       796          2 .000013644             v_CurrentInfo     := f_GetFiscalInfo(v_LastDay);
       797          1  .00000098             v_PendingLastDay  := v_CurrentInfo.LastDay;
       798          1 .000002019             v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
       799          1 .000045313             v_FiscalDate      := '01-AUG-05';
       800
       801          1 .354989302   EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';
       802
       803          1 .000001492  OPEN curcd;
       804
       805                        LOOP                                                       ----loop 1st
       806         16 78.2874728     FETCH curcd
       807                           BULK COLLECT INTO reccd LIMIT 100000;                    --> It depends
       808
       809         16 .000305357     EXIT WHEN reccd.COUNT = 0;
       810
       811    1593020 1.25357991                 FOR i IN 1 .. reccd.LAST
       812                                LOOP
       813
       814    1593004 .625077101                    v_SeqNumb        := 0;
       815    1593004 2.69749856                        v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth;
       816    3186008 3.59208191             v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate);
       817    3186008 4.42603402             v_ToFiscalInfo   := f_GetFiscalInfo(recCD(i).ToDate);
       818
       819    1593004 .492907669                 IF v_ToFiscalInfo.LastDay IS NULL THEN
       820         97 .000232765                    v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
       821                                END IF;                                             -----loop 2nd
       822
       823    1593004 .980360951          IF v_ToFiscalInfo.LastDay > v_PendingLastDay THEN
       824        125 .000078004                v_ToFiscalInfo.FiscalMonth := 'PEND';
       825                                END IF;
       826
       827    1593004 4.21071099                 IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR
       828                                   v_FromFiscalInfo.FiscalMonth = 'PEND'       OR
       829                                   recCD(i).TotalServiceDays <= 0              THEN
       830
       831          9 .000028617             v_ProvAmount            := recCD(i).ProvAmount;
       832          9 .000009165             v_PlanAmount            := recCD(i).PlanAmount;
       833          9 .000004076                        v_SvcFiscalMonth        := 'PEND';                --lk 10/04/2004
       834          9 .000009444             v_SeqNumb               := v_SeqNumb + 1;
       835
       836          9 .000012681                         reccd(i).claimdetailhistseqnumb := v_seqnumb;
       837
       838          9 .000052094     claimsplit.EXTEND;
       839
       840          9  .00002588     claimsplit (claimsplit.LAST).claimid :=
       841                                                           reccd(i).claimid;
       842          9 .000028974     claimsplit (claimsplit.LAST).claimlinenumb :=
       843                                                           reccd(i).claimlinenumb;
       844          9 .000022748     claimsplit (claimsplit.LAST).claimdetailhistseqnumb :=
       845                                                           reccd(i).claimdetailhistseqnumb;
       846          9 .000023617     claimsplit (claimsplit.LAST).claimdetailhistrectypecd :=
       847                                                           reccd(i).claimdetailhistrectypecd;
       848          9  .00002752     claimsplit (claimsplit.LAST).operationcentercode :=
       849                                                        reccd (i).regionalnetworkcenter;
       850          9   .0000217     claimsplit (claimsplit.LAST).feeforservice :=
       851                                                        reccd (i).feeforservice;
       852          9 .000022241     claimsplit (claimsplit.LAST).servicecatid :=
       853                                                        reccd (i).servicecatid;
       854          9 .000016163     claimsplit (claimsplit.LAST).svcfiscalmonth :=
       855                                                        v_svcfiscalmonth;
       856          9 .000029945     claimsplit (claimsplit.LAST).paymentfiscalmonth :=
       857                                                        reccd (i).paymentfiscalmonth;
       858          9 .000022222     claimsplit (claimsplit.LAST).provamount :=
       859                                                           reccd (i).provamount;
       860          9 .000022452     claimsplit (claimsplit.LAST).carid :=
       861                                                           reccd (i).carid;
       862          9 .000029258     claimsplit (claimsplit.LAST).provtypecd :=
       863                                                           reccd (i).provtype;
       864          9 .000021614     claimsplit (claimsplit.LAST).planamount :=
       865                                                           reccd (i).planamount;
       866
       867          9 .000006077                v_FromFiscalInfo.FiscalMonth := 'PEND';
       868                              ELSE
       869
       870
       871    1592995 1.43699647             v_AmtUsed     := 0;
       872    1592995  1.0019323                v_PlanAmtUsed := 0;    --LJK 06/27/2005  Added to initialize new field.
       873    1592995 1.42662269             v_Date        := recCD(i).FromDate;
       874    7327416 12.6085247            WHILE v_FromFiscalInfo.LastDay <=
       875                                           NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1))
       876                                  LOOP
       877    2070829 1.33272801               v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
       878
       879    2070829 .951088351                   IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN  --LK 10/04/2004
       880        116 .000177082               v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month
       881        116 .000159214               v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;  --LJK 08/23/2005.  Set the PlanAmount not the Used Amount.
       882        116 .000057051               v_FromFiscalInfo.FiscalMonth := 'PEND';
       883        116 .000069536               v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
       884    2070713 .972101712             ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
       885    1592879 2.31814951               v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed;
       886    1592879  2.0440113               v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;
       887    1592879 .714622595               v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
       888    1592879 .758996615               v_FromFiscalInfo.FiscalMonth := 'PEND';
       889    1592879  3.1165439               v_FromFiscalInfo.LastDay     := v_ToFiscalInfo.LastDay + 1;
       890                                   ELSE
       891     477834 2.20228293               v_FMPart         := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays;
       892     477834 1.29889431               v_ProvAmount     := Round(recCD(i).ProvAmount * v_FMPart,2);
       893     477834  1.0671404               v_PlanAmount     := Round(recCD(i).PlanAmount * v_FMPart,2);
       894     477834 .375650201               v_AmtUsed        := v_AmtUsed + v_ProvAmount;
       895     477834 .328612569               v_PlanAmtUsed    := v_PlanAmtUsed + v_PlanAmount;
       896     955668 1.60861288               v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
       897     477834 .195676451               v_Date           := v_FromFiscalInfo.FirstDay;
       898                                   END IF;
       899
       900    2070829 1.73724967                        v_SeqNumb := v_SeqNumb + 1;
       901
       902    2070829 11.9630477                 ClaimSplit.EXTEND;
       903    2070829 5.92202957          ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
       904    2070829 4.89309189          ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
       905    2070829  3.6978673          ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := v_seqnumb;
       906    2070829 5.20126715          ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
       907    2070829 4.90498906          ClaimSplit(ClaimSplit.LAST).OperationCenterCode      := recCD(i).RegionalNetworkCenter;
       908    2070829 4.85973593          ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
       909    2070829 4.84971236          ClaimSplit(ClaimSplit.LAST).ServiceCatId             := recCD(i).ServiceCatId;
       910    2070829 3.64029326          ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth           := v_SvcFiscalMonth;
       911    2070829 4.87229358          ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth       := recCD(i).PaymentFiscalMonth;
       912    2070829 3.77270551          ClaimSplit(ClaimSplit.LAST).ProvAmount               := v_ProvAmount;
       913    2070829 4.90257822          ClaimSplit(ClaimSplit.LAST).CarId                    := recCD(i).CarId;
       914    2070829  6.3208152          ClaimSplit(ClaimSplit.LAST).ProvTypeCD               := recCD(i).ProvType;
       915    2070829 3.77312419          ClaimSplit(ClaimSplit.LAST).PlanAmount               := v_PlanAmount;
       916
       917    2070829 1.22766754 IF v_svcfiscalmonth = 'PEND' THEN
       918    2070829 .623008038          EXIT;
       919                              END IF;
       920                              END LOOP;
       921                              end if;
       922                            END LOOP;
       923
       924                               BEGIN
       925         16  58.068514             FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
       926                                      INSERT INTO w_claimsplit
       927                                           VALUES claimsplit (i);
       928                                EXCEPTION
       929                                   --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
       930                                   WHEN bulk_errors
       931                                   THEN
       932      14659 .010037323                FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
       933                                      LOOP
       934      29314 .061665272                   DBMS_OUTPUT.put_line
       935                                                   (   'Error from element #'
       936                                                    || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
       937                                                    || ': '
       938                                                    || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
       939                                                   );
       940                                      END LOOP;
       941
       942          2 .008879581                COMMIT;
       943          2 .000004366          END;
       944
       945         16 4.31921722          claimsplit.DELETE;
       946
       947         16 .000097023                 EXIT WHEN curcd%NOTFOUND;
       948
       949                             END LOOP;
       950          1 .030182013       COMMIT;
       951          1 .000002985       CLOSE curCD;
       952                       EXCEPTION
       953                             WHEN OTHERS THEN
       954          0          0       error_number  := SQLCODE;
       955          0          0       error_message := substr(SQLERRM, 1, 200);
       956          0          0       dbms_output.put_line('error: ' || error_number || ' ' || error_message);
       957          0          0       DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
       958          2 .047362609 END p_claimsplit;
       959
       960     --------END OF PROCEDURE-------********************************************************----------------------------------------------
       961
       962

[Updated on: Fri, 11 August 2006 09:29]

Report message to a moderator

Re: DBMS_PROFILER Results [message #187820 is a reply to message #187262] Tue, 15 August 2006 17:08 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Hello Experts!

Any views on the above query?

to reduce function calls from procedure.
Re: DBMS_PROFILER Results [message #187822 is a reply to message #187820] Tue, 15 August 2006 17:30 Go to previous message
wagnerch
Messages: 58
Registered: July 2006
Member
I see three or four suggestions, did you give them a try?

[Updated on: Tue, 15 August 2006 17:30]

Report message to a moderator

Previous Topic: parsing blob_content
Next Topic: compare strings
Goto Forum:
  


Current Time: Wed Dec 07 12:40:37 CST 2016

Total time taken to generate the page: 0.10115 seconds