Poor Performance for a Query on the DB LINK

[Balji]

I need someone who can help me to tune my query. In my query I am using two tables( only two) which exists in remote database. This query performance is very slow in the local database. but when I run this query at remote database it is very fast.

Any suggestion!!!

ppa has 11 millon rows
pac has 1 million rows
SELECT pac.segment1, decode(pac.segment1,'O','2','V','3','D','4','X')
FROM ps_temp1 ppa,
ps_temp2 pac
WHERE
ppa.person_id = 500
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.id_flex_num = 100
AND pac.segment1 IS NOT NULL

 

[Turkbear]

1: Have you done an explain plan to see what's up?
2lease clarify what you mean by
at local database
and
at remote database


If you mean that the slow one is using DB LINKS to get the data then the slowness is caused by not using the indexes - DB Links cannot use indexes on the remote machine)..

 

[Balji]

Thanks Turk for reply

Local means from where I submit this query( oracle 8.1.7) and Remote means where these two table exists ( oracle 8.0.5).

I am not able to get explain plan from local database. It just hang out there for hours without any output.

But when i try to get explan plan while sitting in remote database it shows me plan which uses proper indexes.

I think this makes it more clear.

 

[uparna]

hi Balji,
If you are running this query often enough , you may consider using Materialized views(snapshots) for these .... they will improve the performance of these queries termendously.

PS: assuming that you are calling another database from your local database ... Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[Turkbear]

Hi again,
Using data thru a db link will always be much slower because no indexes will,or can, be used.

uparna's idea is the only way to improve things..If you can implement it in 8.0.5 ( I'm not sure it was available then)

hth,