Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Dominican Republic

    Unanswered: Confused about RBO vs CBO

    Hi all. Recently I was trying to tune some querys I had in a scheduled stored procedure. While I was investigating, I found something strange in a query:

    Please, click on this link to see the actual plan and execution time.

    As you can see, using cbo was extremely slower than using rule and I wonder what the case for this could be. I tried tricking optimizer_index_cost_adj a litle bit, but that would only get me to use one index as much, and not as near as submitting the query in rule base.

    So, what do you think that is the reason the db is acting like this ? what can I change so this doesn't happen ? Who is right here, RBO or CBO ?

    All oppinions and suggestions welcomed!

  2. #2
    Join Date
    Aug 2004

    First of all, since 9i, Oracle recommends to use the Cost-Based Optimizer instead of the Rule-Based one (cf Oracle doc). Now, the problem with the CBO is that if your db statistics are too old, or if you don't have computed statistics at all, the CBO will WORK VERY POORLY. On the contrary, if you have up-to-date stats, it will WORK BEST. Conclusion : COMPUTE STATS (for that, use the DBMS_STATS package) and USE THE CBO.



  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Do all the tables involved have current statistics; which are used by the CBO?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2002
    Reading, UK
    There are a lot of things which can confuse the CBO.

    1. Use DBMS_STATS.gather_schema_stats to analyze all your tables and indexes.

    for example

    ownname => 'XXX',
    estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE ,
    block_sample => TRUE,
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE 10',
    degree => NULL,
    granularity => 'DEFAULT',
    cascade => TRUE,
    stattab => NULL,
    statid => NULL,
    options => 'GATHER',
    no_invalidate => TRUE,
    gather_temp => FALSE); end;

    2. Gather system stats during typical workload.

    begin dbms_Stats.Create_Stat_Table (ownname => 'SYSTEM', stattab => 'dbastats', tblspace => 'users'); end;

    BEGIN dbms_Stats.Gather_System_Stats(
    interval => 30,
    stattab => 'dbastats',
    statid => 'OLTP',statown=>'SYSTEM');

    begin sys.dbms_stats.import_system_stats('dbastats','OLT P','SYSTEM'); end;

    3. Adjust the most influential parameters.
    a)db_file_multiblock_read_count (less prefers indexes)
    b)optimizer_index_caching (i've currently got 70)
    c)optimizer_index_cost_adj (i've got 30)

    Do a search on google for more info on these and others. You may need to play around quite a bit with these. Other things which can help are buffer pools, rebuilding indexes, compressing indexes etc. It may take some time but with Oracle there is always some way of getting it to do what you want.

    Also dont forget that unless you have identical spec box AND data doing optimising on you TEST or DEV box wont necessarily work on PROD. All the hints are for 9ir2 but will work on earlier versions with the exception of gather_system_stats I think.

    Last edited by AlanP; 08-13-04 at 12:28.

  5. #5
    Join Date
    May 2004
    Dominican Republic
    Sorry, I forgot to mention that I am using 8.1.7!

    The tables are analyzed on a daily basis (the whole schema), but not with dbms_stats (will update with that) but rather with dbms_utility. I wonder, when you COMPUTE using dbms_utility, does it also analyze all the indexes associated with each column, etc.. ? (sorry, Im going to RTFM now but If you can tell before I do would be much much appreciated!)

    Thanks you guys, especially to AlanP for the direct suggestions!

  6. #6
    Join Date
    Jul 2003
    all I know is that with dbms_stats "cascade => TRUE" then analyzes
    all indexes as well as the table statistics.

    dbms_stats is still recommended over dbms_utility for 817.
    I believe I read somewhere that dbms_utility was bugged somehow and
    Oracle was only recommending the use of dbms_stats.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Is it possible that the data in the indexes is skewed ??
    IE, index on dept
    values of 10, 20 , 30 , 40 ,50
    Oracle will assume a 20% return for an index with the above values,
    but suppose you have 100 rows with the following distribution ...

    dept 10 20 30 40 50
    rows 5 5 5 5 80

    In 4 out of 5 cases (ie, where dept=10, dept=20, etc) using the index
    will return the rows quicker ... In this case, you need to use histograms
    in order to tell Oracle about the distribution of the 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