Performance

From: Ramon E. Estevez <restevez_at_blh.com.do>
Date: Fri, 27 Jun 2003 13:43:15 -0700
Message-ID: <F001.005BB63A.20030627132012@fatcity.com>

Hi list,

SCENARIO LAB DB = Oracle 8.1.7.4.0 on Suse Linux 7.2 PRODUCTION DB = Oracle 8.1.7.4.0 on HP-UX B.11.00

I have this strange case, I have this query that generate a text file and in the PRODUCTION environment ran for about 30 minutes. When running the same query in LAB ran in about 2 minutes. The 2 instances have the same parameters setted and the same amount of data, the Lab DB is updated every night with the production data throught IMP/EXP procedure.

Trying to solve the problem, today I ran statistics in LAB and the query lasted more than in PRODUCTION and before was about 2 minutes.

TIA
-------------*----------------------------------------------------------
Here is the code of the CODE in the form and the function.

PROCEDURE genera_archivo IS

        vcOutFile varchar2(30) := 'c:\pruebas\archivo.txt';

  hOutFile                text_io.file_type;
  
  cursor cuentas is
     select  b.MATHOPERATOR, b.MAPACCOUNTLOCAL,
             a.PRINTORDER,   b.sequence
     from tequivaccount a, tequivaccountdetail b
        where 
     a.REPORTCODE = b.REPORTCODE   and 
     a.CODE       = b.CODE         and 
     a.reportcode = 'BDI01'
        order by a.PRINTORDER, b.sequence   ;
   

  vSaldo number;
  vCuenta varchar2(14);
BEGIN

        set_application_property(CURSOR_STYLE,'BUSY');
        hOutFile := text_io.fopen(:nombre_plano,'w');
        
text_io.put_line(hOutFile,to_char(:fecha_fin,'ddmmyyyy')||'cifrado');   
        
  for i in cuentas loop
        IF C.MATHOPERATOR = '+' THEN
           VSALDO :=
bdi_saldo_conta_fecha(I.MAPACCOUNTLOCAL,:fecha_Fin,:suc_ini,:suc_fin)
        ELSE
           VSALDO :=

bdi_saldo_conta_fecha(I.MAPACCOUNTLOCAL,:fecha_fin,:suc_ini,:suc_fin)*-1 ));

        END IF;
        
    if nvl(vSaldo,0) <> 0 then
       text_io.put_line(hOutFile,rpad(i.MAPACCOUNTLOCAL,14,' ')||'   000

'||to_char(round(vsaldo,2),'999999999999999.99'));

    end if;
  end loop;
  set_application_property(CURSOR_STYLE,'DEFAULT'); END;
-------------* This the function *------------------

PROMPT CREATE OR REPLACE FUNCTION hd_bdi.bdi_saldo_conta_fecha

CREATE OR REPLACE FUNCTION hd_bdi.bdi_saldo_conta_fecha (pCuenta in char,pFecha in date,

                                                  pSucIni in number,

pSucFin in number)
return number is

---

• Devuelve el Saldo de una cuenta contable a la fecha pasada en pFecha.
  ---
  

 vSaldo      number;
 vDebito     number;
 vCredito    number;
 vSaldoAnt   number;
 vFechaIni   date ;
 vMoneda     number;

 vLinea      varchar2(150);
 vdate1     date;
 vdate2     date;
 vdate3     date;
 vdate4     date;

begin

--delete log_batch;
--commit;

for i in pSucIni .. pSucFin loop

 vDebito := 0;
 vCredito := 0;
 vSaldoAnt := 0;

    begin

             SELECT MAX(A.ACM_FECACUM),
                    Nvl(A.ACM_ACUMDBANT,0) -
                    nvl(A.ACM_ACUMCRANT,0) +
                    nvl(A.ACM_ACUMDB,0)    -
                    nvl(A.ACM_ACUMCR,0)
             into   vFechaIni, vSaldoAnt
             from  tcon_acum A
             where
                   A.acm_codigo        = pCuenta      AND
                   A.acm_sucursal      = i           AND
                   TRUNC(A.ACM_FECACUM) IN (select
nvl(max(TRUNC(ACM_FECACUM)), '2001/01/01')
                             FROM TCON_ACUM T
                         WHERE
                            T.Acm_codigo   = A.acm_codigo   AND
                            T.ACM_Sucursal = A.ACM_SUcursal AND
                            T.ACM_Oficina  = A.ACM_Oficina  AND
                            T.ACM_Moneda   = A.ACM_MOneda)
            GROUP BY Nvl(A.ACM_ACUMDBANT,0), nvl(A.ACM_ACUMCRANT,0),
               nvl(A.ACM_ACUMDB,0),    nvl(A.ACM_ACUMCR,0);
    exception
               when no_data_found then
            vSaldoAnt := 0;
               when others then
                   dbms_output.put_line((pCuenta));

    end;

    vFechaIni := vFechaIni + 1;

    begin
  SELECT SUM (DECODE(T.TSA_TIPO, 'D', NVL(T.TSA_VALOR,0))) ,

                       SUM (DECODE(T.TSA_TIPO, 'C', NVL(T.TSA_VALOR,0)))
                into vDebito, vCredito
                FROM
                TCON_TRANSA T, TCON_DESTRAN D
                WHERE
                T.TSA_SUCURSAL     =  I      AND
         T.TSA_CUENTA       =   pCuenta                    AND
         D.DST_NUMTRAN      =  T.TSA_NUMTRAN               AND
                D.DST_SUCURSAL     =  T.TSA_SUCURSAL              AND
                D.DST_FECHA BETWEEN  vFechaIni and pFecha AND
                D.DST_CUADRA       = 'S' ;
      exception when no_data_found then
                      vDebito  := 0;
                      vCredito := 0;
                        when others then
                             null;

    end;
   vSaldo :=nvl (vSaldo,0) + (nvl(vSaldoAnt,0) + nvl(vDebito,0) - nvl(vCredito,0)) ;
end loop;
return vSaldo ;

end;

/

Ramon E. Estevez
[EMAIL PROTECTED]
809-535-8994

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Ramon E. Estevez
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jun 27 2003 - 15:43:15 CDT