Re: Performance

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