BEST COMMANDS FOR GATHER STATISTICS IN ORACLE AND PERFORMANCE TUNING OF THE DATABASE STEP BY STEP PROCEDURE:

- To enhance the performance of the database, it is necessary to analyze the ADDM and AWR reports and take the necessary action.

- Apart from these reports it is necessary to run the following analyze commands and gather statistics for performance improvement of the running processes.

You may run the following commands in order :


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

                                                         
OR


EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCHEMA_NAME', OPTIONS=>'GATHER AUTO');


>> 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','COMPUTE');


Compilation of schema also helps in enhancing the performance of the database and running queries as it compiles all invalid objects in a particular schema. If possible, you may run the following command :

>>  EXEC DBMS_UTILITY.compile_schema(schema => 'SCHEMA_NAME');