Database performance and tuning is referring to the performance of database applications (such as SQL Server, Oracle, My SQL and other databases). Database performance monitoring software typically tracks a variety of data about size, response times, and number of connections over time.
Database performance and tuning is difficult in any modern relational database management system. Database performance is not a simple task because it depends on your requirements, the operating system and the target hardware. But when you design a database, there are some topics to keep in mind for increasing database performance.
- Memory Utilization-If you have tuned the instance and still need to improve performance, verify your work or try to reduce system time.
- Wait Stats Analysis-Perform advanced resource wait statistics analysis to proactively reduce performance bottleneck.
- Fragmentation-Identify the optimal settings of your database files and tables to reduce fragmentation and reduce them.
- Backup and Recovery-you may also need to tune performance of the database during instance recovery.
- Deadlock Detection and Resolutions-Multiple processes persistently blocking each other, in an irresolvable state, will eventually result in a halt to processing inside the database engine.
- Index analysis-Query performance as well as speed improvement of a database can be done using Indexes.
Configuring a Database for Performance
This is an overview of the Oracle methodology for configuring a database for performance. Although performance modifications can be made to the Oracle database instance at a later time, much can be gained by proper initial configuration of the database for the intended needs.
This contains the following sections
- Performance Considerations for Initial Instance Configuration.
- Creating and Maintaining Tables for Good Performance.
- Performance Considerations for Shared Servers.
Configuring a Database for Tuning
Database tuning describes a group of activities used to optimize and homogenize the performance of a database. It usually overlaps with query tuning, but refers to design of the database files, selection of the database management system (DBMS) application, and configuration of the database's environment (operating system, CPU etc.).
SQL Tuning
SQL tuning involves three basic steps
