tuning a query over DBLINK
Posted on 2006-04-03
3 Servers, all with Oracle 9.2
* PROD --- live production server
* STANDBY --- standby database, updated every night by applying the logs, the rest of the day it is open as read_only
* DW --- a database that reports using data on the main database.
On PROD (and hence by STANDBY), there is a "big" table (10M rows), partitionned on a time field (creationtime).
+ index on some_field
I have a query that queries this table with a range condition on PROD/STANDBY:
select * from big_table where creationtime >= x and creationtime < y and some_field in ( list of values ) ;
returns in 8:35 minutes (0 rows)
I run the same query over DBLINK:
select * from big_table@PROD where creationtime >= x and creationtime < y and some_field in ( list of values ) ;
returns in 1:44:32 minutes (0 rows)
what could be the cause of this ?
what could be done to optimize this query over the dblink?
* I cannot add any view, functions, MViews or anything else to the PROD database. It's a custom application database, I have no change permissions to it at all.
* I tried to add the following hints with no (positive) effect:
- index ( myindex )