Re: Query takes ages in Oracle 10G

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: isabel_bga@xxxxxxxx
  • Date: Thu, 21 Sep 2006 23:35:10 +0200

Isabel,

I guess from what you say about the subquery returning no row that the various criteria that you provide are likely to be selective (taken together, at least).

Just two remarks:
* I don't know if this is a query in which you have replaced bind variables by constants for testing purposes (if not, and if this type of query is likely to be executed often, you should use bind variables for what varies from call to call). This may be the reason why you have no explicit date conversion function? Always use an explicit TO_DATE(), anybody can decide one day to use the American format by default instead of your current European format. This has moreover the good taste of protecting against unwanted type conversions that make your indexes unusable.
* It is very common with dates to have extreme values that totally mislead the optimizer into believing that a range scan (such as the one you have) is either much more or much less selective than it really is, and therefore happily going for a nested loop when you want a hash join or the reverse. Classic cases are dates entered with a YY format when what was wanted was an RR (hence dates from the reign of Emperor August), or future dates set as of December 31st 3999 or similar, which requires an histogram.
You may find the stat_sanity.sql script on this page : http://www.roughsea.com/rubrique.php3?id_rubrique=14 useful for checking what the optimizer thinks is the scope of data.


HTH

Stéphane Faroult



Isabel Bedoya wrote:
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>.

-- //www.freelists.org/webpage/oracle-l


Other related posts: