AIX > Tips & Techniques > Systems Management

Optimizing SAP R/3 Database Performance

SAP R/3 is an ERP package commonly used at many large IBM eServer pSeries* data centers. This article offers tips for improving DB2* Universal Database (UDB) database performance in SAP R/3 environments.

Here are some important characteristics of SAP systems from a database perspective:

  • As shipped, a 4.6C SAP R/3 database contains approximately 20,000 tables and 35,000 indexes. A typical large customer may use 5,000 tables.
  • Each SAP work process (WP) opens a single database connection at startup. Subsequently, this WP repeatedly processes one or more units of work corresponding to each piece of dialog or update work.
  • A unit of work can consist of 10 to hundreds of SQL statements, with a typical size of 50 statements. In the case of a dialog WP, the majority of these statements are selects. For update WPs, more than half of the statements may be inserts or updates.
  • SAP R/3 is a DB2 call-level interface (CLI) application. There's no embedded SQL--it's all dynamic.

Improving I/O Characteristics

Like any other online transaction processing (OLTP) system, I/O performance is critical to overall SAP system performance. By default, SAP's database is configured with 25 tablespaces, with one to eight containers specified per tablespace. Although sufficient for many installations, this tablespace may need to be extended and/or adapted to accommodate a system's specific requirements or an enterprise's natural growth.

One measurement to watch closely is disk utilization, as reported by iostat. If disk utilization exceeds 25 percent, further investigation may be required. In some cases, though, higher disk-utilization numbers may be acceptable, such as in environments that use external disk subsystems (e.g., IBM's Enterprise Storage Server*). In these cases, disk utilization will be higher because the system views an array of many disks as a single disk.

The filemon utility can be used to determine if a performance bottleneck exists on the disk. This utility examines disk I/O response times. An average response time of greater than 20 milliseconds (ms) per I/O means the disk is probably acting as a brake on transaction performance. Balancing the I/O load across more disks can resolve this situation. You can choose to add containers to existing tablespaces or move "hot" tables to additional tablespaces created on new disks.

On a busy production SAP system, as with most OLTP systems, the I/O profile typically consists mainly of random I/O activity. It's possible to calculate the average I/O size for a particular disk from the output of the iostat program. An average I/O size for reads for a disk containing an SAP tablespace that differs significantly from the tablespace page size indicates that a sequential I/O is occurring on that disk. Unless a significant batch workload is running concurrently with the online system, DB2 prefetchers are likely to be the main source of sequential read activity. Although prefetch activity is normal and in many circumstances desirable, this may indicate some statement plans contain table scans. Excessive table scans are discussed in more detail in the Bufferpool Tuning section.

Allocating tablespaces with raw containers may significantly enhance performance. With raw containers, DB2 handles the I/O itself, bypassing AIX file access mechanisms.

Russ Stocker, IBM, focuses on DB2 UDB performance on the AIX and Windows platforms with IBM. Russ can be reached at

comments powered by Disqus



2018 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Disk I/O and the Network

Increase performance with more tips for AIX 5.3, 6.1 and 7

Paging, Memory and I/O Delays

How to tune AIX versions 5.3, 6.1 and 7 for increased performance

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters
not mf or hp