dbms_stats package used for collecting statistics
summary
gather statistics - system, dictionary, fixed_objects , sys
gather schema statistics
gather table stats
gather index stats
Gather Statistics
gather statistics - system, dictionary, fixed_objects , sys
exec dbms_stats.gather_system_stats('interval', interval=>180);
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_schema_stats('SYS',degree=>30,estimate_percent=>100,cascade=>TRUE);
gather schema statistics
exec dbms_stats.gather_schema_stats(ownname =>' SYSADM',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,granularity=>'ALL',cascade=>TRUE);
gather table stats
exec dbms_stats.gather_table_stats(ownname =>' SYSADM',tabname =>'PM',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,cascade => TRUE );
gather index stats
exec dbms_stats.gather_index_stats( ownname=>’SYSADM’,indname=>’PM_INDX',granularity=>'ALL',estimate_percent=> 10,degree=>1);
Hope this help. Regards Rupam
