Home » RDBMS Server » Performance Tuning » URGENT:-Problem due to Remote access of a Table in SQL
URGENT:-Problem due to Remote access of a Table in SQL [message #65581] Tue, 02 November 2004 01:11 Go to next message
sangeeta Prabhu
Messages: 8
Registered: October 2004
Junior Member
Hi Friends,

I have this query which is taking a long time to execute.I think it is due to the reference of a table in remote schema. I am also specifying the explain plan of the query.

Query:-

Select *
  from         TSOP_RECIPIENT_INFO TRI
 WHERE TRI.RECIPIENT_INFO_ID NOT IN
       (SELECT TRI.RECIPIENT_INFO_ID
          from TSOP_RECIPIENT_INFO TRI,
               CUS.TSOP_TRANSMITTAL@DT.LINK TST
         where TRI.RECIPIENT_INFO_ID = ((SOP_LOG_ID_C * 100) + SOP_TRNSMTL_SET_Q))

Explain Plan:-
                                                     COST          CARD BYTES 
SELECT STATEMENT, GOAL = CHOOSE       3.11450042397975E15 369005 51291695  
 FILTER       
  TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO 14821    369005 51291695  
  NESTED LOOPS                           8440266186 375346       12011072  
   TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO 14821    369005 2214030  
   REMOTE      22873    1         26  

Can anybody suggest how to speed up this query.

Thanks in Adavance

Nandini

Report message to a moderator

Re: URGENT:-Problem due to Remote access of a Table in SQL [message #65583 is a reply to message #65581] Tue, 02 November 2004 06:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In your subquery, one of the 2 tables is going to have to go across the network. You want to minimise that amount of data over the network. Try a driving site hint in the subquery.

SELECT /*+DRIVING_SITE(dept)*/
FROM emp, dept@rsite
WHERE emp.deptno = dept.deptno;

Report message to a moderator

Re: URGENT:-Problem due to Remote access of a Table in SQL [message #65585 is a reply to message #65583] Wed, 03 November 2004 00:11 Go to previous message
sangeeta Prabhu
Messages: 8
Registered: October 2004
Junior Member
Hi Andrew,

Thanks for the suggestion. That really helped I used the hint as mentioned below as well as I created a Function Based index. Now,the subquery responds in 45 seconds.But,whenever I execute the both the queries together the query hangs and there is no response.
Kindly, suggest some point so that the whole query responds faster.

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

Thanks & Regards

San

Report message to a moderator

Previous Topic: Why not use analyze?
Next Topic: SQL Tuning
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 11:33:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.