Mladen,
As one of the authors of DBA 101, I appreciate your plugging my book
for me. But Gaja Vaidyanatha (correct spelling) and Kirtikumar
Deshpande (both on this list) are the authors of the book I think you
meant to talk about: Performance Tuning 101.
Marlene (has she now moved onto single name status, like Cher and
Elvis?) is my co-author, along with James Viscusi
Rachel
- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote:
> Have you set event 10046, lev 8 for the session? If not, try setting
> it and then use 9.2 tkprof to see what is the instance waiting for
> as well to analyze the execution plans and see how they differ.
> Also, during the execution, you can watch v$session_wait and see what
> the
> session is waiting for. If the event is something like 'db file
> scattered
> read', then it is the execution plan that is causing trouble.
>
> Try the "DBA 101" red book, written by Gaja Viyadanthaya (hopefully,
> I didn't misspell his name), Marlene and comp. It's a book which
> has answers precisely to questions like yours.
>
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
>
>
> -----Original Message-----
> Sent: Friday, June 27, 2003 5:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
>
=== message truncated ===
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
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 - 19:40:24 CDT
