| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance
It's true. Gaja Vayanahneenahneeheyheyheyhey (sp?) will star in "BAARF. The Musical." Also - this might come as a surprise to Pete Sharman - the OakTable Choir will be part of the musical, and Pete (who, by the way, is a fantastic baryton singer) will sing one verse of a certain song (lyrics slightly re-written), while Lex de Haan (another excellent singer) will sing the second and Bjørn Engsig (if he participates) the third verse.
Other participants include a group known as FEVER - Five Evil Vendor's Eternal Rotherhood - and of course the members of the BAARF Party, who teach Gaja Vahatneyhatneyhatney that Parity Is Pain.
There'll be a sound-proofed special area for people playing air guitar, too.
It will rock. James Morle and I are still working on the details, but Gaja has (very bravely) accepted the role in the musical. Respect to him. We're still looking for a heroine.
Mogens
Pete Sharman wrote:
<blockquote type="cite"
cite="">
Surely not? I was told just last week by the inestimable Mr. Mogens
Norgaard (who we all know as the source of truth) that Gaja's real name is
Gaja Vaidyanathanathanatha, and he's about to take on a top-secret new
career (details to be revealed at the DB Forum in Denmark in October) ...
:)
Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no it's not. It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA.
-----Original Message-----
Rachel Carmichael
Sent: Friday, June 27, 2003 6:19 PM
To: Multiple recipients of list ORACLE-L
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
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)
---
--- 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
Received on Sat Jun 28 2003 - 04:17:49 CDT
 |
 |