Statistics - DBMS_STATS

Table statistics:

DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => NULL, method_opt =>'FOR ALL INDEXED COLUMNS SIZE AUTO', granularity =>'ALL', NO_INVALIDATE => TRUE, Cascade => TRUE);

Dynamic query

SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '||CHR(39)||owner||CHR(39)||', tabname =>'||CHR(39)||object_name||CHR(39)||', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => '||CHR(39)||'FOR ALL COLUMNS SIZE AUTO'||CHR(39)||', cascade => TRUE);'
from dba_objects 
where object_name IN ('DEPT', 'EMP') and object_type = 'TABLE';

Database Statistics:

EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
EXEC DBMS_STATS.gather_database_stats;

Schema statistics:

dbms_stats.gather_schema_stats( ownname => 'SCOTT', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

dbms_stats.gather_schema_stats( ownname => 'SCOTT_ADM', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

BEGIN 
DBMS_STATS.gather_schema_stats ( 
ownname => 'SCOTT', 
cascade => TRUE, 
options => 'GATHER STALE');
END; 
/ 

Recompile objects:

@?/rdbms/admin/utlrp.sql