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

Report message to a moderator

- 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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 01 22:57:36 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.