Improve procedure performance

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
facc
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 104
Joined: Wed, 27 May 2009 2:37 pm
Location: Cerquilho / SP

Good morning, I come once again ask for help for you.

I have this procedure, and the DBA where my BD is hosted reported that he is consuming a lot of memory. Could anyone analyze and return to improve performance? obs. I can not change the input parameters or exit

The procedure:

Select all

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; 
Access to This procedure is made through an ASP page developed by an outsourced company.
Thank you for any help.
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

Tables GEMCO_PARAMETRO_INTERFACE / GEMCO_SISTEMA
have indications PINT_CD_SISTEMA / SIST_CD_SISTEMA, SIST_DS_SISTEMA respectively
??

How is this PROC sp_int_gemco_gera_log ??

Have you ever debugged to try to fill something abnormal as a die or code that is staring at your runtime?
facc
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 104
Joined: Wed, 27 May 2009 2:37 pm
Location: Cerquilho / SP

Yes, they have the parameters.

The procedure sp_int_gemco_ger_log, is from an outsourced company, I can not be altering, but I sent the same report to them and asked to analyze this procedure. But in the thick, this procedure only generates a log on the machine.


I already debted, and runs normally (local machine and server).
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

See this:

Select all

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'); 
First, let's see this line:

Select all

 AND TO_CHAR(mês_ANO, 'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYYMM'); 
Apparently, this mês_ano field is a date. When you put the to_char in it kills some possible index. Ideal would be something like this:

Select all

AND mês_ANO >= TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') 
AND mês_ANO <  TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM')
See what dates he returns with the code above: (exactly the posterior month)

Select all

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> 
99] Anything:

Select all

 WHERE FLGUSO = 0 
        OR LOJA IS NULL 
        OR LOJA = '' 
        AND TO_CHAR(ME... 
must have a problem of logic here ... because he is catching fluguso = 0 or is null store or (store = '' and dates between the period). See if that's the way.
User avatar
jessica.ff
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 61
Joined: Mon, 11 Jun 2007 2:28 pm
Location: Gravataí - RS
ninguém é tão sabio que não tenha a aprender, e nem tão ignorante que não tenha a ensinar.

Another thing you can use to check is if you use the pl / sql developer you can test the procedure and mark to generate create profiler ringp that is the button before the magnifying glass, and puts the test after that clicks the profiler and vê which is consuming more time. : D
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

Query should have a high I / O and so the DBA is complaining ... sometimes, distribute the discs better at Estorage Help!
miltonbastos
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 22
Joined: Tue, 19 May 2009 12:40 pm
Location: Curitiba - PR

You created the cursor cur_nrsorte only to count how many records do you have this query ??

How about using a count () for this instead of cursor?
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 12 guests