Forum Stats

  • 3,759,176 Users
  • 2,251,510 Discussions
  • 7,870,526 Comments

Discussions

Issue While Updating a Table periodically. Incremental Update on only updated records from Source ta

1223504
1223504 Member Posts: 10
edited Sep 8, 2014 1:19PM in SQL & PL/SQL

Hi All,

I have a requirement to populate a custom table from oracle HRMS standard table with a list of 50 different columns like Person name, Number, basic salary, PF contribution etc.

All are derived columns.

I am using Eligibility criteria to select list of employees eligible for Pension and then populating thier complete details into the Custom table.  In my pervious post Gaff had suggested to use Merge for Incremental update.

Actually i have completed the Task. It is performing incremental updated. I tested with 100 records and it worked in seconds. Now when I go for 1500 records, the performance is not as expected.  Could any one suggest me any workaroud to tackle this.

1. Get list of eligible employees into a Global temp table(takes only peson Id )

2. Execute the master view with where clause person_id in (select person_id from Global_temp_personId_tab)

3. Fetch the Cursor in BULK and insert it into another Global temp Table( takes all employee details )

4. Use merge on Destination table with source as Global Details Temp table.

For 100 records it takes 7 secs .

I have a requirement to process 5000 records. Please let me know any suggestions.... I am on a tight schedule. to complete this.

Please help on this.!

Tagged:
«1

Answers

  • Biju Das
    Biju Das Member Posts: 393
    edited Sep 7, 2014 3:28AM

    How much time it is taking? Is it the merge that is taking more time?

    Can you please post your code.

    Index on GTT can be an option..however,  you need to collect stats after loading to GTT or, may be to use index by adding a hint..but does the GTT required in your case?

    /*+ dynamic_sampling(0) index */
  • 1223504
    1223504 Member Posts: 10
    edited Sep 7, 2014 4:23AM

    hi Biju,

    It is taking 29 Minutes for 1500 persons.  Which is really odd. When I try for 500 records it is completing in 17 secs. Actually there is a call to the Eligibility Criteria package.

    chk              := BEN_PER_ASG_ELIG.ELP_ELIGIBLE(p_person_id => ln_person_id,

                                                          p_assignment_id => ln_assignment_id,

                                                          p_eligy_prfl_id => p_id1,

                                                          p_effective_date => g_sysdate,

                                                          p_pl_id => 10,

                                                          p_node_pl_id => 10,

                                                          p_business_group_id => g_business_group_id);

    First Im calling a query with this in my function and getting all persons who are eligible (CHK = Y).

    but here only when the count is more ... ie 1500+ the performance takes a hit.

    The curson im using to fetch list of eligible employees is

    CURSOR fetch_eligible_emps_cur IS

          SELECT person_id

          FROM   (SELECT papf.person_id,

                         paaf.assignment_id,

                         PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility(papf.person_id,

                                                                            paaf.assignment_id,

                                                                            p_effective_start_date) ELIGIBLE_F

                  FROM   per_all_people_F      papf,

                         per_all_assignments_f paaf

                  WHERE  papf.person_id = paaf.person_id

                         AND paaf.assignment_type = 'E'

                         AND PAPF.Current_Employee_Flag = 'Y'

                         AND PAAF.Primary_Flag = 'Y'

                         AND papf.effective_start_date <= g_sysdate

                         AND papf.effective_end_date >= g_sysdate

                         AND paaf.effective_start_date <= g_sysdate

                         AND paaf.effective_end_date >= g_sysdate)

          WHERE  ELIGIBLE_F = 'Y'

                 AND rownum < 1500;

    Then Im inserting all of these employees person id into

    OPEN fetch_eligible_emps_cur;

        LOOP

          FETCH fetch_eligible_emps_cur BULK COLLECT

            INTO lc_person_id LIMIT l_limit;

          EXIT WHEN lc_person_id.COUNT = 0;

          --- Execute the Details query and

          FORALL idx IN lc_person_id.FIRST .. lc_person_id.LAST SAVE EXCEPTIONS

          --   INSERT INTO PFPS_PF_ELIGIBLE_PERSON_GTT

            INSERT INTO temp_person_id_t

            VALUES lc_person_id

              (idx);

        END LOOP;

        COMMIT;


    Post this I have big query .... where I am using the person id from this table.


    I am executing the query .... and fetching the data into Global temp table (no index defined now....)


    After this im using the merge on the Dest table from the Global Temp Tab.


    Merge is also consuming considerable time .... but the culprit is the Eligibility check package... which goes crazy when the count is high


    MERGE INTO PFPS_PS_ELIG_EMP_DETAILS a -- destination table

          USING PFPS_PS_ELIG_EMP_DET_GTT b

          --      USING PFPS_PS_ELIG_EMP_DET_GTT b -- Source table

          --          USING pfps_pf_elig_emp_details_temp b -- Source table

          ON (A.PERSON_ID = b.PERSON_ID)

          WHEN MATCHED THEN

            UPDATE

            SET    EMPLOYEE_TYPE          = b.EMPLOYEE_TYPE,

                   BUSINESS_GROUP_ID      = b.BUSINESS_GROUP_ID,

                   BUSINESS_GROUP_NAME    = b.BUSINESS_GROUP_NAME,

                   STAFFNR                = b.STAFFNR,

                   STAFF_NAME_EN          = b.STAFF_NAME_EN,

                   STAFF_NAME_AR          = b.STAFF_NAME_AR,

                   SHORT_NAME             = b.SHORT_NAME,

                   FIRST_NAME             = b.FIRST_NAME,

                   LAST_NAME              = b.LAST_NAME,

                   MIDDLE_INITIALS        = b.MIDDLE_INITIALS,

                   ASSIGNMENT_ID          = b.ASSIGNMENT_ID,

                   TITLE                  = b.TITLE,

                   CATEGORY               = b.CATEGORY,

               

                   MARITAL_STATUS         = b.MARITAL_STATUS,

                   DOB                    = b.DOB,

                   DOJ_COMPANY            = b.DOJ_COMPANY,

                   GRADE                  = b.GRADE,

                   CCC_CODE               = b.CCC_CODE,

                   CCC_NAME               = b.CCC_NAME,

                   JOB_TITLE_CODE         = b.JOB_NAME,

                   JOB_NAME               = b.JOB_TITLE_CODE,

                   POSITION               = b.POSITION,

                   NATIONALITY            = b.NATIONALITY,

                   NATIONAL_ID_NUMBER     = b.NATIONAL_ID_NUMBER,

                   RELIGION_CODE          = b.RELIGION_CODE,

                   CONT_START             = b.CONT_START,

                   NOTICE_STARTDT         = b.NOTICE_STARTDT,

                   NOTICE_ENDDT           = b.NOTICE_ENDDT,

                   TERM_DATE              = b.TERM_DATE,

                   CONTRACTUAL_LDS        = b.CONTRACTUAL_LDS,

                   LDO                    = b.LDO,

                   TERM_CODE              = b.TERM_CODE,

                   TERM_REASON            = b.TERM_REASON,

                   TERM_PROCYM            = b.TERM_PROCYM,

                   RESIDENTIAL_ADDR       = b.RESIDENTIAL_ADDR,

                   EMAIL_ADDR             = b.EMAIL_ADDR,

                   PENSION_ENROLMENT_Date = b.pension_enrolment_date,

                   PENSION_SCHEME         = b.pension_scheme,

                   BASIC_SALARY           = b.BASIC_SALARY,

                   ACC_ALWNC              = b.ACC_ALWNC,

                   OTHER_ALWNC            = b.OTHER_ALWNC,

                   TOTAL_SAL              = b.TOTAL_SAL,

                   EOS_SAL                = b.eos_sal,

                   EMPLOYEE_SHARE         = b.employee_share,

                   EMPLOYER_SHARE         = b.employer_share,

                   TOTAL_SHARE            = b.total_share,

                   UAE_GCC                = b.uae_gcc,

                   CREATED_DATE           = SYSDATE,

                   CREATED_BY             = -1,

                   LAST_UPDATE_DATE       = SYSDATE,

                   LAST_UPDATED_BY        = -1,

                   DELIVERY_STATUS        = 'N'

          WHEN NOT MATCHED THEN

            INSERT

              (PERSON_ID,

               EMPLOYEE_TYPE,

               BUSINESS_GROUP_ID,

               BUSINESS_GROUP_NAME,

               STAFFNR,

               STAFF_NAME_EN,

               STAFF_NAME_AR,

               SHORT_NAME,

               FIRST_NAME,

               LAST_NAME,

               MIDDLE_INITIALS,

               ASSIGNMENT_ID,

               TITLE,

               CATEGORY,        

               MARITAL_STATUS,

               DOB,

               DOJ_COMPANY,

               GRADE,

               CCC_CODE,

               CCC_NAME,

               JOB_TITLE_CODE,

               JOB_NAME,

               POSITION,

               NATIONALITY,

               NATIONAL_ID_NUMBER,

               RELIGION_CODE,

               CONT_START,

               NOTICE_STARTDT,

               NOTICE_ENDDT,

               TERM_DATE,

               CONTRACTUAL_LDS,

               LDO,

               TERM_CODE,

               TERM_REASON,

               TERM_PROCYM,

               PENSION_ENROLMENT_DATE,

               PENSION_SCHEME,

               BASIC_SALARY,

               ACC_ALWNC,

               OTHER_ALWNC,

               TOTAL_SAL,

               EOS_SAL,

               EMPLOYEE_SHARE,

               EMPLOYER_SHARE,

               TOTAL_SHARE,

               UAE_GCC,

               RESIDENTIAL_ADDR,

               EMAIL_ADDR,

               CREATED_DATE,

               CREATED_BY,

               LAST_UPDATE_DATE,

               LAST_UPDATED_BY,

               DELIVERY_STATUS)

            VALUES

              (b.PERSON_ID,

               b.EMPLOYEE_TYPE,

               b.BUSINESS_GROUP_ID,

               b.BUSINESS_GROUP_NAME,

               b.STAFFNR,

               b.STAFF_NAME_EN,

               b.STAFF_NAME_AR,

               b.SHORT_NAME,

               b.FIRST_NAME,

               b.LAST_NAME,

               b.MIDDLE_INITIALS,

               b.ASSIGNMENT_ID,

               b.TITLE,

               b.CATEGORY,         

               b.MARITAL_STATUS,

               b.DOB,

               b.DOJ_COMPANY,

               b.GRADE,

               b.CCC_CODE,

               b.CCC_NAME,

               b.JOB_TITLE_CODE,

               b.JOB_NAME,

               b.POSITION,

               b.NATIONALITY,

               b.NATIONAL_ID_NUMBER,

               b.RELIGION_CODE,

               b.CONT_START,

               b.NOTICE_STARTDT,

               b.NOTICE_ENDDT,

               b.TERM_DATE,

               b.CONTRACTUAL_LDS,

               b.LDO,

               b.TERM_CODE,

               b.TERM_REASON,

               b.TERM_PROCYM,

               b.PENSION_ENROLMENT_DATE,

               b.PENSION_SCHEME,

               b.BASIC_SALARY,

               b.ACC_ALWNC,

               b.OTHER_ALWNC,

               b.TOTAL_SAL,

               b.EOS_SAL,

               b.EMPLOYEE_SHARE,

               b.EMPLOYER_SHARE,

               b.TOTAL_SHARE,

               b.UAE_GCC,

               b.RESIDENTIAL_ADDR,

               b.EMAIL_ADDR,

               SYSDATE,

               FND_GLOBAL.USER_ID,

               SYSDATE,

               FND_GLOBAL.USER_ID,

               'N');


    NOte : /*+ dynamic_sampling(0) index */  I am using  PRAGMA SERIALLY_REUSABLE;


    Please help....



    Thanks

    ANtony



  • Biju Das
    Biju Das Member Posts: 393
    edited Sep 8, 2014 1:19PM
    1223504 wrote:
    
    Merge is also consuming considerable time .... but the culprit is the Eligibility check package... which goes crazy when the count is high

    How much time it is taking for each call to Eligibility check ? You can monitor the performance for each call to the package by logging the time just before and after the call.

    DECLARE

    ...

    BEGIN

    /* get the timestamp say t0 */

    l_var:=PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility(1001

                                                                                   12,

                                                                                  sysdate);

    /* get the time stamp say t1 and take the difference of t1 and t0 */

    ...

    END;

        SELECT    papf.person_id
                  FROM   per_all_people_F      papf,
                         per_all_assignments_f paaf
                  WHERE  papf.person_id = paaf.person_id
                         AND paaf.assignment_type = 'E'
                         AND PAPF.Current_Employee_Flag = 'Y'
                         AND PAAF.Primary_Flag = 'Y'
                         AND papf.effective_start_date <= g_sysdate
                         AND papf.effective_end_date >= g_sysdate
                         AND paaf.effective_start_date <= g_sysdate
                         AND paaf.effective_end_date >= g_sysdate)     /* WHERE */     
                         AND PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility(papf.person_id,                                                                         paaf.assignment_id,                                                                         p_effective_start_date) = 'Y';

    There are two packges I can see in your code.

    BEN_PER_ASG_ELIG.ELP_ELIGIBLE

    PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility.

    You need to investigate the time for each call. If there is performance bottleneck then it needs to be tuned.We have no visibility to your package code.

    ED: /* commented: - where */

  • 1223504
    1223504 Member Posts: 10

    For 999 Records

    Total Records fetched is 999

    TOTAL TIME FOR PERSON_ID FETCH        : 12974 hsecs

    no of personid  inserted in GTT : 999

    Program -- POPULATE_PF_ELG_GTT_P completed successfully

  • Biju Das
    Biju Das Member Posts: 393
    edited Sep 7, 2014 5:14AM

    That indicates to process 999 records it is taking around 12974 millisecond?

    1. How much time it is taken for each call to the calling package PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility?

    2. I have no idea where the other package is being called in your code. Where this package is being called?

    3. What is the plan output of the above sql you mentioned in cursor ? What are the indexes in the tables used?

    4. How much time it is taking for 999 records when you ran the sql ?

    Regards

    Biju

  • 1223504
    1223504 Member Posts: 10

    Sorry for the trouble.

    1. I am using this PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility.

    2. Inside this function im calling BEN_PER_ASG_ELIG.ELP_ELIGIBLE to get it an employee is eligible -> this function will return Y or N

    3. I am fetchin all the records with Eligible flag as 'Y' into a temp table Person_list_table. For 2899 employees it is taking 4mins and 36 secs

    4. Post this I am using the query to fetch all details abt employees using the below query.

    SELECT papf.PERSON_ID,

                 DECODE(USER_PERSON_TYPE, 'Employee and Applicant', 'S',

                        'Permanent Part-Timer', 'S', 'Employee', 'S', 'Ex-employee', 'X',

                        'Ex-employee and Applicant', 'X', 'T') EMPLOYEE_TYPE,

                 PAPF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getBGName_F(PAAF.business_group_id), 0,

                        200) BUSINESS_GROUP_NAME,

                 PAPF.EMPLOYEE_NUMBER STAFFNR,

                 PAPF.FULL_NAME STAFF_NAME_EN,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getPersonNameInAraChar_f(PAPF.EMPLOYEE_NUMBER),

                        0, 200) STAFF_NAME_AR,

                 PAPF.KNOWN_AS SHORT_NAME,

                 PAPF.FIRST_NAME FIRST_NAME,

                 PAPF.LAST_NAME LAST_NAME,

                 PAPF.MIDDLE_NAMES MIDDLE_INITIALS,

                 paaf.assignment_id,

                 SUBSTR(PAPF.TITLE, 1, 5) TITLE,

                 PAAF.Assignment_Category CATEGORY,

                           SUBSTR(PAPF.MARITAL_STATUS, 1, 30) MARITAL_STATUS,

                 PAPF.DATE_OF_BIRTH DOB,

                 PPOS.DATE_START DOJ_COMPANY,

                 SUBSTR(PG.NAME, 1, 5) GRADE,

                 SUBSTR(ORG.ATTRIBUTE1, 1, 5) CCC_CODE,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getCCCName(PAPF.PERSON_ID), 0, 200) CCC_NAME,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getJobName_F(paaf.job_id), 0, 200) JOB_NAME,

                 SUBSTR(PFPS_PKG_PF_ELIGIBILITY.getJobTitle_F(PAAF.JOB_ID), 1, 4) JOB_TITLE_CODE,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getPosition(paaf.position_id), 0, 100) Position,

                 PAPF.NATIONALITY NATIONALITY,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getNationalID_f(PAAF.assignment_id), 0,

                        200) NATIONAL_ID_NUMBER,

                 PAPF.ATTRIBUTE10 RELIGION_CODE,

                 PPOS.ATTRIBUTE2 CONT_START, -- Need to check

                 PPOS.NOTIFIED_TERMINATION_DATE NOTICE_STARTDT,

                 PPOS.PROJECTED_TERMINATION_DATE NOTICE_ENDDT,

                 PFPS_PKG_PF_ELIGIBILITY.getActualTermDate_f(PAPF.PERSON_ID) TERM_DATE,

                 :g_sysdate + 2 CONTRACTUAL_LDS, -- NEEd to check

                 PFPS_PKG_PF_ELIGIBILITY.getLDOdate_f(papf.employee_number, :g_sysdate) LDO, -- NEED TO WRITE

                 decode(PFPS_PKG_PF_ELIGIBILITY.getDisplayedTermDate_f(LEAST(nvl(to_date(substr(PPOS.ATTRIBUTE2,

                                                                                                1,

                                                                                                10),

                                                                                         'YYYY/MM/DD'),

                                                                                 to_date('31-12-4712',

                                                                                          'DD-MM-YYYY')),

                                                                             ppos.actual_termination_date)),

                        NULL, NULL, SUBSTR(PPOS.LEAVING_REASON, 1, 30)) TERM_CODE,

                 ' TERM_REASONS' TERM_REASON,

                 decode(PFPS_PKG_PF_ELIGIBILITY.getDisplayedTermDate_f(LEAST(nvl(to_date(substr(PPOS.ATTRIBUTE2,

                                                                                                1,

                                                                                                10),

                                                                                         'YYYY/MM/DD'),

                                                                                 to_date('31-12-4712',

                                                                                          'DD-MM-YYYY')),

                                                                             ppos.actual_termination_date)),

                       

                        NULL, NULL,

                        SUBSTR(TO_CHAR(PPOS.LAST_STANDARD_PROCESS_DATE, 'YYYYMM'), 1, 6)) TERM_PROCYM,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getPersonAddress_F('CRD', papf.person_id),

                        0, 200) RESIDENTIAL_ADDR,

                 papf.email_address AS email_addr,

                 :Var1 AS PENSION_ENROLMENT_DATE,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getPensionScheme_f(paaf.assignment_id,

                                                                   :g_sysdate), 0, 30) AS PENSION_SCHEME,

                 PFPS_PKG_PF_ELIGIBILITY.getPersonSalaryElementsDet_F(papf.employee_number,

                                                                      'Basic_Salary',

                                                                      papf.nationality) Basic_Salary,

                 PFPS_PKG_PF_ELIGIBILITY.getPersonSalaryElementsDet_F(papf.employee_number,

                                                                      'Utilities_Allowance',

                                                                      papf.nationality) ACC_ALWNC,

                 PFPS_PKG_PF_ELIGIBILITY.getPersonSalaryElementsDet_F(papf.employee_number,

                                                                      'Other_Allowance',

                                                                      papf.nationality) OTHER_ALWNC,

                 PFPS_PKG_PF_ELIGIBILITY.getPersonSalaryElementsDet_F(papf.employee_number,

                                                                      'Total_Salary',

                                                                      papf.nationality) AS TOTAL_SAL,

                 0 AS EOS_SAL,

                 PFPS_PKG_PF_ELIGIBILITY.getpensionshare_f('Employee_C',

                                                           papf.nationality, :g_sysdate) AS EMPLOYEE_SHARE,

                 PFPS_PKG_PF_ELIGIBILITY.getpensionshare_f('Employer_C',

                                                           papf.nationality, :g_sysdate) AS EMPLOYER_SHARE,

                 PFPS_PKG_PF_ELIGIBILITY.getpensionshare_f('Total_C', papf.nationality,

                                                           :g_sysdate) AS TOTAL_SHARE,

                 substr(PFPS_PKG_PF_ELIGIBILITY.getNationalityonDate_f(:g_business_group_id,

                                                                       paaf.assignment_id,

                                                                       :g_sysdate), 0, 10) AS UAE_GCC

          FROM   PAY_ALL_PAYROLLS_F        PAP,

                 HR_ALL_ORGANIZATION_UNITS ORG,

                 PER_GRADES                PG,

                 PER_PERSON_TYPES          PPT,

                 --  PAY_PEOPLE_GROUPS         ppg,

                 PER_PERSON_TYPE_USAGES_F PPTUF,

                 HR_LOCATIONS_ALL         HLA,

                 PER_ALL_ASSIGNMENTS_F    paaf,

                 PER_ALL_PEOPLE_F         PAPF,

                 PER_PERIODS_OF_SERVICE   PPOS

          WHERE  PAP.PAYROLL_ID(+) = PAAF.PAYROLL_ID

                 AND ORG.BUSINESS_GROUP_ID = PAAF.BUSINESS_GROUP_ID

                 AND ORG.ORGANIZATION_ID = PAAF.ORGANIZATION_ID

                --  AND PPG.PEOPLE_GROUP_ID = PAAF.PEOPLE_GROUP_ID

                 AND PG.GRADE_ID(+) = PAAF.GRADE_ID

                 AND PPOS.PERSON_ID = PAPF.PERSON_ID

                 AND HLA.LOCATION_ID(+) = PAAF.LOCATION_ID

                 AND PAAF.BUSINESS_GROUP_ID = PAPF.BUSINESS_GROUP_ID

                 AND PAAF.PERSON_ID = PAPF.PERSON_ID

                 AND PPT.BUSINESS_GROUP_ID = PAPF.BUSINESS_GROUP_ID

                 AND PPT.PERSON_TYPE_ID = PPTUF.PERSON_TYPE_ID

                 AND PPTUF.PERSON_ID = PAPF.PERSON_ID

                 AND PAAF.EFFECTIVE_START_DATE <=

                 NVL(PPOS.ACTUAL_TERMINATION_DATE, :g_sysdate)

                 AND PAAF.EFFECTIVE_END_DATE >=

                 NVL(PPOS.ACTUAL_TERMINATION_DATE, :g_sysdate)

                 AND PPTUF.EFFECTIVE_START_DATE <= :g_sysdate

                 AND PPTUF.EFFECTIVE_END_DATE >= :g_sysdate

                 AND PAPF.EFFECTIVE_START_DATE <= :g_sysdate

                 AND PAPF.EFFECTIVE_END_DATE >= :g_sysdate

                 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = 1

                 AND PAAF.PRIMARY_FLAG = 'Y'

                 AND PPT.SYSTEM_PERSON_TYPE LIKE '%EMP%'

                 AND PPT.ACTIVE_FLAG = 'Y'

                 AND PAPF.EMPLOYEE_NUMBER IS NOT NULL

                 AND USER_PERSON_TYPE IN ('Employee and Applicant', 'Employee')

                 AND papf.person_id IN (SELECT person_id

                                        --     FROM   PFPS_PF_ELIGIBLE_PERSON_GTT);

                                        FROM   temp_person_id_t)

    This is a costly query ...

    Cost.jpg

    5. I am collecting the query output and bulk inserting it into a Global Temp table(NO INDEX defined as of now for both source and destination tables)

    6. Then I am using merge on dest table and this global temp Table.

    NO INDEX defined as of now for both source and destination tables

    MERGE INTO PFPS_PS_ELIG_EMP_DETAILS a -- destination table

          USING PFPS_PS_ELIG_EMP_DET_GTT b

          --      USING PFPS_PS_ELIG_EMP_DET_GTT b -- Source table

          --          USING pfps_pf_elig_emp_details_temp b -- Source table

          ON (A.PERSON_ID = b.PERSON_ID)

          WHEN MATCHED THEN

            UPDATE

            SET    EMPLOYEE_TYPE          = b.EMPLOYEE_TYPE,

                   BUSINESS_GROUP_ID      = b.BUSINESS_GROUP_ID,

                   BUSINESS_GROUP_NAME    = b.BUSINESS_GROUP_NAME,

                   STAFFNR                = b.STAFFNR,

                   STAFF_NAME_EN          = b.STAFF_NAME_EN,

                   STAFF_NAME_AR          = b.STAFF_NAME_AR,

                   SHORT_NAME             = b.SHORT_NAME,

                   FIRST_NAME             = b.FIRST_NAME,

                   LAST_NAME              = b.LAST_NAME,

                   MIDDLE_INITIALS        = b.MIDDLE_INITIALS,

                   ASSIGNMENT_ID          = b.ASSIGNMENT_ID,

                   TITLE                  = b.TITLE,

                   CATEGORY               = b.CATEGORY,

                   MARITAL_STATUS         = b.MARITAL_STATUS,

                   DOB                    = b.DOB,

                   DOJ_COMPANY            = b.DOJ_COMPANY,

                   GRADE                  = b.GRADE,

                   CCC_CODE               = b.CCC_CODE,

                   CCC_NAME               = b.CCC_NAME,

                   JOB_TITLE_CODE         = b.JOB_NAME,

                   JOB_NAME               = b.JOB_TITLE_CODE,

                   POSITION               = b.POSITION,

                   NATIONALITY            = b.NATIONALITY,

                   NATIONAL_ID_NUMBER     = b.NATIONAL_ID_NUMBER,

                   RELIGION_CODE          = b.RELIGION_CODE,

                   CONT_START             = b.CONT_START,

                   NOTICE_STARTDT         = b.NOTICE_STARTDT,

                   NOTICE_ENDDT           = b.NOTICE_ENDDT,

                   TERM_DATE              = b.TERM_DATE,

                   CONTRACTUAL_LDS        = b.CONTRACTUAL_LDS,

                   LDO                    = b.LDO,

                   TERM_CODE              = b.TERM_CODE,

                   TERM_REASON            = b.TERM_REASON,

                   TERM_PROCYM            = b.TERM_PROCYM,

                   RESIDENTIAL_ADDR       = b.RESIDENTIAL_ADDR,

                   EMAIL_ADDR             = b.EMAIL_ADDR,

                   PENSION_ENROLMENT_Date = b.pension_enrolment_date,

                   PENSION_SCHEME         = b.pension_scheme,

                   BASIC_SALARY           = b.BASIC_SALARY,

                   ACC_ALWNC              = b.ACC_ALWNC,

                   OTHER_ALWNC            = b.OTHER_ALWNC,

                   TOTAL_SAL              = b.TOTAL_SAL,

                   EOS_SAL                = b.eos_sal,

                   EMPLOYEE_SHARE         = b.employee_share,

                   EMPLOYER_SHARE         = b.employer_share,

                   TOTAL_SHARE            = b.total_share,

                   UAE_GCC                = b.uae_gcc,

                   CREATED_DATE           = SYSDATE,

                   CREATED_BY             = -1,

                   LAST_UPDATE_DATE       = SYSDATE,

                   LAST_UPDATED_BY        = -1,

                   DELIVERY_STATUS        = 'N'

          WHEN NOT MATCHED THEN

            INSERT

              (PERSON_ID,

               EMPLOYEE_TYPE,

               BUSINESS_GROUP_ID,

               BUSINESS_GROUP_NAME,

               STAFFNR,

               STAFF_NAME_EN,

               STAFF_NAME_AR,

               SHORT_NAME,

               FIRST_NAME,

               LAST_NAME,

               MIDDLE_INITIALS,

               ASSIGNMENT_ID,

               TITLE,

               CATEGORY,

              MARITAL_STATUS,

               DOB,

               DOJ_COMPANY,

               GRADE,

               CCC_CODE,

               CCC_NAME,

               JOB_TITLE_CODE,

               JOB_NAME,

               POSITION,

               NATIONALITY,

               NATIONAL_ID_NUMBER,

               RELIGION_CODE,

               CONT_START,

               NOTICE_STARTDT,

               NOTICE_ENDDT,

               TERM_DATE,

               CONTRACTUAL_LDS,

               LDO,

               TERM_CODE,

               TERM_REASON,

               TERM_PROCYM,

               PENSION_ENROLMENT_DATE,

               PENSION_SCHEME,

               BASIC_SALARY,

               ACC_ALWNC,

               OTHER_ALWNC,

               TOTAL_SAL,

               EOS_SAL,

               EMPLOYEE_SHARE,

               EMPLOYER_SHARE,

               TOTAL_SHARE,

               UAE_GCC,

               RESIDENTIAL_ADDR,

               EMAIL_ADDR,

               CREATED_DATE,

               CREATED_BY,

               LAST_UPDATE_DATE,

               LAST_UPDATED_BY,

               DELIVERY_STATUS)

            VALUES

              (b.PERSON_ID,

               b.EMPLOYEE_TYPE,

               b.BUSINESS_GROUP_ID,

               b.BUSINESS_GROUP_NAME,

               b.STAFFNR,

               b.STAFF_NAME_EN,

               b.STAFF_NAME_AR,

               b.SHORT_NAME,

               b.FIRST_NAME,

               b.LAST_NAME,

               b.MIDDLE_INITIALS,

               b.ASSIGNMENT_ID,

               b.TITLE,

               b.CATEGORY,

                       b.MARITAL_STATUS,

               b.DOB,

               b.DOJ_COMPANY,

               b.GRADE,

               b.CCC_CODE,

               b.CCC_NAME,

               b.JOB_TITLE_CODE,

               b.JOB_NAME,

               b.POSITION,

               b.NATIONALITY,

               b.NATIONAL_ID_NUMBER,

               b.RELIGION_CODE,

               b.CONT_START,

               b.NOTICE_STARTDT,

               b.NOTICE_ENDDT,

               b.TERM_DATE,

               b.CONTRACTUAL_LDS,

               b.LDO,

               b.TERM_CODE,

               b.TERM_REASON,

               b.TERM_PROCYM,

               b.PENSION_ENROLMENT_DATE,

               b.PENSION_SCHEME,

               b.BASIC_SALARY,

               b.ACC_ALWNC,

               b.OTHER_ALWNC,

               b.TOTAL_SAL,

               b.EOS_SAL,

               b.EMPLOYEE_SHARE,

               b.EMPLOYER_SHARE,

               b.TOTAL_SHARE,

               b.UAE_GCC,

               b.RESIDENTIAL_ADDR,

               b.EMAIL_ADDR,

               SYSDATE,

               FND_GLOBAL.USER_ID,

               SYSDATE,

               FND_GLOBAL.USER_ID,

               'N');

    My aim is to complete this program withing 15 mins.

    Time taken for the merge ill update you shortly.

    Any suggestion is welcome.

  • Biju Das
    Biju Das Member Posts: 393
    edited Sep 7, 2014 7:00AM

    1. How much time it is taking for the entire update into the target table from source?

    2. PFPS_PS_ELIG_EMP_DETAILS is a regular table ? An index on person_id should give better results.

    3. PFPS_PKG_PF_ELIGIBILITY.get_ps_profile_eligibility.

    Check if there is a scope for improvement in this package. I assume there should be. You can log the time at certain steps and try to find the performance bottleneck ( I assume statistics is up to date).


    Regards

    Biju


    1223504
  • Gaff
    Gaff Member Posts: 1,490

    I would think that you want an index on the destination table.  The reason I don't think you want one on the source is that you are potentially merging every one of those records into the destination so you are going to look at every record anyway.  A full table scan is probably the best way to go about it.  But on a large destination table you want to find the record to update in the most efficient way possible and I would think an index would be key to that.

    I suspect your package function calls are slowing things down speed-wise. I see others have asked about their timing as well. 

    You might also look into parallel execution but again, the function calls are going to complicate that approach.

    1223504
  • Gaff
    Gaff Member Posts: 1,490
    Actually i have completed the Task. It is performing incremental updated. I tested with 100 records and it worked in seconds. Now when I go for 1500 records, the performance is not as expected.  Could any one suggest me any workaroud to tackle this.
    
    

    I'm sure you are curious as to why the 1500 takes so long (and I suspect there is a resource problem of some kind so you might check into locks being generated) but worst case, break your data to be merged into chunks of several hundred records and run them one after another    I know, it's a cop out as far as finding out the "why" of your current query, but if you don't figure it out at least you have a work around approach.

  • Gaff
    Gaff Member Posts: 1,490

    Incidentally, you probably don't need the GTT at all.  I don't think it's your performance problem because it looks like you are only merging a small # of records in any case.  But just FYI, take a look at the 2nd merge example here.  It is a very simple example that just shows where you would put your GTT loading query.

This discussion has been closed.