Statistics

Statistics is a meta-data that contains information about database and database object. This meta-data is used by query optimizer to generate best execution plan. 

To gather statistics for a table

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

Scott – schema
EMP – table
cascade => true will collect statistics for all related objects for the table like index.

To gather statistics for a schema

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

scott – schema

To see the statistics for table

Use DBA_TABLES to view complete statistics for a table. Else at user level use the view “all_tab_statistics” to view the table statistics.

Example

select * from dba_tables where table_name= upper(‘mscompare_input_temp’);
select * from all_tab_statistics where table_name = upper(‘mscompare_input_temp’);