Re: Analyze vs dbms_stats

News wrote:
> someone to confirm that :
>
> - analyze is more suited to RBO and both wont be supported any more ?

Not exactly correct. As has been stated, RBO uses rules to determine how to execute a given SQL statement. CBO uses statistics to help it make better decisions than RBO can. The method that was first introduced to generate those stats was with the ANALYZE command.

RBO is dead, now that 10g is out. It may have a little life in it, but for all intents and purposes, it is dead.

> - in 10g one should use dbms_stats package instead ? stats generated
> are more accurate and plans could be different ?
>
> - what's exactly the difference between the 2 methods ?

In Oracle 9i and higher, you should use DBMS_STATS for calculating statistics. The biggest reason is that Oracle Corp stopped development on the ANALYZE command and focused on DBMS_STATS instead. ANALYZE does not know how to generate stats on any new feature implemented since 9i.

Additionally, DBMS_STATS has abilities that ANALYZE never has. With DBMS_STATS, you can generate statistics with parallel processing. You can generate stats on stale objects. You can generate stats on the Data Dictionary. There are more examples.

The ANALYZE command is not going away, at least not yet. You will still use ANALYZE to LIST CHAINED ROWS for example. But for statistics generation, use DBMS_STATS in Oracle 9i and higher.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown