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