How to gather statistics of a tables in Oracle

The dbms_stats.gather_schema_stats procedure allows you to gather statistics for all objects in a give schema.  This is the easiest way to generate statistics for a large number of objects.

Here is an example of using the dbms_stats.gather_schema_stats procedure to gather statistics on the SCOTT  schema of a database:

EXEC dbms_stats.gather_schema_stats('SCOTT', cascade=>TRUE);

This command will generate statistics on all tables in the SCOTT schema. Since we included the cascade command, the indexes will also have statistics generated on them. This is important, you need statistics on indexes as well as on tables in Oracle!

Of course, this is just the basic way to run this command. Several options are available, but for now as a new DBA this will do.  In fact, Oracle 10g automatically collects database  statistics every night out of the box.  Later you will want to investigate some of the Oracle Database 10g statistics gathering options such as histograms, and granularity.

If you create a new table, then it may not be practical or desirable to re-generate statistics
on the entire schema if the schema is quite large and the database is very busy. Instead you will
use the dbms_stats.gather_table_stats command to generate statistics for a single table,
and optionally for related table indexes. Here is an example:

EXEC dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>TRUE);

EXEC dbms_stats.gather_table_stats('SCOTT','PAYMENT_CEKM',cascade=>TRUE)

Script to gather statistics of a table

#!/bin/ksh

export ORACLE_SID=ORAADB1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus "/ as sysdba" <<EOF
set timing on
spool gather_stats_payment_cekm.out
EXEC DBMS_STATS.GATHER_TABLE_STATS('SARA','PAYMENT_CEKM',ESTIMATE_PERCENT=>10,METHOD_OPT=>'for all columns size AUTO',degree=>16, CASCADE=>TRUE);
spool off;

EOF

How to gather statistics of partitioned tables

BEGIN
DBMS_STATS.gather_table_stats (ownname=>'ORA',tabname=>'PAYMENT',partname=>'PY08MAYJUN',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',granularity=>'PARTITION',cascade=>TRUE,degree=> 8);
END;
/

BEGIN
DBMS_STATS.gather_table_stats (ownname=>'ORA',tabname=>'PAYMENT',partname=>'PY16NOVDEC',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',granularity=>'PARTITION',cascade=>TRUE,degree=> 8);
END;
/