But stats are collected everyday for remote tables,so its not the issue.
We are accessing the tables through DB link and we are joining around 7-8 remote tables in the select queries. Few tables does contain lots of data. What will you recommend?
Are there any drawbacks in using RULE hints in some of the quries in the packages?
Some of the quries written in packages are querying data from few tables from remote database. And what I found out is if I use the Rule hint in such select queries, they does take less time.
Database - 8.1.7 on Windows 2000
Optimizer_mode - choose
Avoid using RULE hint. Once you have exhausted all other possibilites, then you can try RULE hint.
Since, the PKG uses remote table over dblink, you may not be able to see join operation performed at the remote location in the tkprof output.
You can try driving_site or all_rows or first_rows hint , or in-line view to tune the SQL statement. These hints are safe.
One technique I have seen used successfully is to roll the query into a view at the remote site, allowing you to optimize the query at the remote site as much as needed. Then you simply query the view over the link.
In some cases this may not be possible such as if the queries are generated ad hoc, but this doesn't seem to be the case here since you can hint the queries.
Michael R. Ault
Originally posted by mikerault One technique I have seen used successfully is to roll the query into a view at the remote site, allowing you to optimize the query at the remote site as much as needed. Then you simply query the view over the link.
Re-doubled in spades if you're using ODBC
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman