Using DBMS_STATS to Gather Statistics

Collection of statistics on the database objects is very important for the performance of the database. DBMS_STATS package is used to collect these statistics.
To collect system statistics, use following procedure

SQL> exec dbms_stats.gather_system_stats PL/SQL procedure successfully completed.

To collect Dictionary statistics, use following procedure

SQL> exec dbms_stats.gather_dictionary_stats PL/SQL procedure successfully completed.

To collect statistics on full database (all schemas tables/indexes etc.)

SQL>  exec dbms_stats.gather_database_stats PL/SQL procedure successfully completed

To collect stats on a single schema (all tables/indexes). Suppose schema name is SCOTT.

SQL> exec dbms_stats.gather_schema_stats('SCOTT') PL/SQL procedure successfully completed.

To collect stats on a single table.
First parameter of the procedure is table owner name, and second is the table name. Third parameter “cascade” can have value TRUE or FALSE, to mention whether stats on the index(es) of this table need to be collected or not. TRUE means collect index stats along with table stats.

SQL> exec dbms_stats.gather_table_stats('SCOTT','test_table', cascade=>true); PL/SQL procedure successfully completed.

To collect stats on a single index.
First parameter is index owner and second is index name.

SQL>  exec dbms_stats.gather_index_stats('SCOTT',’test_table_idx') PL/SQL procedure successfully completed.

For the full detail of DBMS_STATS package and its procedures (and parameters), see bellow document.

12c
https://docs.oracle.com/database/121/ARPLS/d_stats.htm

11g
http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm