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
Responder
facc
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 104
Registrado em: Qua, 27 Mai 2009 2:37 pm
Localização: Cerquilho / SP

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:

Selecionar tudo

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);
  você_DIR_LOG  VARCHAR2(100);
  você_ID_LOG   VARCHAR2(7) := 'CYBELAR';
  você_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(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM');

  RES CUR_NRSORTE%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 = VG_PROCESSO;
  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,
                            VG_PROCESSO,
                            0,
                            0,
                            0);
  END;
  sp_int_gemco_gera_log('GERA LOG',
                        você_DIR_LOG,
                        você_ID_LOG,
                        você_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.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

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?
facc
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 104
Registrado em: Qua, 27 Mai 2009 2:37 pm
Localização: Cerquilho / SP

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).
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Veja isso:

Selecionar tudo

CURSOR CUR_NRSORTE IS
    SELECT ROWID, NROSORTE
      FROM CYBELAR_NROSORTE
     WHERE FLGUSO = 0
        OR LOJA IS NULL
        OR LOJA = ''
        AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM'); 
Primeiro, vamos ver essa linha:

Selecionar tudo

 AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM'); 
Pelo visto, esse campo mês_ANO é uma data. Quando você coloca o TO_CHAR nele mata algum possível índice. O ideal seria algo assim:

Selecionar tudo

AND mês_ANO >= TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM')
AND mês_ANO <  TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM')
Veja que datas que ele retorna com o codigo acima: (exatamente o mês posterior)

Selecionar tudo

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:

Selecionar tudo

 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.
Avatar do usuário
jessica.ff
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 61
Registrado em: Seg, 11 Jun 2007 2:28 pm
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.

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
RodrigoValentim
Moderador
Moderador
Mensagens: 367
Registrado em: Ter, 25 Mar 2008 3:41 pm
Localização: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

A query deve ter um I/O Alto e por isso o DBA está reclamando... as vezes, distribuir melhor os discos na estorage ajuda!
miltonbastos
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 22
Registrado em: Ter, 19 Mai 2009 12:40 pm
Localização: Curitiba - PR

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?
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 6 visitantes