Important collective commands used for monitoring and enhancing the performance of an Oracle database :


EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME',DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_dictionary_stats;

EXEC DBMS_STATS.gather_system_stats;

EXEC DBMS_STATS.gather_fixed_objects_stats;


EXEC DBMS_UTILITY.analyze_schema('SCHEMA_NAME','ESTIMATE', estimate_rows => 100);

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCHEMA_NAME','COMPUTE');

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCHEMA_NAME','ESTIMATE', estimate_rows => 1000);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME',estimate_percent => dbms_stats.auto_sample_size);

 EXEC DBMS_DDL.alter_compile('PROCEDURE', 'SCHEMA_NAME', 'PROCEDURE_NAME');