Tuning Oracle 10g and later databases

To tune the Oracle 10g or later databases, you must gather statistics frequently through application tables, data dictionary, and system statistics. You can also use the Automatic Workload Repository (AWR) to monitor the performance metrics.

Gathering Statistics

Oracle recommends keeping statistics up-to-date for optimal query performance through:

Application tables statistics
Data dictionary objects statistics
System statistics

By default, Oracle 10g gathers statistics using the GATHER_STATS_JOB scheduled job.

The following table lists the functions to be used for gathering statistics.

Table 1. Gathering statics for the Oracle database
Type of statistics	Description	Example
Application tables	Use the `DBMS_ STATS.GATHER_SCHEMA_ STATS` function to collect statistics on specific schemas.	`SQL> EXEC DBMS_ STATS.GATHER_SCHEMA_STATS ('ECM');`
Data dictionary	Use the `DBMS_ STATS.GATHER_ DICTIONARY_STATS` function to gather the data dictionary statistics.	`SQL> execute DBMS_ STATS.GATHER_DICTIONARY_ STATS`
System statistics	Use the `DBMS_ STATS.GATHER_SYSTEM_ STATS` function to estimate the I/O and processor costs, and to choose a better execution plan. To fine-tune the system statistics for the workload use the `START` and `STOP` options.	`SQL> execute dbms_ stats.gather_system_stats ('Start'); SQL> execute dbms_ stats.gather_system_stats ('Stop');`

Changing database password

For information about changing the database password, see the Suite Installer documentation.