Home » RDBMS Server » Performance Tuning » Why the query is taking so long to execute
Why the query is taking so long to execute [message #65594] Thu, 04 November 2004 00:23 Go to next message
sangeeta Prabhu
Messages: 8
Registered: October 2004
Junior Member
Hi,

I have this SQL statement which is not at all responding.
When I execute the sub-query it responds in 16 seconds but when the whole query is executed it doesn't responds at all.I dont understands why when sub-query is executing in 16 seconds than the whole should at least respond in 1 or 2 hours. Can anybody give any suggestions Please to speed up this query.
I am copying the explain plan of the query.

Select /*+PARALLEL(TRI,4) */
       /*+ FIRST_ROWS */
        *
  from
       TSOP_RECIPIENT_INFO TRI
 WHERE
       TRI.RECIPIENT_INFO_ID
       NOT IN
      (SELECT /*+USE_HASH(TRI TST) */
              /*+DRIVING_SITE(TST)*/
             /*+ index(TST expression_ndx) */
            /*+INDEX_ASC(TRI TEMP_TRECPT_COMPOSE_PNDX) */
             TRI.RECIPIENT_INFO_ID
        from
             TSOP_RECIPIENT_INFO TRI,
             CUS.TSOP_TRANSMITTAL@DT.LINK TST
       where
             TRI.RECIPIENT_INFO_ID  = ((TST.SOP_LOG_ID_C * 100) + TST.SOP_TRNSMTL_SET_Q))
       

                                                              COST  CARD BYTES
SELECT STATEMENT, GOAL = FIRST_ROWS    3328797811 369005 51660700  
 FILTER       
  TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO  3706  369005 51660700  
  HASH JOIN       9021  375346 12386418 
   INDEX FULL SCAN ARROW TEMP_TRECPT_COMPOSE_PNDX 26  369005 2583035  
   REMOTE       4575  7506912 195179712  

kindly, help as this is urgent.

thanks in advance

Milind
Re: Why the query is taking so long to execute [message #65595 is a reply to message #65594] Thu, 04 November 2004 01:21 Go to previous message
AlanP
Messages: 4
Registered: October 2003
Junior Member
You could try the following

1) The join to TSOP_RECIPIENT_INFO is unnecessary in the subquery in your example.
2) Try not exists instead of not in
3) Try an outer join between the two tables i.e.
select * from x, y where x.id=y.id(+) and y.id is null
4) is there an index on recipient_info_id?

Alan
Previous Topic: Memory grows steadily - Oracle 9i
Next Topic: Gather Schema Stats fails on functional/bitmap index
Goto Forum:
  


Current Time: Wed Jan 27 14:33:53 CST 2021