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" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >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 >[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'); >> > >> > >> > ; >> > >> > 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 >> > [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).
