Database Performance

Database performance changes as data is added or deleted. When more than 10% of data has changed or been added, a database may require DBA attention.

The DBA should review the following:

  • Set up a job to collect optimization statistics regularly.
  • Set up a job to generate Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR), or similar reports at regular intervals.
  • Review the report for recommendations and adjust database parameters accordingly. For example, reports may indicate changes to memory allocated to a database instance. If an ADDM report is regularly checked and acted upon, no database performance issues can occur.
  • Regularly purge data using the purge program. (See Database Purge Scheduling.)
  • If there are many deletes (due to purge), indexes and tables may become fragmented and after reviewing the statistics report you may have to defragment the indexes regularly.
  • If a database report shows that inserts or deletes are running slow, it may indicate that:
    • Disks are slow or access paths are slow. Even with a fast SAN, disk performance can be affected if database storage options are not configured correctly. For example, for Oracle using ASM with a FAST SAN resolves most of the disk related issues.
    • Table or index is fragmented. This happens when you need to import and delete a lot of data using purge. Defragment the indexes.
    • Too many indexes have been created. Eliminate some of the indexes if you can.
    • Some records may have too many versions. Consider purging the older versions.
    • Too much concurrency. Consider better database configuration or bigger capacity hardware. Or consider more cache to take some load off the database.
  • Take a full backup if you are using an SQL Server. Incremental backups can take significant amounts of time and perform many reads and writes.