4 Replies Latest reply: Feb 17, 2014 11:21 PM by Karthick_Arp RSS

    Function with a cursor

    user1980

      I see functions using cursors to return single row and this function is called from a SQL query with million of records.

       

      example:

       

      [code]

       

      FUNCTION getVal(i_no NUMBER) RETURN varchar2

      as

         CURSOR cur(i_no NUMBER)

         AS

           SELECT nm from table where num = i_ num;

      v_val;

      begin

              OPEN cur(i_no)

                PROCESS

          CLOSE cur;

      return v_val;

       

      end;

       

      The above function is is used in a SQL query like :

       

      SELECT col1, col2,col3 where table1, table 2 .....and getVal(table1.num) = '30';

       

      [/code]

       

      The above query was taking time to execute and  had to be performance improved, so I re-wrote the function as follows:

       

      [code]

      FUNCTION getVal(i_no NUMBER) RETURN varchar2

      as

      v_val;

      begin

         BEGIN

         SELECT nm INTO v_val from table where num = i _no;

        exception when no_data_found then

              v_val='#';

        end;

       

      return v_val;

       

      end;

      [/code]

       

      But i see that after I re-wrote the function with Native SQL, I see that the performance of the query has worsened, It is taking longer time in execution.

       

      I thought removing the cursor and using Native SQL would improve performance. Please comment.

       

      Thanks,

      S

        • 1. Re: Function with a cursor
          rp0428
          I thought removing the cursor and using Native SQL would improve performance. Please comment.

          If you want to improve performance remove the function and just incorporate the logic in the SQL statement.

           

          Don't use PL/SQL if SQL can do the job.

          • 2. Re: Function with a cursor
            Frank Kulash

            Hi,

            user1980 wrote:

             

            I see functions using cursors to return single row and this function is called from a SQL query with million of records.

             

            example:

             

            [code]

             

            FUNCTION getVal(i_no NUMBER) RETURN varchar2

            as

               CURSOR cur(i_no NUMBER)

               AS

                 SELECT nm from table where num = i_ num;

            v_val;

            begin

                    OPEN cur(i_no)

                      PROCESS

                CLOSE cur;

            return v_val;

             

            end;

             

            The above function is is used in a SQL query like :

             

            SELECT col1, col2,col3 where table1, table 2 .....and getVal(table1.num) = '30';

             

            [/code]

             

            The above query was taking time to execute and  had to be performance improved, so I re-wrote the function as follows:

             

            [code]

            FUNCTION getVal(i_no NUMBER) RETURN varchar2

            as

            v_val;

            begin

               BEGIN

               SELECT nm INTO v_val from table where num = i _no;

              exception when no_data_found then

                    v_val='#';

              end;

             

            return v_val;

             

            end;

            [/code]

             

            But i see that after I re-wrote the function with Native SQL, I see that the performance of the query has worsened, It is taking longer time in execution.

             

            I thought removing the cursor and using Native SQL would improve performance. Please comment.

             

            Thanks,

            S

            I'm having trouble believing that this is the function you're actually running.  For one thing, table is not a valid table name in Oracle, and the parameter the cursor (i_no) is not used anywhere in the cursor.

             

            What is your revised, native SQL version of the function?

             

            Without seeing either the original version, or the revised version, I can't say much about them.


            • 3. Re: Function with a cursor
              user1980

              Here you go:

               

              Original function with cursor:

              [CODE]

               

                 FUNCTION selcritvalue (

                    i_prstt_no       IN   VARCHAR2

                   ,i_ref_prstt_no   IN   VARCHAR2

                   ,i_prsot_type     IN   VARCHAR2

                   ,i_psob_no        IN   VARCHAR2

                   ,i_prsval_no      IN   VARCHAR2

                   ,i_cty_code       IN   VARCHAR2 := gen_const.c_defchar

                   ,i_lang_code      IN   VARCHAR2 := 'GB'

                   ,i_status         IN   VARCHAR2 := 'APPROVED'

                   ,i_valid_at       IN   VARCHAR2 := TO_CHAR (SYSDATE, 'yyyymmdd')

                 )

                    RETURN VARCHAR2

                 IS

                    CURSOR c_selcritvalue (

                       ci_prstt_no     IN   VARCHAR2

                      ,ci_prsot_type   IN   VARCHAR2

                      ,ci_prsval_no    IN   VARCHAR2

                    )

                    IS

                       SELECT prsop_no, prsop_method, prsop_level, prsop_attr

                             ,attr_prsot_type, prodt_no, prodt_char

                       FROM   prsop_wxo

                       WHERE  prstt_no = ci_prstt_no

                       AND    prsot_type = ci_prsot_type

                       AND    prsval_no = ci_prsval_no

                       AND    prsop_type = 'SHOW';

               

                    p_prsop_method      prsop_wxo.prsop_method%TYPE;

                    p_prsop_level       prsop_wxo.prsop_level%TYPE;

                    p_prsop_attr        prsop_wxo.prsop_attr%TYPE;

                    p_attr_prsot_type   prsop_wxo.attr_prsot_type%TYPE;

                    p_prsop_no          prsop_wxo.prsop_no%TYPE;

                    p_prodt_no          prsop_wxo.prodt_no%TYPE;

                    p_prodt_char        prsop_wxo.prodt_char%TYPE;

                 BEGIN

                    -- Get defintion of corresponding value.

                    OPEN c_selcritvalue (i_prstt_no, i_prsot_type, i_prsval_no);

               

                    FETCH c_selcritvalue

                    INTO  p_prsop_no, p_prsop_method, p_prsop_level, p_prsop_attr

                         ,p_attr_prsot_type, p_prodt_no, p_prodt_char;

               

                    IF (c_selcritvalue%NOTFOUND) THEN

                       CLOSE c_selcritvalue;

               

                       RETURN ('<Not Defined>');

                    END IF;

               

                    CLOSE c_selcritvalue;

               

                    -- Get the actual values

                    RETURN (NVL (getactvalue (NVL (i_ref_prstt_no, i_prstt_no)

                                             ,i_prsot_type, i_psob_no, p_prsop_no

                                             ,p_prsop_method, p_prsop_level, p_prsop_attr

                                             ,p_attr_prsot_type, p_prodt_no, p_prodt_char

                                             ,i_cty_code, i_lang_code, i_status, i_valid_at)

                                ,' ')

                           );

                 END;

               

              [/CODE]

               

              The above funciton I re-wrote as Native SQL

               

              [CODE]

               

                 FUNCTION selcritvalue (

                    i_prstt_no       IN   VARCHAR2

                   ,i_ref_prstt_no   IN   VARCHAR2

                   ,i_prsot_type     IN   VARCHAR2

                   ,i_psob_no        IN   VARCHAR2

                   ,i_prsval_no      IN   VARCHAR2

                   ,i_cty_code       IN   VARCHAR2 := gen_const.c_defchar

                   ,i_lang_code      IN   VARCHAR2 := 'GB'

                   ,i_status         IN   VARCHAR2 := 'APPROVED'

                   ,i_valid_at       IN   VARCHAR2 := TO_CHAR (SYSDATE, 'yyyymmdd')

                 )

                    RETURN VARCHAR2

                 IS

                    p_prsop_method      prsop_wxo.prsop_method%TYPE;

                    p_prsop_level       prsop_wxo.prsop_level%TYPE;

                    p_prsop_attr        prsop_wxo.prsop_attr%TYPE;

                    p_attr_prsot_type   prsop_wxo.attr_prsot_type%TYPE;

                    p_prsop_no          prsop_wxo.prsop_no%TYPE;

                    p_prodt_no          prsop_wxo.prodt_no%TYPE;

                    p_prodt_char        prsop_wxo.prodt_char%TYPE;

                   

                    no_data_found    BOOLEAN;

              BEGIN

                

                 BEGIN

                    no_data_found := FALSE;

                    SELECT prsop_no

                         , prsop_method

                         , prsop_level

                         , prsop_attr

                         , attr_prsot_type

                         , prodt_no

                         , prodt_char

                      INTO  p_prsop_no

                          , p_prsop_method

                          , p_prsop_level

                          , p_prsop_attr

                          , p_attr_prsot_type

                          , p_prodt_no

                          , p_prodt_char

                      FROM prsop_wxo

                     WHERE prstt_no = i_prstt_no

                       AND prsot_type = i_prsot_type

                       AND prsval_no = i_prsval_no

                       AND prsop_type = 'SHOW';

                   EXCEPTION

                   WHEN NO_DATA_FOUND THEN

                        no_data_found := TRUE;

                   END;    

                

                     IF ( no_data_found )

                     THEN

                         RETURN ('<Not Defined>');

                     ELSE      

                        -- Get the actual values

                        RETURN (NVL (getactvalue (NVL (i_ref_prstt_no, i_prstt_no)

                                             ,i_prsot_type, i_psob_no, p_prsop_no

                                             ,p_prsop_method, p_prsop_level, p_prsop_attr

                                             ,p_attr_prsot_type, p_prodt_no, p_prodt_char

                                             ,i_cty_code, i_lang_code, i_status, i_valid_at)

                                ,' ')

                           );

                      END IF;    

              END;

               

              [/CODE]

              • 4. Re: Function with a cursor
                Karthick_Arp

                Can you quantify your performance, Do you have numbers? How did you determine the Native SQL is the one that's causing the problem. Can it be the function GETACTVALUE?

                 

                For a starter you can use DBMS_PROFILER to exactly know where the time is being spend in your function. Next you can enable SQL_TRACE and use TKPROF to know what you SQL is doing.

                 

                Please read When your query takes too long ...

                 

                Also i did some change to your function. This would make it looks simple.

                 

                function selcritvalue                 
                (                                     
                      i_prstt_no       in   varchar2  
                     ,i_ref_prstt_no   in   varchar2  
                     ,i_prsot_type     in   varchar2  
                     ,i_psob_no        in   varchar2  
                     ,i_prsval_no      in   varchar2  
                     ,i_cty_code       in   varchar2 := gen_const.c_defchar
                     ,i_lang_code      in   varchar2 := 'GB'
                     ,i_status         in   varchar2 := 'APPROVED'
                     ,i_valid_at       in   varchar2 := TO_CHAR (SYSDATE, 'yyyymmdd')
                )                                     
                      return varchar2                 
                is
                      p_prsop_method      prsop_wxo.prsop_method%type;
                      p_prsop_level       prsop_wxo.prsop_level%type;
                      p_prsop_attr        prsop_wxo.prsop_attr%type;
                      p_attr_prsot_type   prsop_wxo.attr_prsot_type%type;
                      p_prsop_no          prsop_wxo.prsop_no%type;
                      p_prodt_no          prsop_wxo.prodt_no%type;
                      p_prodt_char        prsop_wxo.prodt_char%type;
                begin
                      select prsop_no
                           , prsop_method
                           , prsop_level
                           , prsop_attr
                           , attr_prsot_type
                           , prodt_no
                           , prodt_char
                        into p_prsop_no
                           , p_prsop_method
                           , p_prsop_level
                           , p_prsop_attr
                           , p_attr_prsot_type
                           , p_prodt_no
                           , p_prodt_char
                        from prsop_wxo
                       where prstt_no = i_prstt_no
                         and prsot_type = i_prsot_type
                         and prsval_no = i_prsval_no
                         and prsop_type = 'SHOW';

                 

                      return
                      (
                          nvl
                          (
                              getactvalue
                              (
                                   nvl (i_ref_prstt_no, i_prstt_no)
                                 , i_prsot_type
                                 , i_psob_no
                                 , p_prsop_no
                                 , p_prsop_method
                                 , p_prsop_level
                                 , p_prsop_attr
                                 , p_attr_prsot_type
                                 , p_prodt_no
                                 , p_prodt_char
                                 , i_cty_code
                                 , i_lang_code
                                 , i_status
                                 , i_valid_at
                              )
                            , ' '
                          )
                      );
                exception
                     when no_data_found then
                            return ('<Not Defined>');
                end;