COST vs Rule based optimizer
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: COST vs Rule based optimizer

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    One of our clients, had a certain report that was bringing the database to a complete halt for 10 minutes while it ran. The dba changed the instance from rule based optimization to cost based and suddenly the report ran in a minute or so and did not monopolize the resources.

    I thought that cost based was the default, but am I wrong?

    How do I know what we are using here ?

    thanks
    Sonali
    Sonali

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    select * from v$parameter where name = 'optimizer_mode'

    Check the value column. It will be either Rule, Cost or Choose.

    Rule and Cost speak for themselves.
    Choose (the default value) will use the Cost Based Optmizer when at least one of the objects in the query has statistics gathered for it. Otherwise the rule based optimizer is used.

    HTH

    David.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Thanks a lot David.
    Just one more question you said it will use cost based optimizer if atleast one of the objects in a query has statistics gathered.. How do I do this ???

    thanks again
    Sonali

  4. #4
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    Use "analyze table" command. You can compute statistics for that table OR do estimate statistics if the tablle has many rows.

    Baliga

  5. #5
    Join Date
    Feb 2001
    Posts
    123
    You can also use the DBMS_STATS.ANALYZE_SCHEMA packaged procedure to analyze all the objects in a particular user's schema. (Sorry, I can't recall all the parameter settings required for this procedure off the top of my head, but it should point you in the right direction when you have a look at the documentation at least ;-)

    HTH

    David.

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    Yes, I did infact use the same dbms_utility to analyze schema.
    execute dbms_utility.analyze_schema('OWNER', 'COMPUTE');
    But my problem is even after I ran that

    select * from v$parameter where name = 'optimizer_mode'
    still shows choose !

    How do I know now that it is using cost based optimizer ?

    thanks
    Sonali

  7. #7
    Join Date
    Feb 2001
    Posts
    9
    Don't Worry about the Choose that is Default and it is best to have.

    If your tables are Analyzed, Oracle will use the Cost based Otherwise Rule based.

    Better to Have Choose as it works for Both...
    Cheers.
    The Great Certified DBA in Oracle 8i

  8. #8
    Join Date
    Sep 2000
    Posts
    47
    A few points:

    1. I agree that CHOOSE is the appropriate optimizer mode.
    2. Regenerate your statistics on a regular basis. How often depends on your transaction volume; I do it twice a week in one production database, once a week in the other. I use a cron job to compute (not estimate) the statistics during off-hours.
    3. Sometimes RULE works better for a particular query, so remember that you can use optimizer hints on an individual basis. Experiment (with sql_trace true) to determine which method is better for problem queries.
    4. I have had luck with some problem queries by *deleting* statistics from specific tables. I'm not sure why this works, but it does.

    Tim

  9. #9
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    It works because in the absence of statistics, Oracle uses the rule-based optimizer. In your case, the CBO must have picked a less-appropriate path to your data than that which was taken by the RBO. It may have been you were doing a full table scan before but with the RBO used an index which is preferred by RBO (fts is the last access path chosen after all other possibilities have been exhausted).

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

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