Solved

Oracle stored procedure performance

Posted on 2013-06-14
5
543 Views
Last Modified: 2013-06-22
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
Comment
Question by:nicemanish
  • 2
5 Comments
 

Accepted Solution

by:
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
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 250 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.
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 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:
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
 

Author Closing Comment

by:nicemanish
ID: 39267756
Excellent
0

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.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

25 Experts available now in Live!

Get 1:1 Help Now