Oracle stored procedure performance

Posted on 2013-06-14
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 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
  • 2

Accepted Solution

nicemanish earned 0 total points
Comment Utility
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 250 total points
Comment Utility
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 48

Assisted Solution

PortletPaul earned 250 total points
Comment Utility
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

Comment Utility

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now