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 <http://cf.shopping.yahoo.com> . 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.