Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Unanswered: exaplain plan result for query tunig

    Hi All,

    below is my oracle code and i am using oracle 8i version.
    when run this query it is taking 45-65 minutes. also sometime i am getting

    ORA-01652: unable to extend temp segment by 128 in tablespace PSTEMP

    Code:
    INSERT INTO BP_CURRENT_TBL
    SELECT JOB.EMPLID,
           UPI.NATIONAL_ID   UPI,
           SSN.NATIONAL_ID   SSN,
           PD.NAME,
           PD.LAST_NAME,
           PD.FIRST_NAME,
           PD.MIDDLE_NAME,
           PD.NAME_PREFIX, 
           PD.NAME_ROYAL_PREFIX,
           PD.NAME_SUFFIX,
           PD.SEX,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.ADDRESS1,PD.ADDRESS1_OTHER) HOME_ADDRESS1,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.ADDRESS2,PD.ADDRESS2_OTHER) HOME_ADDRESS2,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.ADDRESS3,PD.ADDRESS3_OTHER) HOME_ADDRESS3,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.CITY,PD.CITY_OTHER) HOME_CITY,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.STATE,PD.STATE_OTHER) HOME_STATE,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.COUNTY,PD.COUNTY_OTHER) HOME_COUNTY,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.POSTAL,PD.POSTAL_OTHER) HOME_POSTAL,
           DECODE(LTRIM(RTRIM(PD.ADDRESS1_OTHER)),'',PD.COUNTRY,PD.COUNTRY_OTHER) HOME_COUNTRY,
           EMAIL.EMAIL_ADDR,
           JOB.EMPL_STATUS,
           JOB.DEPTID,
           JOB.COMPANY,
           CNTRY.COUNTRY_2CHAR   JOB_COUNTRY,
           CNTRY.DESCR COUNTRY_DESCR,
           DECODE(LTRIM(RTRIM(DD.DEPOSIT_TYPE)) || LTRIM(RTRIM(DD.ACCOUNT_TYPE)),'BC',DD.ACCOUNT_TYPE,'BS',DD.ACCOUNT_TYPE,''),
           DECODE(LTRIM(RTRIM(DD.DEPOSIT_TYPE)) || LTRIM(RTRIM(DD.ACCOUNT_TYPE)),'BC',DD.BANK_CD,'BS',DD.BANK_CD,''),
           DECODE(LTRIM(RTRIM(DD.DEPOSIT_TYPE)) || LTRIM(RTRIM(DD.ACCOUNT_TYPE)),'BC',DD.BRANCH_EC_CD,'BS',DD.BRANCH_EC_CD,''),
           DECODE(LTRIM(RTRIM(DD.DEPOSIT_TYPE)) || LTRIM(RTRIM(DD.ACCOUNT_TYPE)),'BC',DD.ACCOUNT_NUM,'BS',DD.ACCOUNT_NUM,'')
      FROM PS_JOB JOB,
           PS_PERSONAL_DATA PD,
           PS_PERS_NID UPI,
           PS_PERS_NID SSN,
           PS_EMAIL_ADDRESSES EMAIL,
           PS_COMPANY_TBL COMP,
           PS_DIR_DEP_DISTRIB DD,
    --     BP_COMPANY_TBL BP,
           PS_COUNTRY_TBL CNTRY
     WHERE JOB.EMPLID = PD.EMPLID
       AND JOB.EMPL_RCD = 0
       AND to_char(JOB.EFFDT,'YYYYMMDD') || to_char(JOB.EFFSEQ) = (SELECT MAX(to_char(EFFDT,'YYYYMMDD') || to_char(EFFSEQ)) 
                                                                     FROM PS_JOB JOB1
    				                                WHERE JOB1.EMPLID = JOB.EMPLID
        			                                          AND JOB1.EFFDT <= TO_DATE('&1','YYYYMMDD')
    					                          AND JOB1.EMPL_RCD = JOB.EMPL_RCD)
       AND JOB.COMPANY IN (SELECT COMPANY FROM BP_COMPANY_TBL)
       AND JOB.EMPLID = UPI.EMPLID(+)
       AND JOB.EMPLID = SSN.EMPLID(+)
       AND JOB.EMPLID = EMAIL.EMPLID(+)
       AND JOB.EMPLID = DD.EMPLID(+)
       AND JOB.EMPL_STATUS IN ('A','L','P','S')
       AND PD.PER_STATUS = 'E'
       AND EMAIL.E_ADDR_TYPE(+) = 'BUSN'
       AND UPI.NATIONAL_ID_TYPE(+) = 'UPI'
       AND UPI.COUNTRY(+) = 'ZZZ'
       AND SSN.NATIONAL_ID_TYPE(+) = 'PR'
       AND SSN.COUNTRY(+) = 'USA'
       AND JOB.COMPANY = COMP.COMPANY
       AND COMP.EFFDT = (SELECT MAX(EFFDT) 
                              FROM PS_COMPANY_TBL 
    			 WHERE COMPANY = COMP.COMPANY
    			   AND EFFDT <= TO_DATE('&1','YYYYMMDD'))
    -- AND COMP.COMPANY = BP.COMPANY
       AND COMP.COUNTRY = CNTRY.COUNTRY  
    --   AND DD.DEPOSIT_TYPE(+) = 'B'
       AND ( (DD.PRIORITY = ( SELECT MAX(PRIORITY) 
    			    FROM PS_DIR_DEP_DISTRIB DD1
    			   WHERE DD1.EMPLID = DD.EMPLID 
    			     AND DD1.EFFDT = DD.EFFDT
    		        )
    	 AND DD.EFFDT = ( SELECT MAX(EFFDT) 
    			    FROM PS_DIR_DEP_DISTRIB DD2
    			   WHERE DD2.EMPLID = DD.EMPLID
    			     AND DD2.EFFDT <= TO_DATE('&1','YYYYMMDD') 
    			) )
    	 OR DD.ROWID IS NULL );
    and below is my explain plan result
    Code:
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=90597 Card=1292 Bytes=388892)
    
       1    0   FILTER
       2    1     NESTED LOOPS (OUTER) (Cost=90597 Card=1292 Bytes=388892)
       3    2       NESTED LOOPS (Cost=88645 Card=244 Bytes=62464)
       4    3         NESTED LOOPS (OUTER) (Cost=88157 Card=244 Bytes=37576)
       5    4           NESTED LOOPS (OUTER) (Cost=87669 Card=244 Bytes=31720)
    
       6    5             HASH JOIN (Cost=87181 Card=244 Bytes=25864)
       7    6               TABLE ACCESS (FULL) OF 'PS_COUNTRY_TBL' (Cost=2 Card=242 Bytes=3872)
    
       8    6               HASH JOIN (OUTER) (Cost=87178 Card=244 Bytes=21960)
    
       9    8                 NESTED LOOPS (Cost=86968 Card=244 Bytes=11468)
    
      10    9                   HASH JOIN (Cost=86968 Card=298 Bytes=13112)
    
      11   10                     TABLE ACCESS (FULL) OF 'PS_COMPANY_TBL' (Cost=8 Card=10 Bytes=130)
    
      12   10                     TABLE ACCESS (FULL) OF 'PS_JOB' (Cost=86959 Card=8667 Bytes=268677)
    
      13    9                   INDEX (UNIQUE SCAN) OF 'BP_COMPANY_PK' (UNIQUE)
    
      14    8                 TABLE ACCESS (FULL) OF 'PS_EMAIL_ADDRESSES'(Cost=209 Card=17284 Bytes=743212)
    
      15    5             TABLE ACCESS (BY INDEX ROWID) OF 'PS_PERS_NID' (Cost=2 Card=578 Bytes=13872)
    
      16   15               INDEX (UNIQUE SCAN) OF 'PS_PERS_NID' (UNIQUE)(Cost=1 Card=578)
    
      17    4           TABLE ACCESS (BY INDEX ROWID) OF 'PS_PERS_NID' (Cost=2 Card=578 Bytes=13872)
    
      18   17             INDEX (UNIQUE SCAN) OF 'PS_PERS_NID' (UNIQUE) (Cost=1 Card=578)
    
      19    3         TABLE ACCESS (BY INDEX ROWID) OF 'PS_PERSONAL_DATA'(Cost=2 Card=438538 Bytes=44730876)
    
      20   19           INDEX (UNIQUE SCAN) OF 'PS_PERSONAL_DATA' (UNIQUE)(Cost=1 Card=438538)
    
      21    2       TABLE ACCESS (BY INDEX ROWID) OF 'PS_DIR_DEP_DISTRIB'(Cost=8 Card=784541 Bytes=35304345)
    
      22   21         INDEX (RANGE SCAN) OF 'PS_DIR_DEP_DISTRIB' (UNIQUE) (Cost=3 Card=784541)
    
      23    1     SORT (AGGREGATE)
      24   23       INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE) (Cost=3 Card=1 Bytes=19)
    
      25    1     SORT (AGGREGATE)
      26   25       INDEX (FAST FULL SCAN) OF 'PS_COMPANY_TBL' (UNIQUE) (Cost=1 Card=1 Bytes=10)
    
      27    1     SORT (AGGREGATE)
      28   27       FIRST ROW (Cost=3 Card=1 Bytes=18)
      29   28         INDEX (RANGE SCAN (MIN/MAX)) OF 'PS_DIR_DEP_DISTRIB'(UNIQUE) (Cost=3 Card=1)
    
      30    1     SORT (AGGREGATE)
      31   30       FIRST ROW (Cost=3 Card=1 Bytes=15)
      32   31         INDEX (RANGE SCAN (MIN/MAX)) OF 'PS_DIR_DEP_DISTRIB'(UNIQUE) (Cost=3 Card=1)

    can any body please help me out how to optimize the above query.
    Advance thanks,

    Thanks,
    Kki
    Last edited by kki; 01-28-14 at 07:15. Reason: still i am waiting for response

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •