Why do you feel old at the magic age of 42. May be 43 Years now.
The book name is ORACLE 101 Performance Tuning by
Gaja Krishna Vaidyanatha, Kirtikumar Deshpande and John A. Kostelac Jr.
Regards Rafiq
Oracle Financial GL, AP, AR, PA, Reporting NJ Morristown 1 - 3 months Date: Jun 27, 2003 http://www.net-temps.com/adcgi/jobref.cgi/jsd/hreed/8814bg
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Fri, 27 Jun 2003 23:49:20 -0800
I didn't have the book with me at the time, so I was talking by heart, from my head. As I am getting older, my memory is obviously playing tricks on me. I humbly apologize to everybody whom I might have offended by my bad spelling, distorting title or forgetting co-authors altogether. I didn't want to insult anybody, it's just my advanced age which is equal to the answer to the question of life, universe and everything.
On 2003.06.27 21:19, Rachel Carmichael wrote:
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).
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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).
