Rule Based optimizer
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Rule Based optimizer

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149

    Rule Based optimizer

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    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?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    You may need to add some hints to work it as you want.. ( Check the plan with CBO )

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ========
    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

  7. #7
    Join Date
    Apr 2005
    Posts
    17

    Use a remote view

    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
    Michael R. Ault
    Senior Consultant
    Burleson Consulting

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Rule Based optimizer

    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?
    I'm stmontgo and I approve of this message

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Use a remote view

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width