Monitoring

Monitoring the database during and after test runs will help understand where any issues with disk, memory, SQL, and configuration might be.

The Oracle Automatic Workload Repository (AWR - http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php) and ADDM reporting tools provide views on the system during and after test runs.

Oracle Enterprise Manager Database Control (OEMDC - http://docs.oracle.com/cd/E11882_01/server.112/e10803/config_intro.htm#HABPT5145) provides a set of tools to monitor and manage your database.

Note: Some aspects of these tools need a separate license from Oracle.
OEMDC provides useful recommendations for SQL performance, indexes and configurations. For example:
  • OEMDC can highlight use of an inefficient SQL execution plan for work list queries, resulting in slow performance.
  • OEMDC may recommend additional indexes depending on what work list filters and sort criteria are in use.
  • Depending on processes and data being used by BPM, OEMDC can suggest other tables that may also benefit from additional indexes.

Tuning analysis data should be generated on the production system. Any recommendations should be implemented on a test system to determine their impact, by running the recommendations against the analysis data copied from your production system, before applying them to the production system.