Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unhappy Unanswered: SQL Tuning : Strange Behaviour

    I have observed something strange.
    If I delete statistics of a table the query runs much faster than how it would run with the statistics present.
    With the statistics present for the table , Explain Plan does not show the correct index usage while after deleting statistics , the correct index choice is seen in theExplain Plan.
    I understand in the absence of statistics , optimizer becomes RULE based as I saw that the Explain Plan of the SQL query with statistics of thetable absent matches with the Explain Plan for the query with the hint as RULE .
    But why is it that the correct index is not being chosen by the optimizer even though I dropped and recreated the index and then analyzed the table.
    I expect Cost based optimizer to be intelligent than the RULE based.
    Pls help!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well the cost based optimizer is more intelligent but that doesnt mean its perfect, occasionally the rule based one will be better (especially if the app has been tuned for the rule based optimizer). After all the optimizer is guessing at the best way of exectuing your query given what your data is and how your database has been configured.

    Things you can do are

    1) use hints to get it to use the right execution plan or modify the sql so it favours a particular execution path.
    2) use different options on your analyze to do a more detailed analysis i.e. for all indexed columns size <n>, look up the analyze command or dbms_stats.
    3) alter your index i.e. rebuild it so it might be more efficient to use the index, or compress it.
    4) look at your init.ora parameters like optimizer* and db_file_multiblock_read_count (smaller values favour indexes).
    5) gather system stats using dbms_stats so the database has a better idea of the cost of table reads versus index reads.

    Dont give up as in oracle you have many ways of getting to your goal, it just takes time.

    Alan

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Thanks Alan!
    My optmizer related parameters are valued as follow

    optimizer_dynamic_sampling integer 1
    optimizer_features_enable string 9.2.0
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    optimizer_max_permutations integer 2000
    optimizer_mode string CHOOSE

    (ii) I am using
    dbms_stats.gather_table_stats with cascade option.

    (iii) I had tried with index dropped and recreated.
    (iv) Db_File_Multiblock_Read Count is set at 32 ( DB Block Size is 16)

    Thanks

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can try the following using alter session to see what effect it has without affecting other users.

    1) set Db_File_Multiblock_Read Count =16 (in effect doubling the cost of FTS)

    2) set
    optimizer_index_caching =70
    optimizer_index_cost_adj =15
    to again make indexes look more favourable.

    And try the following on your dev/test environment before you try it on prod as it may effect your application.

    3) see the method_opt parameter in gather_table_stats to see how to collect histogram data on your columns.

    4) Gather system stats using dbms_stats (look it up in google for more info).

    Alan

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Thanks Alan!
    I will test these and revert back with my findings.
    Thanks again!

Posting Permissions

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