DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: parameter optimizer_index_cost_adj=10

  1. #1
    Join Date
    Apr 2001
    I have posted a thread previously regarding queries not using the bitmap index, and I have been informed by Oracle to use the parameter optimizer_index_cost_adj=10. This will ensure the optimizer will use your indexes. Has anyone had any experience with this parameter? Thanks.

  2. #2
    Join Date
    Jun 2001
    Helsinki. Finland
    With a value of 10 you make the IAP = (Index Access Path) look 10 time less expensive (10 = 100/10).

    I personally think that the default value of 100 should not be touched. Sometimes a FTS might be better than an IS.

  3. #3
    Join Date
    Apr 2001

    Optimizer tricks

    Actually at last years IOUG-Live conference, they were pushing the usage of setting the optimizer_index_cost_adj to 10 or so to make index usage more attractive to the optimizer.

    Another good tip os to set the parameter optimizer_index_caching to a higher percentage. This tells the optimizer approximately how much percentage of the index it can expect to find in the buffer cache rather than have to go to disk. A setting of 90 can make a difference in how the optimizer works your queries.

    And yes, sometimes a full table scan is the better way to go, but not always. It's a matter of knowing both your data and your application.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  4. #4
    Join Date
    Aug 2001
    In regards to optimizer_index_caching, what buffer in the SGA is it using for cache?

    On another note, could you give a conceptual example where a full table scan is better? Is a full table scan better when you have a large table? Thanks.

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