Using DBMS_STATS package

[tpclouis]

Dear All,

We are doing performance-testing of SQL-statements in our project. Its heard DBMS_STATS package can be used for the same . Can anybody tell me how that can be used for performance-testing and how much it is useful .
Can it be used as an alternative of ANALYZE ?.

Louis.

[Prettyman]

Yes. Detail information in "Oracle8i Supplied Packages Reference".

[mrvajrala]

DBMS_STATS can be used to collect the statistics of schema objects, just like we did ANALYZE on schema objects. But there are few differences between these two.

Analyze is a depricated feature in 9i. But to get the CHAINED_ROWS , you may wish to use ANALYZE command.

Oracle recomends to use the DBMS_STATS since 8i

Never analyze the SYS schema, with DBMS_STATS package, It makes the things worse , when you query a data dictionary table, since Oracle will be using the RULE based optimizer when you query data dictionary, but when you use DBMS_STATS to gather stats for a schema, those will be PRO COST based optimizer. I am not sure if this is fixed in 9i or not.

we use as follows:
DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA1',25,FALSE,'FOR ALL COLUMNS SIZE 1',NULL,'DEFAULT',TRUE ) ;

Hope this helps!!