6 Replies Latest reply: Jul 26, 2013 3:57 PM by Skp_Oracle RSS

    Performance tunning

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


      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

          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

            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

              >

              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

                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

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

                   

                  Regards,

                  • 6. Re: Performance tunning
                    Skp_Oracle

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