RE : RE: Query takes ages in Oracle 10G
- From: Isabel Bedoya <isabel_bga@xxxxxxxx>
- To: Ron.Reidy@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Sep 2006 17:31:42 -0400 (EDT)
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 584 0.86 1.85 0 0 2 0
Execute 1510 2.12 4.10 94 64 226 28
Fetch 3353 672.75 1349.55 10158 4238323 1 5223
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5447 675.73 1355.51 10252 4238387 229 5251
Misses in library cache during parse: 97
Misses in library cache during execute: 86
173 user SQL statements in session.
1265 internal SQL statements in session.
1438 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: prt.trc
Trace file compatibility: 10.01.00
Sort options: fchela exeela
1 session in tracefile.
173 user SQL statements in trace file.
1265 internal SQL statements in trace file.
1438 SQL statements in trace file.
107 unique SQL statements in trace file.
14348 lines in trace file.
1356 elapsed seconds in trace file.
"Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx> a écrit :
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
What wait event(s) were shown in your trace file?
---------------------------------
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Isabel Bedoya
Sent: Thursday, September 21, 2006 3:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query takes ages in Oracle 10G
Hi all,
I am trying to run a query with sub-queries in Oracle 10G Release 2 and it is
taking ages, I divided the query into parts and I have no problems running the
first and third subqueries (together and each one separated) but when I try to
run the second subquery along with the other two or just itself, it takes ages
(I generated a trace and returned a wait of 22 minutes executing the query
giving no results at all).
The table has 22567 records in it and runs PERFECTLY in Oracle 9i, we didn't
change anything while migrating to 10G. It seems there is a problem with the
date being invoked in the second subquery. Do you have any clue about what can
be happening with this query?. Thanks
SELECT *
FROM TITULO
WHERE TITULO_ESCENA_CODIGO___ = 'REAL' AND
TITULO_PORTAF_CODIGO___ = 'OBL' AND
TITULO_TIPTIT_CODIGO___ = 'TES_TF' AND
(TITULO_INDTITGAR IS NULL OR TITULO_INDTITGAR = 4) AND
TITULO_FUTURO___ IS NULL AND
TITULO_ESTADO___ = 2 AND
TITULO_CLASIFICA <> 3 AND
TITULO_NUMERO___ NOT IN (SELECT OPETIT_TITULO_NUMERO___
FROM OPETIT, OPERAC
WHERE OPETIT_ESCENA_CODIGO____OPE =
'REAL' AND
OPETIT_PORTAF_CODIGO____OPE = 'OBL'
AND
OPETIT_OPERAC_NUMERO___ =
OPERAC_NUMERO___ AND
OPERAC_ESCENA_CODIGO___ = 'REAL' AND
OPERAC_PORTAF_CODIGO___ = 'OBL' AND
OPERAC_TIPOPE_CODIGO___ = 2 AND
OPERAC_ESTADO___ IN (2, 3)) AND
TITULO_NUMERO___ NOT IN (SELECT VENCIM_TITULO_NUMERO___
FROM VENCIM
WHERE VENCIM_ESCENA_CODIGO___ = 'REAL' AND
VENCIM_PORTAF_CODIGO___ = 'OBL' AND
VENCIM_ESTADO___ = 2
AND
VENCIM_FECCOBORI <= '21/09/2006') AND
TITULO_NUMERO___ NOT IN (SELECT T_TITU.TITULO_NUMERO___
FROM T_TITU, T_OPTI
WHERE T_TITU.TITULO_ESCENA_CODIGO___ = 'REAL'
AND
T_TITU.TITULO_PORTAF_CODIGO___ = 'OBL' AND
T_TITU.TITULO_NUMERO___ =
OPETIT_TITULO_NUMERO___ AND
OPETIT_ESCENA_CODIGO____OPE = 'REAL' AND
OPETIT_PORTAF_CODIGO____OPE = 'OBL'
AND
OPETIT_OPERAC_NUMERO___ = 58)
---------------------------------
Lèche-vitrine ou lèche-écran ? Yahoo! Magasinage.
---------------------------------
This electronic message transmission is a PRIVATE communication which
contains information
which may be confidential or privileged. The information is intended to be for
the use of the individual
or entity named above. If you are not the intended recipient, please be aware
that any disclosure,
copying, distribution or use of the contents of this information is prohibited.
Please notify the sender
of the delivery error by replying to this message, or notify us by telephone
(877-633-2436, ext. 0),
and then delete it from your system.
---------------------------------
Lèche-vitrine ou lèche-écran ? Yahoo! Magasinage.
Other related posts:
