Melhorar a performance da procedure

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Qua, 09 Set 2009 11:19 am

Bom dia, venho mais uma vez pedir ajuda a vocês.

Tenho essa procedure, e o DBA onde meu BD está hospedado informou que está consumindo muita memória. Alguém poderia analizar e me retornar como melhorar a performance? Obs. Não posso alterar os parametros de entrada nem de saida

A procedure:

Código: Selecionar todos
CREATE OR REPLACE PROCEDURE CYBELAR_VER_NRSORTE(P_LOJA       IN VARCHAR2,
                                                P_NRPDV      IN VARCHAR2,
                                                P_QTDNRSORTE IN NUMBER,
                                                P_RETORNO    OUT NUMBER) is
  /*************************************************************
  * PROCEDURE : CYBELAR_VER_NRSORTE                            *
  * OBJETIVO  : VERIFICAR SE POSSUI NRSORTE P/ A LOJA "LIVRES" *
                NA QTDD PEDIDA, CASO POSITIVO RETORNA 1, CASO  *
                NEGATIVO RETORNA 0                             *
  * CRIACAO   : 13/05/2009                                     *
  * VERSAO    : 1.0                                            *
  * AUTOR     : FABIO A. CAMPOS CRUZ - fabioc@*******.com.br   *
  *************************************************************/

  RETORNO     NUMBER;
  ERRO_INT    VARCHAR2(1000);
  VC_DIR_LOG  VARCHAR2(100);
  VC_ID_LOG   VARCHAR2(7) := 'CYBELAR';
  VC_ARQ_LOG  VARCHAR2(15) := 'VERNRSORTE.LOG';
  VG_PROCESSO VARCHAR2(50) := 'VER_NRSORTE';
  VU_FILE     UTL_FILE.file_type;

  VN_QTREG NUMBER := 0;

  CURSOR CUR_NRSORTE IS
    SELECT ROWID, NROSORTE
      FROM CYBELAR_NROSORTE
     WHERE FLGUSO = 0
        OR LOJA IS NULL
        OR LOJA = ''
        AND TO_CHAR(MES_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');

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

  BEGIN
    VU_FILE := UTL_FILE.fopen(VC_DIR_LOG, VC_ARQ_LOG, 'r');
    UTL_FILE.FCLOSE(VU_FILE);
  EXCEPTION
    WHEN OTHERS THEN
      sp_int_gemco_gera_log('INICIO DO LOG',
                            VC_DIR_LOG,
                            VC_ID_LOG,
                            VC_ARQ_LOG,
                            SYSDATE,
                            NULL,
                            VG_PROCESSO,
                            0,
                            0,
                            0);
  END;
  sp_int_gemco_gera_log('GERA LOG',
                        VC_DIR_LOG,
                        VC_ID_LOG,
                        VC_ARQ_LOG,
                        SYSDATE,
                        'INICIO DO LOG',
                        VG_PROCESSO,
                        0,
                        0,
                        0);
  OPEN CUR_NRSORTE;
  LOOP
    FETCH CUR_NRSORTE
      INTO RES;
    IF CUR_NRSORTE%NOTFOUND THEN
      EXIT;
    END IF;
    VN_QTREG := VN_QTREG + 1;
  END LOOP;

  IF VN_QTREG < P_QTDNRSORTE THEN
    RETORNO := 0;
  ELSE
    RETORNO := 1;
  END IF;

  CLOSE CUR_NRSORTE;

  P_RETORNO := RETORNO;
END CYBELAR_VER_NRSORTE;


O acesso a essa procedure é feita através de uma página ASP desenvolvida por uma empresa terceirizada.
Agradeço desde já qualquer ajuda.
facc
Localização: Cerquilho / SP

Mensagemem Qua, 09 Set 2009 12:00 pm

as tabelas GEMCO_PARAMETRO_INTERFACE / GEMCO_SISTEMA
tem os indices PINT_CD_SISTEMA / SIST_CD_SISTEMA, SIST_DS_SISTEMA respectivamente
??

como esta esse proc sp_int_gemco_gera_log ??

você já debugou para tentar enchergar algo anormal como aulgum dado ou codigo que esteja onerando seu tempo de execução?
victorhugomuniz
Localização: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Imagem JavaBlackBelt

Mensagemem Qua, 09 Set 2009 12:06 pm

Possuem sim os parametros.

A procedure sp_int_gemco_gera_log, é de uma empresa terceirizada, não posso estar alterando, porém enviei o mesmo relatório para eles e pedi para analizarem essa procedure. Mas no grosso, essa procedure apenas gera um log na maquina.


já debuguei, e roda normalmente (maquina local e servidor).
facc
Localização: Cerquilho / SP

Mensagemem Qua, 09 Set 2009 1:20 pm

Veja isso:
Código: Selecionar todos
CURSOR CUR_NRSORTE IS
    SELECT ROWID, NROSORTE
      FROM CYBELAR_NROSORTE
     WHERE FLGUSO = 0
        OR LOJA IS NULL
        OR LOJA = ''
        AND TO_CHAR(MES_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');


Primeiro, vamos ver essa linha:
Código: Selecionar todos
AND TO_CHAR(MES_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');


Pelo visto, esse campo MES_ANO é uma data. Quando você coloca o TO_CHAR nele mata algum possível índice. O ideal seria algo assim:
Código: Selecionar todos
AND MES_ANO >= TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM')
AND MES_ANO <  TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM')


Veja que datas que ele retorna com o codigo acima: (exatamente o mês posterior)
Código: Selecionar todos
SQL> select
  2    TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') data1
  3  , TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM')  data2
  4  from dual
  5  ;

DATA1     DATA2
--------- ---------
01-OCT-09 01-NOV-09

SQL>


Outra coisa:
Código: Selecionar todos
WHERE FLGUSO = 0
        OR LOJA IS NULL
        OR LOJA = ''
        AND TO_CHAR(ME...

Deve ter algum problema de lógica aqui... Pois ele ta pegando FLGUSO = 0 OU LOJA is NULL OU (loja = '' E datas entre o periodo). Veja se é assim mesmo.
dr_gori
Localização: Seattle, WA, USA

Thomas F. G

Mensagemem Sex, 11 Set 2009 3:45 pm

Uma outra coisa que você pode utilizar para verificar é se você utiliza o pl/sql developer você pode testar a procedure e marcar para gerar Create profiler retorp que é o botão antes da lupa, e põe a testar depois disso clica na aba profiler e vê o que ta consumindo mais tempo. :D
jessica.ff
Localização: Gravataí - RS

ninguém é tão sabio que não tenha a aprender, e nem tão ignorante que não tenha a ensinar.

Mensagemem Sex, 11 Set 2009 10:58 pm

A query deve ter um I/O Alto e por isso o DBA está reclamando... as vezes, distribuir melhor os discos na estorage ajuda!
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qui, 01 Out 2009 11:23 am

você criou o cursor CUR_NRSORTE só pra contar quantos registros tem essa query??

Que tal usar um COUNT() pra isso em vez de cursor?
miltonbastos
Localização: Curitiba - PR


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


      Voltar para PL/SQL

      Quem está online

      Usuários navegando neste fórum: Google [Bot] e 7 visitantes