Chapter 12: Making Things Run Fast (Enough)
Tuning a database is an ongoing job for the busy DBA. Users never seem to stop complaining about queries running slowly. And once you think that everything is at peak performance, a new application is added to the mix, a new server is added to the server pool, the volume of orders for widgets doubles mysteriously, or a data warehouse is using up more and more of the server’s resources.
In this chapter, we’ll talk about several ways to optimize the performance of the database, beginning with Oracle’s Tuning Methodology. Then we’ll cover indexes, data design tuning, application tuning, and memory tuning.
Oracle’s Tuning Methodology
When tuning a newly developed database system or a system that has experienced major changes, you can follow Oracle’s Tuning Methodology. This methodology prioritizes the steps to take when optimizing a database system:
| Priority | Tuning Focus |
|---|---|
| 1 | Data design |
| 2 | Application design |
| 3 | Memory allocation |
| 4 | I/O and physical structures |
| 5 | Resource contention |
| 6 | Underlying platform |
Oracle’s Tuning Methodology
A tuning method recommended by Oracle Corporation that prioritizes areas in tuning database performance. The six areas, in order of priority, are data design, application design, memory allocation, I/O and physical structures, resource contention, and underlying platform.
The tuning focus areas are as follows:
Data design This step focuses on what kinds of indexes to create and on which tables, using views and other variations on the basic table to achieve better performance, and similar considerations.
Application design This area is somewhat intertwined with data design, especially when analyzing the SQL statements that run against the tables and indexes. Application design focuses on how to use Oracle tools to write effective and efficient SQL SELECT and other DDL statements against the database tables.
Memory allocation This step is concerned with making sure that you not only have enough system memory overall, but also are dividing that memory judiciously among the main Oracle memory structures. It is possible to allocate too much memory for one Oracle memory structure and potentially have an adverse performance impact on another Oracle memory structure.
I/O and physical structures This step tunes the communication between the memory structures and disk structures to reduce the amount of time it takes to retrieve data blocks from disk or to avoid disk I/O completely.
Resource contention This area analyzes the Oracle structures that control concurrent access to the various Oracle structures directly and indirectly accessible by the user. At the table level, this means locking rows versus locking the entire table, for example. At the block level, this means allowing more than one user to insert or update row data concurrently.
Underlying platform This step deals primarily with placing Oracle file objects on the appropriate physical disk devices, as well as taking advantage of multiple CPUs on a server for improving the overall throughput of queries and data loads.
Ninety percent or more of all tuning issues fall within the first three areas—data design, application design, and memory allocation—and they are the focus of this chapter.
