Rule Based optimizer

[pareshg]

Guys,

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

Any suggestions are welcome.

Thanks

[davey23uk]

if it takes less time then use it. At the same time investigate why it takes longer as RULE is deprecated is later version and you won't able to rely on it.

Bad stats on remote tables possibly

[pareshg]

Thanks dave.

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?

[davey23uk]

whether statistics are gathered everyday is kinda irrelevant if the stats you actually gather are worthwhile or give enough information.

Trace the queries, find the execution plan - fix the problem

[abhaysk]

You may need to add some hints to work it as you want.. ( Check the plan with CBO )

Abhay.

[tamilselvan]

========
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.

Tamil

[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.

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.

Mike

[stmontgo]

Originally posted by pareshg
Guys,

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

Any suggestions are welcome.

Thanks

Forgive me for asking the obvious but do you have any control over generation of statistics on the tables at the remote site?

[DaPi]

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