RE: RE: Performance

Ramon,

   Whatever the plan, your stats border on the insignificant (I mean these are very small values and it shouldn't even take 2mn to run). Set TIMED_STATISTICS to TRUE if this is not already done and check system events. There is something unorthodox going on - not purely a matter of SQL processing.

>----- ------- Original Message ------- -----
>From: "Ramon E. Estevez" <restevez_at_blh.com.do>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 03 Jul 2003 07:30:37
>
>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
>restevez_at_blh.com.do
>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
>> restevez_at_blh.com.do
>> 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');
>> >
>> >
>> > ;
>> >
>> > for i in cuentas loop
>> > IF C.MATHOPERATOR = '+' THEN
>> > VSALDO :=
>> >
>> > ELSE
>> > VSALDO :=
>> >
>> > )*
>> > -1
>> > ));
>> > END IF;
>> >
>> > if nvl(vSaldo,0) <> 0 then
>> >
>> 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
>> > restevez_at_blh.com.do
>> > 809-535-8994

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).