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.
Copyright © Cloud Software Group, Inc. All rights reserved.