Value setting for optimizer_index_caching
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Value setting for optimizer_index_caching

  1. #1
    Join Date
    Sep 2000
    Posts
    384

    select * from ACT where STATUS='I'
    Total rows in a table 508633
    no of rows returned is 4
    Index is there on status column .
    optimizer_mode=choose

    The index is not used ..Under rule based optimizer it works .
    when given a hint the index is used ..

    what values should I give under thses circumstance for these two parameters to make index usage ...

    optimizer_index_caching 0
    optimizer_index_cost_adj 100
    Radhakrishnan.M

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    I have had the best results with:

    optimizer_index_caching=80
    optimizer_index_cost_adj=10

    For more information check this Url:

    http://www.EvDBT.com/

    Regards

    Angel

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    After testing these parameters I noticed that they have almost now influence on the speed of the queries. Probably depends on the system.




  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Julian is correct, changes to these setting may give you no benefit, and even unpredicible timings.

    I'd be checking you have analyzed your ACT table. Because if there are no statistics present, Oracle with select to run RULE optimizer as a default when optimizer_mode=choose.

    Good luck,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    What type of index is on the status column?

    If the cardinality of the column is low try creating this index as a bitmap - It may speed up the query.

    Cheers
    Moff

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Bitmap indexes may speed up many ad hoc queries, yes, but you should not forget to set up proper values for CREATE_BITMAP_AREA_SIZE and BITMAP_MERGE_AREA_SIZE.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what is your db, oltp or dwh?

    is your data distribution uniform?

  8. #8
    Join Date
    Sep 2000
    Posts
    384
    the database is oltp type ...
    The data is distibuted evenly ..

    i have indexed tables ,indexes then also it is not using the indexes .On rule based it uses the indexes ....
    Radhakrishnan.M

  9. #9
    Join Date
    Sep 2000
    Posts
    384
    I deleted the statistics now the index is working ...
    Radhakrishnan.M

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Well I guess your status column has very low cardinality... how many distinct status do you have?

    Your data is not distributed evenly, I just saw the number of rows returned, 4 out of half million.... if you generate histograms for that index most probably optimizer would use the index for that specific query. However if your application is using bind variables then histograms is useless....

    Also by deleteing statistics is forcing using RULE optimizer

    Try generate the histogram wth 20 hash buckets see if it uses optimizer, if not increase hash buckets to 40 and see

    But are you sure your query is faster with RULE optimizer????

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