Design – Build – Run: Applied Practices and Principles for Production-Ready Software Development

18.1. Tuning the Database Layer

The database is one of the key areas where tuning can really benefit the application and its overall performance. There are many ways that the database can be tuned effectively, including setting block sizes, setting optimizer options, cursor caching, database parameters, and so forth. A good DBA will be able to provide many tips for performance tuning the database, and the database itself typically will offer a suite of tools for performance analysis and tuning. You should use these tools as early as possible to highlight areas of potential improvement. The execution of these tools should be included in the work plans and their outputs, and details of how to rectify situations should be clearly understood.

For the purposes of this chapter, you should understand the following four key principles and implement them, as appropriate:

• Indexing

• Partitioning

• Optimizing queries

• Caching

The following sections do not go into the nth level of detail; they simply provide an overview of the particular subject, which should be bolstered by further investigation.

18.1.1. Indexing Tables

The database tables should be properly indexed to improve the performance of database queries. This is one of the most basic principles in database design. Understanding how the data in the database is accessed and used helps to define the right indexes. Some database systems require the indexes on tables to be "re-indexed" periodically to improve query performance. As data is inserted, ...