• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

Oracle stored procedure performance

Hi,

For below sp having a performance issue,if i am passing all parameter its work fine ,but when pass 2 or 3 parameter its goes to time out.expert plz look and provide to improve performance might be work on join."this sp is only checking duplicate records in DB"
-----------------------------------------------------------------------------

create or replace PROCEDURE SP_Duplicate

        (

        pi_meme_ssn                IN                INT,

        pi_meme_first_nm        IN                VARCHAR2,

        pi_meme_last_nm            IN                VARCHAR2,

        pi_meme_birth_dt        IN                DATE,

        pi_meme_city            IN                VARCHAR2,

        pi_meme_state            IN                VARCHAR2,

        pi_sbad_zip_cd            IN                VARCHAR2,

        pi_grgrck_no            IN                INT,

        po_return_value            OUT                VARCHAR2

        )

 

IS

 

vl_total_cnt    INT;

 

BEGIN

/* On the basis of the input parameters validation of DUPCHK process is done */

    IF    pi_meme_ssn        =    0

        AND    pi_meme_first_nm    IS    NULL

        AND    pi_meme_last_nm        IS    NULL

        AND    pi_meme_birth_dt    IS    NULL

        AND    pi_meme_city        IS    NULL

        AND    pi_meme_state        IS    NULL

        AND    pi_sbad_zip_cd        IS    NULL

        AND    pi_grgrck_no    =    0        THEN

 

        po_return_value :='N';

 

    ELSE

 

        SELECT    COUNT(1)

        INTO    vl_total_cnt

        FROM    cmc_meme_member meme,

                cmc_mepe_prcs_elig mepe,

                cmc_sbad_addr sbad

        WHERE    meme.meme_ck                =        mepe.meme_ck

        AND        mepe.mepe_elig_ind            =    'Y'

        --AND    SYSDATE BETWEEN mepe.MEPE_EFF_DT AND mepe.MEPE_TERM_DT

        AND        meme.meme_ssn                =        NVL(DECODE(pi_meme_ssn,0,NULL,pi_meme_ssn),meme.meme_ssn)

        AND        LOWER(meme.meme_first_name)    =        NVL(LOWER(pi_meme_first_nm),LOWER(meme.meme_first_name))

        AND        LOWER(meme.meme_last_name)    =        NVL(LOWER(pi_meme_last_nm),LOWER(meme.meme_last_name))

        AND        meme.meme_birth_dt            =        NVL(decode(pi_meme_birth_dt,'',NULL,pi_meme_birth_dt),meme.meme_birth_dt)

        AND        meme.grgr_ck                =        NVL(DECODE(pi_grgrck_no,0,NULL,pi_grgrck_no),meme.grgr_ck)

        AND        LOWER(sbad.sbad_city)        =        NVL(LOWER(pi_meme_city),LOWER(sbad.sbad_city))

        AND        LOWER(sbad.sbad_state)        =        NVL(LOWER(pi_meme_state),LOWER(sbad.sbad_state))

        AND        sbad.sbad_zip                =        NVL(pi_sbad_zip_cd,sbad.sbad_zip)

        AND        sbad.grgr_ck                =        meme.grgr_ck;

 

        /* If the count is greater than or equal to 1 then the output will be 'Y' that is duplicate */

        IF    vl_total_cnt    >=    1    THEN

 

            po_return_value :='Y';

 

        ELSE

 

        /* If the count is equal to 0 then the output will be 'N' that is  not a duplicate */

            po_return_value :='N';

           

        END IF;

 

    END IF;

END;
0
nicemanish
Asked:
nicemanish
  • 2
3 Solutions
 
nicemanishAuthor Commented:
1. if i am passing null for all parameter its give me timed out.
2.if passed all parameter ,sometime provide result or some time not
3.if passed some parameter its gives timeout.

plz correct my sp
0
 
flow01Commented:
If there is a timed out you probably hit bad performance full table scan because no index can be used.

check the available indexes,  provide a check to verify there  is at least one argument provided that can use an index.
Be aware that  LOWER(meme.meme_last_name)  does not use an index build on meme_last_name  (you wil need a function-based index).

You could build the query dynamically avoiding the nvl's.
declare
  n1 number;
  begin
     execute immediate 'select 1 from dual' into n1;
     dbms_output.put_line(n1);
  end;
/

declare
  v_statement varchar2(32767);
begin
  v_statement := ' select  ...  where 1 = 1'; -- your quere without the where's and the into
  if pi_meme_first_nm is not null then
    v_statement := v_statement || chr(10) ||
        '  AND        LOWER(meme.meme_first_name)    =   LOWER(meme.meme_first_name)';
  end if;
  ..  same for other arguments

  execute immediate v_statement into vl_total_cnt;
end;

or contact your dba to get rid of your time limitation  (and wait for the results to come)
0
 
PortletPaulCommented:
The comment above regarding indexes is important - please take note of that, it affects all those columns where you are applying LOWER() - however Oracle will adopt a full table scan if it requires >30% of the rows anyway - so I think performance will be dependent on table scans no matter what you  do here.

You are also then currently forcing several fields to be equal to themselves as default if a parameter is null which is avoidable by testing if the parameter is null or not a different way than through decode:
CREATE OR REPLACE PROCEDURE SP_Duplicate
                                          (
                                             pi_meme_ssn IN      INT
                                           , pi_meme_first_nm IN VARCHAR2
                                           , pi_meme_last_nm IN  VARCHAR2
                                           , pi_meme_birth_dt IN DATE
                                           , pi_meme_city IN     VARCHAR2
                                           , pi_meme_state IN    VARCHAR2
                                           , pi_sbad_zip_cd IN   VARCHAR2
                                           , pi_grgrck_no IN     INT
                                           , po_return_value OUT VARCHAR2
                                          )
IS
   vl_total_cnt INT;
BEGIN
   /* On the basis of the input parameters validation of DUPCHK process is done */
   IF pi_meme_ssn = 0
      AND pi_meme_first_nm IS NULL
      AND pi_meme_last_nm IS NULL
      AND pi_meme_birth_dt IS NULL
      AND pi_meme_city IS NULL
      AND pi_meme_state IS NULL
      AND pi_sbad_zip_cd IS NULL
      AND pi_grgrck_no = 0 THEN
      po_return_value :='N';
   ELSE
      SELECT COUNT(1)
      INTO vl_total_cnt
      FROM cmc_meme_member meme
      INNER JOIN cmc_mepe_prcs_elig mepe ON meme.meme_ck = mepe.meme_ck
      INNER JOIN cmc_sbad_addr sbad      ON meme.grgr_ck = sbad.grgr_ck
      WHERE  mepe.mepe_elig_ind = 'Y'
         --AND    SYSDATE BETWEEN mepe.MEPE_EFF_DT AND mepe.MEPE_TERM_DT
         
      AND ( pi_meme_ssn      IS NULL OR ( pi_meme_ssn      IS NOT NULL  AND meme.meme_ssn               = pi_meme_ssn  ) )
      
      AND ( pi_meme_first_nm IS NULL OR ( pi_meme_first_nm IS NOT NULL  AND LOWER(meme.meme_first_name) = LOWER(pi_meme_first_nm) ) )
      
      AND ( pi_meme_last_nm  IS NULL OR ( pi_meme_last_nm  IS NOT NULL  AND LOWER(meme.meme_last_name)  = LOWER(pi_meme_last_nm) ) )
      
      AND ( pi_meme_birth_dt IS NULL OR ( pi_meme_birth_dt IS NOT NULL  AND meme.meme_birth_dt          = pi_meme_birth_dt ) )
      
      AND ( pi_meme_city     IS NULL OR ( pi_meme_city     IS NOT NULL  AND LOWER(sbad.sbad_city)       = LOWER(pi_meme_city) ) )
      
      AND ( pi_meme_state    IS NULL OR ( pi_meme_state    IS NOT NULL  AND LOWER(sbad.sbad_state)      = LOWER(pi_meme_state) ) )
      
      AND ( pi_sbad_zip_cd   IS NULL OR ( pi_sbad_zip_cd   IS NOT NULL  AND sbad.sbad_zip               = pi_sbad_zip_cd ) )
      
      AND ( pi_grgrck_no     IS NULL OR ( pi_grgrck_no     IS NOT NULL  AND meme.grgr_ck                = pi_grgrck_no ) )
      ;
      
      /* If the count is greater than or equal to 1 then the output will be 'Y' that is duplicate */
      IF vl_total_cnt >= 1 THEN
         po_return_value :='Y';
      ELSE
         /* If the count is equal to 0 then the output will be 'N' that is  not a duplicate */
         po_return_value :='N';
      END IF;
   END IF;
END

Open in new window

note I have applied ANSI joins to simplify the where clause.

Regardless of alternative syntax. this procedure looks "expensive". It returns just one integer value (i.e. looks like a scalar function) and I suspect you may be calling it frequently (like a function) - in which case you may be doing a lot of table scans for very little reward.
0
 
nicemanishAuthor Commented:
Excellent
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now