This discussion is archived
6 Replies Latest reply: Jul 26, 2013 1:57 PM by Skp_Oracle RSS

Performance tunning

fcad88ce-96a8-46f6-bc89-6826edcf2362 Newbie
Currently Being Moderated


Hi,

 

I have one procedure in that 70 variables assigenments like below.

 

create or replace procedure test ()

is

policy_type varchar2(100);

policy_no varchar2(100);

etc ..

begin

policy_type := select text from policy_tbl where code='TYPE';

policy_no := select text type from policy_tbl where code='NUMBER';

etc. ..

end;

 

I am facing performance issue for the procedure.So that I used bulk collect concept like below.

 

create or replace procedure test ()

is

policy_type varchar2(100);

policy_no varchar2(100);

type a20 is table of policy_tbl%row_type index by pls_integer;

obj_a20 a20;

etc. ..

begin

select code,type buk collect into obj_a20

from policy_tbl;

for i in 1 .. policy_tbl.count

loop

if (policy_tbl(i).code='TYPE')

THEN

policy_type := policy_tbl(i).text;

ELSIF (policy_tbl(i).code='NUMBER')

policy_no := policy_tbl(i).text;

etc..

END IF;

end;

 

Here it is possible to avoid 'if' or 'case' statement or any other method for tunning.

  • 1. Re: Performance tunning
    Karthick_Arp Guru
    Currently Being Moderated

    fcad88ce-96a8-46f6-bc89-6826edcf2362 wrote:

     


    begin

    policy_type := select text from policy_tbl where code='TYPE';

    policy_no := select text type from policy_tbl where code='NUMBER';

    etc. ..

    end;

     

    This does not look like PL/SQL code. PL/SQL uses INTO clause to assign scalar value from SELECT statement to a variable. Anyway remember one thing. I/O is inversely proportional to performance. Low I/O high performance. So scan the table only once. Bulk operation is unnecessary in your case. You can just do this.

     

    select max(decode(code, 'TYPE', text))

         , max(decode(code, 'NUMBER', text))

      into policy_type

         , policy_no

      from policy_tbl

  • 2. Re: Performance tunning
    Pacmann Journeyer
    Currently Being Moderated

    Hi,

     

    Use an associative array to populate it after you bulk collect your table :

    for... policy(policy_tbl(i).code) := policy_tbl(i).text ...end

     

    So, instead of "policy_type", you will use your associative array as policy('TYPE') in your code.

  • 3. Re: Performance tunning
    rp0428 Guru
    Currently Being Moderated

    >

    Here it is possible to avoid 'if' or 'case' statement or any other method for tunning.

    >

    That code doesn't need 'tuned'; it needs to be thrown away.

     

    You are trying to use PL/SQL when it is likely that SQL will do whatever it is you are needing to do.

     

    Start over and tell us what PROBLEM you are trying to solve. Tell us what RESULT you need.

  • 4. Re: Performance tunning
    Skp_Oracle Newbie
    Currently Being Moderated

    Problem :

    Oracle 11 G.

    Need to pull the data from one table , the table is Key-Value pair table .or entity table . In which you store all the column names in one column and its value/values in another columns .

    The composite primary key is present in the table .

     

    Result:  Get the "Value" from the table , I am creating a procedure with primary keys as input and the out parameter will be  a Type which will hold the values.

     

    Table structure as below . (Please ignore the names language)

     

      TABLE  a2000020

    (

      COD_CIA           NUMBER(2)                  

      NUM_POLIZA        VARCHAR2(13 BYTE)          

      NUM_SPTO          NUMBER                     

      NUM_APLI          NUMBER                     

      NUM_SPTO_APLI     NUMBER                     

      NUM_RIESGO        NUMBER(7)                 

      NUM_PERIODO       NUMBER(2)                  

      TIP_NIVEL         NUMBER(1)                  

      COD_CAMPO         VARCHAR2(30 BYTE)          

      VAL_CAMPO         VARCHAR2(80 BYTE),

      VAL_COR_CAMPO     VARCHAR2(10 BYTE),

      NUM_SECU          NUMBER(4),

      TXT_CAMPO         VARCHAR2(80 BYTE),

      MCA_BAJA_RIESGO   VARCHAR2(1 BYTE)           

      MCA_VIGENTE       VARCHAR2(1 BYTE)          

      MCA_VIGENTE_APLI  VARCHAR2(1 BYTE)           

      COD_RAMO          NUMBER(3)                  

    )

     

    Primary Key : NUM_POLIZA, COD_CIA, NUM_SPTO, NUM_APLI, NUM_SPTO_APLI, NUM_RIESGO, COD_CAMPO, NUM_PERIODO .

     

    COD_campo column  is storing the column names .

     

    VAL_CAMPO  and txt_campo  column  is storing its corresponding value.

     

    i.e.

      Rows in the table  looks like

     

         PRIMARY_KEY_COLUMNS             COD_CAMPO              VAL_CAMPO     TXT_CAMPO        OTHER COLUMS

    <Primary key columns values >            <POLICY_NAME>          < XXXXX >              < ZZZZZZ >

    <Primary key columns values >            <POLICY_CODE>           <1234 >               < SSAS>

    <Primary key columns values >            <POLICY_SECTOR>       <64789 >               <WWWW>

     

    *<> are just to  differentiate.

     

    Each Row value I want to assign it to attribute variable of a TYPE (POLICY_OBJECT).and that type would be the OUT parameter for the Procedure .

    The Primary key values can be the INPUT parameter for the Procedure.

     

    Approach:

     

    PROCEDURE p_tunning (p_auto_policy_obj    IN OUT  auto_policy_obj )

    IS

      TYPE type_a20 IS TABLE OF a2000020%Rowtype  INDEX BY PLS_INTEGER;

       table_a20 type_a20;

    BEGIN

     

    SELECT

                      COD_CIA,

                      NUM_POLIZA,

                      NUM_SPTO,

                      NUM_APLI,

                      NUM_SPTO_APLI,

                      NUM_RIESGO,

                      NUM_PERIODO,

                      TIP_NIVEL,

                      COD_CAMPO,

                      VAL_CAMPO,

                      VAL_COR_CAMPO,

                      NUM_SECU,

                      TXT_CAMPO,

                      MCA_BAJA_RIESGO,

                      MCA_VIGENTE,

                      MCA_VIGENTE_APLI,

                      COD_RAMO

      BULK COLLECT INTO table_a20

       FROM (SELECT   COD_CIA,

                      NUM_POLIZA,

                      NUM_SPTO,

                      NUM_APLI,

                      NUM_SPTO_APLI,

                      NUM_RIESGO,

                      NUM_PERIODO,

                      TIP_NIVEL,

                      COD_CAMPO,

                      VAL_CAMPO,

                      VAL_COR_CAMPO,

                      NUM_SECU,

                      TXT_CAMPO,

                      MCA_BAJA_RIESGO,

                      MCA_VIGENTE,

                      MCA_VIGENTE_APLI,

                      COD_RAMO,

                      MAX(num_spto) OVER(PARTITION BY cod_cia, num_poliza, cod_campo) AS max_endrstm

               FROM a2000020

              WHERE  <CONDITIONS>   )

     

      WHERE <CONDITIONS>;

      --

      FOR i IN 1 .. table_a20.count

      LOOP

        CASE table_a20(i).cod_campo

     

        WHEN 'POLICY_NAME' THEN

              p_auto_policy.policy_type_code := table_a20(i).val_campo;

              p_auto_policy.policy_type_name := table_a20(i).txt_campo;

       

    WHEN     'POLICY_SECTOR' THEN

              p_auto_policy.policy_sector_code := table_a20(i).val_campo;

              p_auto_policy.policy_sector_name := table_a20(i).txt_campo;

     

    <      MORE SIMILAR  CASE STATEMENT AS ABOVE AROUND 70 -80 FOR EACH VAL CAMPO  AND assignment  to the  variables >  

            

      END ;

     

    the problem here is  case statements is taking some time to execute and i want to avoid the case .

      can we use index by varchar  cod_campo , and loop in and assign the values ? no case statement would require. this is just

      a thought , i am not sure how to implement it  , any  more suggestions please.

     

    Similar things can be achieved by Select statements as well using decode, or write a function which takes as input the val_campo  and a select statement in that function selecting the val_campo,txt_campo.

    but the same function needs to be called for each assignment (70-80)  times, I tried this approach and it takes time as 70-80 time I/O .

     

     

      Kindly suggest . Thank you!

  • 5. Re: Performance tunning
    34MCA2K2 Journeyer
    Currently Being Moderated

    For a start, please put a new thread instead of hijacking someone else's thread.

     

    Regards,

  • 6. Re: Performance tunning
    Skp_Oracle Newbie
    Currently Being Moderated

    Hi , the thread was started by my colleague.however I am starting a new thread. Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points