0545-0548

Previous Table of Contents Next

Page 545

CHAPTER 23

Performance Tuning and Optimization

IN THIS CHAPTER

• General Concepts in Database Tuning 546
• Parallel Processing 553
• Parallel Server Option 553
• Parallel Query Option 553
• Performance Tools 554
• Tuning Database SGA 564
• Contention Issues 572
• Database Objects 577
• Database Locking 583

Page 546

Give a user an inch, and he wants a mile. If you change a database query so that it runs in one minute instead of five, the user will want it to work in 30 seconds. No matter how fast a database runs, there is always the need to make it go faster. Ultimately, this task falls to the DBA. A DBA really has two levels of responsibility: actual and perceived.

Actual responsibility means the tasks for which a DBA is genuinely responsible: keeping the database available for day-to-day business needs, creating new user accounts, monitoring the overall health of the database, and so on. Perceived responsibility means the responsibility incurred when there is any problem with the database ”or even a conflict in the corporate IS structure. A DBA is often asked why the database is down when a link has broken in the WAN, or why the database is performing slowly when a poorly written application is deployed into a production environment.

Because all database problems are perceived to be the responsibility of the DBA, it falls to him ”whether he likes it or not ”to validate the claims or dispel the rumors. The DBA must have a solid foundation of knowledge to base his decisions on. In many larger IS departments, the DBA might not be responsible for performance tuning. In others, the DBA may be responsible only for database ”but not application ”performance tuning. At some sites, the DBA is responsible for all performance tuning functions of the database.

This chapter deals with the art of performance tuning.

NOTE

For more information about performance considerations while designing a database, see Chapter 25, "Designing a Database."

General Concepts in Database Tuning

When you are called on to optimize or tune a system, it is of paramount importance that you distinguish between the two levels of performance tuning: applications tuning and database tuning. They are distinct areas of expertise and are often handled by different people. The DBA should have at least an overview of the importance and functions of each type of tuning.

At the base of everything is the operating system, which drives the physical functionality ”such as how to access the physical disk devices. On top of this level rests the RDBMS, which interacts with the operating system to store information physically. Applications communicate with the RDBMS to perform business tasks.

Applications Tuning

Applications tuning deals with how the various applications ”forms, reports , and so on ”are put together to interact with the database. Previous chapters discussed how a database is little

Page 547

more than a series of physical data files. Essentially, an application is nothing more than a program that issues calls to the database, which in turn are interpreted as physical reads and writes from the physical data files. Applications tuning means controlling the frequency and amount of data that the application requests from or sends to the database.

Here are some general guidelines for tuning applications:

• Generate an EXPLAIN PLAN on all the queries in the application. This helps you determine whether a query has been properly optimized. The EXPLAIN PLAN is discussed later in this chapter.
• Check the EXPLAIN PLAN of database views. This is important because views are indistinguishable from tables when they are used in queries. Because the SQL for a view is not executed until it is queried, an inefficient view can drastically slow down the performance of an otherwise efficient application. Be especially wary of joining views with other views.
• If an application that was performing acceptably begins to perform slowly, stop and determine what has changed. In many cases, queries run fine in test environments and in the first few months of production until data accumulates; an index might now be needed to expedite the database searches. In other cases, however, an index that invalidates existing EXPLAIN PLANs might have been added. This is a real danger when too many people can create indexes on production tables. The more indexes that a table has, the longer it takes to load or change data in a database table; it also impacts the speed with which the database returns query results.
• Match SQL where possible. Applications should use the same SQL statements wherever possible to take advantage of Oracle's Shared SQL Area. The SQL must match exactly to take advantage of this.
• Be as specific as possible. The more specific a database query is, the faster a query executes. For example, querying a table by a ROWID is far more specific than querying with the LIKE clause. Unless it is necessary to use less specific queries in an application, always write queries that can use the PRIMARY KEY or other indexed information.
• Be aware of how often queries are made against the database and whether they are necessary. Avoid too frequent or unnecessary calls, such as calling a loop that initially queries the DUAL table for the name of the user. Each time the loop executes, the query is executed. Other types of queries are even more expensive. Whenever possible, process data in memory and refrain from querying the database.
• SQL is not a file handler. One of the most common mistakes in SQL programming is made by people who have previous programming experience using file handlers, such as BTRIEVE or ISAM. Software developers should be wary of writing two separate queries for master/detail relationships ”that is, one query for the master and another for the details for that master ”instead of just a single query. They involve extra processing overhead that can have a substantial overhead for applications programs.

Page 548

• Tuning does not solve the problems of poor design. This is the most essential truth in applications tuning. It emphasizes what everyone who has ever worked in systems development knows : Spend time proactively, not reactively. No matter how many indexes are created, how much optimization is done to queries, or how many caches and buffers are tweaked and tuned ”if the design of a database is faulty, the performance of the overall system suffers.

These are only guidelines for applications tuning. Each site has its own specific problems and issues that affect the problems that occur in applications. More often than not, it is the duty of the developers to tune and modify their own programs without the involvement of the DBA. Because of perceived responsibility, however, the DBA must work with the applications development staff to resolve these problems.

Database Tuning

Whereas applications development addresses how a task is accomplished, tuning at the database level is more of a nuts and bolts affair. Performance tuning at the applications level relies on a methodical approach to isolating potential areas to improve. Tuning at the database level, however, is more hit and miss . It concentrates on things such as enlarging database buffers and caches by increasing INIT.ORA parameters or balancing database files to achieve optimum throughput.

Unlike applications tuning, which can be done by an applications group or the DBA depending on the environment, database tuning is the almost exclusive province of the DBA. Only in rare cases, where there are multiple DBA groups, one of which specializes in performance tuning, does database tuning fall outside the domain of the DBA.

At the database level, there are three kinds of tuning:

• Memory tuning
• I/O tuning
• Contention tuning

Each kind has a distinct set of areas that the DBA must examine. Memory tuning deals with optimizing the numerous caches, buffers, and shared pools that reside in memory and compose the core memory structures for the Oracle RDBMS. I/O tuning is concerned with maximizing the speed and efficiency with which the RDBMS accesses the physical data files that make up its basic storage units. Contention tuning seeks to resolve problems in which the database fights against itself for database resources.

There are only four basic steps involved in database tuning. They hold true for all three types of tuning:

1. Gather information.
2. Determine optimal changes.

Previous Table of Contents Next