Melhorar Performance

facc · Sex, 21 Mai 2010 8:57 am

Bom dia!

tenho essa procedure

CREATE OR REPLACE PROCEDURE CYBELAR_RES_NRSORTE(P_LOJA     IN varchar2,
                                                P_PDV      IN VARCHAR2,
                                                P_QTD      IN NUMBER,
                                                P_ORIGEM   IN NUMBER,
                                                P_NROSORTE OUT varchar2,
                                                P_CONFIRMA OUT NUMBER) is
  /***************************************************************
  * PROCEDURE : CYBELAR_RES_NRSORTE                               *
  * OBJETIVO  : RESERVAR O NRSORTE P/ A LOJA E "ESCONDER"         *
                DAS DEMAIS LOJAS E OUTRAS RESERVAS                *
  * CRIACAO   : 08/05/2009                                        *
  * ALTERADO  : 29/04/2010 - ADD CAMPO P_ORIGEM - COMERCIALIZACAO *
  * VERSAO    : 1.0                                               *
  * AUTOR     : FABIO A. CAMPOS CRUZ -       *
  ****************************************************************/
  look_error exception;

  pragma exception_init(look_error, -54);
  
  i number := 1;

  retorno_nrsorte varchar2(5);
  ListaNrsorte    varchar2(1000);
  RETORNOCONFIRMA NUMBER;
  erro_int        varchar2(1000);
  você_dir_log      varchar2(100);
  você_id_log       varchar2(7) := 'CYBELAR';
  você_arq_log      varchar2(60) := 'NRSORTE_' || to_char(sysdate, 'DDMMYY') ||
                                  '.LOG';
  você_nome_pgm     varchar2(50) := 'PDV_NRSORTE';
  vu_file         utl_file.file_type;

  VN_QTREG NUMBER := 0;

  /*cursor cur_nrosorte is
  --cursor pega numeros "livres"
    select nrosorte
      from cybelar_nrosorte
     where flguso = 0
       AND (LOJA IS NULL OR LOJA = '')
       and rownum <= 1;
  
  res cur_nrosorte%rowtype;*/

begin
  BEGIN
    SELECT PINT_NM_DIRETORIO_LOG
      INTO você_DIR_LOG
      FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
     WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
       AND SIST.SIST_DS_SISTEMA = você_nome_pgm;
  EXCEPTION
    -- SE não EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
    -- GERADO O LOG DE OCORRENCIAS
    WHEN NO_DATA_FOUND THEN
      você_DIR_LOG := '/integra/Log';
    WHEN TOO_MANY_ROWS THEN
      você_DIR_LOG := '/integra/Log';
    WHEN OTHERS THEN
      você_DIR_LOG := '/integra/Log';
  END;

  begin
    vu_file := utl_file.fopen(você_dir_log, você_arq_log, 'r');
    utl_file.fclose(vu_file);
  exception
    when others then
      sp_int_gemco_gera_log('INICIO DO LOG',
                            você_dir_log,
                            você_id_log,
                            você_arq_log,
                            sysdate,
                            null,
                            você_nome_pgm,
                            0,
                            0,
                            0);
  end;
  --open cur_nrosorte;
  FOR rNrSorte IN 1 .. P_QTD loop
    --fetch cur_nrosorte into res;
    --if CYBELAR_LCK_REGISTRO(res.nrosorte) > 0 then  
    BEGIN
          
      LOCK TABLE CYBELAR_NROSORTE IN EXCLUSIVE MODE;
                    

    
      SELECT NRO.NROSORTE
        INTO retorno_nrsorte
        FROM CYBELAR_NROSORTE NRO
       WHERE LOJA IS NULL
         AND FLGUSO = 0
         AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM')
         AND ROWNUM <= 1         
         FOR UPDATE NOWAIT;
    
      ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
    
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO      = 1,
             LOJA        = P_LOJA,
             PDV_CAIXA   = P_PDV,
             DATANRSORTE = SYSDATE,
             IDENTORIG = P_ORIGEM
       WHERE NROSORTE = retorno_nrsorte
         AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');
      COMMIT;
    EXCEPTION
      WHEN look_error THEN
        NULL;
    END;
  
    RETORNOCONFIRMA := 0;
    --end if;
    VN_QTREG := VN_QTREG + 1;
  end loop;
  COMMIT;
  --close cur_nrosorte;

  IF VN_QTREG = 0 THEN
    ERRO_INT        := 'ERRO AO RESERVAR O NRSORTE (' || P_NROSORTE ||
                       ') PARA A LOJA ' || P_LOJA || '.';
    RETORNOCONFIRMA := 1;
  ELSE
    ERRO_INT        := 'RESERVADO COM SUCESSO (' || ListaNrsorte ||
                       '). LOJA ' || P_LOJA;
    RETORNOCONFIRMA := 0;
  END IF;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        erro_int,
                        você_nome_pgm,
                        0,
                        0,
                        0);

  sp_int_gemco_gera_log('FIM LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        null,
                        você_nome_pgm,
                        0,
                        0,
                        0);
  COMMIT;

  P_CONFIRMA := RETORNOCONFIRMA;
  P_NROSORTE := ListaNrsorte;

end cybelar_res_nrsorte;

Como poderia melhorar a performance? Dou um lock table pelo motivo da tabela conter números para sorteio, portando deve ser um número único para as 73 lojas da rede, ou seja, o número 1 sendo reservado não pode ser reservado para mais nenhuma loja. A procedure é acessada praticamente ao mesmo tempo por todas as lojas. Após essa reserva chamo outra procedure dando um update com o nome, telefone, cpf do cliente que adquiriu o número.

fsitja · Seg, 24 Mai 2010 10:04 am

Uma coisa simples que dá para fazer para melhorar o desempenho é retirar o commit de dentro do loop e deixar ele apenas fora.

O lock vai criar um gargalo na tabela independentemente do que seja feito no restante do procedimento. Talvez desse para melhorar um pouquinho transformando o cursor for loop em um comando só de update, sem usar loop. O loop tem muitas iterações? Não entendi o motivo de ter o loop se não usa a variável rNrSorte nenhuma vez.

facc · Seg, 24 Mai 2010 10:52 am

Fiz essa alteração na procedure

Selecionar tudo

CREATE OR REPLACE PROCEDURE CYBELAR_RES_NRSORTE(P_LOJA     IN varchar2,
                                                P_PDV      IN VARCHAR2,
                                                P_QTD      IN NUMBER,
                                                P_ORIGEM   IN NUMBER,
                                                P_NROSORTE OUT varchar2,
                                                P_CONFIRMA OUT NUMBER) is
  /***************************************************************
  * PROCEDURE : CYBELAR_RES_NRSORTE                               *
  * OBJETIVO  : RESERVAR O NRSORTE P/ A LOJA E "ESCONDER"         *
                DAS DEMAIS LOJAS E OUTRAS RESERVAS                *
  * CRIACAO   : 08/05/2009                                        *
  * ALTERADO  : 29/04/2010 - ADD CAMPO P_ORIGEM - COMERCIALIZACAO *
  * VERSAO    : 1.0                                               *
  * AUTOR     : FABIO A. CAMPOS CRUZ -                            *
  ****************************************************************/
  look_error exception;
  
  pragma exception_init(look_error, -54);

  retorno_nrsorte varchar2(5);
  ListaNrsorte    varchar2(1000);
  RETORNOCONFIRMA NUMBER;
  erro_int        varchar2(1000);
  você_dir_log      varchar2(100);
  você_id_log       varchar2(7) := 'CYBELAR';
  você_arq_log      varchar2(60) := 'NRSORTE_' || to_char(sysdate, 'DDMMYY') ||
                                  '.LOG';
  você_nome_pgm     varchar2(50) := 'PDV_NRSORTE';
  vu_file         utl_file.file_type;

  VN_QTREG NUMBER := 0;

  cursor cur_nrosorte is
  --cursor pega numeros "livres"
    select nrosorte
      from cybelar_nrosorte
     where flguso = 0
       AND LOJA IS NULL
       and to_char(mês_ano, 'YYYYMM') = to_char(add_months(sysdate, 1), 'YYYYMM')
       and rownum <= P_QTD;
  
  res cur_nrosorte%rowtype;

begin
  BEGIN
    SELECT PINT_NM_DIRETORIO_LOG
      INTO você_DIR_LOG
      FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
     WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
       AND SIST.SIST_DS_SISTEMA = você_nome_pgm;
  EXCEPTION
    -- SE não EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
    -- GERADO O LOG DE OCORRENCIAS
    WHEN NO_DATA_FOUND THEN
      você_DIR_LOG := '/integra/Log';
    WHEN TOO_MANY_ROWS THEN
      você_DIR_LOG := '/integra/Log';
    WHEN OTHERS THEN
      você_DIR_LOG := '/integra/Log';
  END;

  begin
    vu_file := utl_file.fopen(você_dir_log, você_arq_log, 'r');
    utl_file.fclose(vu_file);
  exception
    when others then
      sp_int_gemco_gera_log('INICIO DO LOG',
                            você_dir_log,
                            você_id_log,
                            você_arq_log,
                            sysdate,
                            null,
                            você_nome_pgm,
                            0,
                            0,
                            0);
  end;
   
  /*FOR rNrSorte IN 1 .. cur_nrosorte loop
    --fetch cur_nrosorte into res;
    --if CYBELAR_LCK_REGISTRO(res.nrosorte) > 0 then  
    BEGIN
     LOCK TABLE CYBELAR_NROSORTE IN EXCLUSIVE MODE;    
    
      SELECT NRO.NROSORTE
        INTO retorno_nrsorte
        FROM CYBELAR_NROSORTE NRO
       WHERE LOJA IS NULL
         AND FLGUSO = 0
         AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM')
         AND ROWNUM <= 1         
         FOR UPDATE NOWAIT;
    
      ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
    
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO      = 1,
             LOJA        = P_LOJA,
             PDV_CAIXA   = P_PDV,
             DATANRSORTE = SYSDATE,
             IDENTORIG = P_ORIGEM
       WHERE NROSORTE = retorno_nrsorte
         AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');
      COMMIT;
    EXCEPTION
      WHEN look_error THEN
        NULL;
    END;*/
    
    ----------------------------
    -- ALTERADO EM 24/05/2010 --
    ----------------------------
    open cur_nrosorte;
    if cur_nrosorte%isopen then
      loop
        Fetch cur_nrosorte into retorno_nrsorte;
        exit when cur_nrosorte%notfound;
        update cybelar_nrosorte
           set flguso = 1,
               loja = p_loja,
               pdv_caixa = p_pdv,
               datanrsorte = sysdate,
               identorig = P_ORIGEM
         where nrosorte = retorno_nrsorte
           and to_char(mês_ano, 'YYYYMM') = to_char(add_months(sysdate, 1), 'YYYYMM');
           
           --Retorna os numeros
           ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
         commit;
         VN_QTREG := VN_QTREG + 1;
      end loop;
    end if;
  
    RETORNOCONFIRMA := 0;
    /*--end if;
    VN_QTREG := VN_QTREG + 1;
  end loop;*/
  COMMIT;
  close cur_nrosorte;

  IF VN_QTREG = 0 THEN
    ERRO_INT        := 'ERRO AO RESERVAR O NRSORTE (' || P_NROSORTE ||
                       ') PARA A LOJA ' || P_LOJA || '.';
    RETORNOCONFIRMA := 1;
  ELSE
    ERRO_INT        := 'RESERVADO COM SUCESSO (' || ListaNrsorte ||
                       '). LOJA ' || P_LOJA;
    RETORNOCONFIRMA := 0;
  END IF;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        erro_int,
                        você_nome_pgm,
                        0,
                        0,
                        0);

  sp_int_gemco_gera_log('FIM LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        null,
                        você_nome_pgm,
                        0,
                        0,
                        0);
  COMMIT;

  P_CONFIRMA := RETORNOCONFIRMA;
  P_NROSORTE := ListaNrsorte;

end cybelar_res_nrsorte;
/

Melhorar Performance

Quem está online