Stephane,
I continue having the same problem, in LAB 2 minutes and in PRODUCTION
forever. I made the changes you indicate me.
This is the explain plan in LAB, NO STATISTICS with data from yesterday
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 VIEW
4 3 SORT (GROUP BY)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_ACUM'
6 5 INDEX (RANGE SCAN) OF 'IDX_ACUM_02' (NON-UNIQUE)
7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_ACUM'
8 7 INDEX (RANGE SCAN) OF 'IDX_ACUM_02' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
311 consistent gets
0 physical reads
0 redo size
353 bytes sent via SQL*Net to client
1159 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
------------------------------------------------------------------------
---------
This the explain plan in PRODUCTION.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=53)
1 0 SORT (GROUP BY) (Cost=9 Card=1 Bytes=53)
2 1 NESTED LOOPS (Cost=7 Card=1 Bytes=53)
3 2 VIEW (Cost=6 Card=1 Bytes=15)
4 3 SORT (GROUP BY) (Cost=6 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'TCON_ACUM' (Cost=4 Card=1
Bytes=26)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'TCON_ACUM' (Cost=2 C
ard=1 Bytes=38)
7 6 INDEX (RANGE SCAN) OF 'IDX_ACUM_02' (NON-UNIQUE) (Co
st=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
108 consistent gets
0 physical reads
0 redo size
245 bytes sent via SQL*Net to client
981 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
Ramon E. Estevez
[EMAIL PROTECTED]
809-535-8994
-----Original Message-----
Stephane Faroult
Sent: Tuesday, July 01, 2003 4:16 PM
To: Multiple recipients of list ORACLE-L
Ramon,
I have had a closer look at your coe. My gut feeling is that
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,
(select ACM_Oficina,
ACM_Moneda,
nvl(max(TRUNC(ACM_FECACUM)), '2001/01/01')
MAXFECACUM
FROM TCON_ACUM
WHERE
Acm_codigo = pCuenta AND
ACM_Sucursal = i
GROUP BY ACM_Oficina, ACM_Moneda) T
where
A.acm_codigo = pCuenta AND
A.acm_sucursal = i AND
T.ACM_Oficina = A.ACM_Oficina AND
T.ACM_Moneda = A.ACM_MOneda AND
TRUNC(A.ACM_FECACUM) = T.MAXFECACUM
GROUP BY Nvl(A.ACM_ACUMDBANT,0), nvl(A.ACM_ACUMCRANT,0),
nvl(A.ACM_ACUMDB,0), nvl(A.ACM_ACUMCR,0);
nvl(A.ACM_ACUMDB,0), nvl(A.ACM_ACUMCR,0);
would return the same thing as what you have, only faster. On first
readin I had not noticed that you IN (SELECT ...) was correlated. Ouch.
If ACM_CODIGO and ACM_SUCURSAL are indexed (and the index is
discriminant enough), and if (ACM_OFICINA, ACM_MONEDA) are also
separately indexed (with the same restriction as before), it should run
reasonably fast.
"Ramon E. Estevez" wrote:
>
> Tks Stephane and Madlen,
>
> Still the same problem.
>
> I added the hint /*+ FIRST_ROWS */ to the query that invoke the
> function and it changed from FTS to use Index but still have the same
> problem. I added the same hint to the function and Nothing.
>
> I checked the v$session_wait during the execution of the procedure and
> the only thing that Was waiting was SQL NET TO CLIENT MESSAGE.
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-535-8994
>
> -----Original Message-----
> Stephane Faroult
> Sent: Friday, June 27, 2003 6:15 PM
> To: Multiple recipients of list ORACLE-L
>
> Ramon,
>
> This is not a strange case at all; I find quite customary to see
> dazzling fast queries in a development environment crawl pathetically
> in production.
> My Spanish being reduced to some vague remnants of Latin (and just
> enough to understand the promotion of Mexican holiday resorts) I must
> confess to some difficulty in understanding your code. Anyway, CBO
> seems to be the culprit, isn't it? What is the main behavioural
> difference between CBO and RBO? Primarily, CBO doesn't shy as much of
> full table scans, and disdain indices much more often, jumping for the
> (usually quite efficient) hash join instead. When stats slow down a
> query, it usually means that nested loops were efficient, and in that
> case hash joins are not. To put the CBO back on tracks, /*+ FIRST_ROWS
> */ is usually enough. If it isn't, list the tables in the FROM clause
> in the order you know to be suitable (the table for which you feed the
> most selective values in the query first) and add ORDERED to the hint
> to ram the message home.
> I have found this to be efficient in most cases.
>
> HTH,
>
> Stephane Faroult
>
> "Ramon E. Estevez" wrote:
> >
> > 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: Stephane Faroult
> 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).
>
> --
> 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).
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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).
--
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).
