Home » RDBMS Server » Performance Tuning » Performance Tuning for a package (Oracle 10g)
Performance Tuning for a package [message #317923] Mon, 05 May 2008 00:35 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have been given a package to tune as this is giving a network load.

There is a login page where data is captured from user and captured in database and inserts are done. The package is having some procedures where lot of parameters are being sent and inserts done on them.

I am pasting the package body though its long one so that the picture can become clear. Sorry for the long code I am pasting here.

Please suggest me as what can be done to this package to make it faster.
CREATE OR REPLACE PACKAGE BODY LOGIN_ASSISTANT.Login_Assistant AS
PROCEDURE Proc_Insert_ProductDetails
(
p_First_Name varchar2,
p_Last_Name varchar2,
p_DOB date,
p_Gender varchar2,
p_Age integer,
p_Telephone varchar2,
p_Age_Proof varchar2,
p_Marital_Status varchar2,
p_Staff varchar2,
p_COMBOFLAG varchar2,
p_COMBOFIRSTMINPREMIUM float,
p_COMBOFIRSTMINANNUALPREMIUM float,
p_SUMOFPRODUCTANNUALPREMIUM float,
p_SUMOFPRODUCTANNUALPREMIUMEQU float,
p_TOTALFPD float,
p_AGENTNO varchar2,
p_ORIGIN varchar2,
p_nomineeAppointeeProposerDeta varchar2,

p_Application_Form_No varchar2,
p_Product_Code varchar2,
p_PROD_NAME varchar2, p_PAYMENT_MODE varchar2, p_PREMIUM_TYPE varchar2,
p_PREMIUM_AMT float, p_TOTAL_FIRST_PREMIUM float, p_ANNUAL_PREMIUM float,
p_SUM_ASSURED float, p_PROD_DATE date, p_ADBR_SA float,
p_ABR_SA float, p_CIR_SA float, p_IBR_SA float,
p_WOPR_SA float, p_ADBR_TERM float, p_ABR_TERM float,
p_CIR_TERM float, p_IBR_TERM float, p_WOPR_TERM float,
p_MAXIMISER float, p_BALANCER float, p_PROTECTOR float,
p_PRESERVER float, p_ADDRESS_PROOF varchar2, p_IDENTITY_PROOF varchar2,
p_PIN_CODE integer, p_MICR_CODE varchar2, p_HT_WT varchar2,
p_APPLICATION_NUMBER varchar2, p_INCOME_PROOF varchar2, p_EBI_PROOF varchar2,
p_NSAPALLOWED VARCHAR2, p_BASEPLANSUMASSURED FLOAT, p_PRODUCTCATEGORY VARCHAR2,
p_PRODUCTTYPE VARCHAR2, p_BASEPREMIUM  FLOAT, p_TOTALRIDERPREMIUM FLOAT,
p_ACCIDENTBENEFITRIDERSSA   FLOAT, p_MEDICALREQUIREMENTSLIST   VARCHAR2, p_TOTALPREMIUMWOXRT FLOAT,
p_TOTALPREMIUMXRT FLOAT, p_TOTALPREMIUM FLOAT,p_MODEOFPAYMENT VARCHAR2, p_FREQUENCY INTEGER,
p_ANNUALPREMIUM FLOAT, p_ANNUALPREMIUMEQUIVALENT   FLOAT, p_MULTIPLYINGFACTOR FLOAT,
p_TOTALFIRSTPREMIUMWOXRT    FLOAT,p_TOTALFIRSTPREMIUMXRT FLOAT,p_PRODUCTFPD  FLOAT,
p_COLLECTEDPRODUCTFPD       FLOAT, p_PRODUCTAMOUNTREQUIRED  FLOAT, p_SINGLEPREMIUM FLOAT,
p_COMBOSEQNO INTEGER, p_MODEOFPAYMENTFREQUENCY VARCHAR2, p_PRODUCTTERM  INTEGER, p_PRODUCTMATURITYAGE INTEGER,
p_PRODUCTVESTINGAGE INTEGER, p_PRODUCTRATINGFACTOR INTEGER, p_SUSTAINABILITYFACTOR INTEGER, p_SUSTAINABILITYSUMASSURED  FLOAT,
p_COMPONENTCODE VARCHAR2,p_LIFEANNUITYWROP VARCHAR2,p_LIFEANNUITYROP VARCHAR2,p_LASTSURVIVORROP           VARCHAR2,
p_LASTSURVIVORWROP VARCHAR2, p_LIFEANNUITYGUARANTEEDFOR  VARCHAR2, p_FREQUENCYOFPENSION VARCHAR2,
p_WITHDB VARCHAR2,p_WITHOUTDB VARCHAR2,p_PAYMENTTERM   INTEGER,p_NONSTDAGEPROOFEXTRA FLOAT,
p_PREMIUMINSTALLMENT FLOAT,p_EDUCESS FLOAT,p_PREMIUMARREAR FLOAT, p_STANDEDUCESSARREARS FLOAT,
p_INTERESTAMOUNT FLOAT,p_PURCHASEPRICE  FLOAT,p_PERIODICITY VARCHAR2,p_ANNUITYAMOUNT FLOAT,
p_modalPremium FLOAT, p_coverContinuanceOptionAvaile FLOAT,
p_SPOUSE_AGE INTEGER, p_CHILDAGEATENTRY INTEGER, p_CHILDAGEATMATURITY INTEGER,p_ESCCITY_NAME VARCHAR2,
p_TERMOFILLUSTRATION VARCHAR2,
  p_h_NOOFUNITSOF_COVERAGE          INTEGER,
  p_h_FINAL_PREMIUM                 NUMBER,
  p_h_PREMIUM_80C                   NUMBER,
  p_h_PREMIUM_80D                   NUMBER,
  p_h_NOOFUNITSOFCOVERAGE           INTEGER,
  p_h_SERVICE_TAX_80C               NUMBER,
  p_h_SERVICE_TAX_80D               NUMBER,
  p_SERVICETAX                      NUMBER,
  p_STDEDUCESSARREAR                NUMBER,
  p_h_EDUCESS_80C                   NUMBER,
  p_h_EDUCESS_80D                   NUMBER,
  p_INTESRESTAMOUNT                 NUMBER,
  p_h_FIRST_INSTALLMENT             NUMBER,
  p_h_CRITICALILLNESS_COVER         NUMBER,
  p_h_RIDER_SUM_ASSURED             NUMBER,
  p_h_REDUCED_PRE_LEVEL_BASE_PRE    NUMBER,
  p_h_REDUCED_PRELEVEL1_RIDER       NUMBER,
  p_h_REDPRE1_SERVICETAX  NUMBER,
  p_h_REDPRE1_BASEPREM   NUMBER,
  p_h_REDPRE2_BASEPREM   NUMBER,
  p_h_REDPRE2_RIDERPREM  NUMBER,
  p_h_REDPRE2_SER_TAX     NUMBER,
  p_h_REDPRE2_BASEPRE    NUMBER,
  p_h_REDPRE3_BASEPRE    NUMBER,
  p_h_REDPRE3_RIDERPRE   NUMBER,
  p_h_REDPRE3_SERTAX     NUMBER,
  p_h_REDPRE4_BASEPRE    NUMBER,
  p_h_REDPRE4_RIDERPREM  NUMBER,
  p_h_REDPRE4_SERVICETAX  NUMBER,
  p_h_REDPRE4_BASEPREM   NUMBER,
  p_h_DIAENCD_BNFT_RIDER      INTEGER,
  p_h_RIDER_PREMIUM                 NUMBER,
  p_h_TAX                           NUMBER,
  p_h_TOTAL_PREM                    NUMBER,
  p_POLICY_COMMENCE_DATE            DATE,
  p_h_TOTAL_PREM1                   NUMBER,
  p_h_TOTAL_PREM2                   NUMBER,
  p_h_TOTAL_PREM3                   NUMBER,
  p_h_TOTAL_PREM4                   NUMBER,
  p_T_AMOUNTOFCOVER                 NUMBER,
  p_T_COVERAGETERM                  INTEGER,
  p_T_COVERAGETILLAGE               INTEGER,
  p_T_PREMIUM_INSTALLMENT           NUMBER,
  p_T_MONTHLYPREMIUM                NUMBER,
  p_T_ADBR_COVERAGE_TILL_AGE        INTEGER,
  p_T_ADBR_PREMIUM_INSTALLMENT      NUMBER,
  p_T_WAVIEROFPREMIUM               VARCHAR2,
  p_T_SPOUSEDOB                     DATE,
  
  
     p_T_ABR_COVERAGE_TILL_AGE              INTEGER  ,
    p_T_IBR_COVERAGE_TILL_AGE                INTEGER  ,
   p_T_CIR_COVERAGE_TILL_AGE                INTEGER     ,
    p_T_ADBR_PREMIUM                        FLOAT      ,
    p_T_ABR_PREMIUM                            FLOAT      ,
    p_T_IBR_PREMIUM                            FLOAT     ,
    p_T_CIR_PREMIUM                           FLOAT      ,
    p_T_BENEFITILLUSTRATION                VARCHAR2      ,    
    p_T_WOPR_PREMIUM                       FLOAT     ,
    p_T_PARENTS_GENDER                      VARCHAR2,
    p_PRODSUBNAME VARCHAR2,
    p_PAYMENT_FPD VARCHAR2,
    P_COMBOQUOTE_FLAG VARCHAR2,
    P_Portfolio   varchar2,
    p_DeathBenefit varchar2,
  
    cur_CustId out sys_refcursor
)
AS
v_custId integer;
v_noOfRecords integer;
BEGIN

Select count(cust_Id) into v_noOfRecords from LA_CUSTOMER_DETAILS Where APPLICATION_FORM_NO = p_Application_Form_No;

IF v_noOfRecords = 0 then

   Select SQE_LA_CUSTID.NEXTVAL into v_custId from dual;

   INSERT INTO LA_CUSTOMER_DETAILS
       (Cust_Id,First_Name, Last_Name,DOB, Gender, Age,Telephone, Age_Proof,Marital_Status,Staff,Application_Form_No,Application_Date,COMBOFLAG,COMBOFIRSTMINPREMIUM,COMBOFIRSTMINANNUALPREMIUM,SUMOFPRODUCTANNUALPREMIUM,SUMOFPRODUCTANNUALPREMIUMEQUIV,TOTALFPD,AGENTNO,ORIGIN,nomineeAppointeeProposerDetail)
   VALUES
       (v_custId,p_First_Name ,p_Last_Name ,p_DOB ,p_Gender ,p_Age , p_Telephone ,p_Age_Proof,p_Marital_Status,p_Staff,p_Application_Form_No,
       current_date,p_COMBOFLAG,p_COMBOFIRSTMINPREMIUM,p_COMBOFIRSTMINANNUALPREMIUM,
       p_SUMOFPRODUCTANNUALPREMIUM,p_SUMOFPRODUCTANNUALPREMIUMEQU,p_TOTALFPD,p_AGENTNO,p_ORIGIN,
       p_nomineeAppointeeProposerDeta);
Else

    Select cust_Id into v_custId from LA_CUSTOMER_DETAILS
           Where APPLICATION_FORM_NO = p_Application_Form_No;

End if;

INSERT INTO LA_PRODUCT_DETAILS (
    PROD_SEQUENCE, cust_Id, Application_Form_No,
    Product_Code,Prod_Name, Payment_Mode, Premium_Type,
    Premium_Amt, Total_First_Premium, Annual_Premium,
    Sum_Assured, Prod_Date, ADBR_SA,
    ABR_SA, CIR_SA, IBR_SA,
    WOPR_SA, ADBR_Term, ABR_Term,
    CIR_Term, IBR_Term, WOPR_Term,
    Maximiser, Balancer, Protector,
    Preserver, Address_Proof, Identity_Proof,
    Pin_Code, MICR_Code, HT_WT,
    Application_Number, Income_Proof, EBI_Proof,
    NSAPALLOWED , BASEPLANSUMASSURED , PRODUCTCATEGORY ,
    PRODUCTTYPE , BASEPREMIUM  , TOTALRIDERPREMIUM ,
    ACCIDENTBENEFITRIDERSSA   , MEDICALREQUIREMENTSLIST   , TOTALPREMIUMWOXRT ,
    TOTALPREMIUMXRT , TOTALPREMIUM ,MODEOFPAYMENT , FREQUENCY ,
    ANNUALPREMIUM , ANNUALPREMIUMEQUIVALENT   , MULTIPLYINGFACTOR ,
    TOTALFIRSTPREMIUMWOXRT    ,TOTALFIRSTPREMIUMXRT ,PRODUCTFPD  ,
    COLLECTEDPRODUCTFPD       , PRODUCTAMOUNTREQUIRED  , SINGLEPREMIUM ,
    COMBOSEQNO , MODEOFPAYMENTFREQUENCY , PRODUCTTERM  , PRODUCTMATURITYAGE ,
    PRODUCTVESTINGAGE , PRODUCTRATINGFACTOR , SUSTAINABILITYFACTOR , SUSTAINABILITYSUMASSURED  ,
    COMPONENTCODE ,LIFEANNUITYWROP ,LIFEANNUITYROP ,LASTSURVIVORROP           ,
    LASTSURVIVORWROP , LIFEANNUITYGUARANTEEDFOR  , FREQUENCYOFPENSION ,
    WITHDB ,WITHOUTDB ,PAYMENTTERM   ,NONSTDAGEPROOFEXTRA ,
    PREMIUMINSTALLMENT ,EDUCESS ,PREMIUMARREAR , STANDEDUCESSARREARS ,
    INTERESTAMOUNT ,PURCHASEPRICE  ,PERIODICITY ,ANNUITYAMOUNT, modalPremium,
    coverContinuanceOptionAvaile,SPOUSE_AGE,CHILDAGEATENTRY,CHILDAGEATMATURITY,ESCCITY_NAME,TERMOFILLUSTRATION,
  H_NOOFUNITSOF_COVERAGE, H_FINAL_PREMIUM, H_PREMIUM_80C, 
   H_PREMIUM_80D, H_NOOFUNITSOFCOVERAGE, H_SERVICE_TAX_80C, 
   H_SERVICE_TAX_80D, SERVICETAX, STDEDUCESSARREAR, 
   H_EDUCESS_80C, H_EDUCESS_80D, INTESRESTAMOUNT, 
   H_FIRST_INSTALLMENT, H_CRITICALILLNESS_COVER, H_RIDER_SUM_ASSURED, 
   H_REDUCED_PRE_LEVEL_BASE_PRE, H_REDUCED_PRELEVEL1_RIDER, H_REDUCED_PRELEVEL1_SERVICETAX, 
   H_REDUCED_PRELEVEL1_BASE_PREM, H_REDUCED_PRELEVEL2_BASE_PREM, H_REDUCED_PRELEVEL2_RIDER_PREM, 
   H_REDUCED_PRELEVEL2_SER_TAX, H_REDUCED_PRELEVEL2_BASE_PRE, H_REDUCED_PRELEVEL3_BASE_PRE, 
   H_REDUCED_PRELEVEL3_RIDER_PRE, H_REDUCED_PRELEVEL3_SER_TAX, H_REDUCED_PRELEVEL4_BASE_PRE, 
   H_REDUCED_PRELEVEL4_RIDER_PREM, H_REDUCED_PRELEVEL4_SERVICETAX, H_REDUCED_PRELEVEL4_BASE_PREM, 
   H_DIABETIS_ENCD_BNFT_RIDER, H_RIDER_PREMIUM, H_TAX, 
   H_TOTAL_PREM, POLICY_COMMENCE_DATE, H_TOTAL_PREM1, 
   H_TOTAL_PREM2, H_TOTAL_PREM3, H_TOTAL_PREM4, 
   T_AMOUNTOFCOVER                 ,
  T_COVERAGETERM                  ,
  T_COVERAGETILLAGE               ,
  T_PREMIUM_INSTALLMENT           ,
  T_MONTHLYPREMIUM                ,
  T_ADBR_COVERAGE_TILL_AGE        ,
  T_ADBR_PREMIUM_INSTALLMENT      ,
  T_WAVIEROFPREMIUM               ,
  T_SPOUSEDOB                     ,
    T_IBR_COVERAGE_TILL_AGE                  ,
   T_CIR_COVERAGE_TILL_AGE                     ,
   T_ADBR_PREMIUM                              ,
   T_ABR_PREMIUM                                  ,
   T_IBR_PREMIUM                                 ,
   T_CIR_PREMIUM                                 ,
   T_BENEFITILLUSTRATION                      ,    
   T_WOPR_PREMIUM                             ,
   T_PARENTS_GENDER,
   PRODUCT_SUBNAME,PAYMENT_FPD,COMBOQUOTE_FLAG,
   T_ABR_COVERAGE_TILL_AGE, Portfolio               
   
  )
VALUES (SQE_LA_PROD_SEQ.NEXTVAL ,v_custId ,p_Application_Form_No,p_Product_Code,
    p_Prod_Name, p_Payment_Mode, p_Premium_Type,
    p_Premium_Amt, p_Total_First_Premium, p_Annual_Premium,
    p_Sum_Assured, p_Prod_Date, p_ADBR_SA,
    p_ABR_SA, p_CIR_SA, p_IBR_SA,
    p_WOPR_SA, p_ADBR_Term, p_ABR_Term,
    p_CIR_Term, p_IBR_Term, p_WOPR_Term,
    p_Maximiser, p_Balancer, p_Protector,
    p_Preserver, p_Address_Proof, p_Identity_Proof,
    p_Pin_Code, p_MICR_Code, p_HT_WT,
    p_Application_Number, p_Income_Proof, p_EBI_Proof,
    p_NSAPALLOWED , p_BASEPLANSUMASSURED , p_PRODUCTCATEGORY ,
    p_PRODUCTTYPE , p_BASEPREMIUM  , p_TOTALRIDERPREMIUM ,
    p_ACCIDENTBENEFITRIDERSSA   , p_MEDICALREQUIREMENTSLIST   , p_TOTALPREMIUMWOXRT ,
    p_TOTALPREMIUMXRT , p_TOTALPREMIUM ,p_MODEOFPAYMENT , p_FREQUENCY ,
    p_ANNUALPREMIUM , p_ANNUALPREMIUMEQUIVALENT   , p_MULTIPLYINGFACTOR ,
    p_TOTALFIRSTPREMIUMWOXRT    ,p_TOTALFIRSTPREMIUMXRT ,p_PRODUCTFPD  ,
    p_COLLECTEDPRODUCTFPD       , p_PRODUCTAMOUNTREQUIRED  , p_SINGLEPREMIUM ,
    p_COMBOSEQNO , p_MODEOFPAYMENTFREQUENCY , p_PRODUCTTERM  , p_PRODUCTMATURITYAGE ,
    p_PRODUCTVESTINGAGE , p_PRODUCTRATINGFACTOR , p_SUSTAINABILITYFACTOR , p_SUSTAINABILITYSUMASSURED  ,
    p_COMPONENTCODE ,p_LIFEANNUITYWROP ,p_LIFEANNUITYROP ,p_LASTSURVIVORROP           ,
    p_LASTSURVIVORWROP , p_LIFEANNUITYGUARANTEEDFOR  , p_FREQUENCYOFPENSION ,
    p_WITHDB ,p_WITHOUTDB ,p_PAYMENTTERM   ,p_NONSTDAGEPROOFEXTRA ,
    p_PREMIUMINSTALLMENT ,p_EDUCESS ,p_PREMIUMARREAR , p_STANDEDUCESSARREARS ,
    p_INTERESTAMOUNT ,p_PURCHASEPRICE  ,p_PERIODICITY ,p_ANNUITYAMOUNT, p_modalPremium,
    p_coverContinuanceOptionAvaile,p_SPOUSE_AGE, p_CHILDAGEATENTRY, p_CHILDAGEATMATURITY,p_ESCCITY_NAME,p_TERMOFILLUSTRATION,
      p_h_NOOFUNITSOF_COVERAGE,
  p_h_FINAL_PREMIUM,
  p_h_PREMIUM_80C,
  p_h_PREMIUM_80D,
  p_h_NOOFUNITSOFCOVERAGE,
  p_h_SERVICE_TAX_80C,
  p_h_SERVICE_TAX_80D,
  p_SERVICETAX,
  p_STDEDUCESSARREAR,
  p_h_EDUCESS_80C,
  p_h_EDUCESS_80D,
  p_INTESRESTAMOUNT,
  p_h_FIRST_INSTALLMENT,
  p_h_CRITICALILLNESS_COVER,
  p_h_RIDER_SUM_ASSURED,
  p_h_REDUCED_PRE_LEVEL_BASE_PRE,
  p_h_REDUCED_PRELEVEL1_RIDER,
  p_h_REDPRE1_SERVICETAX,
  p_h_REDPRE1_BASEPREM,
  p_h_REDPRE2_BASEPREM,
  p_h_REDPRE2_RIDERPREM,
  p_h_REDPRE2_SER_TAX,
  p_h_REDPRE2_BASEPRE,
  p_h_REDPRE3_BASEPRE,
  p_h_REDPRE3_RIDERPRE,
  p_h_REDPRE3_SERTAX,
  p_h_REDPRE4_BASEPRE,
  p_h_REDPRE4_RIDERPREM,
  p_h_REDPRE4_SERVICETAX,
  p_h_REDPRE4_BASEPREM,
  p_h_DIAENCD_BNFT_RIDER,
  p_h_RIDER_PREMIUM,
  p_h_TAX,
  p_h_TOTAL_PREM,
  p_POLICY_COMMENCE_DATE,
  p_h_TOTAL_PREM1,
  p_h_TOTAL_PREM2,
  p_h_TOTAL_PREM3,
  p_h_TOTAL_PREM4,
 p_T_AMOUNTOFCOVER                 ,
  p_T_COVERAGETERM                  ,
  p_T_COVERAGETILLAGE               ,
  p_T_PREMIUM_INSTALLMENT           ,
  p_T_MONTHLYPREMIUM                ,
  p_T_ADBR_COVERAGE_TILL_AGE        ,
  p_T_ADBR_PREMIUM_INSTALLMENT      ,
  p_T_WAVIEROFPREMIUM               ,
  p_T_SPOUSEDOB                     ,
   p_T_IBR_COVERAGE_TILL_AGE                  ,
   p_T_CIR_COVERAGE_TILL_AGE                     ,
    p_T_ADBR_PREMIUM                              ,
    p_T_ABR_PREMIUM                                  ,
    p_T_IBR_PREMIUM                                 ,
    p_T_CIR_PREMIUM                                 ,
    p_T_BENEFITILLUSTRATION                      ,    
    p_T_WOPR_PREMIUM                         ,
      p_T_PARENTS_GENDER,
      p_PRODSUBNAME, p_PAYMENT_FPD,
    P_COMBOQUOTE_FLAG,
    p_T_ABR_COVERAGE_TILL_AGE  , P_Portfolio            
   
) ;

update LA_PRODUCT_DETAILS
set NOMINEEAPPOINTEEPROPOSERDETAIL = p_nomineeAppointeeProposerDeta
WHERE APPLICATION_FORM_NO=p_Application_Form_No;

    open cur_CustId for
    Select v_custId from dual;

--commit;
END;

PROCEDURE Proc_Insert_CustDetails
(
p_First_Name varchar2,
p_Last_Name varchar2,
p_DOB date,
p_Gender varchar2,
p_Age integer,
p_Telephone varchar2,
p_Age_Proof varchar2,
p_Marital_Status varchar2,
p_Staff varchar2,
p_Application_Form_No varchar2,
p_Application_Date date,
p_COMBOFLAG varchar2,
p_COMBOFIRSTMINPREMIUM float,
p_COMBOFIRSTMINANNUALPREMIUM float,
p_SUMOFPRODUCTANNUALPREMIUM float,
p_SUMOFPRODUCTANNUALPREMIUMEQU float,
p_TOTALFPD float,
p_AGENTNO varchar2,
p_ORIGIN varchar2,
p_nomineeAppointeeProposerDeta varchar2,
cur_InsCust out sys_refcursor

)
AS
v_custId integer;

BEGIN

    Select SQE_LA_CUSTID.NEXTVAL into v_custId from dual;
    INSERT INTO LA_CUSTOMER_DETAILS
           (Cust_Id,First_Name, Last_Name,DOB, Gender, Age,Telephone, Age_Proof,Marital_Status,Staff,Application_Form_No,Application_Date,COMBOFLAG,COMBOFIRSTMINPREMIUM,COMBOFIRSTMINANNUALPREMIUM,SUMOFPRODUCTANNUALPREMIUM,SUMOFPRODUCTANNUALPREMIUMEQUIV,TOTALFPD,AGENTNO,ORIGIN,nomineeAppointeeProposerDetail)
    VALUES
           (v_custId,p_First_Name ,p_Last_Name ,p_DOB ,UPPER(p_Gender) ,p_Age , p_Telephone ,p_Age_Proof,p_Marital_Status,p_Staff,p_Application_Form_No,
           p_Application_Date,p_COMBOFLAG,p_COMBOFIRSTMINPREMIUM,p_COMBOFIRSTMINANNUALPREMIUM,
           p_SUMOFPRODUCTANNUALPREMIUM,p_SUMOFPRODUCTANNUALPREMIUMEQU,p_TOTALFPD,p_AGENTNO,p_ORIGIN,
           p_nomineeAppointeeProposerDeta);

    open cur_InsCust for
    Select v_custId from dual;
commit;
END;


PROCEDURE Proc_Update_ProductDetails
(
        p_First_Name varchar2,
        p_Last_Name varchar2,
        p_DOB                  date,
        p_Gender              varchar2,
        p_Age                  integer,
        p_Telephone varchar2,
        p_Age_Proof varchar2,
        p_Marital_Status varchar2,
        p_Staff varchar2,
        p_Application_Form_No varchar2,
        p_Application_Date date,
        p_COMBOFLAG varchar2,
        p_COMBOFIRSTMINPREMIUM float,
        p_COMBOFIRSTMINANNUALPREMIUM float,
        p_SUMOFPRODUCTANNUALPREMIUM float,
        p_SUMOFPRODUCTANNUALPREMIUMEQU float,
        p_TOTALFPD float,
        p_AGENTNO varchar2,
        p_ORIGIN varchar2,
        p_nomineeAppointeeProposerDeta varchar2,
        --p_custId integer,
        --p_Application_Form_No varchar2,
        p_Product_Code varchar2,
        p_PROD_NAME varchar2,
        p_PAYMENT_MODE varchar2,
        p_PREMIUM_TYPE varchar2,
        p_PREMIUM_AMT float,
        p_TOTAL_FIRST_PREMIUM float,
        p_ANNUAL_PREMIUM float,
        p_SUM_ASSURED float,
        p_PROD_DATE date,
        p_ADBR_SA float,
        p_ABR_SA float, p_CIR_SA float, p_IBR_SA float,
        p_WOPR_SA float, p_ADBR_TERM float, p_ABR_TERM float,
        p_CIR_TERM float, p_IBR_TERM float, p_WOPR_TERM float,
        p_MAXIMISER float, p_BALANCER float, p_PROTECTOR float,
        p_PRESERVER float, p_ADDRESS_PROOF varchar2, p_IDENTITY_PROOF varchar2,
        p_PIN_CODE integer, p_MICR_CODE varchar2, p_HT_WT varchar2,
        p_APPLICATION_NUMBER varchar2, p_INCOME_PROOF varchar2, p_EBI_PROOF varchar2,
        p_NSAPALLOWED VARCHAR2, p_BASEPLANSUMASSURED FLOAT, p_PRODUCTCATEGORY VARCHAR2,
        p_PRODUCTTYPE VARCHAR2, p_BASEPREMIUM  FLOAT, p_TOTALRIDERPREMIUM FLOAT,
        p_ACCIDENTBENEFITRIDERSSA   FLOAT, p_MEDICALREQUIREMENTSLIST   VARCHAR2, p_TOTALPREMIUMWOXRT FLOAT,
        p_TOTALPREMIUMXRT FLOAT, p_TOTALPREMIUM FLOAT,p_MODEOFPAYMENT VARCHAR2, p_FREQUENCY INTEGER,
        p_ANNUALPREMIUM FLOAT, p_ANNUALPREMIUMEQUIVALENT   FLOAT, p_MULTIPLYINGFACTOR FLOAT,
        p_TOTALFIRSTPREMIUMWOXRT    FLOAT,p_TOTALFIRSTPREMIUMXRT FLOAT,p_PRODUCTFPD  FLOAT,
        p_COLLECTEDPRODUCTFPD       FLOAT, p_PRODUCTAMOUNTREQUIRED  FLOAT, p_SINGLEPREMIUM FLOAT,
        p_COMBOSEQNO INTEGER, p_MODEOFPAYMENTFREQUENCY VARCHAR2, p_PRODUCTTERM  INTEGER, p_PRODUCTMATURITYAGE INTEGER,
        p_PRODUCTVESTINGAGE INTEGER, p_PRODUCTRATINGFACTOR INTEGER, p_SUSTAINABILITYFACTOR INTEGER, p_SUSTAINABILITYSUMASSURED  FLOAT,
        p_COMPONENTCODE VARCHAR2,p_LIFEANNUITYWROP VARCHAR2,p_LIFEANNUITYROP VARCHAR2,p_LASTSURVIVORROP           VARCHAR2,
        p_LASTSURVIVORWROP VARCHAR2, p_LIFEANNUITYGUARANTEEDFOR  VARCHAR2, p_FREQUENCYOFPENSION VARCHAR2,
        p_WITHDB VARCHAR2,p_WITHOUTDB VARCHAR2,p_PAYMENTTERM   INTEGER,p_NONSTDAGEPROOFEXTRA FLOAT,
        p_PREMIUMINSTALLMENT FLOAT,p_EDUCESS FLOAT,p_PREMIUMARREAR FLOAT, p_STANDEDUCESSARREARS FLOAT,
        p_INTERESTAMOUNT FLOAT,p_PURCHASEPRICE  FLOAT,p_PERIODICITY VARCHAR2,p_ANNUITYAMOUNT FLOAT,
        p_modalPremium FLOAT, p_coverContinuanceOptionAvaile FLOAT
)
AS

BEGIN
            update  LA_CUSTOMER_DETAILS
           set

                First_Name = p_First_Name,
                Last_Name = p_Last_Name,
                DOB = p_DOB,
                Gender = p_Gender,
                Age  = p_Age,
                Telephone  = p_Telephone,
                Age_Proof = p_Age_Proof,
                Marital_Status = p_Marital_Status,
                Staff = p_Staff,
                Application_Form_No =  p_Application_Form_No,
                Application_Date =  p_Application_Date,
                COMBOFLAG=  p_COMBOFLAG,
                COMBOFIRSTMINPREMIUM = p_COMBOFIRSTMINPREMIUM,
                COMBOFIRSTMINANNUALPREMIUM = p_COMBOFIRSTMINANNUALPREMIUM,
                SUMOFPRODUCTANNUALPREMIUM = p_SUMOFPRODUCTANNUALPREMIUM,
                 SUMOFPRODUCTANNUALPREMIUMEQUIV = p_SUMOFPRODUCTANNUALPREMIUMEQU,
                 TOTALFPD = p_TOTALFPD,
                 AGENTNO = p_AGENTNO,
                 ORIGIN = p_ORIGIN,
                 nomineeAppointeeProposerDetail = p_nomineeAppointeeProposerDeta
                 where
                  Application_Form_No=p_Application_Form_No;

update  LA_PRODUCT_DETAILS
set
     --cust_Id =p_custId,
     Application_Form_No=p_Application_Form_No,
    Product_Code=p_Product_Code,Prod_Name=p_Prod_Name, Payment_Mode=p_Payment_Mode, Premium_Type=p_Premium_Type,
    Premium_Amt=p_Premium_Amt, Total_First_Premium=p_Total_First_Premium, Annual_Premium=p_Annual_Premium,
    Sum_Assured=p_Sum_Assured, Prod_Date=p_Prod_Date, ADBR_SA=p_ADBR_SA,
    ABR_SA=p_ABR_SA, CIR_SA=p_CIR_SA, IBR_SA=p_IBR_SA,
    WOPR_SA=p_WOPR_SA, ADBR_Term=p_WOPR_SA, ABR_Term=p_ABR_Term,
    CIR_Term=p_CIR_Term, IBR_Term=p_IBR_Term, WOPR_Term=p_WOPR_Term,
    Maximiser=p_Maximiser, Balancer=p_Balancer, Protector=p_Protector,
    Preserver=p_Preserver, Address_Proof=p_Address_Proof, Identity_Proof=p_Identity_Proof,
    Pin_Code=p_Pin_Code, MICR_Code=p_MICR_Code, HT_WT=p_HT_WT,
    Application_Number=p_Application_Number, Income_Proof=p_Income_Proof, EBI_Proof=p_EBI_Proof,
    NSAPALLOWED=p_NSAPALLOWED , BASEPLANSUMASSURED = p_BASEPLANSUMASSURED, PRODUCTCATEGORY=p_PRODUCTCATEGORY ,
    PRODUCTTYPE = p_PRODUCTTYPE, BASEPREMIUM =p_BASEPREMIUM , TOTALRIDERPREMIUM = p_TOTALRIDERPREMIUM,
    ACCIDENTBENEFITRIDERSSA = p_ACCIDENTBENEFITRIDERSSA  , MEDICALREQUIREMENTSLIST = p_MEDICALREQUIREMENTSLIST  , TOTALPREMIUMWOXRT = p_TOTALPREMIUMWOXRT,
    TOTALPREMIUMXRT = p_TOTALPREMIUMXRT , TOTALPREMIUM = p_TOTALPREMIUM ,MODEOFPAYMENT = p_MODEOFPAYMENT , FREQUENCY = p_FREQUENCY ,
    ANNUALPREMIUM = p_ANNUALPREMIUM , ANNUALPREMIUMEQUIVALENT = p_ANNUALPREMIUMEQUIVALENT   , MULTIPLYINGFACTOR = p_MULTIPLYINGFACTOR,
    TOTALFIRSTPREMIUMWOXRT = p_TOTALFIRSTPREMIUMWOXRT   ,TOTALFIRSTPREMIUMXRT = p_TOTALFIRSTPREMIUMXRT ,
    PRODUCTFPD = p_PRODUCTFPD  ,
    COLLECTEDPRODUCTFPD  =  p_COLLECTEDPRODUCTFPD    , PRODUCTAMOUNTREQUIRED = p_PRODUCTAMOUNTREQUIRED ,
    SINGLEPREMIUM = p_SINGLEPREMIUM,
    COMBOSEQNO = p_COMBOSEQNO, MODEOFPAYMENTFREQUENCY = p_MODEOFPAYMENTFREQUENCY , PRODUCTTERM  = p_PRODUCTTERM ,
    PRODUCTMATURITYAGE = p_PRODUCTMATURITYAGE ,
    PRODUCTVESTINGAGE = p_PRODUCTVESTINGAGE , PRODUCTRATINGFACTOR = p_PRODUCTRATINGFACTOR ,
    SUSTAINABILITYFACTOR = p_SUSTAINABILITYFACTOR , SUSTAINABILITYSUMASSURED = p_SUSTAINABILITYSUMASSURED ,
    COMPONENTCODE = p_COMPONENTCODE ,LIFEANNUITYWROP = p_LIFEANNUITYWROP ,LIFEANNUITYROP = p_LIFEANNUITYROP ,
    LASTSURVIVORROP = p_LASTSURVIVORROP          ,
    LASTSURVIVORWROP = p_LASTSURVIVORWROP , LIFEANNUITYGUARANTEEDFOR = p_LIFEANNUITYGUARANTEEDFOR ,
    FREQUENCYOFPENSION = p_FREQUENCYOFPENSION,
    WITHDB = p_WITHDB ,WITHOUTDB = p_WITHOUTDB ,PAYMENTTERM = p_PAYMENTTERM   ,
    NONSTDAGEPROOFEXTRA = p_NONSTDAGEPROOFEXTRA,
    PREMIUMINSTALLMENT= p_PREMIUMINSTALLMENT ,EDUCESS = p_EDUCESS ,PREMIUMARREAR = p_PREMIUMARREAR,
    STANDEDUCESSARREARS = p_STANDEDUCESSARREARS,
    INTERESTAMOUNT= p_INTERESTAMOUNT ,PURCHASEPRICE = p_PURCHASEPRICE ,PERIODICITY = p_PERIODICITY,
    ANNUITYAMOUNT= p_ANNUITYAMOUNT,
    modalPremium= p_modalPremium, coverContinuanceOptionAvaile = p_coverContinuanceOptionAvaile
    where
     Application_Form_No=p_Application_Form_No and
    Product_Code=p_Product_Code;

     commit;
end;

End Login_Assistant;


Thanks in advance,
Mona
Re: Performance Tuning for a package [message #317990 is a reply to message #317923] Mon, 05 May 2008 04:20 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Quote:
as this is giving a network load


1. IMHO the package can not cause network load, because it's executed inside the db.
The multiple calls to that package issued from client may be the cause.

2. Run trace for the session and post TKPROF output.

HTH.

Re: Performance Tuning for a package [message #318182 is a reply to message #317990] Tue, 06 May 2008 01:05 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I was looking for a advice on whether the procedures written with numbers of parameters is the best way to write or if there is any other alternative as this might be giving load as there are so many parameters passed and the procedures are called many times in the application.


Thanks,
Mona
Previous Topic: Query Executing Every Second
Next Topic: Perfomance Tuning
Goto Forum:
  


Current Time: Fri Dec 09 09:57:36 CST 2016

Total time taken to generate the page: 0.11695 seconds