[Mullins02] Chapter 9. Performance Management

Chapter 9. Performance Management

Whennon-DBAs think about what it is that a DBA does, performance monitoringand tuning are quite frequently the first tasks that come to mind. Thisshould not be surprising. Almost anyone who has come in contact with acomputer has experienced some type of performance problem. Moreover,relational database systems have a notorious reputation (mostlyundeserved) for poor performance.

Thischapter, as well as the following three, will discuss performancemonitoring, tuning, and management within the context of databaseadministration. This chapter defines performance, discusses thedifference between performance monitoring and performance management,looks at managing service levels, and defines three specific subsets ofdatabase performance management. Chapters 10 through 12 delve further into the three subsets.

Defining Performance

Mostorganizations monitor and tune the performance of their ITinfrastructure. This infrastructure encompasses servers, networks,applications, desktops, and databases. However, the performancemanagement steps taken are usually reactive. A user calls with aresponse-time problem. A tablespace runs out of disk storage space inwhich to expand. The batch window extends into the day. Someonesubmitted a “query from hell” that just won't stop running. Those ofyou in the trenches can relate—you've been there, done that.

Performance management is usually reactive.

Handlingperformance problems is truly an enterprisewide endeavor. However, thetask of enterprise performance management frequently becomes the job ofthe DBA group. Anyone who has worked as a DBA for any length of timeknows that the DBMS is usually “guilty until proven innocent.” Everyperformance problem gets blamed on the database regardless of its truecause. DBAs need to be able research and ascertain the source of allperformance degradation, if only to prove that it is not caused by adatabase problem. As such, DBAs must be able to understand at least thebasics of the entire IT infrastructure, but they also need to have manyfriends who are experts in other related fields (such as networking,operating systems, and communication protocols). Possessing a soundunderstanding of the IT infrastructure enables DBAs to respondeffectively when performance problems arise. Event-driven tools existon the market that can make performance management easier byautomatically invoking predefined actions when specific alerts aretriggered. For example, an alert can be set to proactively reorganize adatabase when it reaches its storage capacity or to allocate morememory when the DBMS is reaching its limit. Moreover, other tools existthat can ease the burden of performance management and analysis.However, many of the supposedly proactive steps taken against completedapplications in production are truly mostly reactive. Let's face it,DBAs are often too busy taking care of the day-to-day tactical databaseadministration tasks to proactively monitor and tune their systems tothe degree they wish they could.

Handling performance problems is truly an enterprisewide endeavor.

All of this discussion is useful, but it begs the question: Just what do we mean by database performance?You need a firm definition of database performance before you can planfor efficiency. Think, for a moment, of database performance using thefamiliar concepts of supply and demand. Users request information fromthe database. The DBMS supplies information to those requesting it. Therate at which the DBMS supplies the demand for information can betermed “database performance.” However, this definition captures database performance only in a most simplistic form.

Weneed a more comprehensive definition of database performance. Fivefactors influence database performance: workload, throughput,resources, optimization, and contention.

The workloadis a combination of online transactions, batch jobs, ad hoc queries,data warehousing analysis, and system commands directed through thesystem at any given time. Workload can fluctuate drastically from dayto day, hour to hour, and even minute to minute. Sometimes workload ispredictable (such as heavy month-end processing of payroll, or verylight access after 7:00 P.M.,when most users have left for the day), whereas workload is veryunpredictable at other times. The overall workload has a major impacton database performance.

Throughputdefines the overall capability of the computer to process data. It is acomposite of I/O speed, CPU speed, parallel capabilities of themachine, and the efficiency of the operating system and systemsoftware. The hardware and software tools at the disposal of the systemare known as the resourcesof the system. Examples of resources include the database kernel, diskstorage devices, random access memory chips, cache controllers, andmicrocode.

The fourth defining element of database performance is optimization.All types of systems can be optimized, but relational databases areunique in that query optimization is primarily accomplished internal tothe DBMS. However, many other factors need to be optimized (such as SQLformulation and database parameters) to enable the database optimizerto create the most efficient access paths.

When the demand (workload) for a particular resource is high, contention can result. Contentionis the condition where two or more components of the workload areattempting to use a single resource in a conflicting way (e.g., dualupdates to the same piece of data). As contention increases, throughputdecreases.

Therefore,database performance can be defined as the optimization of resource useto increase throughput and minimize contention, enabling the largestpossible workload to be processed. Of course, I do not advocatemanaging database performance in a vacuum. Applications regularlycommunicate with other subsystems and components of the ITinfrastructure. Each of these must also be factored into the overallperformance planning of your organization. However, it is wise to placelimits on the DBA's actual responsibility for performance tuningoutside the scope of this definition. If the task is not included in the definition above, it probably requires expertise outside the scopeof database administration. Therefore, performance management tasks notcovered by the above description should be handled by someone otherthan the DBA—or at least shared among the DBA and other technicians.

Database performance is the optimization of resource use to increase throughput and minimize contention, enabling the largest possible workload to be processed.

A Basic Database Performance Road Map

Planningfor database performance management is a crucial component of anyapplication implementation. Therefore, the DBA needs to forge a basicplan to ensure that database performance management and analysis isaccomplished for all database applications across the organization. Acomplete performance management plan will include tools to help monitorapplication performance and tune the database and SQL.

Following the 80/20 rule (see “The 80/20 Rule”sidebar), the first step should be to identify the most troublesomeareas. However, this is not always as easy as it might seem.

Themost likely culprit for most database application performance problemsis inefficient SQL and application code. In my experience, 75% to 80%of all database performance problems can be traced to poorly coded SQLor application logic. This does not mean that the SQL in applicationsis necessarily bad to begin with. Although an application may be 100%tuned for rapid relational access when it first moves into theproduction environment, it can suffer performance degradation overtime. This degradation can occur for many reasons, such as databasegrowth, new data access patterns, additional users, changes in thebusiness, and so on.

The most likely culprit for most performance problems is inefficient SQL and application code.

Ofcourse, the SQL and application code can be just plain bad to beginwith, too. Any number of problems can cause poorly performing SQL,including

• Table scans

• Lack of appropriate indexes

• Improper indexing choices

• Not using the available indexes

• Outdated database statistics

• Tables joined in a suboptimal order

The 80/20 Rule

The 80/20 rule, also known as the Pareto Principle, is an old maxim stating that 80% of the results come from 20% of the effort. This rule is usually applicable to most efforts. Whether the percentages are precisely 80% and 20%, the underlying logic of the rule holds—namely, that a small amount of effort brings the most rewards.

So, from the perspective of database performance tuning, the wise DBA will concentrate on the most likely causes of performance problems first, because he will receive a high return on his tuning investment.

• Application joins instead of (usually) more efficient SQL joins

• Improper join method (nested loop, merge scan, etc.)

• Efficient SQL inside of inefficient application code (loops)

• Inefficient subquery formulation (exists, not exists, etc.)

• Unnecessary sorting (group by, order by, union)

Findingthe SQL statements that are the most expensive in a large shop is anextremely difficult thing to do. Resource hogging SQL statements mightbe hiding in one of hundreds or even thousands of programs. Interactiveusers who produce dynamic, ad hoc SQL statements might reside anywhere,and any one person who is generating ad hoc queries can severely affectoverall production performance.

Agood approach is to use an SQL monitor that identifies all SQL runninganywhere in your environment. Typically, these tools will rank SQLstatements, based on the amount of resources being consumed, and trackthe statement back to who issued it and from what program. Once youhave identified the top resource consuming statements, you canconcentrate your tuning efforts on the most costly statements.

However,it is not always obvious how to tune poorly coded SQL statements. Theproper coding and tuning of SQL statements is a detailed endeavor.In-depth strategies for SQL tuning and additional applicationperformance management details can be found in Chapter 12.

The proper coding and tuning of SQL statements is a detailed endeavor.

Ofcourse, other factors can negatively impact database performance. It iswise to periodically check the overall performance of the databaseinstance and the server operating system. Some quick items to checkinclude the following:

• Memory allocation (buffer/cache for data, SQL, authorization)

• Logging options (log cache, log size, Oracle rollback segments)

• I/O efficiency (separation of tables and indexes on disk, database size, fragmented and extended files)

• Overall application and database workload on the server

• Database schema definitions

Toassure optimum database performance, plan on combining a gooddefinition of database performance with a detailed performance planspecific to your shop.