DBMS_STATS package

Use DBMS_STATS to gather Oracle dictionary statistics. DBMS_STATS is the preferred and recommended method as it is more complete that the ANALYZE command which soon (read on http://asktom.oracle.com) will soon be depreciated. DBMS_STATS gathers more information about the objects it works on.

1. To gather table statistics with DBMS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'KUBILAY', tabname => 'PART_USER')

2. To gather table statistics with DBMS_STATS with estimate percent

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'KUBILAY', tabname => 'USERS', estimate_percent =>50)

3. To gather schema statistics with estimate percent

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('KUBILAY', estimate_percent =>50)

4. To gather a partition’s statistics in full

SQL> EXECUTE dbms_stats.gather_table_Stats( ownname => 'SCOTT', tabname => 'SOME_TABLE', partname => 'P0705');

* To gather statistics on a partition without calculating statistics for indexes, i.e. if the table partition is really big and there are lots of indexes and you want to skip them, use this:


EXECUTE dbms_stats.gather_table_stats
(
ownname=>'SCOTT',
tabname=>'SOME_TABLE',
partname=>'SOME_PARTITION',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>FALSE,
degree=>DBMS_STATS.AUTO_DEGREE,
granularity=>'PARTITION'
);


This will work exclusively and only on the partition specified, it will ignore all indexes of the partition!



The Oracle Way

Faster and neater, with more accurate estimates if you use Oracle recommended procedures, and parameters see below. These parameters have a mind of their own and they decided how much they should estimate and if they should run the operation in parallel or not.

1. On Schema with DBMS_STATS

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'MY_SCHEMA', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

2. On Table with DBMS_STATS

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SESSIONS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

I find using Oracle Recommended values (i.e. degree=>DBMS_STATS.AUTO_DEGREE) always makes the statistics gathering faster and more effective.


The documentation for the full package is here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461