Cost Based and Rule Baed Optimizer Problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Cost Based and Rule Baed Optimizer Problem

Hybrid View

  1. #1
    Join Date
    Nov 1999
    Posts
    226

    Angry

    Hi

    I am working on Oracle 8.0.6 and the Init file has the Optimizer_mode set to = CHOOSE .
    I did an estimate of statistics and the registration of the webiste became very slow .

    It became fine when I gave the command " Analyze table tablename delete statistics"

    I want to know under what conditions the analisation of tables can be disadvantageous .

    regards


  2. #2
    Join Date
    Jun 2000
    Posts
    295
    Although Oracle recommends to use cost optimizer,
    but as I know, in the industry, there are a lot shops
    are still using rule base optimzer.
    We are using rule base optimzer and running
    Oracle 8.0.6 on Sun Solaris.

    I heard there are a lot of Oracle applications are still
    using rule. :-(

  3. #3
    Join Date
    Nov 2000
    Posts
    205
    I noticed that having the optimizer_index_cost_adj parameter a bit high sometimes makes performance suffer. This
    gives the optimizer a high cost associated with using an index.

    A good number to set it at would be 20.

    This can be done at the database level by modifying the init.ora, or just for a sql session. To do it for a sql session run the following at an sql prompt:

    alter session set optimizer_index_cost_adj=20;

    This will set the parameter for your entire sql session.

    After doing this you should be able to run the code without the hints. I suggest making the parameter change in the init.ora file for the whole
    database.

    Anyway, normally (like in our case it was a default value which was high) it is not listed in the init.ora file.

    This helps sometimes and it did in our case. I normally compute statistics because the difference is worth the extra wait.

  4. #4
    Join Date
    Jun 2000
    Posts
    295
    I am curious about:
    optimizer_index_cost_adj

    What is it used for?

    Thanks,


  5. #5
    Join Date
    Nov 2000
    Posts
    205
    Hi There,

    Even though there are statistics, sometimes our index is faster. The higher the value, the more it would disregard our index.

    Anyway, we were running queries and could see that it was not going faster. We used hints to force usage of our index and wham it was great. We wanted the system to consider our index even without hints, so I tried changing per session, and it was great.

    Anyway I am not sure if it would address your problem, but there is so much new stuff (and fewer people that know about it), I just thought it was worth a check out.

    Good luck, and let me know if it helps.
    Nirasha

  6. #6
    Join Date
    Jun 2000
    Posts
    295
    Yeah, since I can't find the definition from my doc,
    I will appreciate if you post the explaination of it.

    You know from Oracle doc, I can find
    OPTIMIZER_MODE:
    OPTIMIZER_MODE specifies the behavior of the optimizer. When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)

    You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time.

    For more information about tuning SQL statements, see Oracle8 Tuning. For more information about the optimizer, see Oracle8 Concepts and Oracle8 Tuning.



  7. #7
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    It could also be that in this case, the estimate wasn't large enough. I'd try doing a compute statistics and see if that helps. If that still doesn't force the use of the index, I'd look and see if you need to create histograms on the column(s) that the index references.

    When you deleted the statistics it forced the CBO to default back to rule_based optimization.

    As was said earlier, you can use the parameter

    optimizer_index_cost_adj = ## which will influence how much weight is given to indexes when computing the cost for using indexes. Oracle typically responds to users complaining that the CBO isn't using their indexes correctly will tell users to set the value to 10 and if it works then start adjusting it until it seems to be selecting the best access path.

    You could always use the CBO and use a hint to influence the particular query as well.

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  8. #8
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    An additional comment on RBO vs. CBO

    Oracle itself actually continues to user RBO. Oracle
    does not support (at least this used to be the case)
    analyzing the system tables.

    Typically in an OLTP environment you want to use
    first_rows because it optimizes to return the first
    row as quickly as possible, while it may take longer
    to retrieve the rest of the rows. It is typical in these
    environments for a user to want to see only a single
    row.

    In a DSS environment you typically are returning
    many rows during a query and very rarely are only
    returning a single row. Most of the queries involve
    full table scans vs. indexes (one of the reasons Oracle
    put in the CBO was to deal with the fact that with
    queries that are returning a large percentage of table
    rows it is better to do a full table scan than using an
    index - in RBO, a full table scan is the absolute last
    resort). In the case of these types of queries, you
    don't care about how quickly the first row is returned
    (best response time) but rather how much resource
    is used to return the entire data set (best throughput).

    Just as a little extra....

    Access Paths
    1. Single row by ROWID
    2. Single row by cluster join
    3. Single row by hash cluster key w/ unique or primary key
    4. Single row by unique or primary key
    5. Cluster join
    6. Hash cluster key
    7. Index cluster key
    8. Composite key
    9. Single-column indexes
    10. Bounded range search on indexed columns
    11. Unbounded range search on indexed columns
    12. Sort-merge join
    13. MAX or MIN of indexed column
    14. ORDER BY on indexed columns
    15. Full table scan

    The RBO will choose the lowest numbered access path possible.

    There are also several unranked access paths (can't be used with RBO)

    Fast full index scan
    Bitmap index scan

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  9. #9
    Join Date
    Nov 2000
    Posts
    205
    This is just because I came across an explanation in my manual (oracle 8i DBA Handbook) pg 919- Appendix B:

    " Optimizer_Index_Cost_Adj

    Default is 100; sets a percentage multiplier (0 to 10, 000) by which the optimizer should adjust the cost of index_based accesses. Setting a value below 100 will cause index accesses to have a lower estimated cost, and therefore be more likely to be chosen"

    Hope this helps.

    Nirasha

  10. #10
    Join Date
    Nov 2000
    Posts
    212

    do not generate statistic if data is 'wrong'

    CBO works by its own rules which makes decisions using statistics. If statistics are wrong, then it builds wrong plans. Recently I had this problem:

    table had a lot of test records with the same column f.k. value. CBO desided not to use the index just because it found out this index has only 7 distinct values for 500.000 records.

    So, because w/o statistics CBO uses rule, then it is better to clean statistcsif one is not sure about them: CBO features still are available, like partition pruning, but no messing with 'wrong' data.




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