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).
