Oracle stored procedure performance

Posted on 2013-06-14
Medium Priority
Last Modified: 2013-06-22

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





vl_total_cnt    INT;



/* 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';




        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        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';




        /* 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;

Question by:nicemanish
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

nicemanish earned 0 total points
ID: 39249603
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
LVL 20

Assisted Solution

flow01 earned 1000 total points
ID: 39249612
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.
  n1 number;
     execute immediate 'select 1 from dual' into n1;

  v_statement varchar2(32767);
  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;

or contact your dba to get rid of your time limitation  (and wait for the results to come)
LVL 49

Assisted Solution

PortletPaul earned 1000 total points
ID: 39250902
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:
                                             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
   vl_total_cnt INT;
   /* 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';
      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 ( 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';
         /* 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;

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.

Author Closing Comment

ID: 39267756

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question