[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

649 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