Re: Performance

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