Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Unanswered: When to use hints?

    Up to now, I found that the oracle cost-based optimizer will give the best plans with lowest cost in most cases, if not in all cases.

    No matter what hints I specified in the statements, I would never get a better plan than oracle cost-based optimizer.

    Question is:
    Do we need to use hints for performance tuning any more?
    Should we use hints only when we use the rule-based optimizer?

    Thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well I have found occasions where hint were required to get the best execution plan (also a way of ensuring plan stability if you know our data distribution isnt going to change much) though usually with really complex sql (where the number of permutations is very large). I have also found it useful to get round oracle bugs i.e. in 9i I have used hints to get round index skip scan bugs.

    Alan

  3. #3
    Join Date
    Aug 2003
    Posts
    13
    HI, Alan.

    Can you use hints that are oracle's product to get round bugs of optimizer, which is also oracle's product?

    How can we trust one rather than another?


    Originally posted by AlanP
    Well I have found occasions where hint were required to get the best execution plan (also a way of ensuring plan stability if you know our data distribution isnt going to change much) though usually with really complex sql (where the number of permutations is very large). I have also found it useful to get round oracle bugs i.e. in 9i I have used hints to get round index skip scan bugs.

    Alan

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well if you come across a bug in Oracle like the index skip scan one then your query just doesnt run, with the hint it does and it produces correct results. The key thing is check your query runs OK on production aswell as other environments.

Posting Permissions

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