SAP Database Administration With Oracle: Michael Höding, André Faustmann, Gunnar Klein, Ronny Zimmermann

Michael Hding, Andr Faustmann,
Gunnar Klein, Ronny Zimmermann
SAP Database Administration with Oracle
Bonn Boston
Contents at a Glance
1 Introduction ........................................................................ 15
2 SAP Fundamentals .............................................................. 27
3 Oracle Fundamentals .......................................................... 65
4 SAP and Oracle ................................................................... 149
5 Planning the System Landscape ......................................... 243
6 SAP Change and Transport Management ........................... 277
7 System Lifecycle .................................................................. 341
8 Performance ........................................................................ 405
9 System Operation and Monitoring ..................................... 495
10 Backup, Restore, and Recovery .......................................... 545
11 Administrating the Java Stack ............................................ 669
12 SAP NetWeaver BI and Oracle ........................................... 723
13 Afterword and Outlook ...................................................... 785
A Flight Data Model .............................................................. 789
B General Options of the BR*Tools ........................................ 791
C References ........................................................................... 797
D Authors ............................................................................... 799

Contents
Who is this book for? For users! 15
1 Introduction ......................................................................... 15
1.1 Reasons for Owning This Book ............................................... 16
1.2 Tasks of the SAP Basis ............................................................ 18
1.3 Structure of This Book ............................................................ 19
1.4 Conventions and Other Information ....................................... 23
1.5 Acknowledgements ................................................................ 24
What is SAP? What are its capabilities? How does it work? In this chapter, well address these questions. 27
2 SAP Fundamentals ............................................................... 27

2.1 Overview of the SAP Software ................................................ 27
2.1.1 Standard Software versus Individual Software ............. 27
2.1.2 Integration ................................................................. 28
2.1.3 Development of SAP R/3 ........................................... 28
2.1.4 SAP Terminology ....................................................... 32
2.2 Architecture and Scalability .................................................... 35
2.3 Application Server .................................................................. 37
2.3.1 Overview of the SAP Application Server Processes ...... 38
2.3.2 Memory Structures .................................................... 39
2.3.3 Dispatcher ................................................................. 40
2.3.4 Dialog Work Process .................................................. 42
2.3.5 Batch Work Process (BTC) .......................................... 45
2.3.6 Update Process (UPD) ................................................ 47
2.3.7 Lock Management with the Enqueue Process (ENQ) ... 48
2.3.8 Message Server Process ............................................. 49
2.3.9 Gateway Process ........................................................ 49
2.3.10 Variants of an Instance ............................................... 49
2.4 SAP Administration ................................................................ 50
2.4.1 Profile Files and System Startup ................................. 50
2.4.2 Software Maintenance ............................................... 52
2.4.3 Database Administration ............................................ 53
2.4.4 Data Backup ............................................................. 54
2.4.5 Performance Optimization ......................................... 55
2.5 SAP and Software Development ............................................. 56
2.5.1 ABAP Framework ....................................................... 57
2.5.2 Java in the SAP Kernel ............................................... 60
2.5.3 Internet Transaction Server ........................................ 61
2.5.4 Remote Function Call ................................................. 62
2.6 Summary ................................................................................ 63
7
Contents
It seems easy to store data, but harder to store data safely. Usually, it takes a lot of effort to search stored data. Modern database management systems provide easy storage, quick research, security, and other functions, even in multiuser mode. 65
3 Oracle Fundamentals ........................................................... 65

3.1 Basics of Database Technology ............................................... 65
3.1.1 Motivation and History .............................................. 65
3.1.2 Relational Data Model and SQL ................................. 71
3.1.3 Short Overview of SQL ............................................... 75
3.1.4 Implementation Techniques for DBMS ....................... 79
3.2 Development of Oracle .......................................................... 81
3.3 Tools for the Oracle Administrator .......................................... 84
3.3.1 sqlplus ....................................................................... 84
3.3.2 isqlplus ...................................................................... 86
3.3.3 Oracle Enterprise Manager ......................................... 87
3.4 Oracle Kernel ......................................................................... 89
3.4.1 Oracle Processes ........................................................ 90
3.4.2 Oracle Main Memory Structures ................................ 93
3.4.3 Oracle File System ..................................................... 96
3.4.4 Oracle Tablespace Concept ........................................ 98
3.4.5 Other Important Files ................................................ 109
3.4.6 Interaction of Processes and Storage Structures........... 116
3.4.7 Accessing Oracle with Oracle Net .............................. 119
3.4.8 Query Optimization ................................................... 126
3.4.9 Data Backup and Recovery ......................................... 133
3.4.10 Users and Privileges ................................................... 140
3.4.11 Monitoring an Oracle Instance with the
Enterprise Manager .................................................... 145
3.5 Summary ................................................................................ 147
Seventy percent of all installed SAP production systems run on Oracle databases. This chapter describes the interaction between SAP and Oracle products. 149
4 SAP and Oracle .....................................................................149

4.1 Processes of SAP and Oracle Systems ...................................... 150
4.1.1 System Startup ........................................................... 152
4.1.2 Relationships Between Processes ............................... 161
4.1.3 Communication Between SAP Instances and
Oracle Processes ........................................................ 163
4.1.4 Log and Trace Files .................................................... 165
4.1.5 System Stop ............................................................... 171
4.2 Requirements at the Operating System Level .......................... 173
4.2.1 Users, Groups, and Environment Variables (UNIX) ...... 177
4.2.2 Oracle Client .............................................................. 180
4.2.3 SAP Kernel ................................................................. 185
4.3 Authentication Between SAP and Oracle ................................ 190
4.3.1 Database Users .......................................................... 190
8
Contents
4.3.2 Login Processes .......................................................... 192

4.3.3 Privileges in the Database .......................................... 197
4.3.4 Security Aspects ......................................................... 199
4.4 SAP Tablespaces ..................................................................... 202
4.4.1 Tablespace Layout ...................................................... 202
4.4.2 Tablespace Types ....................................................... 207
4.4.3 Object Assignment and Object Parameters ................ 209
4.4.4 Reorganizing Tables and Tablespaces ......................... 211
4.5 Administrating Oracle with the BR*Tools ................................ 216
4.5.1 Development and Content ......................................... 217
4.5.2 Environment, Options, and Log Files .......................... 219
4.5.3 BRTOOLS and BRGUI ................................................. 224
4.5.4 BRCONNECT ............................................................. 227
4.5.5 BRSPACE ................................................................... 230
4.5.6 Parameter Maintenance for Oracle ............................. 235
4.6 Summary ................................................................................ 240
"Imagine a house. The whole thing must be organized like a computer. Power, heat, costs, safety. All of its parameters must be optimally tuned to one another. Effectively, and cost-effectively."
Professor Djamshid Tavangarian (University of Rostock) 243
5 Planning the System Landscape ..........................................243

5.1 From Product to Solution Landscape ...................................... 243
5.2 Overview of Planning Criteria ................................................. 245
5.2.1 Construction Infrastructure ....................................... 246
5.2.2 Server Technology and Platforms ............................... 250
5.2.3 Storage and SAN Infrastructure .................................. 254
5.2.4 Backup ....................................................................... 257
5.2.5 Frontend .................................................................... 259
5.3 High Availability ..................................................................... 263
5.4 IT and System Security ............................................................ 267
5.5 Extending an Existing Landscape ............................................. 274
5.6 Summary ................................................................................ 276
While production systems have to be stable and highly available, they also have to adjust flexibly to changing realities. SAP has developed an extensive set of techniques and tools for this purpose. They are subsumed under the term transport system and support the maintenance and development of entire system landscapes. 277
6 SAP Change and Transport Management ............................277

6.1 Standard Software and Changes to the Standard ..................... 278
6.2 Basic Principles of Software Logistics ...................................... 280
6.2.1 Data in the SAP System .............................................. 280
6.2.2 System Landscape ...................................................... 284
6.2.3 Change and Transport System (CTS) ........................... 288
6.2.4 Recording Changes .................................................... 291
6.2.5 Change Requests ........................................................ 292
6.2.6 Transport Management System .................................. 296
6.2.7 Transport Routes and Transport Layers ....................... 302
9
Contents
6.3 Change Management for Customizing Settings ....................... 309

6.4 Change Management for Developments ................................. 313
6.5 Transport Management .......................................................... 318
6.5.1 Transport Organizer Tools .......................................... 318
6.5.2 Transport Strategy ...................................................... 319
6.5.3 Import Queue and Import Buffer ................................ 326
6.5.4 Transports Between Transport Groups ........................ 328
6.5.5 Transports Between Transport Domains ..................... 329
6.5.6 Using the Transport Control Program ......................... 333
6.5.7 Logging Transports ..................................................... 335
6.5.8 Transports Between Unicode and
Non-Unicode Systems ................................................ 337
6.6 Tips and Tricks ........................................................................ 338
6.7 Summary ................................................................................ 339
A business application system such as an SAP system can typically be operated for several decades. However, the underlying technological basis, that is, the hardware, operating system, database system, and application server, has a lifecycle of only a few years. Therefore, an SAP installation must be continuously enhanced and further developed. 341
7 System Lifecycle ...................................................................341

7.1 Installation ............................................................................. 341
7.1.1 Installation Tools ........................................................ 341
7.1.2 Phases ....................................................................... 344
7.1.3 Unattended Installation ............................................. 355
7.2 System Maintenance .............................................................. 357
7.2.1 Client Tools ................................................................ 357
7.2.2 SAP Support Packages, Patches, and Corrections......... 364
7.2.3 Maintaining the Oracle Database ................................ 377
7.2.4 Maintaining the Operating System ............................. 384
7.3 Upgrades ................................................................................ 386
7.3.1 SAP Upgrade .............................................................. 386
7.3.2 Oracle Upgrade .......................................................... 402
7.4 Summary ................................................................................ 404
Performance is not everything, but without performance everything is nothing, because most users find waiting almost impossible to bear. 405
8 Performance .........................................................................405
8.1 Administrative and Program-Based Problems .......................... 406
8.2 Analyzing Administrative Performance Problems .................... 408
8.2.1 Analyzing the Hardware and Operating System ........... 409
8.2.2 Analyzing the Database .............................................. 416
8.2.3 Analyzing the SAP System .......................................... 458
8.3 Analyzing Program-Based Performance Problems:
SQL Optimization ................................................................... 473
8.3.1 Two Goals: Functionality and Performance ................. 474
10
Contents
8.3.2 Effects ........................................................................ 475

8.3.3 Problem Analysis Tools .............................................. 477
8.3.4 Detailed Analysis of SQL Statements .......................... 480
8.3.5 Prevention: The Silver Bullet ...................................... 485
8.3.6 Indexes for Faster Access ........................................... 488
8.4 Summary ................................................................................ 492
The administrator of a system landscape must be able to get an overview of the status of the components at any time. If problems are recognized too late or if the system fails, substantial financial damage may result. 495
9 System Operation and Monitoring ......................................495

9.1 Monitoring in the SAP Environment: Motivation and Scope ... 495
9.1.1 Monitoring Areas ....................................................... 496
9.1.2 Monitoring Problems ................................................. 499
9.1.3 Solutions for SAP Monitoring ..................................... 500
9.2 Parameters for Monitoring an SAP System Based on Oracle .... 502
9.2.1 Monitoring the Oracle Database ................................ 503
9.2.2 Monitoring the SAP System ....................................... 517
9.2.3 Monitoring the Hardware and Operating System ........ 533
9.3 Background Jobs in the Scope of Monitoring .......................... 538
9.3.1 SAP Standard Jobs ..................................................... 538
9.3.2 Oracle Jobs ................................................................ 543
9.4 Summary ................................................................................ 544
Data is money! One fact is absolutely clear: data security is essential for the financially valuable data resources of a company. 545
10 Backup, Restore, and Recovery ............................................545

10.1 What Must Be Backed Up? ..................................................... 547
10.1.1 Interaction of Oracle Processes and
Database Objects ....................................................... 549
10.1.2 Operating Modes of the Oracle Database .................. 553
10.1.3 Archiver Stuck ............................................................ 556
10.2 Data Backup Methods ............................................................ 561
10.2.1 Data Export ............................................................... 561
10.2.2 Offline Data Backup ................................................... 562
10.2.3 Online Data Backup ................................................... 564
10.3 Recovery Methods .................................................................. 568
10.3.1 Recovery from an Offline Data Backup ....................... 572
10.3.2 Recovery from an Online Data Backup ....................... 573
10.3.3 Error Scenario: Loss of a Normal Tablespace ............... 574
10.3.4 Partial Restore and Complete Recovery ...................... 575
10.3.5 Database Reset .......................................................... 576
10.3.6 Point-in-Time Recovery ............................................. 577
10.3.7 Full Restore and Complete Recovery .......................... 578
11
Contents
10.4 BR*Tools for Backup, Restore, and Recovery ........................... 579

10.4.1 Data Backup Using BRBACKUP .................................. 582
10.4.2 Backing Up Redo Log Files Using BRARCHIVE............. 592
10.4.3 Restoring Using BRRESTORE ...................................... 602
10.4.4 Recovering the Database Using BRRECOVER ............. 606
10.4.5 Post-Processing for an Incomplete Recovery .............. 612
10.4.6 BR*Tools and Temporary Tablespaces ........................ 614
10.4.7 Disaster Recovery Using BR*Tools .............................. 614
10.4.8 BR*Tools in Windows Environments .......................... 616
10.4.9 Backup Media and Volume Management ................... 617
10.5 Oracle Recovery Manager (RMAN) ......................................... 622
10.5.1 Backups Without Backup Library ................................ 627
10.5.2 Backups with the SAP Backup Library ......................... 628
10.5.3 Backups with an External Backup Library .................... 629
10.6 Other Error Scenarios ............................................................. 629
10.6.1 Loss of a Control File .................................................. 630
10.6.2 Loss of All Control Files and the System Tablespace
and Rollback Tablespace ............................................ 631
10.6.3 Loss of the System Tablespace and Rollback
Tablespace ................................................................ 636
10.6.4 Loss of a Data File of a Temporary Tablespace............. 637
10.6.5 Loss of an Online Redo Log File ................................. 639
10.6.6 Loss of an Online Redo Log Group ............................. 641
10.6.7 Loss of All Online Redo Log Files ............................... 645
10.6.8 Loss of Offline Redo Log Files .................................... 646
10.6.9 Database Crash During an Online Backup .................. 647
10.7 Backup Strategies ................................................................... 649
10.7.1 Partial Backups .......................................................... 655
10.7.2 Two-Phase Data Backup ............................................ 657
10.7.3 Standby Databases ..................................................... 658
10.7.4 Split Mirror Databases ............................................... 660
10.8 Tips and Tricks ........................................................................ 665
10.9 Summary ................................................................................ 666
The Java programming language has become more and more widespread since its introduction. Particularly since Java 2 Enterprise Edition has been available, it is an inevitable part of every large application system. 669
11 Administrating the Java Stack .............................................669

11.1 Using Java in SAP Systems ...................................................... 670
11.2 Architecture of the J2EE Engine .............................................. 673
11.2.1 Internal Structure ....................................................... 678
11.2.2 Interplay of the Java Stack and the Database .............. 686
11.2.3 Monitoring ................................................................ 695
12
Contents
11.3 Java Software Logistics ........................................................... 705

11.3.1 SAP NetWeaver Development Infrastructure .............. 705
11.3.2 SAP Component Model ............................................. 707
11.3.3 Patching Java Instances and Applications ................... 709
11.4 Tips and Tricks ........................................................................ 714
11.4.1 Profile Parameters for the J2EE Engine ....................... 714
11.4.2 Parameters of the Property File .................................. 717
11.4.3 Minimum Configuration of the instance.properties File 719
11.5 Summary ................................................................................ 720
The goal is to provide large amounts of data in such a way that it can be understood and analyzed. 723
12 SAP NetWeaver BI and Oracle .............................................723

12.1 Basics and Concepts of Data Warehousing .............................. 723
12.1.1 OLAP and OLTP ......................................................... 723
12.1.2 Data Warehouse Architecture .................................... 724
12.1.3 Extraction, Transformation, Loading ........................... 726
12.1.4 Data Structures and Design of a Data Warehouse ....... 728
12.1.5 Operations for Data Analysis ...................................... 732
12.1.6 Data Mining .............................................................. 732
12.1.7 Benefits ..................................................................... 733
12.2 Data Warehousing with Oracle ............................................... 733
12.2.1 Technology and Architecture ..................................... 734
12.2.2 Concepts and Language Extensions in Oracle ............. 738
12.2.3 Tools .......................................................................... 741
12.3 SAP NetWeaver BI: An Overview ............................................ 742
12.3.1 Business Content ....................................................... 744
12.3.2 Data Modeling ........................................................... 745
12.3.3 Modeling of Business Intelligence Objects ................. 750
12.3.4 Basics of Data Extraction ............................................ 752
12.3.5 Loading of Master and Transaction Data .................... 755
12.3.6 Delta Extraction from Source Systems ........................ 758
12.3.7 Reporting ................................................................... 760
12.4 SAP NetWeaver BI on Oracle .................................................. 765
12.4.1 SAP NetWeaver BI Tables and Indices in the Oracle
Database .................................................................... 766
12.4.2 Configurations of the Oracle Database ....................... 770
12.4.3 PGA and Temporary Tablespace ................................. 774
12.4.4 Statistics for SAP NetWeaver BI Tables ....................... 778
12.4.5 Oracle Data Miner and the SAP BI Accelerator............ 779
12.5 Summary ................................................................................ 782
13
Contents
"Understanding is love; that which we do not love, we do not

understand; what we do not understand, is not there for us."
Bettina von Arnim 785
13 Afterword and Outlook ........................................................785

787
Appendix ....................................................................................787
A Flight Data Model ............................................................................. 789

A.1 SQL Script for Creating the Database ...................................... 789
A.2 Perl Script for Generating the Database Content ..................... 790
B General Options of the BR*Tools ....................................................... 791
B.1 Functions of BRSPACE ............................................................ 791
B.2 Functions of BRARCHIVE ........................................................ 792
B.3 Functions of BRCONNECT ...................................................... 793
B.4 Functions of BRBACKUP ......................................................... 794
B.5 Functions of BRRESTORE ........................................................ 795
B.6 Functions of BRRECOVER ....................................................... 796
C References ........................................................................................ 797
D Authors ............................................................................................. 799
Index ......................................................................................................... 801
14
Performance is not everything, but without performance everything
is nothing, because most users find waiting almost impossible to
bear.
8 Performance
In IT, performance describes a system's ability to carry out a task within a

given timeframe. Therefore, performance is always measured in terms of
tasks per time, such as FLOPS (floating-point operations per Second) or SAPS
(SAP Application Performance Standard). One hundred SAPS are defined as
2,000 completely finished order items per hour, that is, 6,000 technical dia-
log steps and 2,000 update processes.
Usually, the transfer of a specific amount of data is the challenge, so that the
unit can be described as quantity per time unit. Standardization of units is
essential for comparing different systems and their performance. Such com-
parisons of defined and reproducible performance are referred to as bench-
marking.
In the real world, it is the user who determines whether the performance of
a system is good or poor. It's a matter of personal or subjective perception. A
user does not necessarily recognize the scope of a task that has to be pro-
cessed by a system. Consequently, on the one hand, good performance is an
absolute characteristic when comparing systems, and, on the other hand, it
has to be regarded in relation to the requirements. In addition, sociological
factors play a role in this field: Five seconds of queue time for a data ware-
house request is no problem for a user in an enterprise, whereas a customer
of a web shop might be less patient.
Therefore, the overall goal of a system administrator must be to meet the dif-
ferent performance requirements to enable users to efficiently use a system.
Performance optimization is part of the lifecycle of every system and

involves the steps of implementation, operation, and revision. Figure 8.1
shows a lifecycle including its different phases.
405
8 Performance
Planning
Implementation
Implementation
Analyse Verification
Revision Analysis
Implementation
Verification
Operation
System Lifecycle
Small Optimization Cycle
Large Optimization Cycle
Figure 8.1 System Lifecycle and Optimization Cycles
The process of optimization is divided into the phases of analyzing, imple-

menting, and verifying. There are two optimization cycles. The first, "small,"
cycle is carried out entirely in the lifecycle phase of operation. This is the phase
of performance optimization, which has no or only short time effects on a sys-
tems availability. Examples of these processes include SAP kernel parameters
for memory areas or work processes as well as the parameters of the Oracle
database, some of which can be dynamically changed during runtime.
The "big" cycle covers the lifecycle phases of implementation, operation, and
revision. Optimizations that require more time to be completed, as they
involve tests and affect the system operation, must be performed within this
wider context. Modifications on application code or extensive reorganiza-
tions of databases are also part of this cycle.
This chapter describes the analysis, implementation, and verification phases:

Which analyses can be performed on a combination of SAP with an Oracle
database and how can the results be implemented? To answer this question,
we should first categorize the problems.
8.1 Administrative and Program-Based Problems

There are three sources of performance problems: program-based, adminis-
trative, and user-specific problems.
406
Administrative and Program-Based Problems 8.1
If performance problems are caused by the code of an application, the cause

is program-based. Unfortunately, there are many examples of poorly coded
software, as coding involves a wide range of bug types, such as memory leaks
and inefficient algorithms, to name just a few. If, on top of that, a database is
used, the probable bugs cover SQL statements that are required for the inter-
action. The issue of program-related performance problems is covered in
Section 8.3, Analyzing Program-Based Performance Problems.
Administrative performance problems are caused by the configuration of hard-

ware and software. This covers a wide range of areas, from incorrect disk
layout to insufficient storage parameters of the database and the SAP system
to the incorrect assignment of permissions. The methods used to analyze and
solve these problems are described in Section 8.2, Analyzing Administrative
Performance Problems.
The third source for possible problems is the behavior of users, in other
words, user-specific causes. In this context, the problematic question is: Who
caused the problem? The user who, for example, runs extensive queries and
therefore causes the system performance to go down, or the programmer or
administrator who does not prevent different kinds of "excessive use," by
setting maximum values for input boxes or running plausibility checks?
User-specific performance problems are not further discussed in this book.
The solution to this type of problem is not the administration of SAP and
Oracle databases but the development of applications or the administration
of user permissions.
Besides the causes of the problems, the locations of problems represents the
second part of a problem analysis, in this context, location means: Where
does the performance problem occur? For a further specification of this
issue, a system must be divided into its individual components. From a per-
formance analysis point of view, an SAP system consists of the following
components:
1. Hardware
2. Operating system
3. Database
4. SAP Basis (that is, SAP Kernel + SAP Basis = SAP NetWeaver Application
Server)
5. SAP application
407
8 Performance
Table 8.1 shows an overview of the possible combinations of cause and loca-
tion for the assignment of performance problems. Note that this chapter
focuses only on the problematic points related to Oracle and SAP.
Cause\Location Program-Based Administrative
Hardware Errors in firmware Selection of inappropriate com-

ponents, such as slow hard disks
or storage
Operating system Inefficient storage management by Incorrect parameterization of

operating system kernel the operating system kernel
Device drivers not optimized Inappropriate layout for mas-
sive I/O operations
Database Use of "expensive" SQL statements Insufficient parameterization

Poor indexing Inefficient table structures,
for instance, because of too
many extents
SAP Basis Errors in the ABAP code for basic Inefficient buffer sizes
functions such as in communication Wrong parameters for the
components SAP kernel
Application Incorrect use of standard SAP None

functions in custom developments
Bugs in the standard SAP system
Table 8.1 Overview with Examples of Performance Optimization Problems
We will now continue this chapter in two parts. The first part, Section 8.2
Analyzing Administrative Performance Problems deals with administrative
performance problems in all fields including hardware, operating system,
the Oracle database, and the SAP system, with a particular focus on Oracle
and the SAP system, according to the intention of this book. Then, Section
8.3, Analyzing Program-Based Performance Problems, deals with the program-
related issues, such as expensive SQL statements, indexing, and to a smaller
extent, ABAP programming.
8.2 Analyzing Administrative Performance Problems

For an analysis of performance problems, two points of entry are quite use-
ful: the general analysis of the system status or of the components and the
workload analysis. The analysis of the components, which is also referred to
as the system analysis, refers to the state, that is, the configuration and utili-
408
Analyzing Administrative Performance Problems 8.2
zation of the system components, such as hardware, operating system, data-

base, and the SAP Basis. The most important key figures for this type of anal-
ysis are filling levels, hit ratios, error statistics, and so on. Therefore, this
section deals particularly with the examination of the components, gives
information on how to solve problems, and provides configuration reference
values.
The SAP workload analysis uses the times required for processing the indi-
vidual dialog steps (roll-in and roll-out, database time, CPU time, and so on).
These time values are collected in the system. This analysis used to monitor
not only the components of the system, but also their interaction. Time is
obviously the relevant key figure is in this context.
Experience has shown that starting with the workload analysis is useful
when individual users complain about performance problems or when the
problem occurs only at certain times. If the performance is generally poor or
if a system analysis is carried out on a regular basis, starting with a general
component analysis is preferable. For a complete analysis of the system per-
formance, you should carry out both analyses.
The SAP workload analysis is not further covered here, as this would go
beyond the scope of this chapter. Some excellent literature is already availa-
ble on this topic, such as the following: SAP Performance Optimization by
Thomas Schneider (SAP PRESS 2006).
A similar performance analysis method is available in the context of Oracle

databases: the wait event analysis. As the name suggests, the flow of a trans-
action is analyzed on the basis of the different wait times of that transaction
within a database. Section 8.2.2, Analyzing the Database, provides further
information about the wait event analysis.
8.2.1 Analyzing the Hardware and Operating System

From the point of view of an SAP Basis administrator, no clear separation can
be drawn when analyzing the hardware and the operating system, as the SAP
Basis does not allow for that. However, such a separation is not necessary, as
both the hardware and operating system form the basis of the SAP system
and database and can therefore be viewed as one entity. If a performance
problem is detected in the hardware or operating system, the system admin-
istrator or hardware partner often participates in the process of finding a
solution, as the SAP partner normally does not have any access to the oper-
ating system level or lacks the required knowledge.
409
8 Performance
All data that is available for a hardware and operating system analysis in the
SAP system is collected by the SAP OS Collector (SAPOSCOL), which is a com-
ponent of the SAP Kernel that depends on the hardware and operating sys-
tem. A background job (SAP_COLLECTOR_FOR_PERFORMANCE) reads the data
and writes it to the performance database of the SAP system (Table MONI).
The analysis is started via the operating system monitor, which uses the data
from the performance database or queries the SAPOSCOL directly. Transac-
tion ST06 starts the OS monitor for the local instance of the system. For sys-
tems that have several instances on different servers, Transaction OS07 is
used to navigate to the corresponding operating system monitor of an
instance that is installed on a different server.
In general, the performance checks of hardware and operating system focus

on four areas: CPU, memory requirement, I/O load, and network. All data
for these components are collected by the SAP OS Collector and stored in the
SAP database using a batch job. This allows access to current data as well as
to a data history. Figure 8.2 shows the operating system monitor of an SAP
instance.
Figure 8.2 Operating System Monitor (ST06 or OS07)
410
Table 8.2 provides information about the meaning of the most important key
figures in the operating system monitor and states critical performance limits
where possible or wherever it makes sense.
Field Description Critical Value
Utilization user CPU load caused by user pro- S > 80% ( per h)
cesses including SAP system
and database
Utilization system CPU load caused by operating

system kernel
Utilization idle CPU idle <20% ( per h)
Utilization i/o wait CPU load caused by waiting >40% ( per h)

for I/O operations
Count Number of CPUs
Load average Number of processes waiting >3.0 (specific OS, such as Solaris,
for a CPU also count the active processes, then
>3 + number of CPUs)
Phy. mem avail Complete main memory of the

server
Phy. mem avail Free memory of the server <3% Phy. mem avail (except AIX,
which uses the free RAM as file
cache)
Pages in/out Number of memory pages Windows: Kb-in 3600 > 20% RAM
paged in and out between UNIX: Kb-out 3600 > 5% RAM
RAM and swap
Kb in/out Size of memory pages paged in

and out between RAM and
swap
Swap-space (Free, Display depends on operating

Maximum, Actual) system (SAP Note 63906)
Disk Hard disk with currently high- Utilization > 50% ( per h)
est response time (menu path
Detail analyses menu Disk
Packets in/out Number of sent and received

network packets (total of all
network interfaces)
Errors in/out Error when sending and Should no longer occur with the cur-
receiving network packets rent state of technology; therefore
(total of all network interfaces) should be checked in the case of >0
Collision Collisions on the network
Table 8.2 Overview of Operating System Monitor
411
8 Performance
The critical values are not absolute values; rather, they indicate problems. If
one of these values is exceeded or fallen below, you should check further.1
8.2.1.1 Reference Values for Hardware Components

A CPU utilization of more than 80% per hour (also idle + i/o wait < 20%) is
referred to as a CPU bottleneck. Many hardware partners, however, recom-
mend a maximum utilization of 60% to 70% for production systems to
ensure sufficient reserves for peak loads. However, you should keep in mind
that the CPU values in Transaction ST06 are average values across all CPUs of
the server; that is, a machine with two CPUs with a utilization of 70% has
less reserve than a machine with eight CPUs with a utilization of 80% (at
equal CPU performance).
Different values are available regarding the size of a swap memory. SAP rec-
ommends using three times more swap memory than physical memory, but
at least 3.5 GB. This recommendation, however, is unrealistic for systems
with a memory of more than 64 GB. In that case, it is difficult to reserve the
appropriate amount of swap memory on the local disks. However, the oper-
ating systems often provide the corresponding solution, such as the use of a
pseudo-swap for HP-UX.
Paging, that is, outsourcing memory pages from the memory and transfer-
ring them to the swap partition or the swap file on the hard disk, should gen-
erally be regarded as critical. The swap memory is merely a kind of emer-
gency help for the operating system in order to be able to start more
processes than the existing memory allows and to prevent processes from
failing in situations of extreme memory load. With paging, you should
always bear in mind that, theoretically, the factor that's responsible for the
difference in access speed between the hard disk and RAM is approximately
500,000 (8 milliseconds for the positioning of the hard disk head 15
nanoseconds of latency time for memory access). Although these values are
only theoretical values that can be changed considerably by employing dif-
ferent hardware techniques, such as hard disk arrays or parallel memory
access, a considerable difference still remains.
Generally, we advise that you not page out more than 5% of the memory
within one hour. The best thing, however, is to entirely avoid paging and to
size the memory according to your specific requirements. As a rule, when
1 Five percent can also be a poor result when the RAM is larger than 8 GB or the I/O for swap
memory is too slow, for example due to a software RAID.
412
planning hardware resources, the memory should be of the highest impor-

tance.
The third component is the I/O load. When you double-click on the current
Disk with highest response time in the operating system monitor, a list with
all hard disks of the system displays including their current statuses. If a hard
disk is indicated with Utilization 100%, this does not necessarily mean that
there's a bottleneck. In fact, you should merely ensure that the average
Utilization per hour does not exceed 50%. The history of the I/O load of
each hard disk is displayed under Detail Analysis Menu Disk.
The network can be checked from within the SAP system using a simple ping
test. This LAN check of the presentation servers (SAP GUI) only works if the
servers don't access the system via an SAP router. A second and much better
way of checking the network is to use the niping program, which can be
called from Transaction SM49 as an external operating system command.
SAP Note 500235 contains detailed instructions on how you can use niping.
The operating system monitor displays the number of received network

packets per second and provides a summary on an hourly basis. Critical
points are the sent errors and collisions within a period of one hour. With
todays modern network structures in a LAN, every value above 0 is suspi-
cious and should be checked together with the network administrator (be
insistent).
8.2.1.2 Identifying the Causes of Bottlenecks in Hardware Components

If a CPU, memory or I/O bottleneck is detected, you must search for the
cause of the bottleneck in a second step.
The processes at the operating system level are responsible for the CPU uti-
lization. In Transaction ST06, the current processes of the server are listed in
the order of their CPU utilization under Detail Analysis Menu Top CPU. The
displayed CPU utilization percentage always refers to a CPU of the system;
that is, in a system with multiple processors (n CPUs), the maximum utiliza-
tion is n 100%. If it is possible, you can also use the tools provided by the
operating system, such as "top" that's available in the different UNIX deriv-
atives.
The further procedure depends on the processes that are identified as CPU
users. SAP work processes can be recognized by the <sid>adm user and the
process names, dw.sap<instance> (UNIX) and Disp+Work (Windows). If
413
8 Performance
these are the processes that produce the CPU load, these are further analyzed
against the SAP process overview (Transaction SM50; see Section 8.2.3, Ana-
lyzing the SAP System). Individual processes are identified by a process ID
(PID), which is displayed in the operating system monitor and in the SAP
process overview.
Oracle processes are typically executed under the ora<sid> (UNIX) or

SAPService<sid> (Windows) user, respectively, and have the identifier ora-
cle<sid> (shadow processes) or ora_<process>_<sid> (Oracle system pro-
cess). If one of the Oracle processes utilizes an unreasonably high CPU capac-
ity, further analysis is performed using the database process monitor
(Transaction ST04N; see Section 8.2.2, Analyzing the Database). Different
reasons exist for an extensive CPU utilization by the Oracle processes; refer
to SAP Note 712624 for further information.
Important Note
This book mainly refers to the application server of SAP Releases 6.40 and 7.00.
Transaction ST04N, which will be mentioned frequently in the following sections,
will no longer be available in the coming Release 7.10. In the new release, it will be
called ST04 again. Moreover, from Release 7.00 onward, Transaction DBACOCK-
PIT for Oracle is available.
If an external process causes the high CPU load, this process has to be ana-
lyzed, and the bottleneck has to be eliminated in collaboration with the
operating system administrator.
Analyzing the memory utilization by the processes is much more compli-

cated than the analysis of the CPU utilization. On the one hand, that's
because the memory is used in various different ways, for instance, as a local
process memory or as a shared memory; on the other hand, the different
operating systems use different methods of memory management.
There are considerable differences regarding the management of swap and

memory not only between the Windows and UNIX worlds but also
between the different UNIX derivatives. In general, in-depth knowledge
about the operating system is essential for a precise analysis.
First, you must use the options provided by the operating system to deter-
mine whether the memory utilization is caused by the SAP system or the
Oracle database or by other processes. External and high memory utilization
which is not caused by SAP or Oracle is often caused by the file system
cache, which reserves a particular percentage of the memory as a buffer for
414
data access. The maximum size that can be recommended depends on

whether the server runs both the Oracle and SAP instances or only the SAP
instance. Oracle generally recommends disabling all I/O buffers for the data-
base access, as the access to database blocks then won't be buffered twice,
namely, by the file system cache and the SGA memory. Instead, the memory
should be completely allocated to the Oracle-optimized SGA buffer. If you
use raw devices, you can't use the file system cache. The cache of a server
with an SAP instance should not exceed 8% to 10% of memory, but no more
than 1 GB. In AIX, you should also use the file system cache as little as pos-
sible. Refer to Section 8.2.2 for further information on the different types of
I/O.
If the memory utilization occurs within the SAP system, that is, with the SAP
work processes, a further analysis of the SAP memory areas is executed. Note
that the SAP system is only capable of allocating the memory in accordance
with the relevant instance parameters (see Section 8.2.3). The analysis of the
memory usage of the Oracle database should be performed in the same way
(see Section 8.2.2).
There are three possible causes for a high I/O load: massive paging in the
swap area, a high load on the database, or an external program. If you recog-
nize a high paging rate in the operating system monitor, you can use the disk
analysis (ST06 Detail Analysis Menu Disk) to verify if the hard disk that
contains the swap area has a high load. If that is the case, solving the paging
problem also solves the problem with the I/O load. Because the swap area is
never located on the same hard disk as the database, an I/O problem caused
by paging usually never causes any I/O performance problems in the data-
base.
If the high load occurs in the area where the database is installed, further
analysis is required (see Section 8.2.2).
Problems with the communication hardware can theoretically occur for

three connections:
SAP instance: SAP GUI

SAP instance: Oracle database server
SAP instance: connected systems
The connection to the database server and possibly to the connected third-
party system, for example, as a data source in SAP NetWeaver BI, has a par-
ticularly high bandwidth utilization. In this context, SAP requires the SAP
415
8 Performance
instance and the database server to be located together in a 100-Mbit LAN.

A network overload can only be recognized in the SAP system by means of
indications such as collisions and long runtimes in the LAN. The SAP system
administrator can perform further checks using the niping program. A pre-
cise analysis and identification of causes requires the use of external network
tools and the profound knowledge of a system administrator.
8.2.2 Analyzing the Database

Because the database is the core of the SAP system, its performance is essen-
tial for the performance of the entire system.
The analysis includes the following performance-relevant components:
Buffers
The buffer areas of the Oracle database store frequently used information
in the main memory of the server to provide a considerably faster access
than is made possible by the hard disk storage.
Wait event
The analysis of wait events indicates when and which event the database
has to wait for during the processing of a request. This is a relatively sim-
ple way to identify bottlenecks in the database.
General parameterization
In addition to the buffer parameters, there are many other performance-
relevant Oracle parameters. These must also be included in the complete
analysis.
Statistics
The Oracle Cost-Based Optimizer (CBO) calculates the costs of the poten-
tial access paths (for example, full scan, index range scan) to determine the
fastest possible access path.
I/O
The task of a database is to read and write data blocks. Therefore, having
the best possible I/O for the performance of an SAP system is essential.
SQL analysis
The quality of SQL queries affects the speed of the database significantly.
Consequently, the identification and enhancement of bad SQL queries
represent an important task in the context of performance optimization.
When is an analysis of the database useful? The amount of the total response
time for the database in an SAP system is the best indicator. Usually, you can
416
use a workload analysis to determine this amount, which should normally be

less than 40%. A further indicator is the ratio between Busy wait time and
CPU time (Transaction ST04N; see Figure 8.4), which should be approxi-
mately 60:40. If the Busy wait time is considerably higher, you can perform
a wait event analysis (see Section 8.2.2.2, Analyzing Wait Events), whereas an
increased CPU time indicates a CPU resource bottleneck. Another factor to
be taken into account regarding the Busy wait time is the following: Possi-
bly, Transaction ST04 also includes idle wait events in the Busy wait time.
For this reason, you should check the correctness of the Busy wait time using
the data from the V$SYSTEM_EVENT view.
As a prerequisite for the analysis of Oracle performance data, you must set
the TIMED_STATISTICS parameter to TRUE. However, this is already the case
after a standard SAP installation. Otherwise, you can set this parameter
dynamically as a SYS database user (logon via sysdba):
f05:oram05 1>sqlplus "/as sysdba"

SQL> alter system set TIMED_STATISTICS = TRUE;
8.2.2.1 Analyzing the Database Buffers

Two factors determine the quality of a buffer: On the one hand, there is the
logical access (logical read), which refers to every access to a block, and the
physical access to a block on the hard disk (physical read). Figure 8.3 illus-
trates this concept.
The buffer quality can be calculated based on these factors by using the fol-
lowing formula:
Quality (hit ratio) = Number of hits/Number of queries 100%
Basically, you must consider that all buffers are initialized after a system star-
tup and therefore have no informational value. For a buffer analysis, the sys-
tem has to be in an established state. In general, you can assume that this state
is reached after one or two days of operation. The number of logical reads on
the buffer cache of the database is another reference value for the established
state. This value should be greater than 50,000,000.
The database buffers of the Oracle database are located in the system global
area (SGA). You can find a detailed description of the individual buffer areas
and their functions in Chapter 3, Oracle Fundamentals.
417
8 Performance
Database SQL Command Database Instance

Interface in SAP Work
(Shadow Process)
Process
Logical Read Buffer Buffer

Hit Miss
Data Buffer
SGA
Physical
Read
Data Files
Figure 8.3 Access to Buffers and the Database
The overview in Table 8.3 includes the most important buffers in the SGA of
the Oracle database.
SGA Buffer Description
Data buffer Contains the buffered data blocks from the data files on the hard disk.
Parameter: DB_CACHE_SIZE
Shared pool The two main subcaches: data dictionary cache and library cache.
Parameter: SHARED_POOL_SIZE
Java pool Used by the Oracle JVM, but not by the SAP system. Parameter:
JAVA_POOL_SIZE
Large pool Buffer for special data (for example, message buffer for processes running
parallel queries). This buffer is very small and hardly used in SAP systems.
Parameter: LARGE_POOL_SIZE
Streams pool New buffer area in Oracle 10g for Oracle Stream, which manages data and
events in distributed environments not used in the SAP system. Parame-
ter: STREAMS_POOL_SIZE
Redo buffer Buffer for redo log data. Parameter: LOG_BUFFER
Table 8.3 Overview of SGA Buffers
As of Oracle Version 9i, the administrator can change the most important
parameters (DB_CACHE_SIZE and SHARED_POOL_SIZE) of the SGA at runtime
418
of the Oracle instance. This feature is referred to as dynamic SGA and should
not be confused with the Automatic Shared Memory Management (ASMM).
The old parameters from Oracle 8.1.x for the data buffer (DB_BLOCK_BUFF-
ERS) can no longer be used. SAP has generally approved the use of the
dynamic SGA, which is enabled by default during SAP installations based on
SAP Basis 6.40.
As of Oracle 10g, you can fully automate the SGA management function. In
that case, Oracle adjusts the individual areas, DB_CACHE_SIZE, SHARED_POOL_
SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, and STREAMS_POOL_SIZE, to your
current requirements. The SGA_TARGET parameter provides the total size of
the SGA and enables the ASMM. Moreover, if the DB_CACHE_SIZE and
SHARED_POOL_SIZE parameters are set, they provide the lower limits for each
buffer area. Due to the lack of experience with ASMM, its use in an SAP sys-
tem is not recommended. Nevertheless, it makes sense to use this parameter
in a nonproduction environment to minimize the administrative effort, but
only if you do not intend to use the system as an image of the production
system for testing purposes.
Access to analysis data in the SAP system occurs via the Oracle database
monitor (Transaction ST04N). This monitor provides you with all informa-
tion about the Oracle database, which can be accessed from within the SAP
system. The information about the database monitor originates from the
Oracle database, specifically from the V$ views. Figure 8.4 shows the initial
screen of the database monitor.
Table 8.4 explains the meanings of the most important key figures in the
Oracle database monitor and provides recommendations for its optimal
states after establishing the database.
In general, recommendations for particular buffers and characteristics are

only reference values. The values may deviate significantly without affecting
the performance of the SAP system. The user load on a training system, for
example, barely reaches the user load of a production system, so the load
that's generated by administrative tasks clearly prevails. Because these activ-
ities, such as the standard SAP background jobs and the creation of the data-
base statistics, focus on different load aspects, the individual performance
characteristics vary substantially. In this case, a ratio of User calls to Recur-
sive calls or <0.5 would not be unusual.
419
8 Performance
Figure 8.4 Oracle Database Monitor Main View
Buffer/ Descripton Recom-

Characteristic mendation
Data buffer Main database buffer for the data blocks (warning: This >94%
recommendation is very general, because there are
extreme cases in both directions, which means there are
systems running with 80% without problems and systems
having serious problems at 98%.)
DD cache Data Dictionary buffer contains metadata of the database >80%

(structures, users, authorizations)
Table 8.4 Essential Characteristics of the Oracle Database Monitor
420
Buffer/ Descripton Recom-

Characteristic mendation
SQL area get SQL cache stores the parse tree and execution plan of pre- >95%
ratio viously run SQL statements
Get ratio = S (hit)/S (request) 100
SQL area Indicates the quantity of all requests (in percent) that still >99%
pin ratio have required objects to be executed in the memory:
Pin ratio = S (executions = pin hits)/S (requests for execu-
tion = pins) 100
Reloads/Pin Ratio between reloads of a SQL statement (for example, <0.04

invalidated entry due to age) and execution requests
User/Recursive Ratio between user requests to the database and requests >2
calls that the database executes in addition to the user requests
(for example, due to a missing dictionary cache entry)
Busy wait time Total amount of all wait times of the database in terms of Ratio
seconds, without idle events (see Section 8.2.2.2, Analyz- approx.
ing Wait Events) 60:40
CPU time Total amount of CPU time consumed by all Oracle sessions
Table 8.4 Essential Characteristics of the Oracle Database Monitor (Cont.)
In the following text, we will look more closely at the relevant Oracle buffers
in the SAP environment with regard to performance.
Without a doubt, the data buffer for the actual data blocks of the database has
the greatest impact on performance because it reduces the total number of
physical disk accesses.
The logical reads include all reading requests to the database. During a buffer
get, the system tries to read the corresponding data block from the data
buffer for all requests that are not declared as direct path, which means they
don't have an explicit direct access to the database. A successful read access
is referred to as a buffer hit, whereas a failure leads to a physical read in
which the block is read from the data files on the hard disk (see Figure 8.4).
The hit ratio for the data buffer can be calculated as follows:
Quality (hit ratio) = (Logical reads Physical reads)/Logical read 100%
To obtain a good database performance, the buffer should process at least

94% of all block accesses (except for direct path operations) to the database.
You must check the following possible causes if the actual value falls below
this reference value:
421
8 Performance
The data buffer is too small

Many direct path operations that circumvent the data buffer when access-
ing data blocks (Note: Direct path operations are deducted from the hit
ratio when displayed in ST04N, but not in ST04. Therefore, there may be
differences.)
Expensive SQL statements (see Section 8.3)
The direct path operations include the following wait events:
direct path read and direct path write

direct path read (lob) and direct path write (lob) (Oracle 9i)
direct path read temp and direct path write temp (Oracle 10g)
You can view the number of direct path operations in Transaction ST04N
under Additional Function Display V$ V$SYSTEM_EVENT (see Figure 8.5).
This number should be very small (<0.5%), primarily in comparison with
the number of regular accesses to data blocks via the data buffer
(db file sequential read). SAP NetWeaver BI systems are an exception in
this case, because substantially higher values are acceptable here (see Chap-
ter 12, SAP NetWeaver BI and Oracle). You can use direct path operations, for
example, to access the PSAPTEMP tablespace. As an example, increasing the
PGA memory of individual database work processes can help you reduce the
number of these accesses to the temporary tablespace for JOIN or SORT oper-
ations. Figure 8.5 shows an excerpt of the V$SYSTEM_EVENT view.
Another note regarding this view: This view contains only the wait events
that occurred after the last database startup. You shouldn't be surprised if
you don't see all of the wait events described above in this excerpt.
Figure 8.5 Direct Path Operations in V$SYSTEM_EVENT
If you can exclude expensive SQL statements and direct path operations as
a reason for a poor hit ratio, you should, if possible, try to improve the per-
422
formance by increasing the data buffer. If you can only implement this
increase by extending the hardware, you should first exclude all possible
causes for a performance degradation before making a corresponding invest-
ment.
The initial configuration of the data buffer depends completely on the

intended use and the load on your system. In real life, many SAP production
systems work with data buffers of more than 100 GB. Therefore, we cannot
provide a general recommendation at this point. We would rather recom-
mend that you have SAP experts perform sizing sessions.
Since the introduction of the dynamic SGA with Oracle 9i, the Oracle admin-
istrator can test changes to the data buffer in a simple and convenient way.
The V$DB_CACHE_ADVICE view enables you to check how a change to the
buffer size affects the number of physical reads. The factor representing the
changes between the physical database accesses and the current status repre-
sents the possible reduction of the buffer in MB without a significant per-
formance degradation or the efficient expansion of the buffer in MB to fur-
ther minimize the physical reads. For this purpose, you must enable the
dynamic SGA and set the Oracle parameter, DB_CACHE_ADVICE, to ON.
In addition to the actual data buffer, the keep pool and the recycling pool also
buffer data blocks. If you use the dynamic SGA (Oracle 9i), you can see that
these two pools are no longer part of the data buffer but are included sepa-
rately in the SGA. The keep pool can be used for tables and blocks that
should not be displaced from the data buffer. The recycling pool, on the
other hand, can be used for tables that should not displace other blocks from
the data buffer but whose own blocks can be displaced immediately. The
standard settings in SAP do not use these pools; however, their usage is rec-
ommended under specific circumstances (see SAP Note 762808).
Apart from the data buffer, other important buffers of the Oracle database
are located in the shared pool, namely, SQL cache and dictionary cache.
The SQL cache (formerly known as shared cursor cache) is located in the
library cache and stores all Oracle-internal information for later reuse, if
required. This information is related to an SQL statement call, such as the
parse tree and the execution plan.
Another key figure for the SQL cache in the shared pool is the pin ratio. You
can calculate the pin ratio as follows:
Pin ratio = (Pin hits/Pin) 100%
423
8 Performance
Oracle processes an SQL statement by splitting it up into different compo-

nents. A pin is the request for the reuse of one of the components resulting
from the decomposition of the SQL statement, and, accordingly, a pin hit is
the successful reuse. However, this is not always the case because cache
entries may be invalidated by timeouts or displaced by other entries. If the
described reuse fails, the system must reload the corresponding SQL com-
mand with its new components. The "Reloads per pin" key figure results
from the relationship between requests (pins) and reloads (see Figure 8.4
and Table 8.4 above).
A buffer hit in the SQL cache simply means that the parsing of the queried
SQL statement was already performed. However, the pin ratio indicates the
number of successful reuses for a found cache entry. If the reuse fails, the
system reloads the corresponding component.
In the Oracle library cache, you can find further subcaches for the PL/SQL
(Procedural Language/Structured Query Language) packages as well as for
the control structures, such as locks and library cache handles. These play
only a minor role regarding the system performance.
The dictionary cache buffers rows from the dictionary of the Oracle data-
base, that is, information about structures of tables, authorizations, and so
on. This metadata of the database is needed regularly to process user
requests.
According to SAP, the minimum size of the shared pool should be approxi-
mately 400 MB. If the hit ratio values are permanently under the values
listed in Table 8.3, it may be useful to increase the value of the SHARED_POOL_
SIZE parameter. However, you should take into account that, for instance,
the structure of the database statistics may temporarily decrease the hit rate
in the shared pool significantly.
Possibly, you can also minimize the shared pool again if the performance
values (see Table 8.4) are acceptable and a larger subarea of the shared pools
remains free (>50 MB). You can find the free area in the shared pool in Table
V$SGASTAT free memory or by using the following SQL command:
Select bytes from V$SGASTAT

where pool = shared pool AND name = free memory;
In the V$SHARED_POOL_ADVICE view, you can also see how changes to

the shared pool size affect the cache hits and then resize the pool accord-
ingly.
424
As of Oracle 10g, you have the option to display the history of the load of the
shared pool. Furthermore, the DBA_HIST_SGASTAT view displays the
progress of the free space development.
The Program Global Area (PGA) component of the Oracle memory is locally
assigned to a server process (shadow process or background process). The
entire PGA memory of an Oracle instance can be calculated based on the
amount of PGAs of all database processes. You can find the total amount of
allocated PGA memory in Transaction ST04N or under Additional Function
Display V$/GV$ Views and Values V$PGASTAT total PGA allocated or by
using the following SQL call:
SelectVALUE from V$PGASTAT

where name = total PGA allocated;
The PGA of a process contains only the data and information that is needed
or to be processed. The size of the PGA plays a particularly important role for
memory-intensive sort and hash operations. Consequently, the administra-
tor should place special emphasis on the optimum configuration of this
memory, in particular in the SAP NetWeaver BI environment (see Chapter
12).
However, the configuration of the PGA has been considerably simplified

since Oracle Release 9i. Whereas the administrator of older Oracle versions
had to specify the PGAs for individual operations (for example, SORT_AREA_
SIZE and HASH_AREA_SIZE), you can now use the automatic PGA manage-
ment function. Similar to the ASMM for the SGA, Oracle adjusts the PGAs
for all server processes automatically. It is worth mentioning that, in contrast
to earlier versions, PGA memory that is no longer needed is released using
the automatic PGA management. You can limit the entire size of the PGA
using the PGA_AGGREGATE_TARGET parameter. As of Oracle 9i, the automatic
PGA management can be used (according to SAP Note 619876, it is even rec-
ommended) and is enabled by default in every SAP installation with SAP
Basis 6.40 or higher.
To better understand the PGA tuning settings, we will now introduce some
terms. To execute an operation, the Oracle process needs local memory, the
work area. If the available PGA memory for the process is sufficient for the
entire work area, we refer to this as an optimal work area size, and the corre-
sponding operation is called optimal execution. If the PGA is not sufficient,
the operation uses the temporary permanent storage (PSAPTEMP). The
resulting I/O activities (direct path operations without buffering) have a sig-
425
8 Performance
nificantly negative impact on the system's performance. If the first pass (first
recursion level) of the PSAPTEMP is successful, we refer to it as a one-pass
operation. If the PSAPTEMP is used for several passes, it is called multi-pass
operation.
There are different indicators to determine if the configured PGA memory is

too small. First, you should verify the following values in the V$PGASTAT
view (this applies to the automatic PGA management; see Figure 8.6):
Over allocation count

Specifies how often the PGA memory was insufficient. This value should
be around 0; otherwise, you must extend the PGA.
Cache hit percentage
Specifies the number of hits for the optimal work area size. Ideally, if the
value is 100%, no one-pass or multipass operations exist. This value
should be >70% for a normal ERP system and >90% for a BI system.
Figure 8.6 Characteristics of the PGA
426
The V$PGA_TARGET_ADVICE view displays how a change to the PGA_

AGGREGATE_TARGET parameter affects the PGA quality (over allocation count
and cache hit percentage).
The V$SQL_WORKAREA_HISTOGRAM view displays the frequency and

quantity of the PGA memory used by a process and, accordingly, when an
optimum, one-pass, or multipass operation was performed. The goal of siz-
ing the PGA is to avoid multipass operations entirely. Depending on the type
of system (ERP or BI), the percentage of optimum operations should be
>70% or >90%, respectively.
8.2.2.2 Analyzing Wait Events

The quality of the individual buffer memories (data buffer, shared SQL, and
so on) is not sufficient to make a reliable statement about the Oracle per-
formance. If, for example, a query resulted in a hit, this does not tell you
anything about the processing speed for the query and the output of results.
That's where Oracle wait events come into play. The database response time
consists of two elements:
CPU time: the time during which the Oracle session uses the CPU
Wait event: the times during which the Oracle session waits for an event,
such as reading a data block from a hard disk
A wait event is a situation in which an Oracle session waits for an event. This
event can come from different database areas. For example, the wait event,
log buffer space, indicates that the session had to wait for free space in the
redo log buffer. After starting the database, all wait events are collected in X$
tables and can be queried using different V$ views. The most important of
these views are as follows:
V$SYSTEM_EVENT
Contains all wait events since the database was started including their fre-
quency and average length.
V$SESSION_EVENT
Contains all waits since the database was started including their frequency
and the average and maximum lengths for every Oracle session.
V$SESSION_WAIT
Contains the current waits for every Oracle session or the information that
the CPU is currently being utilized.
427
8 Performance
With Oracle database Release 9i or lower, all monitoring data for the wait
events are deleted after restarting the database. Oracle 10g, however,
includes some history tables or views that store historical data. You can find
the history of wait events in the DBA_HIST_SYSTEM_EVENT view.
The consideration of waits allows you to precisely determine which actions

an Oracle session is currently performing or for which actions it is currently.
This makes it easier for the administrator to identify potential problems.
Moreover, the analysis of the system-wide collected waits provides details
on the optimization potential within the database.
Wait events are always composed of an event name and up to three optional
parameters to include more specific information on the event, as described
in the following example:
Event: direct path read: Waiting for a read operation on a data block
from the hard disk while circumventing the data buffer
Parameter 1: file number: File number of the file to be read
Parameter 2: first dba: First block to be read in the file
Parameter 3: block count: Quantity of blocks to be read
You can use the following SQL command to determine the file name for a file
number and the corresponding tablespace:
Select tablespace_name, file_name

From dba_data_files
Where file_id = ID;
Oracle 10g contains more than 850 wait events (Oracle 9i has about 400),
which are grouped in the classes shown in Table 8.5 to provide a better over-
view (as of Oracle 10g).
Wait Event Class Number of Wait Events in Class
Administrative 46
Idle 62
Application 12
Network 26
Cluster 47
Scheduler 1
Table 8.5 Wait Event Classes (Number of Wait Events)
428
Wait Event Class Number of Wait Events in Class
System I/O 24
Commit 1
User I/O 17
Concurrency 24
Other 591
Configuration 23
Table 8.5 Wait Event Classes (Number of Wait Events) (Cont.)
The following SQL statement can be used to determine to which class a wait
event belongs:
Select WAIT_CLASS from dba_hist_event_name where EVENT_NAME=Name;
It is important to know that some wait events don't influence the database
response time at all and can therefore be neglected in performance analyses.
On the on hand, these are all events that belong to class Idle. These events
are reported if an Oracle process is in idle state (that is, not performing any
action). The most commonly known and used event of this class is SQL*Net
message from client, which occurs if an Oracle shadow process is waiting for
a new query. On the other hand, there are wait events that are irrelevant to
the database, especially in the context of SAP. One reason for such a situation
can be that the time of an event is already included in another event; for exam-
ple, log file parallel write is already covered by log file sync. Further-
more, many (but not all) events that occur in Oracle shadow processes (DBWR,
PMON, SMON, etc.) are only of secondary importance, because the corresponding
operations are performed asynchronously to the Oracle work processes.
The following list shows the most frequent wait events that are usually irrel-
evant from the SAP perspective.
Oracle Wait Events not Relevant to SAP
db file parallel write

log file sequential read
smon timer
SQL*Net message from client
Log archive I/O
ARCH wait on SENDREG
429
8 Performance
Oracle Wait Events not Relevant to SAP
rdbms ipc message

jobq slave wait
log file parallel write
pmon timer
Streams AQ: <action>
As already described in Table 8.4, the ratio between Busy wait time and CPU
time (ideally 60:40) is generally a first indicator.
When starting a general wait event analysis, it is useful to create a list con-
taining the top wait events, that is, a list with the totaled wait times in
descending order. You can create this list in Transaction ST04N (see Figure
8.6) using the V$SYSTEM_EVENT view or by executing the following SQL
command:
select EVENT, TIME_WAITED, AVERAGE_WAIT

from V$SYSTEM_EVENT
order by TIME_WAITED desc;
Figure 8.7 Lists of Wait Events in the V$SYSTEM_EVENT View
430
The AVERAGE_WAIT column is formatted differently in Oracle 9i than in

10g. In Oracle 9i, the contained values are displayed as 1/100 seconds with-
out decimal places, so they are not precise enough for a serious performance
analysis. As an alternative, you can use the TIME_WAITED_MICRO column,
which contains the total wait time in microseconds. The exact average wait
time can then be calculated by dividing TIME_WAITED_MICRO by TOTAL_
WAITS.
Table 8.6 provides an overview of the individual columns of the V$SYSTEM_

EVENT view including their meaning.
Column Description
TOTAL_WAITS Number of occurrences of the wait event since the last start of
the Oracle database
TOTAL_TIMEOUTS Number of waits for which the corresponding event has not
occurred
TIME_WAITED Total wait time for the wait event in hundredths of a second
AVERAGE_WAIT Average wait time for the event in hundredths of a second

(AVERAGE_WAIT = TIME_WAITED / TOTAL_WAITS)
TIME_WAITED_MICRO Total wait time for the wait event in microseconds

(last column in Figure 8.7)
Table 8.6 Columns of the V$SYSTEM_EVENT View
Once the list has been created, it is searched from top to bottom to find crit-
ical wait events; during this step, idle wait events are ignored.
Figure 8.8 V$SESSION_WAIT View
431
8 Performance
You can obtain further important information in the V$SESSION_WAIT

view (see Figure 8.8). This view displays the current or most recently active
wait events of all Oracle processes.
Table 8.7 describes the meaning of the individual columns in the

V$SESSION_WAIT view.
Column Description
SID Session ID of the Oracle process.
P1TEXT, P2TEXT, Description and unit of the corresponding parameter.

P3TEXT
P1, P2, P3 Parameter values of the wait event.
WAIT TIME Time waited for the wait event (in hundredths of a second) once the
wait event is no longer active. The value of an active wait event is 0.
Moreover, there are two special values: Value = 1 if the duration of
the event was below the measurement accuracy and value = 2 if
TIMED_STATISTICS is not active.
STATE Wait events can have the following statuses:

WAITING: waiting/active (WAIT TIME = 0)
WAITED KNOWN TIME: has expired and had a duration of more
than 1/100 sec (WAIT TIME > 0)
WAITED UNKNOWN TIME: has expired and had a duration of less
than 1/100 sec (WAIT TIME = 1)
TIME STATISTICS OFF: has expired, but statistics are not recorded
(WAIT TIME = 2)
Table 8.7 Columns of the V$SESSION_WAIT View
Furthermore, it is often necessary or it simply makes sense to map the SAP

work processes to an Oracle work process or vice versa. The easiest way to
do this is to use the Oracle Session Monitor. In the Clnt proc column, this
component includes the process ID of the linked SAP work process for every
entry of an Oracle work process. This client PID corresponds to the Pid col-
umn of the SAP process monitor (Transaction SM50).
Warning
A CPU bottleneck can also cause a large number of different wait events. If the
CPU load is very high, it is possible that Oracle processes that currently hold a lock
are displaced. If other processes are waiting for this lock, several wait times can
increase drastically. You should therefore first ensure that sufficient CPU resources
are available.
432
Remark
As mentioned earlier, compared to the previous Release Oracle 9i, the number of
wait events was increased considerably in Oracle 10g. This is reflected, for
instance, in the splitting up of wait events for a more detailed root cause specifica-
tion. We will mainly use the wait events from Oracle 10g and only point out the
differences in comparison to Oracle 9i in a few situations.
The following sections describe the most important wait events and provide
some background information on these. You'll find several tables with the
most important details followed by a text section containing a description of
the wait event.
Name db file sequential read/db file parallel read
Parameter 1 File number(s)
Parameter 2 Block number(s)
Parameter 3 One or a number of parallel reads
Meaning These events represent the process of waiting for one or more parallel
read operations to be performed on blocks on the hard disk. In this case,
parallel does not refer to reading several blocks successively, but to simul-
taneous reads of different, nonsuccessive blocks.
Rating Average wait time should be less than 2, i.e., 2/100 s = 20 ms.
Table 8.8 db file sequential read/db file parallel read
If there are problem values for the average wait time, this primarily indicates
an I/O performance bottleneck. For information on the analysis of I/O prob-
lems, refer to Section 8.2.2.3, Analyzing the Database I/O. Another important
factor apart from wait time is the occurrence frequency of db file sequen-
tial read. If this value is very high, the wait event usually occurs in conjunc-
tion with a bad hit ratio of the data buffer. In this case, there are two solution
scenarios: You either tune potentially existing bad SQL statements (see Sec-
tion 8.3) or you increase the data buffer size.
Name db file scattered read
Parameter 1 File number
Parameter 2 Block number
Parameter 3 Number of blocks
Table 8.9 db file scattered read
433
8 Performance
Name db file scattered read
Meaning If this event occurs, an Oracle session is waiting for a successive read
operation on several blocks from the hard disk.
Rating A maximum of 10% of the WAIT_TIME of db file sequential read

(exception: in case of SAP NetWeaver BI, a higher value can be accepted).
Table 8.9 db file scattered read (Cont.)
A successive read operation on several blocks usually occurs only with

Full table scan or Index fast full scan. These access types reduce per-
formance significantly and should thus be avoided if possible. Once again,
SAP NetWeaver BI represents an exception to this rule (see Chapter 12). If
the occurrence of db file scattered read exceeds the values listed in
Table 8.9, you should determine the SQL commands that cause this situa-
tion. Information on these commands can be found using the function SQL
Request in Transaction ST04N or following menu path Resource Consump-
tion SQL Request. Pay attention to SQL statements with high disk reads.
Better yet, focus only on commands that include a full scan in their process.
SAP Note 619188 describes an SQL command that can be used as of Oracle
9i. Using this command, you can determine the 20 SQL statements that gen-
erate the largest number of disk reads because of full scans. You should then
determine if these commands can be tuned. Attention: If you make extensive
use of the Oracle transactions in the SAP system (for example, Transaction
ST04N) during performance analysis, this may be reflected in the results. In
this case, some of the top 20 SQL statements contain queries on Oracle spec-
ifications or Oracle monitoring data that are not associated with the normal
business-related SQL queries. These SQL statements should be ignored in
your analysis.
Name direct path read/direct path read temp

direct path write/direct path write temp
Parameter 3 Number of blocks
Table 8.10 direct path [read|write|temp]
434
Name direct path read/direct path read temp

direct path write/direct path write temp
Meaning This wait event is registered if the data buffer is circumvented when data
blocks are accessed. As of Oracle 10g, waits are categorized by either
access to "normal" blocks or access to temporary blocks from the
PSAPTEMP tablespace (temp).
Rating None of these events should be among the first 10 in the wait event list (in
descending order according to the totaled wait time; see Figure 8.7). Fur-
thermore, similar to db file sequential read, a maximum value of 2
applies to the average wait time, i.e., 2/100 s = 20 ms.
Table 8.10 direct path [read|write|temp] (Cont.)
If the problem is caused by a too long average wait time, this is probably also
caused by an I/O bottleneck. In this case, you should perform the steps
described in Section 8.2.1.2, Identifying the Causes of Bottlenecks in Hardware
Components.
If the direct path operations are performed too often and are therefore dis-
played among the first entries in the list, you must distinguish between the
reasons for these operations in subsequent actions.
For the Oracle database, there are three reasons why direct path opera-
tions are performed:
1. PSAPTEMP accesses
2. Parallel queries
3. Access to LOB data (large object)
For many operations on the PSAPTEMP tablespace (these can be recognized

by the wait event, direct path read/write temp), increasing the PGA stor-
age (see Section 8.2.2.1, Analyzing the Database Buffers) is a possible solution
to the problem. Operations that are performed for the second or third reason
cannot be distinguished in Oracle 10g. In contrast, when accessing unbuff-
ered LOB data in Oracle 9i, a separate wait event is generated:
direct path read/write (lob). LOB data, that is, large unstructured data
in table columns, is primarily used by the SAP system in tables with ABAP
code. Because of their size (up to four gigabytes), these LOBs are no longer
buffered in the data buffer. This again results in direct path operations. If
problems occur, buffering special LOB data might be advisable (see SAP Note
563359).
435
8 Performance
Parallel queries, that is, performing special actions such as a full table scan in
parallel, are generally not used by SAP. They are only used for SAP
NetWeaver BI systems. The reason for this is that these queries have several
disadvantages regarding the CBO and the resulting resource allocation (see
SAP Note 651060).
Name log file sync/log buffer space/log file parallel write
Parameter 1 Number of buffers/ - / file number
Parameter 2 - / - / Number of blocks
Parameter 3 - / - / Number of I/O requests
Meaning log file sync

Represents the process of waiting for the full synchronization of the
log files with the redo buffer by the LGWR (e.g., after a COMMIT ).
log buffer space
Represents the process of waiting for a free block in the redo buffer.
log file parallel write
Occurs if there is a wait time for the writing of blocks in the redo log
files.
Rating For all three wait events, a maximum average wait time of 4 applies, i.e.,
4/100 s = 40 ms. However, for current hardware, significantly lower val-
ues should be obtained that allow for about 15 ms.
Table 8.11 log file [sync|parallel] and log buffer space
All three wait events described above usually depend directly on I/O per-
formance during write operations for redo log files. You should therefore
first analyze and examine if there are I/O problems and whether these areas
can be optimized (see Section 8.2.1.2, Identifying the Causes of Bottlenecks in
Hardware Components, and Section 8.2.2.3, Analyzing the Database I/O). The
redo log files are the most I/O-intensive area of an Oracle database and
therefore have special requirements regarding their storage location and
parameterization. Section 5.2.3, Storage and SAN Infrastructure, provides
further information on this topic.
Under certain circumstances, it makes sense to completely deactivate logging

when importing or modifying large data volumes. This takes place, for
instance, during the initial loading of the SAP system in the database. How-
ever, after deactivating logging, you can only restore and recover data up to
the time when this action was performed. This means you need to create a
new full backup of the system once you reactivate the logging function.
436
Another aspect is the size of the redo buffer. If this buffer is configured with
less than one megabyte contrary to the SAP recommendation this may
also result in log buffer space wait events. If this situation occurs, you need
to change the LOG_BUFFER parameter to the size of one megabyte (offline).
There are a few other cause of the log file sync wait event, such as
enqueue wait situations (see SAP Note 745639, Section 12).
Name log file switch completion/(archiving needed)/(checkpoint

incomplete)/(private strand flush incomplete)
Parameters
Meaning These wait events are reported if the system needs to wait for a log file
switch for different reasons (see below).
Rating archiving needed

Should never occur
checkpoint incomplete
Should never occur (special SAP Note 79341)
completion
Should not be among the top 10 of the wait event list, at most one log
switch per minute
private strand flush incomplete
Should never occur
Table 8.12 log file switch completion
The term log file switch is a generic term for several wait events that occur
when switching to the next redo log file:
log file switch (archiving needed)

Occurs if the log switch cannot be performed, because the next redo log
file has not been archived yet
log file switch (checkpoint incomplete)
Represents the process of waiting for completion of the checkpoint of the
subsequent redo logs before the log switch
log file switch completion
Represents the process of waiting for completion of the log switch
log file switch (private strand flush incomplete)
Occurs if the LGWR waits for the DBWR to completely write the in-mem-
ory UNDO buffer (IMU) into the log buffer
An "archiving needed" event always has an average wait time of 98100

(time in one-hundredth of one per second), because the writing Oracle pro-
cesses (LGWR, DBWR, etc.) always wait for exactly one second in case of an
437
8 Performance
archiver stuck before a new write attempt is carried out. An archiver stuck
must not occur in an SAP production system, as this would cause a system
standstill. Only in a nonproduction system is a short archiver stuck accepta-
ble under certain circumstances, if the system reaches a very high load, for
instance, during client copies or data loading at night-time. To avoid this
standstill of the Oracle database, your backup strategy must ensure that the
disk volume on which the offline redo logs are saved (usually the directory
oraarch) is always backed up and purged so that there is sufficient space for
new offline redo logs after a redo switch. If you define other archiver desti-
nations using the LOG_ARCHIVE_DEST parameter, you need to ensure that
these are backed up as well.
In the case of a log file switch (checkpoint incomplete) wait event, the
"checkpoint not complete" error has occurred and was recorded in the Ora-
cle alert log. Checkpoints are performed during every log switch. Several
checkpoints can be active at the same time. The "checkpoint not complete"
error is recorded if a log switch is to be performed to a redo log with a check-
point that has not yet been completed.
The following four situations can cause a repeated occurrence of the wait
event or the "checkpoint not complete" situation:
1. Numerous redo logs are being written.

2. DBWR performance bottleneck.
3. Not enough redo logs.
4. Redo logs are too small.
If the Oracle database writes many redo logs, you should first examine
whether you are dealing with an operational load, that is, whether the
number of redo logs is caused by the normal system usage. If there is no
indication that your applications are responsible for the high redo log fre-
quency, there are several other possible reasons, such as misconfigurations
and Oracle bugs. Read SAP Note 584548 for a description of the possible
causes.
Usually, the reason for a high amount of redo logs can, of course, be found in
the system operation. As a first step, you should ensure that no more than
one redo log switch is performed per minute. If this is not the case, you
should increase the size of your redo log files. To do that, proceed as follows:
438
1. Log on to the database as sysdba:

sqlplus "/ as sysdba"
2. Delete a log file group:

ALTER DATABASE DROP LOGFILE GROUP 11;
If the error ORA-01623 is reported, use the following command to switch

to a new redo log file and wait for a few seconds before you repeat the
DROP command:
ALTER SYSTEM SWITCH LOGFILE;
If the error ORA-01624 is reported, the current checkpoint has not yet
been completed. Wait for a few seconds and repeat the DROP command.
3. Delete the corresponding operating system files in the redo log directory.
4. Set up the log file group 11 with a new larger size (<new_size> in MB):
ALTER DATABASE ADD LOGFILE GROUP 11
('/oracle/<sid>/origlogA/log_g11_m1.dbf',
'/oracle/<sid>/mirrlogA/log_g11_m2.dbf')
SIZE <new_size>M;
5. Repeat steps 2 through 4 for all existing redo log groups.
In the standard SAP installation, the redo log files of the four groups have a
size of 50 MB each. Increase the files incrementally and verify whether this
solves the problem. The scope of the increase depends on the number of
redo logs that are written per minute. If five log switches are performed per
minute with a size of 50 MB, there is no point in increasing the log size to
100 MB, but, change the size to, for instance, 300 MB right away.
If the log file switch (private strand flush incomplete) wait event
occurs or there are other indications of a bottleneck in the DBWR process,
for example, from the free buffer waits wait events (see below), you can
increase the number of DBWR processes to enhance write performance. To
do this, set the DB_WRITER_PROCESSES parameter using the following com-
mand (prerequisite: parameter management with SPFILE):
alter system set db_writer_processes=X scope=spfile;
Attention: The number of DBWR processes should not exceed the number of
available CPUs.
439
8 Performance
Another way to enhance write performance is, of course, to tune the Oracle
environment, that is, all I/O relevant components. Refer to Section 8.2.2.3
for further information on this topic.
The log file switch completion wait event occurs if an Oracle shadow pro-
cess must wait for the completion of a log switch. As described above, if too
many redo log switches exist during operation (more than once per minute),
this results in a critical condition regarding the database performance. How-
ever, if this happens, proceed as described earlier.
Name read by other session/buffer busy wait
Parameter 3 ID
Meaning These wait events describe the process of waiting for a block in the data
buffer, because this block is currently being read (read by other session)
or modified (buffer busy wait).
Rating The average wait time value should be below 2, i.e., 2/100 s = 20 ms.
Table 8.13 read by other session/buffer busy wait
In Oracle 9i, both events were named buffer busy wait, and parameter
value 3 indicated the reason: The IDs started with 1 or 2 and contained fur-
ther places depending on the exact reason. If the ID starts with 1 (ID = 1xx),
the event deals with the reading of a block. If it starts with 2 (ID = 2xx), the
wait event is caused by a write or change operation for a block. As of Oracle
10g, the name of the wait event already distinguishes whether the event was
caused by a read or write operation. Detailed information on the event cause
can be obtained from the parameters.
As all data that are read from or saved in the database "pass through" the data
buffer (an exception is the already mentioned direct path access), high I/O
loads always result in buffer busy waits. You always have the option to
reduce I/O load to decrease the amount of waits on the data buffer. This can
either be done by redistributing data loads or by tuning SQL statements so
that fewer data blocks must be read (see Section 8.3).
The second criteria besides I/O load is the management of the data blocks
themselves. In this area, in particular, Oracle 9i provided significant
enhancements with the introduction of Automatic Segment Space Manage-
ment (ASSM). Previously, the individual blocks of a tablespace or a segment
440
were managed using the PCTUSED, PCTFREE, FREELISTS, and FREELISTGROUPS

parameters. These parameters were used, for example, to define the fill level
of a block (PCTUSED): As long as this value was not reached, the block would
accept new data. From this group of parameters, only one was also imple-
mented in ASSM so that you can still use its function: PCTFREE.
Without ASSM, the database administrator had to or could decide for each
table how the individual blocks of the segments were used. This made it pos-
sible to choose between performance and efficient space usage depending on
the change frequency. This task is now performed by ASSM. SAP made the
use of ASSM possible as of Version 9.2.0.5, and on installations with SAP
Basis 6.40 and higher all data tablespaces are set to ASSM by default. If prob-
lems occur that are related to buffer busy waits, you can now switch to
ASSM to resolve issues relating to segment management. In Oracle 9i, this
switching procedure involves downtime, whereas Oracle 10g allows you to
make the transition online. SAP Note 620803 provides step-by-step instruc-
tions for this transition.
Name write complete waits/free buffer waits
Parameter 3 ID
Meaning These wait events occur if an Oracle process must wait for the DBWR process
to write a block into the relevant data file.
Rating Both wait events must not be among the first 10 entries in the wait event list.
Table 8.14 write complete waits/free buffer waits
If these wait events occur too often, the data buffer may be too small or the
performance of the DBWR process is poor. If possible, resolve this problem
by increasing the value for the DB_CACHE_SIZE parameter or optimizing the
I/O performance (Section 8.2.2.3). Furthermore, you can raise the number of
DBWR processes as described in the previous section.
Name rdbms ipc reply
Parameter 1 PID of the background process
Parameter 2 Timeout in seconds
Parameter 3
Table 8.15 rdbms ipc reply
441
8 Performance
Name rdbms ipc reply
Meaning This wait event occurs if an Oracle shadow process must wait for a back-
ground process.
Rating This event must not be among the top 10 entries in the wait event list.
Table 8.15 rdbms ipc reply (Cont.)
In general, the occurrence of the rdbms ipc reply wait event is not a prob-
lem, as there are various reasons why a process must wait for a background
process. The crucial factor is the duration of the wait time for the background
process. The main reason for this event is wait situations in the
BEGIN BACKUP, TRUNCATE, and DROP operations, because the CKPT process
must perform a checkpoint in these operations. In Oracle 9i and lower, there
is the additional drawback of a design weakness that results in the entire data
buffer being searched for affected blocks in a DROP or TRUNCATE operation;
this process can take quite some time with larger buffer sizes. This problem
does longer exists in Oracle 10g.
In general, the duration of rdbms ipc reply wait events is very short. For
this reason, the average wait time should not exceed 10 ms, as this would
indicate several wait periods that are much too long and increase the average
value. In this case, it is advisable to examine the enqueue wait events,
because some of them are closely related to the rdbms ipc reply wait event.
Refer to SAP Note 745639 for further information on this topic.
If you suspect a problem with this wait event, you can use the Oracle Session
Monitor (Transaction ST04N; Resource Consumption Oracle Session) and
V$SESSION_WAIT to determine which Oracle work process is waiting for
which background process. In V$SESSION_WAIT you'll find the SID of the
process that is waiting for the rdbms ipc reply wait event, while parameter
1 (column P1) displays the PID of the background process for which the
work process is waiting. Subsequently, you can use the Session Monitor to
find out which actions the background process is currently performing. For
a more detailed analysis of the actions performed by Oracle processes, you
should use the functions of the ORADEBUG trace. The procedure is
described in SAP Note 613872.
Name latch free/latch: <latch_name>/wait list latch free
Parameter 1 Latch address
Parameter 2 Latch number
Table 8.16 Latch Wait Events
442
Name latch free/latch: <latch_name>/wait list latch free
Parameter 3 Number of sleeps
Meaning This wait event occurs if a process must wait for the release of a latch.
Rating The rating heavily depends on the specific latch wait event. In general,
latch wait events should not appear among the top 10 entries in the wait
event list.
Table 8.16 Latch Wait Events (Cont.)
A latch is a very low-level lock mechanism for the SGA memory structures.
In contrast to a lock, a latch is applied only for a very short time. For this rea-
son, latch requests are not placed in a queue, but the requesting processes
permanently try to apply the latch. This so-called spinning process is per-
formed as many times as set in the _SPIN_COUNT parameter. If a process
applies a latch and another process tries to access the respective memory
area, but does not succeed in doing so during the spin phase, a latch <latch_
name> wait event is activated. Oracle 10g contains 27 latch wait events, and
their names specify the location or the memory structure in which the latch
is applied. All "irrelevant" latches are referred to as "latch free."
In older Oracle releases (before 10g), all waits are summarized under the
term latch free. As an analysis of the different latch wait events would go far
beyond the scope of this chapter, we refer you to SAP Note 767414 for more
detailed information on this topic.
Name enqueue (9i)/enq: <type> - <description> (10g)
Parameter 1 Type
Parameter 2 ID1 (9i)/Detailed information in plain text (10g)
Parameter 3 ID2 (9i)/Detailed information in plain text (10g)
Meaning An event of this type occurs if a process is waiting for the release of an
Oracle lock.
Rating The average wait time value (of all enqueue events) should be below 10,
i.e., 10/100 s = 100 ms. As all possible lock situations are collected under
one event in 9i, it is generally not a problem if these events are listed
among the lower top 10 entries in the wait event list. With Oracle 10g,
however, the different enqueue waits should not appear among the top
10 (an exception is TX "row lock contention").
Table 8.17 Enqueue Wait Events
443
8 Performance
Finally, we want to introduce another important Oracle wait event related to

Oracle database locks (enqueues and locks). The meaning of this event can be
seen from the development between 9i and 10g. In Oracle 9i, all enqueue
waits were still accumulated as one event so that it was rather difficult to
perform an analysis. With 10g, however, 184 enqueue wait events were
introduced, which, are grouped in different classes (see Section 8.2.2.4,
Other Performance-Relevant Aspects of the Oracle Database; this section
describes database locks in greater detail).
8.2.2.3 Analyzing the Database I/O

If you have found a hint on problems in the I/O area in one of the preceding
analyses, for example, due to an anomaly of a corresponding wait event, you
inevitably get to the I/O analysis. Unfortunately, from the SAP administra-
tors point of view, the analysis of an I/O system is restricted. In addition, an
I/O bottleneck can generally not be solved during the operation if it is caused
by hardware or the distribution of data.
Term Clarification
Before getting started with the analysis, we should clarify the term hard disk. (Also
refer to Figure 8.11.) Regarding server systems for business-critical applications,
namely, the world of SAP and Oracle, the term hard disk has two meanings:
Physical: magnetic memory; the hardware part
Virtual: operating system resource (device); the software part
In the following text, the term hard disk is always used with the second meaning,
because the SAP system or the database considers only the operating system with
the resources available as the underlying level. Regardless of whether the hard disk
is visible as a hard disk with file system or as a raw device, the virtual hard disk in
a modern IT infrastructure is far more than a physical hard disk. In fact, there is a
complex storage architecture with many different components behind the operating
system as the abstraction layer. All of these parts of a storage system, for instance,
interface cards to a SAN, storage switches, or array controllers, can be relevant for
I/O problems. Unfortunately, you as an SAP or Oracle administrator have no chance
to identify and solve problems but need assistance from specialists of the respective
hardware partner. You should keep this definition in mind and remember the
"veiled" complexity of the term in the relevant parts of the following sections.
Critical points in the structure of an Oracle database are as follows: The most
I/O intensive areas are without any doubt the redo log files followed by the
data files. Out of those, the undo (or rollback) and PSAPTEMP tablespaces
can be pointed out, which always (undo) or, especially in the OLAP environ-
ment (PSAPTEMP), show increased access rates. Offline redo logs and Oracle
444
executables are less important. Read Section 5.2.3, Storage and SAN Infra-
structure, to find out more about the optimal distribution of Oracle files.
We already briefly touched upon analyses in Section 8.2. In the operating

system monitor (ST06) you can view the current utilization of all hard disks
of the operating system (see Figure 8.9) under Detail Analysis Menu using
the Disk button.
Figure 8.9 Overview of the Hard Disks in the Operating System Monitor
By double-clicking on one of the displayed disks, you can view the utilization
of the selected disk during the last 24 hours. The most important key figure,
Utilization, shows a mean value over a period of one hour (see Figure 8.10).
Figure 8.10 History of a Single Hard Disk
445
8 Performance
If you discover a problem with a hard disk via the I/O utilization, you must
identify which parts of the Oracle and SAP installation are on the disk.
Unfortunately, the SAP system does not enable you to retrace the direct
assignment of files and raw devices to hard disks. The reason is the already
mentioned resource of the virtual hard disk that is displayed in the operating
system monitor. Between the virtual hard disk and the actual files, server
systems have another virtualization layer, such as the typical Logical Volume
Manager (LVM) for UNIX operating systems. Figure 8.11 illustrates the rela-
tionship between the individual components.
For example, if you want to create a connection between the hard disk and
the Oracle data files or raw devices, you must use tools of the operating
system.
You have two options to increase the I/O performance: You can increase the
performance of the hard disk(s) or try to reduce the I/O load.
Hardware
Disk Array
Storage Network
Interface Internal
Card Hard
Disks
Operating System Kernel

Drivers Drivers
Devices, e.g.,
/ dev/dsk/c12t 0d3
Logical Volume
Logical Volume, e.g., Manager
/ dev/ vg00/lvol1 Volume
Group Volume
e.g. vg00 Group
Ra
ss
w
ce
De
Ac
v ic
e
eA
vic
De
cc
File System, e. g .,VxFS File System, e. g.,HFS

es
w
s
Ra
Applications
Application Files of the Swap and Data
Application
e.g., Oracle Operating System, e.g., Space
e.g., SAP
Root Directory (/)
Figure 8.11 Overview of a UNIX System with Logical Volume Manager
Let us first take a look at the options to increase the hard disk performance.
First, you should check if you have used all options of the operating system
for optimal performance:
446
1. Does the layout of the structure of your Oracle files correspond to the rec-
ommendations, especially regarding the separation of load-intensive files
(see Chapter 5, Planning the System Landscape)?
2. Have you used all possible options of I/O processing?
3. Are all current drivers for I/O subsystems installed?
You should pay particular attention to the options of I/O processing. In gen-
eral, all operating systems provide two functions for I/O operations: the file
system caching and file lock mechanisms. File system caching works in a similar
way to the Oracle data buffer (but is easier) as a temporary storage for the
access of applications to I/O systems. File locks serve as write locks so that
data or files cannot be changed simultaneously by two different processes.
For the options of I/O processing, the handling of the mentioned operating
system functions is decisive. Table 8.18 lists all options in descending order
of their performance.
Name Description
Raw I/O When raw I/O (or raw devices) is used, the operating system functions
are bypassed completely, and logical volumes and hard disks are directly
accessed. Furthermore, there is no file system and correspondingly no
data in the traditional sense.
Concurrent I/O As is the case for raw I/O, file system caching and file locks are bypassed
completely, but there is a file system and thus normal files. Of course,
the used file system must support concurrent I/O (e.g., Veritas VxFS).
This I/O type can be used for Oracle databases, as the Oracle-internal
locking functions already provide a collision-free access and hence
ensure the integrity of data. Caution: Only volumes that contain only
Oracle data or redo log files may be operated in this mode (Oracle exe-
cutables are also excluded).
Direct I/O Direct I/O disables only the file system caching of the operating system
but uses the locking mechanism for the data access.
Cached I/O Uses all operating system functions for I/O and is generally the default
setting for I/O processing.
Table 8.18 Options of I/O Processing
In addition to the I/O modes already mentioned, there are two more inde-
pendent options: synchronous I/O and asynchronous I/O. When a process per-
forms a synchronous input or output, the process must wait until the opera-
tion is completed and only then can continue to work or perform the next
input or output. This is not the case for asynchronous I/O. Thus, the process
can continue working simultaneously with the I/O operation. Generally, you
should use asynchronous I/O.
447
8 Performance
SAP Note 834343 provides a table with the currently supported combina-
tions of operating system, file system, and I/O options.
Another remark on raw I/O: Basically, with raw I/O, you can assume that
you can reach an I/O performance that is about 20% higher than the per-
formance with other I/O options. Why is raw I/O not always used? The main
disadvantage of raw I/O is that it always involves significantly increased
administration efforts for setting up and managing the Oracle database.
However, a psychological aspect also assumes an important role regarding
the usage of raw I/O: The administrator "misses" his files. The Oracle recom-
mendation for the usage of raw devices is as follows:
Oracle recommends that raw devices should only be considered when the
Oracle database is I/O bound.2
Raw I/O is fully supported and integrated by SAP, Oracle, and all relevant
monitoring and backup solutions so that it can be especially used for per-
formance-critical installations, such as SAP Business Information Ware-
house. In this case it is also possible to operate only parts of the Oracle data-
base, for instance, redo log files or the temporary tablespace, on raw devices.
For the usage of the described I/O options, the administrator also has to keep
two relevant Oracle parameters in mind:
DISK_ASYNCH_IO (Default: TRUE)

This option ensures that asynchronous I/O is always used when it is
offered by the operating system.
FILESYSTEMIO_OPTIONS (Default: none on the part of Oracle, however,
depending on the version SAP uses ASYNC (9i) or SETALL (10g))
This parameter overrides DISK_ASYNCH_IO when file systems are used and
includes the following options:
NONE
No direct I/O and no asynchronous I/O.
DIRECTIO
Enables direct I/O.
DIRECTIO
Enables asynchronous I/O.
SETALL
Enables concurrent I/O (if available), direct I/O, and asynchronous I/O.
2 Oracle recommends that raw devices should only be considered when the Oracle database
is I/O bound. See www.oracle-training.cc/oracle_tips_raw_devices.htm.
448
For example, this can be used for installations with the combination of raw
devices and file system to use asynchronous I/O on the raw device (DISK_
ASYNC_IO=TRUE) on the one hand, but also to use cached I/O for file system
data (FILESYSTEMIO_OPTIONS=NONE).
In addition to the options for I/O processing, SAP supports further parame-
ters for the different systems that can affect the I/O performance. SAP Note
793113 is a good starting point that refers to the individual operating sys-
tem-specific notes. To increase the hard disk performance, you can also
exchange hardware. Especially regarding the already described complex
storage systems that are now used in important areas, many components can
be significantly enhanced when they are replaced by a new generation.
Together with your hardware partner, you should decide whether such an
exchange makes sense or not. Let us now take a look at the second option for
increasing the I/O performance: the attempt to reduce the I/O load. Here,
you must bear in mind which I/O type occurs where. Table 8.19 provides
notes on the I/O reduction.
Data type/ Read I/O Write I/O

I/O type
Redo log files / Keep data files no longer than neces-

sary in backup mode (parameter
backup_dev_type)
If possible, use NOLOGGING
Avoid long transactions with timeouts
and rollbacks (because changes are
unnecessarily logged)
Avoid unnecessary INSERT-, UPDATE,
and DELETE operations
Avoid unnecessary indexes
Data files Tuning of expensive SQL Time between log switches more than
statements (Section 8.3) one minute
Caching of LOB accesses Distribution of data files across differ-
(SAP Note 563359) ent volumes or hard disks
Extension of the Oracle Extension of the PGA
buffer pool Extension of the buffer pool (at free
Extension of the PGA buffer waits)
Temporary Tuning of expensive sort- Tuning of expensive sorting with sort,

data files ing with sort, hash, or bit- hash, or bitmap functions
(PSAPTEMP) map functions Extension of the PGA
Extension of the PGA
Table 8.19 Notes on the I/O Load Reduction
449
8 Performance
Regarding the reduction of I/O load, we will concentrate on one aspect in

more detail: the distribution of data files to reduce the accesses to individual
volumes and hard disks. "File system requests" in the Oracle monitor (Trans-
action ST04N; Overall Activity) are suited to get an impression of the
number of accesses to the individual data files. Figure 8.12 shows the corre-
sponding view, sorted by the number of read accesses.
Figure 8.12 Access Statistics for Oracle Data Files
With the access statistics, the administrator can, for example, distribute the
top 10 data files to different media or data subsystems. You find an example
for moving data files below:
1. Log in as sysdba user. The corresponding tablespace must be set to offline

for moving the data files:
alter tablespace PSAP<SID> offline;
2. Move the file at the operating system level (to simplify matters from the
Oracle shell with a preceding "!"):
! mv /path with old volume/<sid>.dataX /path with new volume/
<sid>.dataX
3. Change the path of the file in the Oracle database:

alter tablespace PSAP<SID> rename file
450
/path with old volume/<sid>.dataX to /path with new volume/<s

id>.dataX;
4. Set the tablespace to online again:

alter tablespace PSAP<SID> online;
Because the data files are generally rather big and the corresponding
tablespace needs to be offline, it is not possible to move the files while the
SAP system is running. When files of the SYSTEM or of the UNDO tablespace
need to be moved, the procedure mentioned above does not work. Instead,
the database needs to be offline to move the files. The new path to the file is
published in the MOUNT status with the following command:
alter database rename file ...
8.2.2.4 Other Performance-Relevant Aspects of the Oracle Database

At the end of this section we will discuss two additional very important and
performance-relevant aspects:
Oracle enqueues and Oracle optimizer statisticsare locks at database level

that ensure permanent access to Oracle resources, such as objects or data
records in tables. For example, if an Oracle process wants to change a data
record, an enqueue is created to protect this modification as an atomic
operation. If a second process wants to change this data record, it also cre-
ates an enqueue that is placed in a queue. The processing of the queue fol-
lows the FIFO principle (first in, first out). Oracle enqueues are often
referred to as exclusive lockwaits or Oracle locks. If an enqueue or a lock sit-
uation occurs, the corresponding enqueue wait event is generated. Gener-
ally, Oracle distinguishes between two types of enqueues that are again
differentiated by type: User enqueues are the enqueue types that occur
with "normal" data changes, for instance, inserting or deleting data
records or restructuring an index. There are three types:
TX (transaction enqueue): Evolves from every change made to a data
record when the system needs to wait for this change. This is the most
frequent enqueue type.
TM (DML enqueue):
Occurs when a complete object, for example, an index, is locked.
UL (user enqueue): Is generated when a user sets a lock with DBMS_
LOCK.REQUEST. This function is not used in the SAP standard.
451
8 Performance
System enqueues are the enqueue types that occur in many Oracle-inter-
nal management mechanisms. There are more than 40 different types.
However, most of them are only partially or not at all relevant. The most
important type in the SAP environment should be mentioned, though: ST
(space transaction enqueue). This enqueue is generated during extent
management in DMTS (dictionary-managed tablespace).
A data lock mostly affects a row of a table if it is changed via update, delete,
insert, or select for update. Such locks, as described above, are called TX
enqueues. Other enqueues, for example, also lock entire segments (e.g., TM
enqueues) or critical paths (ST enqueues). The set lock is always kept up to
the data base command COMMIT and is then released again. Because a commit
or rollback is carried out after every SAP transaction step (caution: not
transaction), the database locks should generally be kept no longer than a
few seconds.
Of course, database locks are generally important for data consistency in the
database. Therefore, their occurrence is normal and presents no problem for
the performance. However, this only applies when the locks are only held as
long as necessary and no serialization effects occur. This would mean that
increasingly more processes wait for the release of a database lock. You
should therefore observe the Oracle lock monitor in Transaction ST04N or
by following menu path Exceptional Conditions Lock Monitor (or Transac-
tion DB01). Caution: You will not see the current locks that are kept in the
Oracle database but only the lockwaits and thus the requested locks that
have not been immediately assigned (that are being waited on). Information
about the current database locks can be found in the views V$LOCK and
V$LOCKED_OBJECT. Figure 8.13 shows these two views also called via
Transaction ST04N or by going to Additional Function Display V$/GV$
Views and Values.
The figure demonstrates how the Oracle Process 31 (Session ID) locks the
object with ID 18.992. V$LOCKED_OBJECT illustrates that it is a TM enqueue
in Mode 3. Furthermore, V$LOCK demonstrates that Process 31 also keeps
another TX enqueue in Mode 6.
The mode shows how restrictively the object is locked. Table 8.20 illustrates
the possible modes of locks.
452
Figure 8.13 Locks of the Oracle Database
Mode Name
0 Not hold or not requested
1 Null mode
2 Row shared table locks (RS)
3 Row exclusive table locks (RX)
4 Shared table locks (S)
5 Shared row exclusive table locks (SRX)
6 Exclusive table locks (X)
Table 8.20 Modes of Oracle Locks
The description of the individual modes and their corresponding effects

would fill several pages. You should therefore refer to the Oracle documen-
tation at www.oracle.com/pls/db102/homepage under Concepts Data Con-
currency and Consistency. We will only give a brief description of the basic
difference between shared and exclusive locks:
An exclusive lock protects a resource against a shared or another exclusive

lock. It is set when the resource is supposed to be changed. The first pro-
cess that requests and receives an exclusive lock for a resource is the only
process that can change the resource until it releases the lock.
453
8 Performance
A shared lock on a resource also allows other shared locks on that resource
but prevents exclusive locks. That means write access to the resource is
not possible with a shared lock. Therefore, the resource can be read con-
sistently.
In the individual modes, these principles are linked at table and row level.
Let us take another look at the example of Figure 8.13. What is happening? As
described, Process 31 holds a lock with Mode 3 on Table 18992. That means
it has a shared lock on the entire table (TM enqueue) and wants to make changes
to the row. This also corresponds to the meaning of mode row exclusive table
locks. Of course, changing the rows also determines an exclusive lock on the
corresponding row, which can be seen in V$LOCK. The last entry demonstrates
that Process 31 holds an exclusive lock (Mode 6) on a row (TX enqueue). That
is, Process 31 only makes entries or changes in Table 18992.
The Oracle session monitor (Transaction ST04N; Resource Consumption

Oracle Session; see also Chapter 4, SAP and Oracle) shows which SAP work
process is linked with Oracle Process 31. Using the following SQL command,
you can determine which object is specified by the object ID:
SQL> select object_name, object_type from dba_objects where object_

id=41733;
Finally, we will take another look at the V$LOCK view and, in particular, at
the lock mode column in Figure 8.13. (Caution: Dont confuse this with the
column with the same name in V$LOCKED_OBJECT.) This column contains
the mode of a requested lock. That is, in case of a value >0, the process waits
for a lock from the lock mode that it requested. At this point, you can recog-
nize a lockwait. As mentioned before, such a requested but not assigned lock
would also be displayed by the Oracle lock monitor (Transaction DB01) in
the SAP system. Furthermore, you can also determine the Oracle process that
holds the lock on the "demanded" object (lock mode = 0) via the columns
Lock ID1 and Lock ID2 (objects).
If you discover Oracle locks that are held longer or that are being waited on,
you should identify the SAP work process that causes the lock via the client
host and the client ID. Afterwards, you can determine which program sets
the lock for which user and does not release it. A further analysis can then be
made with the help of the user or the developer.
If you cannot identify an SAP process with the respective lock, there are two
possibilities: First, the SAP work process was cancelled and the "attached"
454
database shadow process was not closed properly. In this case, you can
delete the lock manually by cancelling the corresponding database shadow
process using tools of the operating system. (Caution: Ensure that you dont
cancel the wrong database shadow process or even an Oracle system pro-
cess.) To prevent such a situation, you can set the parameter SQLNET.EXPIRE_
TIME in the file sqlnet.ora, which enables an automatic cleanup of cancelled
sessions. See SAP Note 20071 for further information. Second, the lock
could also be kept by an external process or its attached database shadow
process.
See the corresponding Oracle documentation and SAP Note 745639 for fur-
ther information on Oracle enqueues.
The table statistics of the Oracle database provide another essential perform-
ance aspect. The Oracle Database Optimizer is supposed to determine the
optimum access path for accessing the data. Generally, there are two types of
optimizers: the Rule-Based Optimizer (RBO) and the Cost-Based Optimizer
(CBO). The RBO calculates the access paths according to rules that derive
from the "where" clause of the SQL statement to be optimized. However,
because all databases running on SAP systems use the CBO, the exact process
of the RBO is not relevant for us. The R/3 systems version 3.x or older on
Oracle older than 7.3.3 were the only exceptions, because the SAP applica-
tions for the RBO were developed on these systems due to technical prob-
lems with the newly introduced CBO.
The cost-based optimizer calculates the access path to the data on the basis of
the costs required for the access. In Oracle systems, you recognize the usage
of the CBO with the parameter OPTIMIZER_MODE. In SAP systems on Oracle
9i, the parameter has the CHOOSE value, which determines that the CBO is
always used when statistics are available for a table and that otherwise the
RBO is used. From Oracle 10g onward, the RBO is no longer supported, so
different levels of the CBO are provided for selection. As of Version 10g, the
SAP default value for the parameter is therefore ALL_ROWS.
The exact definition of the access costs depends on the database. For Oracle
releases older than 9i, the costs are exclusively determined via the blocks to
be read, whereas versions higher than Oracle 10g allow several key values
(single-block reads, multiblock reads, and CPU) for cost determination. The
exact working method of the CBO is kept secret by Oracle. A description and
summary of the known facts regarding the CBO can be found in SAP Note
750631, Rules of Thumb for Cost Calculation of the CBO. The most important
thing to keep is mind is that the costs are mainly calculated on the basis of
455
8 Performance
table statistics. Therefore, the generation of these statistics is an important

task of administrators. Optimizer statistics are generated in two steps:
1. Analyzing the table

2. Creating the statistics
In the first step, all tables of the database are analyzed to determine for
which table statistics have to be renewed. Because the actual creation of sta-
tistics is very resource-intensive, this two-phase process prevents unneces-
sary new statistics from being created when the content of a table is insignif-
icantly changed. SAP urgently recommends that you use only the SAP tools
to create optimizer statistics, as these tools are especially customized to meet
the requirements of the SAP software for the Oracle database. The BR*Tools
introduced in Chapter 4 can be called for the creation of statistics by means
of the command line (as <sid>adm user)
brconnect -u / -c -f stats -t [<TABLESPACE_NAME>|ALL]
or can be scheduled via the DBA planning calendar (Transaction DB13) (see
Figure 8.14).
Figure 8.14 DBA Planning Calendar
456
Chapter 9, System Operation and Monitoring, provides further information on

the DBA planning calendar.
For every execution of the BR*Tools, and thus for every table analysis run
and statistics creation, you find the corresponding log file in the log display
for DBA operations (Transaction DB14). There, by clicking on the BRCON-
NECT button, you can view all brconnect logs. You can identify the logs for
the update of the optimizer statistics by means of the description or abbre-
viation "sta" in the FID column. By double-clicking, you can view the log file
and find an entry for every table, whose statistics are recalculated (method =
C) or estimated (method = E), such as:
BR0881I Collecting statistics for table SAPA22.CCMSBIDATA with

method/sample C ...
BR0881I Collecting statistics for table SAPA22.CPRTYPET with
method/sample E/P30 ...
Which table of the Oracle database is analyzed and how it is analyzed is con-
trolled by two aspects:
Most important are the rules that are programmed in the BRCONNECT pro-
gram. Those are:
First, it is determined whether new statistics are required or not (on
the basis of the number of changed rows), followed by the actual cre-
ation of the statistics (two-phase concept).
No statistics on pool and cluster tables for Oracle 9i or earlier.
Accuracy of the statistics based on the number of entries in the table
and so on.
The second aspect is the content of the DBSTATC table. Using this table,
the system administrator can influence the statistics creation for individ-
ual tables. Therefore, the table is also referred to as exception table. In
every row of DBSTATC, the parameters for running the statistics creation
are set for a specific table. The maintenance of table DBSTATC is per-
formed via Transaction DB21 (see Figure 8.15).
Table 8.21 lists the most important columns of the DBSTATC table.
You can also implement new tables in the DBSTATC table that were, for
example, created through developments. See SAP Note 106047 for further
information on the maintenance of the DBSTATC table.
457
8 Performance
Figure 8.15 Excerpt from the DBSTATC Table
Column Description
Database object Name of the table
Usage A = Application Monitor (ST09) and Optimizer

O = Only for the optimizer
Active Controls if the statistics for the table are renewed. Possible values are,
for example:
A: Active (is checked and updated, if required)
I: Ignore
U: Unconditional (statistics are always updated)
N: No statistics
R: Only temporary statistics
ToDo Forces the statistic to be generated once.
Method How the statistic is generated either by the exact analysis of the entire
table (C) or by the estimation according to procedure <sample> (E).
Sample You have two options:

P <n> n = Percent of the table rows are analyzed
R <n> n 1,000 table rows are analyzed
Table 8.21 Columns in the DBSTATC Table
8.2.3 Analyzing the SAP System

Having discussed the analysis of the hardware and operating system as well
as of the Oracle database, we now turn to the analysis of the SAP system, or
more precisely, the individual instances of the SAP system.
The criteria that are relevant for the performance of an SAP instance can be
divided into two main categories:
458
1. Configuration of the characteristics of an instance, for example, the

number of work processes and their types or, even more importantly, the
memory configuration of the instance.
2. Configuration of the SAP buffers, that is, the analysis and administration
of table buffering, of number range buffering, and of program buffering in
other internal SAP buffers.
The following sections briefly describe these categories and give an overview
of the most important options and settings.
8.2.3.1 Configuring the Memory of an SAP Instance

In general, the importance of the SAP memory configuration (not of the SAP
buffer) has decreased due to the technological progress in recent years.
Above all, the enhancement of the main memory capacities, which also
involves changing to the 64-bit technology, means the main memory is usu-
ally no longer the limiting factor of an SAP instance. However, the configu-
ration must still be performed so that the instance can work with high per-
formance. In the following text, we will briefly introduce the SAP memories
and their configuration and provide information that is relevant to perform-
ance. However, we won't go into too much detail, as the SAP memory con-
figuration depends on the operating system and would thus fill an entire
book if it was described thoroughly.
The memory configuration of an SAP instance must be defined in the

instance profile. The parameters of the individual memory areas are also in
these sections.
Note
The following text refers only to the SAP memory management for typical UNIX
operating systems (HP-UX, Solaris, and AIX). The concepts for configuring the
memory of other platforms supported by SAP, such as Linux, Windows, or IBM
iSeries, sometimes deviate considerably. For example, Windows and Linux provide
an option for Zero Administration Memory Management, where only one param-
eter defines the total memory that is available for the instance and where the indi-
vidual memory areas are automatically configured.
The SAP memory areas are:
Roll memory
Every work process contains a roll memory area that is located in the local
process memory. It stores the initial user context that is swapped to the
459
8 Performance
roll buffer when the process is changed (SAP process multiplexing). The
roll buffer itself (dont confuse it with the roll memory) is also referred to
as the roll area and, like the extended memory, is a shared memory area.
From SAP R/3 3.0 onward, the roll memory plays only a minor role,
because the main part of the user context is directly stored in the extended
memory and the access change is performed through the pointer. This
method is significantly faster than copying the data in the memory.
Extended memory
This shared memory is used by all work processes and is the most impor-
tant memory area of an SAP instance. It contains all user contexts of the
users that are logged in to the instance, with the exception of the small
initial context that is being copied between roll memory and roll buffer.
Heap memory
This memory area is a local memory that belongs to one work process.
The work process type determines when the heap memory is used. It is
used for dialog processes when the extended memory or at least the part
of the extended memory that may be used by a single work process is
entirely utilized. For nondialog processes, the local heap memory is used
immediately after the roll memory, because here no process multiplexing
is performed, and the extended memory is therefore reserved for the dia-
log processes.
Paging memory
Previously, this memory area served to reduce the load of the roll memory
for operations with large amounts of data via a paging procedure similar
to the paging process of an operating system. Today, the memory is only
used when the ABAP commands, EXTRACT and EXPORT ... TO MEMORY...,
are used.
Table 8.22 provides an overview of the most important parameters of the

SAP memory areas. The SAP Help provides a complete overview of all
parameters.
SAP Memory Parameter Description

Area
Roll memory and ztta/roll_first Defines the size of the initial local roll mem-
roll buffer (area) ory of a work process. The default value is
only 1 byte.
Roll memory and ztta/roll_area Defines the size of the entire local roll mem-
roll buffer (area) ory of a work process.
Table 8.22 Parameters for the SAP Memory Areas
460
SAP Memory Parameter Description

Area
Roll memory and rdisp/ROLL_SHM Defines the size of the roll buffer in the
roll buffer (area) shared memory.
rdisp/ROLL_MAXFS If the roll buffer is not sufficient, an overflow

file exists whose size is specified with this
parameter.
Extended em/initial_size_MB Defines the total size of the extended mem-

memory ory of an SAP instance.
ztta/roll_extension Defines the maximum amount of extended

memory that can be used by a single work
process. Two other parameters can also be
used to specify the maximum memory for
dialog and nondialog processes.
abap/ heap_area_total Defines the total size of the local heap

memory as the total of all work processes in
one SAP instance.
abap/heaplimit If one work process uses more heap mem-

ory than this parameter specifies, the pro-
cess is started again at the end of the trans-
action to release the memory.
Paging memory rdisp/PG_SHM Specifies the size of the paging memory of

an SAP instance.
rdisp/PG_MAXFS If the paging memory is not sufficient, an

overflow file exists whose size is defined
with this parameter.
Table 8.22 Parameters for the SAP Memory Areas (Cont.)
Transaction ST02 (memory monitor) gives you an overview of the configu-

ration and the current status of the SAP memory areas. Figure 8.16 shows
the section that is relevant for the memory areas.
Figure 8.16 SAP Memory Areas in Transaction ST02
461
8 Performance
The Current use column provides information on the current use of the
instance. Max. use lists the maximum use since the last start of the instance.
The In memory column contains the maximum values of the individual areas
in kilobytes. The areas are defined according to the parameters from Table
8.22. Note that the parameters of the extended and heap memories are
defined in megabytes or bytes, whereas the roll buffer (roll area) and paging
memory (paging area) sizes are specified by the number of blocks with a size
of 8k. The last column, On disk, shows the maximum sizes of the overflow
files of the roll buffer and of the paging memory.
The Detail analysis menu button in Transaction ST02 enables you to obtain
a selection with further analysis options. Using the SAP memory function,
you can display a detailed analysis of the SAP memory areas. Here, the
administrator can view which user is using how much of which SAP memory
at a given time. You can also obtain a historical overview.
Before the extended memory was implemented, the roll-in and roll-out
processes were critical for performance during process multiplexing, that is,
copying the user context. Thanks to the usage of pointers, this problem has
become obsolete. What needs to be done now is to configure the SAP mem-
ory areas with enough memory of each area and without wasting memory
space that could be of more use somewhere else (for instance, for SAP buff-
ers or the Oracle database). However, if not enough memory is available for
one of the areas, the performance of the SAP instance may automatically
decrease considerably, or program aborts may occur. The following list sum-
marizes the most important rules for the configuration of SAP memory areas:
Choose the roll buffer and the paging memory so that the overflow files
are never used, that is, the value in Max. use is always smaller than the
value in In memory, as shown in Figure 8.16. The SAP default values for
these areas are usually sufficient. An exception is SAP NetWeaver BI,
which sometimes places considerably more load on the paging memory.
The extended memory is the most important SAP memory area. There-
fore, you should calculate it more generously. Earlier recommendations of
6 to 10 MB per user are too small for todays requirements. Consequently,
you should consider 20 to 30 MB per user.
As a contingency reserve, 20% of the extended memory should remain
free, that is, the Current use should never exceed 80% of the In memory
value. If the Max. use value indicates that this rule has not been adhered
to, you should check if the history if this contingency reserve was often
used and increase the extended memory, if required.
462
If Max. use or the history clearly shows that the extended memory is
never used up to 80%, you can reduce it accordingly and use the memory
somewhere else.
Regarding the maximum amount of the extended memory of a single
work process, SAP recommends 10% to 20% of the entire extended mem-
ory.
You can use heap memory (Current use) without a problem as long as you
use it only for nondialog processes. However, if dialog processes use local
heap memory, they change to the so-called PRIV mode, that is, they can
no longer perform a user change and are bound to the running transac-
tion. You can identify this status in the SAP process overview (Transaction
SM50). You should avoid this status by all means. If required and possible,
you should specify more extended memory.
Of course, you should keep an eye on the hardware resources when config-
uring the memory for an SAP instance. It is important that there is enough
space in your operating system for all SAP memory areas plus the SAP buff-
ers plus a contingency reserve. If there isnt enough space, the SAP instance
won't start. The best option for the performance is that everything (including
the maximum heap memory) can be mapped onto the physical memory of
the server.
Note on SAP Memory Management

The extended memory is not created completely at the start of the instance but,
depending on the usage, grows to its maximum size. During this process, the
extended memory, which has been allocated once, is not released for the operat-
ing system even if it is no longer used by the SAP instance and is thus actually avail-
able. This is a problem if a memory bottleneck occurs on the server, because "avail-
able" memory is not recognized as being really available by the operating system
and therefore can be removed during swapping, for example. Some new instance
parameters that are provided as of Kernel 4.6D (with the relevant patch) can solve
this problem. For more details, see SAP Note 724140.
8.2.3.2 SAP Buffers

The SAP system also uses the buffers concept to access data quickly that is
often reused. This concept is equivalent to that of buffers in the database.
Figure 8.17 illustrates the two-level buffer architecture and the relationships
between the buffers. Note that, of course, each instance of an SAP system has
its own buffers.
463
8 Performance
ABAP
Processor
O pen S Q L
Data
Native SQL or BYPASSING BUFFER
Table Single
buffer request Program Buffer
buffer hit
Screen Buffer
Data
Database buffer saved

Interface
buffer missed
Native SQL
SAP Buffer in Shared Memory

Data
SAP Oracle
buffer request
buffer hit
Database buffer saved

Work Process buffer missed
Data Buffer in SGA
phy. read
raw Data
Data Files
Figure 8.17 Two-Level Buffer Architecture of SAP-Oracle
The combination of database and SAP buffers involves a performance-rele-

vant feature. Lets take a data record for currency conversion as an example.
This data record is loaded from the Oracle database if a relevant transaction
is performed by the user. First, the Oracle database loads the data record
from the data files (or from the raw device) and stores it or the data blocks in
the data buffer. The SAP system or the work process receives the data record
for the current transaction and stores it also in the SAP buffer for table
entries (table single buffer or table generic buffer) if the table that originally
contained the data record is provided for buffering.
If the currency conversion is performed again, the data record is provided by

the SAP table buffer if it has not become invalid, due to changes, for exam-
ple. As a result, the data record or the respective blocks are swapped from
the Oracle data buffer after a short period of time (depending on the buffer
size). If they are also swapped from the SAP table buffer, for example,
because other objects have been loaded in the meantime and the buffer size
is not large enough, another query of the currency conversion leads inevita-
464
bly to a physical read of the database. That is, the objects and data that are
buffered in the SAP application server are generally swapped from the data-
base buffer, which causes a physical data access when they are queried again
from the database.
Therefore, SAP buffers primarily contain other data than the database buffer.
Table 8.23 shows which individual buffers are available.
SAP Buffer Description
Table definition Buffers the entries of the DDNTT table. Explanation: The name table
(TTAB) (NTAB) contains all information on the table and field definitions in
the ABAP repository. It consists of the DDNTT (table definitions) and
DDNTF (field descriptions) tables.
Field descriptions Buffers the entries of the DDNTF table.

(FTAB)
Short NTAB Stores a combination of TTAB and FTAB buffers.
Initial record Stores the layout of the data records.
Program Here, the compiled executable versions of the ABAP codes are buff-
ered. The swaps are performed on the basis of the LRU concept (least
recently used).
CUA Buffers objects from the SAP GUI, such as menus or button definitions
based on the LRU concept.
Screen Stores the dynpro screens that have been generated previously.
Calendar Here, all defined factory and holiday calendars from the TFACS and
THOCS tables are buffered (also on the basis of the LRU concept).
OTR This is the Online Text Repository buffer that stores texts that are, for
example, used in BSPs.
Table generic/ These are the entries for table buffering in an SAP instance (as
Table single described below).
Export/Import This buffer is used for all work processes and stores data clusters using
Exp/Imp SHM specific ABAP commands (see SAP Note 702728).
Table 8.23 SAP Buffers
To carry out a qualitative evaluation of the buffers, it is required that they be

in an established state, as is the case with database buffers.
Transaction ST02 enables you to access the memory monitor of the instance
to which you are currently logged in. Figure 8.18 shows the part of the mon-
itor that lists and evaluates the individual SAP buffers. The individual fields
and their meanings are described in Table 8.24.
465
8 Performance
Figure 8.18 SAP Memory Monitor (Excerpt Containing the SAP Buffers)
Field Description
Hit ratio The hit ratios of a buffer are indicated as a percentage. They are calcu-
lated in the same way as the hit ratio of the database buffers:
Buffer quality (hit ratio) = (Buffer requests Database requests)/
Buffer requests 100%
Allocated Memory space (in RAM) occupied by the SAP buffer.
Free space Memory space that is available for the buffer.
Dir. size Maximum number of possible buffer entries.
Free directory Number of buffer entries that are available.
Swaps Number of swaps from the buffer since the last start of the instance.
Table 8.24 Fields in the Memory Monitor
Hit ratio and swaps are the essential criteria for SAP buffers. SAP recom-
mends that the hit ratio of the buffers should be >98%. The two export and
import buffers are an exception with an optimum hit ratio that is supposed
to be >80%. If possible, swaps from buffers should be avoided. However,
depending on the utilization and usage of the SAP instance, it is not always
possible to avoid them. For example, if an instance runs in another operating
mode to execute more batch jobs at night, other ABAP programs and tables
are required that are loaded into the program or nametable buffer. This usu-
ally leads to swaps. You can therefore tolerate a small number of swaps (a
few hundred swaps per day). SAP recommends that up to 10,000 swaps per
day can be accepted for the program buffer.
If the number of swaps increases considerably, you must check if the free
space or the number of free directory entries of the buffer is insufficient. The
respective instance parameter must then be increased step by step to elimi-
466
nate the swaps. You can find the parameters for the SAP buffers using the
Current parameter button in the memory monitor.
In addition to the hit ratio and swaps, you should also keep an eye on the
free space for SAP buffers. You can possibly find unused memory resources
if large parts of a buffer are not used in the established state. For example, in
Figure 8.18, this is the case for the field description buffer because 30 MB are
unused. It makes more sense to use this memory somewhere else.
There are three types of table buffering in an SAP system:
1. Single buffering
In this process, each record (row of a table) is stored individually in the
TABLP buffer if it has been read once on the database. To use single buff-
ering, it is important that all key fields are qualified in the where condition
for a query
2. Full buffering
If a record is read from the database, the entire table is stored in the TABL
buffer.
3. Generic buffering
This buffering type is specified by the number of key fields used for selec-
tion. If a record is read from a table buffered as generic 1 from the data-
base, all other data records are buffered that are identical to the record ini-
tially read in key field 1. Corresponding buffering with n key fields is also
possible. The generic buffered data records are in the TABL buffer.
Note
When full buffering is activated, a client-dependent table, that is, a table that
always has the MAN key first, is automatically buffered as generic 1.
The entire buffer management is mapped onto the database interface of the
individual SAP work processes, that is, here it is decided when which buffer
is accessed. For the access of the buffer, it is decided whether all required
keys of a table are specified in the where condition. Let us assume that the
TAB table with the key fields KEY1, KEY2, and KEY3 is buffered as generic 2,
that is, via KEY1 and KEY2. The following call would benefit from a buffering
process:
SELECT * FROM TAB WHERE KEY1=X and KEY2=Y;
467
8 Performance
In contrast, the following calls could not be buffered and would thus cause
an access to the database:
SELECT * FROM TAB WHERE KEY1=X;

SELECT * FROM TAB WHERE KEY1=X and KEY3=Z;
Additionally, there are numerous exceptions where the table buffer is not
used either:
When the SQL commands SELECT FOR UPDATE or SELECT DISTINCT are
used
When the aggregate functions SUM, MIN, MAX, and AVG are used
When the Native SQL statements or the Open SQL condition
BYPASSING BUFFER is used
Because all SAP buffers are kept separately for each instance, the table buffer
is forced to synchronize the instances. If the content of a table buffer is
changed, the running work process writes a corresponding entry to the
DDLOG database table. The instances read this table regularly to invalidate
data records in their own table buffers that are affected by the changes. That
is, if a table is individually buffered, only the affected data record is declared
as invalid; however, if tables are generic or fully buffered, the complete
generic part of the table or the entire table in the buffer is invalidated.
This synchronization process is controlled via the profile parameters

rdisp/bufrefmode (controls reading and writing of the DDLOG table) and
rdisp/bufreftime (specifies the frequency at which the DDLOG table is
read default: 60s). Furthermore, you can monitor the buffer synchroniza-
tion in the SAP memory monitor: SAP_memory monitor (ST02) Detail
analysis menu Buffer syncron.
The synchronization of the table buffers between the instances may have a
negative effect on the system performance. Therefore, some criteria must be
met by tables and views (they can also be buffered) for useful buffering:
The table must be small and read very often.

The change rate must be very low, for example, less than 1% changes per
day for tables with a size of one megabyte.
A short-term inconsistency must be acceptable because delays during syn-
chronization between the instances (rdisp/bufreftime) may occur.
468
On the basis of the characteristics mentioned, the individual data classes in

an SAP system can be assigned relatively rigidly to one buffering procedure.
SAP distinguishes between three data classes that are contained in a database
in the system (apart from the actual ABAP code and technical data):
1. Transaction data
All data that is generated and changed in large quantities during operation,
such as invoices, delivery notes, sales orders, material movements, and so
on. The tables grow rapidly during operation and can thus reach a size of
several gigabytes. Therefore, they are generally not suited for buffering in
the SAP system.
2. Master data
Master data is changed rarely or never during live operation and contains
information on material, customers, vendors, and so on. The respective
tables change less than the transaction data but still reach a size of several
hundred megabytes. Therefore, master data tables are also not included in
the SAP table buffer.
3. Customizing data
Data that is generated when mapping the enterprise processes onto the
SAP system (customizing). The most common examples are company
codes, factories, sales organizations, conditioning, and so on. The respec-
tive table records are changed or supplemented rarely during operation
and are therefore usually buffered in the SAP system.
On the basis of SAPs own experience, table buffering functions are already
configured in the supplied versions of the different SAP software solutions.
To decide when and how a table is buffered reasonably or not, you have to
monitor the SAP table buffering processes. This can be done in the SAP
memory monitor (ST02) via Detail analysis menu Call statistic or using
Transaction ST10. A selection screen is displayed where you must select the
Table type, Period, and SAP instance factors. Because every instance has its
own buffers, you must theoretically analyze each instance. However, this is
only virtually relevant if different tasks are performed on these instances,
such as batch against dialog instances, or if organizational enterprise parts
(for example, international branch offices) are distributed across different
instances and thus other data must be buffered.
Figure 8.19 shows an excerpt from the table access statistics, and Table 8.25
lists the most important columns and their meanings. (Note: You can expand
the individual detail columns via buttons.)
469
8 Performance
Figure 8.19 Excerpt from the Table Access Statistics
Column Description
Table Name of the table
Buffer state The most important possible states are:

Valid: Table is in the buffer and valid.
Invalid: Table buffer has become invalid and cannot be loaded yet
because the change has not been completed.
Pending: Table buffer has become invalid and cannot be loaded yet
because the grace period is still running.
Loadable: Table buffer was invalid and can be loaded again.
Absent: Table has never been loaded.
Displaced: Table was swapped from the buffer.
Error: Very important state, particularly regarding the performance,
because it indicates that table buffering was cancelled (see SAP Note
618868, Section 9, Table Buffering)
Buffer key opt Buffering type: ful = full, gen = generic, sng = single.
Buffer size Space in the buffer currently occupied by the table.
Size maximum Maximum space in the buffer occupied by the table.
Invalidations Indicates how often the table buffer was invalid.
ABAP/IV proces- Number of ABAP requests for the table, which can be broken down as
sor requests follows: direct reads, sequential reads, and changes (update, inserts,
deletes)
DB calls Combination of direct and sequential fetches (transferring the results of

an SQL request to the calling SAP work process).
DB rows Number of data records that are transferred from the database to the
affected SAP system. Exception: initial load of the buffer.
Table 8.25 Columns of the Table Access Statistics
470
The following list describes how to check the buffered tables and how to
decide if it makes sense to buffer them:
Estimation of the database accesses (DB rows affected)

The database accesses of buffered tables should be considerably smaller
than those of unbuffered tables at a similar number of requests. If there
are buffered tables at the beginning of the table access statistics after the
table was sorted by DB rows affected, you should further examine them.
Change rates of the tables
They are calculated on the basis of the following formula:
(Values from the table access statistics from the ABAP request area): Change
rate = Changes/(direct reads + seq. reads) 100%
Reference values for acceptable change rates are:

Table size < 1 MB Change rate < 1 %
Table size > 1 MB and < 5 MB Change rate < 0.1 %
Table size > 5 MB are rarely buffered; if buffered, change rate < 0.01 %
Size of the table in the buffer
Sort the table access statistics by the buffer size column and ensure that all
larger buffered tables (>100,000 bytes) are set to valid, if possible. If this
is not the case, the table buffer should be extended, if possible. Note: The
Analyze table button enables you to perform a complete table analysis in
the background. This analysis indicates, for instance, the size of the table
in the database or the distribution of the generic areas.
Select quality
When you double-click on a row of the table access statistics, a detail screen
for the respective table opens. There, you will find the select quality as the
ratio between ABAP requests and database calls (Fetches/Exec: fetch-
ing/changing data records). The quality should be approximately >95%.
Vice versa, the administrator searches for tables that are not buffered but
should or could be buffered. For this purpose, here is a brief overview of the
most important criteria:
Number of ABAP requests

Sort the table access statistics by total ABAP requests (for a better over-
view, you can view only the unbuffered tables). The ABAP Dictionary
tables, DDNTF and DDNTT, are usually listed at the top. However, these
tables are already stored in the nametab buffer. Look for Customizing or
customer-specific tables in the top request entries (see the Remark box on
the next page).
471
8 Performance
Change rates of the tables

Once you have found the relevant tables, determine the change rates as
described above and compare them with the recommendations.
Remark
How do you recognize customizing tables? There are numerous customizing tables,
such as the condition tables Axxx (xxx = 000 999). If you search for a table in the
standard SAP system, you can find further information as follows:
1. Look at the short text of the table in the Data Dictionary (Transaction SE11) and
check the specifications under Goto Technical Settings in the Logical storage
parameters field (see Figure 8.20).
2. Search for the table for your application (for example, ERP or SCM) in the SAP
documentation under http://help.sap.com. Because Customizing is documented
very well, this documentation mentions or describes nearly all Customizing
tables.
3. Look for SAP Notes on the table in the SAP Support Portal. There are some
explicit notes on buffering for some tables.
The buffering settings for a table can be made in the Data Dictionary (Trans-
action SE11). There, you must enter the respective table and view and
change it via Goto Technical Settings (see Figure 8.20).
Figure 8.20 Table Buffering Settings
472
Analyzing Program-Based Performance Problems: SQL Optimization 8.3
Some final remarks on table buffering: Always check whether it is a table

from the standard SAP system or a customer-specific table. As already men-
tioned, SAP provides your standard tables with the relevant buffering set-
tings. You should only change them if SAP explicitly recommends or author-
izes this (for example, in an SAP Note). Regarding customer-specific tables,
you should discuss with the developer of the table or application that uses
the table if buffering is possible or makes sense.
In general, activating the buffering function is more dangerous than deactivat-

ing it. If you deactivate a buffering function, performance problems of appli-
cations that use the table are the worst that can happen. In contrast to that, if
the table buffering is switched on caused by a delay in the synchronization
between instances of the SAP system logical inconsistencies may occur.
Having viewed the individual components and areas of an SAP system with
an Oracle database regarding the performance under administrative aspects,
the last section deals with aspects related to the program.
8.3 Analyzing Program-Based Performance Problems:

SQL Optimization
In Chapter 3, Oracle Fundamentals, the possibility of optimizing the query lan-
guage SQL via DBMS is described as one of the strengths of relational data-
bases. The SQL query describes the desired result and leaves it up to the data-
base system to calculate the result set. In general, the DBMS provides much
better ways to create the best execution plan . Theoretically, expert software
developers should design a better plan only in exceptional cases. However, in
practice, the Optimizer is not able to locate the best plan in all cases. A devel-
oper with knowledge of the interdependencies between the attributes and of
the value distribution often must support the Optimizer by providing hints.
As an SAP-Oracle administrator, you are responsible for detecting and avoid-

ing program-based bottlenecks. Thomas Schneider reports that quite fre-
quently, situations occur in which a few expensive SQL statements cause more
than 50% of the database load (SAP Performance Optimization, SAP PRESS,
2005). These issues can often only be handled with the support of the devel-
opment team, even if you as an administrator can immediately alleviate the
problems by creating an additional index or using other technical measures.
Our examples show you how to develop high-performance programs, dem-

onstrate a few typical errors, and describe their impact on the interaction
between SAP and Oracle. In this context, we will introduce essential SAP
473
8 Performance
tools for analyzing SQL statements in the following sections. In addition,

we'll also demonstrate how you can use and create indexes.
On the one hand, this will provide you with material for advising the devel-
oper team regarding a high-performance use of SQL in a qualified manner.
On the other hand, you will learn how to use these tools to identify prob-
lems in that area, and you will get to know basic solution approaches.
8.3.1 Two Goals: Functionality and Performance

ABAP programs are often developed by application experts whose IT knowl-
edge is rather limited. You have to ensure not only that the programs pro-
vide functionally correct results, but also that the tools be implemented in
such a way that they provide high performance.
REPORT zbuch_bsp1. REPORT zbuch_bsp2.

DATA wa LIKE sbook. DATA wa LIKE sbook.
SELECT * SELECT *
FROM sbook FROM sbook
INTO wa. INTO wa.
IF wa-passname = 'Cindy Lindworm'. WHERE passname = 'Cindy Lindworm'.
WRITE: wa-fldate,/. WRITE: wa-fldate,/.
ENDIF. ENDSELECT.
ENDSELECT.
Bad Statement Good Statement
Figure 8.21 Inefficient and Efficient Use of SQL
The example shown in Figure 8.21 shows two functionally identical variants
of a report that based on the flight data table, SBOOK lists all days on
which Cindy Lindworm booked flights. For this purpose, in the variant on the
left, all data records of the SBOOK table are read from the database. After that,
the data records determined for further processing (here: output) are selected.
In the other variant, the selection is performed by the where clause, which is
triggered by the Oracle DBMS. The second variant has two advantages:
1. The Oracle server process transfers only the data that is really needed for
the work process.
2. Oracle can select the correct data records very quickly by using existing
help data structures.
An old IT saying goes that you make mistakes whenever they are possible.
Functional errors can either be avoided by using proper specifications or
identified by means of testing. A performance-critical programming style
may remain undetected for a long time. That's because hardware performs at
474
different speeds or the SAP systems in use have different loads. Conse-
quently, the statement that the execution of the SAPBC_DATA_GENERATOR
report takes about 138 seconds for creating the flight data model is of no real
relevance.3
Note
Not every "bad" SQL statement causes performance problems. SAP and Oracle
proactively provide mechanisms to process such statements quickly (see Chapters
2, 3, and 4). On the other hand, not every optimized SQL statement is fast. Some
processes simply need a lot of time.
8.3.2 Effects
A poorly formulated SQL statement has both indirect and direct effects on
performance. Figure 8.22 illustrates the direct effects. In the example shown
in this figure, the transferred data packages between Oracle and the SAP
application server consists of exactly one SQL query that is submitted and
requests the entire SBOOK table. Therefore, the Oracle database needs to
deliver the entire table, which can involve many physical reads (). In addi-
tion, the transfer of the entire table with delivery of an unnecessary number
of data packages is involved (). The Oracle system itself cannot perform
any optimization action because it does not know that numerous data
records in the application are discarded. In the application server itself, all
data records are (sequentially) verified with regard to whether they corre-
spond to the IF condition (). This is quite time-consuming for the gener-
ated flight database that contains 90,000 data record in the SBOOK table.
SAP GUI
SELECT * Sequential Processing of Data in ABAP

FROM SBOOK SAP NetWeaver
900,000 Loop Passes
AS
SFW TCP/IP DP1 DP2 DP3 DP4 DP5 DPn
Oracle Transfer of Multiple Data Packets via TCP/IP

Physical Read on Database Files
Figure 8.22 Effect of a Bad SQL Statement
3 We have chosen the monster data record variant and created the database in a background
job (Transaction SM36).
475
8 Performance
Figure 8.23 shows the processing of a good SQL statement. The statement is
improved because not all attributes of the desired data records that are
marked with * are requested only the FLDATE attribute is. Based on an
exact description of the desired result quantity, the Oracle system can deliver
the minimum result set. Consequently, only a small number of loop passes is
necessary for the work process (), and only some data is transferred, which
fits into a single package in our example (). Moreover, Oracle can create an
optimized execution plan. For example, by using indexes, the physical read
operations can be limited to some index data blocks as well as to those data
blocks containing the requested data records.
SELECT fldate SAP GUI

FROM SBOOK
WHERE passn= Sequential Processing of Data in ABAP
Cindy Lindw SAP NetWeaver 17 Loop Passes
AS
SFW TCP/IP DP1
Oracle Transfer of Few Data Packets via TCP/IP

Few Physical Reads via Index Access
Figure 8.23 Effect of a Good SQL Statement
However, not every SQL statement that requests only the necessary data, like
the one in Figure 8.23, is optimized. You also need to consider the process-
ing by the Oracle system, particularly the use of indexes. From the point of
view of performance, it is sometimes useful to extend the SQL queries with
seemingly redundant where conditions to use an existing index. However,
sometimes you may have to create a new index. We will describe these two
aspects in greater detail in Section 8.3.6, Indexes for Faster Access.
In addition to the direct effects, there are also some indirect effects we
should take a look at. For example, data that is requested unnecessarily occu-
pies space in the buffers of the application server and database system. In the
case of write requests, buffered data must be processed by transaction man-
agement.
For experiments and optimizing operational processes, SAP tables allow you
to define whether and to what extent they are buffered in the table buffer of
the application server. To do that, you must select a table in Transaction
476
SE11 (ABAP Dictionary), go to Display or Change and then select Technical

Settings. Figure 8.24 shows the buffering settings. You can define whether
you want a table to be buffered, whether buffering is allowed but switched
off, or whether buffering is not allowed at all. If you allow buffering, you can
also specify if individual data records, table sections, or entire tables should
be buffered. Because the SCARR table used in the example is part of the
standard SAP system, the settings can only be changed using an object key,
which must be requested from SAP. If a table is buffered, it is very likely that
the Oracle database is not accessed in experimental runs, which you should
take into account when discussing the results.
Figure 8.24 SCARR Is Not Buffered
8.3.3 Problem Analysis Tools

SAP provides a range of tools to detect and analyze performance problems
that are related to SQL queries. There are tools that support the examination
477
8 Performance
of the entire system and others that enable a detailed analysis of an SQL
statement or program.
In this context, the analysis of the transaction profiles (Transaction ST03) and
Oracle performance (Transaction ST04 or ST04N) again plays a major role
(see Figure 8.25). You can use these tools to identify and isolate problems
from the point of view of the entire system first.
Figure 8.25 Oracle Performance Analysis with ST04N
The following parameters are important for optimizing SQL statements:
SQL area getratio describes the ratio between matches and requests for an
object in the library cache and should be close to 100% for a production
system. This shows that the shared pool size is well selected for the actual
query load, which can be caused by good queries or a generous measure-
ment.
SQL area pinratio describes the ratio between matches and requests for
reading and executing objects and should also be close to 100%. Here
again, expensive queries can have a negative impact due to displacements.
Vice versa, SQLA.Reloads/Pin describes the ratio between the necessary
reloads (SQL query parsing) and the accesses. Consequently, a value close
to 0 should be reached here.
The quality of the dictionary cache should also be close to 100 because it is
required for the plan creation and query processing. In addition, the size of
478
the data dictionary is known because of the fixed structure4. Therefore, the
system can size the memory area in an optimal manner. This is not the case
with the system used in Figure 8.25 because it has been in use only for a
short period.
You should know the typical values for your own system. Deviations may
occur when maintenance work is carried out on the system. Usually, you as
an administrator will carry out this work or at least will be involved. In this
respect, you will develop a feeling for the behavior of your system.
Deviations in the normal operation, however, must be checked thoroughly.

The goal is to locate the cause of the problem, which might be related to the
use of an inefficient SQL statement. The analysis of the SQL requests may be
an option to identify this type of statement. In Transaction ST04, you can
access a sorted list of all SQL statements that are stored in the Oracle system
by selecting Detail Analysis SQL Request. If you want to select the requests
of a specific user in the selection screen, note that only the UNIX users of the
SAP system are referred to here, but not the SAP users that are unknown to
the Oracle system. As of SAP Basis 8.1, the CLIENT_ID will be available,
which corresponds to the SAP end user.
Figure 8.26 Analysis of the Shared Cursor Cache
The top SQL statements should be primarily analyzed in terms of disk reads,
buffer gets, and elapsed time. The read access per execution (bgets/exec) and
the read access per record (bgets/row) provide important information. They
are important when performing a thorough and individual analysis of an SQL
statement. bgets/exec can be critical if a request carries out many buffer
accesses that may be redundant, as shown earlier in the example in Figure
4 Apart from custom developments, patches, and upgrades, the database structures generally
remain stable.
479
8 Performance
8.21. If the value for gbets/row is high, many blocks must be read from the
database to deliver a small number of data sets to the application server. In
this context, the database access is possibly performed without using appro-
priate indexes. However, complex joins and several inlists can entail high val-
ues for bgets/exec without having the technical potential for optimization.
8.3.4 Detailed Analysis of SQL Statements

Furthermore, you can view the execution plan for individual SQL queries
(Ctrl-Shift-F6). The query plan for the query
SELECT * FROM SBOOK

INTO WA
WHERE PASSNAME = Cindy Lindworm
provides the execution plan shown in Figure 8.27. Because no appropriate

index is available, the entire table is accessed using the primary index.
Figure 8.27 Execution Plan for an SQL Query
The ABAP Dictionary (Transaction SE11; see Figure 8.28) provides more
information about the available indexes. No index is defined for the SBOOK
table via the PASSNAME attribute queried by the where clause.
480
Figure 8.28 Transaction SE11 for Displaying Available Secondary Indexes
8.3.4.1 Analyzing Programs

An alternative approach is to examine the programs that produce high loads
or send SQL statements causing these loads. The ABAP Dictionary provides
the Where used function to enable the identification of programs that use
the table. The list of found programs can be very long. In that case, the use of
the shared SQL analysis table is more appropriate (see Figure 8.26), which
also contains the name of the program that submitted the listed SQL state-
ment first.
Starting from here, a runtime analysis of the program can be carried out (use
Transaction SE30 or follow menu path Tools ABAP Workbench Test
Runtime Analysis). In the first step, the program to be examined must be
executed. In the second step, you can view the evaluation (see Figure 8.29).
481
8 Performance
Figure 8.29 Initial Screen of the Runtime Analysis
Figure 8.30 shows the results of the bad SQL statement used at the begin-
ning including the use of the IF condition. With a total execution time of
three seconds, the program has already slowed down noticeably. Most of the
time is assigned to the database system that has to transfer about 900,000
records of the SBOOK table to the application server. From the point of view
of the architecture, this is absolutely necessary because the buffering of the
SBOOK table is not permitted.
In contrast, the program, which has been optimized by shifting the name
verification into the where condition, shows a runtime behavior that has
been improved by a factor of >10 (see Figure 8.31). Whereas the processing
with IF requires approximately 3 seconds, the variant using WHERE only
takes 0.2 seconds. On the one hand, the load in the database system is lower,
because only a few records needed to be delivered. On the other hand, the
application server is also less stressed, because it has to perform the SELECT
loop only a few times.
482
Figure 8.30 Runtime of a Bad Statement
Figure 8.31 Runtime of a Better Statement
Note that the runtime measurements do not always provide the same results.
For buffered tables, the initial execution can involve a high database load,
whereas the second execution can leverage the data from the buffer of the
application server, and the query does not access the Oracle database. The
Oracle buffers behave in a similar manner. Consequently, the Oracle data
buffer also buffers tables for which no buffering (in the application server) is
allowed from the point of view of the SAP system. In this respect, it is not
always easy to construct clear examples. The actual system behavior also has
an impact on the measurements:
Other transactions generate loads and require CPU time and data transfer
volume.
483
8 Performance
Other SQL queries displace data from the buffers to the application server
and Oracle levels.
Other SQL queries already stored the data viewed during the measure-
ment in the buffers at both levels (which is actually positive in terms of the
overall performance).
Thus, the runtime analysis is helpful, especially for addressing a specific

problem.
You can use the SQL Trace (Transaction ST05 or menu path Tools ABAP
Workbench Test SQL Trace) to perform a very detailed examination of a
single program. Here, the database interface on the side of the SAP system
logs the processing steps for SQL queries in detail. This includes the opera-
tions provided by the record-oriented interface of the five-layer architecture
(see Chapter 3). Figure 8.32 shows the large number of fetch operations that
occur with an unspecific SQL query against the SBOOK table.
Figure 8.32 SQL Trace: High Number of Fetch Operations
484
An optimized statement that performs the selection using the where clause
needs only one fetch for the few Cindy Lindworm records. However, this
fetch runs relatively long (see Figure 8.33).
Note that when you use an SQL trace, all SQL queries are logged if no filters
are used for specific tables, users, or work process numbers. The result can
thus be falsified by the side effects of other transactions. Therefore, you
should use the filtering options. Tracing processes are time-consuming and
may falsify the result.
Figure 8.33 SQL Access with the Where Clause
The large number of logged operations can be reduced to a more manageable

amount by defining suitable restrictions during the preselection. It is useful
to specify the SAP user (FHB-00 in the example), because the SAP application
server logs the operations of the database layer.
You can also view the execution plan for individual SQL statements from
within the SQL trace (Explain F9). In addition, you can change and test SQL
statements on a trial basis (Ctrl-F6).
We have now briefly discussed some of the main approaches for detecting
and analyzing problematic SQL queries. Lets take a closer look at the solu-
tion to these problems.
8.3.5 Prevention: The Silver Bullet

The best way to avoid problematic SQL requests is prevention. The SAP sys-
tem itself offers numerous good and bad examples via the Tips and Tricks
function of the runtime analysis (Transaction SE30) or through the ABAP
Workbench (Transaction SE80 or Environment Examples Performance
Examples). Based on small code examples, you can become familiar with the
alternative programming methods and can start measuring the runtime right
485
8 Performance
away. The examples illustrate typical programming errors including their

effects and troubleshooting options (see Figure 8.34). As a prerequisite, you
need the flight data model in each case.
Figure 8.34 Performance Examples with Runtime Measurement
The examples also illustrate the five golden rules of high-performing SQL pro-
gramming (see Schneider, Thomas: SAP Performance Optimization, SAP
PRESS, 2005), which we will describe briefly here:
1. The number of data records to be transferred between the DBMS and SAP
application server must be kept as small as possible. The impact of non-
specific SQL requests that transport large amounts of data has been dis-
cussed several times in this section. In addition, multiple reads of identical
data by a program can cause large data volumes. You can detect such
behavior in the SQL Trace using the Display Identical Selects function
(Ctrl-Shift-F8).
2. The transported data volume must be kept as small as possible. In addition
to Rule 1, you must ensure that no complete data records are transferred,
that is, you should avoid using select *. Furthermore, for typical calcu-
lations, such as calculating averages or totals, you should use the aggregate
functions of the Oracle system (see the example in Figure 8.34).
3. The number of transfers between the Oracle database and the application
server should also be kept small. Consequently, it is better to use a few
SQL requests with large5 return quantities than many SQL requests with
5 Of course, taking into account Rules 1 and 2.
486
very small return quantities. On one hand, this is because processing SQL
requests involves a certain overhead. On the other hand, data packages
that contain small amounts of data and have a constant size on the network
unnecessarily consume resources. With many small selects, the trans-
ferred gross quantity of data can be a multiple of the net quantity of data.
If you succeed in determining all necessary data with a single SQL request,
waste occurs only in the last package, which is probably not completely
filled with data.
For this reason, you should avoid using SQL requests with nested loops. In
current SAP versions, join operators are available for this purpose. In
older SAP versions (older than 4.0), joins could be emulated by defining
views. The alternative and intuitive way of formulating joins as nested
loops can still be found in older developments and offers some potential
for optimization.
4. You can keep the overhead for processing the request small by using a
where clause that corresponds to the existing indexes. For this purpose,
the where condition should be simple, that is, it should consist of AND
links as far as possible. AND constrains the search area, whereas OR extends
it. When formulating a request, it is often possible to transfer OR condi-
tions into AND conditions.6 We'll describe the use of the correct index in
this context in greater detail later in this chapter.
5. It can be useful to shift the load more toward the application server. The
architecture of the SAP system can best be scaled at the level of the appli-
cation server. It is possible to use a larger number of instances to increase
the computing performance at that level. However, most SAP installations
use only a single integrating database server (see Chapter 2, SAP Funda-
mentals). For operations such as sorting or grouping, which can be per-
formed equally at the database server and application server levels, a shift
toward the application server is advisable. Here, you can also use particu-
larly efficient algorithms internally, such as a sorting algorithm. Of course,
you need to take Rules 1 to 3 into account in this context, that is, this pro-
cedure only makes sense if the entire data quantity considered is used in
the application.
Whereas Rules 1 through 4 are universally valid to a large extent, Rule 5

sometimes doesnt make sense or may even be counter-productive in your
6 Procedures for the conversion into disjunctive normal form and conjunctive normal form
(by negation) are described in the algorithm literature, for example the application of De
Morgan or the Quine-McCluskey procedure.
487
8 Performance
specific installation. If the application level and database level are located on
the same server computer, this would cause a load on the same CPUs, irre-
spective of the execution level. If the database server computer is compara-
bly overdimensioned, an execution in the database layer would certainly
have some advantages. In this context, it is essential to understand the
underlying mechanisms to obtain a working solution.
8.3.6 Indexes for Faster Access

Indexes support the quick access to specific data in tables. Take this book as
an example: Without a doubt, you can find the key word Runtime analysis by
reading all pages of the book in their entirety. However, this kind of search
is pretty time-consuming. Instead, you may want to use the index at the end
of the book where thanks to the alphabetic sorting you will only need
to search one or two pages more closely to find the reference to the correct
page. Ultimately, you will only have to read 3 to 5 pages instead of approxi-
mately 800 pages (in the worst case).
For tables, you can create several indexes, which connect single attributes or
combinations of attributes. First, there is always the primary index that con-
tains the key attributes. It can be mapped by means of a sorted storage of the
data records. Other indexes are referred to as secondary indexes. These sec-
ondary indexes store the combinations of attribute values with references to
the associated data records in additional memory pages. The organization
can occur, for example, as a B-tree, via hash procedures, or as a bitmap
index.
An important aspect in this context is that an index has a highly efficient

selection function, which means it can help you significantly reduce the
number of accesses to the data record blocks. Furthermore, the index should
be necessary, that is, the attribute combination should be frequently used by
application programs in the where clause. Moreover, it is useful if an index
consumes significantly fewer memory pages than the data, that is, it is com-
posed of only a few attributes.
In addition to the memory consumption, the maintenance of indexes costs

time as well. Insert, change, and delete operations require the adjustment of
associated indexes. If many indexes exist, this can considerably slow down
the system operation. For this reason, you should act with special care when
creating indexes. The creation of unnecessary indexes should be avoided.
488
8.3.6.1 Using Indexes

In the following text, we will demonstrate the effectiveness of indexes and
optimization options on the basis of some examples. Figure 8.27 showed the
execution plan for a selection by passenger names. In this context, the pri-
mary index is used, it doesn't provide any advantage. Actually, 277 seems to
be a pretty high value for the costs.7 However, the passenger name can be
replaced with the customer ID, because the sample data contains a customer
ID for every passenger name and vice versa. If you modify the request by
performing an additional selection via the CUSTOMID attribute, the Oracle
Optimizer leverages the secondary index via the CUSTOMID attribute (see Fig-
ure 8.28). This is shown in the plan in Figure 8.35, which now displays an
estimated cost of 3.
Figure 8.35 Plan with Additional CUSTOMID
Note that by taking into account existing indexes, you can often accelerate
the program execution considerably. In this context, you should particularly
consider attributes that do not change from the perspective of a specific
7 The costs (Estim. Costs, Estim. Rows) are relative values, which support the comparison of
alternative plans. See also SAP Note 766349.
489
8 Performance
application and are therefore often ignored when a request is formulated.

Important examples in this context include the MANDT and BUKRS attributes.
Sometimes these attributes must be added to the where clause to make the
optimizer use a specific index.8
8.3.6.2 Creating Indexes

It may become necessary to create an additional index. However, note that
this is a substantial intervention in the system. You should only create
indexes for tables of the standard SAP system after consulting the SAP Notes
and by taking into account the above considerations. Also, note that creating
an index takes some time, and the affected table may be locked for that time.
For this reason, you should create the index at times of low usage. Because
indexes consume additional resources, you should check the effectiveness of
the index. In general, however, the creation or deletion of secondary indexes
does not affect the functionality of the system, if they are not unique
indexes.
The following example shows how an index is created and becomes effec-
tive. As changes to the structure of Table SBOOK are not allowed without an
access key, we'll first create a copy of that table. To do that, you must start
Transaction SE11 and click on the Copy icon. This creates an empty table,
ZBOOK, which has the same structure as SBOOK including the secondary
indexes.
The following small ABAP program allows you to copy all data records from
SBOOK to ZBOOK:
REPORT zh_sbook_copy.
DATA booking LIKE sbook.
SELECT * FROM sbook INTO booking.
insert into zsbook values booking.
ENDSELECT.
For Table ZBOOK, the runtime analysis shows the values measured in Figure
8.36 for a selection by "Cindy Lindworm." No index is used here, because no
suitable index exists for that passenger name. Compared to a selection using
Table SBOOK, the values shown here are slightly different. One possible rea-
son might be the different physical characteristics of the two tables.
8 See Schneider, T.: SAP Performance Optimization. SAP PRESS 2005.
490
Figure 8.36 Runtime Analysis Without Index
As shown in Figure 8.37, you can use Transaction SE11 to create an index,
PNI, for the PASSNAME attribute.
Figure 8.37 Creating the PNI Index for PASSNAME
When measuring the unchanged program, the new results are significantly
better, as shown in Figure 8.38. The processing in the application server
requires a similar amount of time. The number of packages transported
between the database and application server is the same. However, in the
example, processing in Oracle is 250 times faster due to the use of the index.
491
8 Performance
Figure 8.38 Runtime Analysis with Index
The display of the selected plan (see Figure 8.39) shows the use of index
PZBOOK-PNI via the PASSNAME attribute, as expected.
Figure 8.39 Execution Plan with PASSNAME Index
Note that this is an ideal-world example. It makes more sense to perform a

selection not via the passenger name, but via the equivalent customer ID. It
is very likely that other mechanisms of SAP or Oracle become effective in
similar cases to achieve very short response times, even if the formulation of
the request is not made in the best possible way. The powerful statistical
optimizer from Oracle implements numerous methods and heuristics, which
frequently (but not always) enable an ideal plan creation. In addition, the
optimizer is enhanced continuously.
8.4 Summary
Performance is a far-ranging and complex topic. Hardware operating
system Oracle SAP system: Nobody knows everything. The intention of
this chapter was to provide an overall picture to enable you to handle per-
492
Summary 8.4
formance problems. Whenever possible, you should try to consult an expert

on the topic you need help with.
Avoid making snap decisions. Performance is important, but isnt every-

thing. Data security is always the top priority. If the system halts, immediate
action is required, but this situation should generally happen very rarely. Try
to adhere to the small or big optimization cycle: Problem analysis
troubleshooting verification.
Performance-critical SQL statements may significantly affect the overall sys-

tem and are often caused by the primarily function-oriented programming
style of the application developers. Prevention helps here as well as training
the developers with regard to a high-performance programming style. You
can often improve problematic SQL statements in collaboration with the
development team. In this chapter, we demonstrated ways to recognize and
precisely analyze this type of statement. Another option, which, however,
should be the responsibility of an experienced Oracle administrator, is to
create additional secondary indexes. If you identify a problem with an orig-
inal SAP program, youre probably not the first to encounter that problem.
In this case, the SAP Service Marketplace or the SAP Notes often contain
essential information for problem handling, either by means of a correction
or a workaround.
493
Index
28-day backup cycle 651 Application data 282

64 bit 252 Application layer 35
64 bits Application server 35, 37
database 253 memory structure 39
hardware 253 process 38
operating system 253 Application Service Provider 245
software 252 ARCH 90
Archive directory 134, 135
ArchiveLog mode 553
A activate 555
online data backup 564
ABAP 33 Archiver directory 93, 555, 601, 665
buffer 40, 43 change 559
Dictionary 59, 477 Archiver process 553
dump 530 Archiver stuck 508, 556, 592, 600
framework 33, 57 avoidance 558
processor 43 Archiving 90, 93, 391
report 34 ARIS 745
Support Package 365 AS SYSDBA 85
Workbench 278, 313 ASCII terminal 29
ABC analysis 66 Assignment of privileges 134
abort 133 Asynchronous I/O 447
Access Asynchronous update 48
control 68 Attribute 71, 747
costs 455 Authentication 121
path 80 Authorization model 141
access autoallocate 106
algorithm 128 autoextend 103
ACID properties 47, 69, 135 Auto-join 78
action log 336 Automatic Segment Space Management
Add-On Installation Tool 365, 374 (ASSM) 106, 440
Admin option 143 Automatic Shared Memory Management
Administrator Workbench 743 (ASMM) 419
After-image 550, 566 Automatic Storage Management (ASM)
Aggregate function 78 82, 97, 736
Aggregation 728 Automatic Tuning Optimizer (ATO) 126
Ajax 119 Availability 37, 264
ALERT log 136, 570
Algebraic optimization 128
all_ 101 B
ALL_ROWS 133
alter table 73, 75 Background job 355, 538
Analytic workspace 738, 741 BACKINT 587, 600
Analyze command 132 disaster recovery 616
801
Index
Oracle Recovery Manager 624 Blade technology 37

split mirror database 664 Block boundary 118
volume management 619 Block split 567
Backup 112, 133, 257, 354 Bootstrap 682, 690
cumulative incremental 139 BR*Tools 54, 89, 140, 216, 368, 543,
cycle 618, 650 579
development and test system 655 attended versus unattended mode 221
device 617 BRCONNECT 227
differential incremental 139 BRGUI 226
domain controller 299 BRSPACE 230, 237
incremental 138, 585, 653 BRTOOLS (frontend) 224
level 138 configuration file 220
library 624, 627 general options 220
log 510 information 219
mechanism 70 log files 222
offline 138, 391, 562 operating system user 219
online 138, 564, 647 Oracle Recovery Manager 623, 627
partial tablespace 655 SAPDBA 217
scheduler 140 standby database 659
size 510 temporary tablespace 614
speed 652 Windows environment 616
split-mirror 258 BRARCHIVE 580, 592
strategy 257, 649 backup device 599
technology 138 backup mode 593
time 70, 510 compression 598
two-phase 657 continual backup 595
Backup library database object 592
external 629 detailed log file 598
Backup, incremental 625 Oracle Recovery Manager 623
BAPI (Business Application Programming split mirror database 663
Interface) 60 standby database 600, 659
Basic operations 67 summary log file 595
Basis extent 103 tape drive 594
Batch input sessions 46 unattended 599
Batch mode 32 verification 600
Batch work process 39, 45 volume 619
bdump 136 BRBACKUP 580, 582
Before-image 566 backup device 587
BEx Analyzer 760 backup mode 585
BEx Map 763 backup type 586
BI 723 compression 588
BIGFILE 106 database object 584
Bitmap 105 Oracle Recovery Manager 623
Index 488 parallelization 588
index 737 split mirror database 663
join index 737 split scenario 664
tablespace 105 standby database 659
Blade server 37 unattended 590
802
Index
user 590 C
verification 591
volume 619 Cached I/O 447
volume management 589 Calendar 465
BRCONNECT 544 Cardinality, high 752
BRRECOVER 580, 606 Catalog 67
backup device 608 Central instance 49
detailed log file 611 Change and Transport System (CTS) 288
disaster recovery 614 Change Management Service (CMS) 706
parallelization 608 Change request 293
post-processing 612 local 308
recovery scenario 609 release 320
structural change 610 Characteristic 750
summary log file 611 Check table 68
unattended 609 Checkpoint 93, 113, 550
user 610 Checkpoint not complete 438
BRRESTORE 580, 602 Checkpointer 90, 93, 551
backup device 602 Classical star configuration 746
compression 604 Classification 733
data file 603 Client 35, 280, 357
detailed log file 605 administration 291
parallelization 604 background job 363
redo log file 603 export 360
restore mode 602 import 360
summary log file 606 initial storage space 360
unattended 604 maintenance 285
user 604 new creation 362
verification 605 number 280
volume 604 role 285
BRTOOLS 555, 580 transport 358
BSP (Business Server Page) 60 Client copy 35, 355, 358
B-tree index 768 copy log 362
Buffer 536 copy profile 358
administration 472 local 359
management 81 remote 359
quality 417 test run 362
synchronization 468 very large production clients 363
Buffer busy wait 440 Client library 83
Bug fix 378 Client process 121
Business Add-In 279 Client/server system 29
Business Application Programming Inter- Climate control 245, 246, 247
face (BAPI) 60 Clustering 733
Business Content 744 Codd's rules 67
Business Explorer 743, 760 Collector job 538
Business Intelligence 30, 723 COMMIT 95
Business process 28 fast 93
Business Server Pages (BSP) 60, 261 Communication hardware 415
Business transaction 32 Communication SAP Oracle 163
Busy wait time 417, 421, 430 Communication structure 754
803
Index
Company code 35 D
Compliance 29
Component Data
Build Service 706 backup 133
development 708 backup method 561
partitioning 737 buffer 117, 418, 421, 423
software 708 cleaning up 727
Support Package 365 cleansing 727, 741
Component model 707 cube 728
Compression 618 definition language 74
BRARCHIVE 598 Dictionary 80, 117, 127
BRBACKUP 588 element 59
compute_statistics 130 export 561
Concurrent I/O 447 file 134, 321, 547
Config Tool 673 flow 758
Configuration file 111 integration 733
Configuration with one machine 36 manipulation language 74
CONNECT 143 mart 730, 734, 738
Connecting SAP and Oracle 158 mining 724, 732
Connection pooling 124 model 71, 745
Consistency modeling 71
archive log 514 package 475
spooler 541 protection 140
TemSe 541 pump 725, 735
Consolidation route 303 quality 741
Consolidation system 303 security 54, 66, 70, 133, 135
Constraint 82 storage 36
Control file 109, 111, 136, 321, 551, target 742
630, 631 type 59, 74
Copy profile 358 warehouse 724
Correction 316 Warehousing Workbench 743
Cost-Based Optimizer (CBO) 455, 516 Data basis, initial 34
Cost-based selection 128 Data buffer 420
count 77 Data class (database object) 209, 469
CPU 497, 536 Data Definition Language (DDL) 74
time 417, 421, 430 Data Manipulation Language (DML) 74
utilization 412, 413 Database
create any table 142 buffer 549
create index 129 buffer cache 94
create session 142 check 227
create table 73, 75 consistency 511
Critical patch update 382 Creation Assistant (DBCA) 89
CUA 465 export 513
Cube 728, 740, 743 growth 618
Cursor 59 interface 43, 467
Customer namespace 57, 314 layer 36
Customizing 34, 279 lock 452
data 281, 469 management software 17
request 293 object 140, 547, 584, 592
tables 472
804
Index
point-in-time recovery 577 Deployment 57, 675, 709

reorganization 81 Description 732
reset 576 Design process 73
standby 514 Design Time Repository (DTR) 706
statistics 517 Developer key 57, 313
structures 99 Development 279
user 160, 190, 200 class 58
Writer 90, 93 environment 34
writer 549 landscape 134
DataSource 693, 753 object 708
db file parallel read 433 partnership 29
db file scattered read 433 system 284, 655
db file sequential read 433 task 706
DB LUW 47 Deviation analysis 732
DB Reconnect 163 Dialog mode 29
DB reconnect 197 Dialog work process 38, 42, 549
DB release 83 Dicing 732
DB_BLOCK_BUFFERS 419 Dictionary cache 95, 424, 478
DB_CACHE_SIZE 418 Dictionary statistics 516
DBA 143 Dictionary-Managed Tablespace (DTMS)
planning calendar 456, 512, 514 100, 102
views 101 Dimension 728, 744, 750
dba_data_files 100 Dimension ID 747
dba_lmt_free_space 107 Direct hand-off process 121
DBMS_STATS 132, 778 Direct I/O 447
dbms_xplan 129 direct path operation 421, 422, 435
DBSNMP 145 direct path read 422, 434
DBSTATC 457 direct path read temp 434
DBVerify 512 direct path write 422, 434
DBWR 90, 93, 117 direct path write temp 434
DD cache 420 Dirty 93, 118
DDIC user 46 Disaster recovery 607, 614
DDL 74 logging 616
Dedicated server 119 prerequisite 615
process 92, 121 Disk layout 256
Default DISK_ASYNCH_IO 448
password (Oracle) 191 Disp+Work program 38
Default profile 51, 153 Dispatcher 38, 40, 91, 523
Default tablespace 195 queue 40, 526
delete_from 76 Distribution strategy 41
Delivery client 282 DML enqueue (TM) 451
Delivery route 303 DMTS 100, 102, 771
Delta domain link 329
procedure 759 Downtime 388, 400
queue 759 Downtime minimized 390
update 739 dpmon 526
upload 758 Drill-across 732
Demilitarized zone (DMZ) 272 Drill-down 732, 740
Dependency analysis 732 Driver class 687
805
Index
drop any table 142 system tablespace 636

drop table 73, 75 tablespace 574
Dummy file 558, 666 temporary tablespace 637
Duplicates 728 ETL process 726, 734, 742, 752
Dynamization 82 Exception reporting 763
Dynpro 33 Exclusive lock 453
Dynpro processor 42 Exclusive lockwaits 451
Executables 110, 111
Execution plan 116, 129, 473, 480, 485
E Exp/Imp SHM 465
Expert mode 349
EarlyWatch 284 Expert system 126
E-Business 30 explain_plan 129
E-Commerce 30, 138 Export 735
Effects forecast 733 Export/Import 465
Embedding of SQL 34 Extended memory 460, 462
Enqueue 443, 522 Extended SAP star configuration 747
lock 523 dimension ID 747
process 39, 48 surrogate ID 748
replication server 523 Extension semantics 34
service 675 Extent 102
ensmon 522 Extraction 726
Enterprise Core Component (ECC) 31
Enterprise Extension Set (EES) 31
Enterprise Java Beans 60 F
Entity 71
Entity Relationship (ER) Factors
diagram 72 external 546
model 71 Failover 266
schema 72 Fast refresh mechanism 739
Entity relationship (ER) Fat client 35
model 745 Federated database system 727
Environment variable 179 Fiber channel (FC) 257
dbs_ora_schema 179 Field descriptions (FTAB) 465
DIR_LIBRARY 186 File
ORACLE_BASE 179 flat 759
ORACLE_HOME 179 File cache 97
ORACLE_SID 179 File system
SAPDATA_HOME 179, 206 caching mechanism 97
SAPSYSTEMNAME 179 local 96
Ergonomic presentation 29 File system cache 414
Error class 546 File, flat 753
Error scenario 574, 629 FILESYSTEMIO_OPTIONS 448
control file 630, 631 Fire protection 269
offline redo log file 646 Firmware 384
online backup 647 FIRST_ROWS 133
online redo log file 639, 645 Five-layer architecture 79
redo log group 641 Flash backup technology 140
rollback tablespace 636 Flashback interval 140
806
Index
Flashback recovery log 140 High availability 49, 245, 249, 266
Foreign key 73 license 353
Foreign system 753 High-availability system 345
Forwarding Hint 127, 133
direct 755 Hit ratio 417
flexible 756 HP-UX 385
rule 754 HTML 60
Fragmentation 107 Humidity 248
external 107
internal 108
free buffer waits 441 I
Free list 104
Frequent itemset 741 I/O
Frontend 401 analysis 444
Full buffering 467 configuration 777
Full restore and complete recovery 578 load 413, 415, 446, 449
Full table scan 128 modes 447
Full upload 758 performance 97, 117, 446
Idle wait event 429
IDoc (Intermediate Document) 63
G Implementation Guide 278, 309
Import 735
Galaxy configuration 731 Monitor 322
Gateway 528 overview 321
monitor 529 Puffer 326
process 39, 49 queue 321, 326
Generic Request and Message Genera- Index 80, 127, 488
tor 696 range scan 131
Geofeature 764 rebuild 234
Granularity 752 Individual software 27, 278
Grid computing 82 InfoCube 751
group by 78 InfoObject 750
grouping 78, 487, 740, 751 InfoPackage 755
gwmon 529 InfoProvider 742
InfoSet 761
InfoSource 754
H init.ora 96, 111
init.sap 582
Hardware error 546 Initial record 465
Hash insert_into 76
grouping 129 Installation
join 128, 737 phases 341, 344
partitioning 737 planning 110
HASH_AREA_SIZE 425 tools 341
having 78 Instance 36, 49
Heap memory 460, 463 name 50
Heterogeneity 726 number 345
Heuristic 128 profile 51, 153
Hierarchy 747, 750 Instance recovery 551
807
Index
Instant Client 119, 124, 688 jlaunch 682

Integration 28, 67, 725 JNDI 693
Integration Engine 725 Jobs
Integration platform 36 log 539
Integration system 303 Join 74, 77, 480
Integrity check 68 JPREPARE 393
Integrity rules 74
Interactive Query Language (IQL) 74
Interface 681 K
Interim patch 378, 386
critical patch update 382 Keep pool 423
installation 382 Kernel 534
merge patch 378 Kernel parameter 96
MOPatch 384 Key 73
OPatch 381 Key figure 728, 750, 762
Intermediate Document (IDoc) 63
Internet Communication Manager (ICM)
31, 670 L
Internet Graphics Service 367
Internet Transaction Server (ITS) 61, 259 Large database
integrated 260 backup 652
IPC 120, 123 Large objects 106
IQL 74 Large pool 418
ISO/OSI-seven-layer model 120 LARGE_POOL_SIZE 418
isqlplus 86, 87 Latch 443
latch free 442
LCK 90
J LDAP 140
Least Recently Used (LRU) 93
J2EE Least recently used block 550
cluster 678 Legal regulations 29, 133
engine 673 Level 0 backup 624
server 672 Level 1 backup 625
specification 672 lgtst 521
Java 60 LGWR 90, 93, 117, 118, 140
cluster 670 Library 681
Connector 63, 670 Library cache 423
pool 418 Lifecycle 364
schema 686 like 77
stack 670 Line item 752
Startup and Control Framework 682, Linux 385
716 List partitioniung 737
Support Package 366 Listener 121
Support Package Manager 365, 709 listener.ora 122
Upgrade program 709 LISTSCHEMA 760
upgrade program 714 Load balancing 49
JAVA_POOL_SIZE 418 Load distribution 124
JCMon 684, 700 Loading 728
jcontrol 682 LOB 106
JDBC driver 686 Local client copy 359
808
Index
Locally Managed Tablespace (LMTS) Match 741

100, 105, 771, 776 Matching, fuzzy 77
Location of problems 407 Materialized View 738
Lock conflict 515 max 78
Lock management 48, 81 MaxDB 70
Lock table 48 MCP 735
Log 165 Media error 70, 133, 135
Listener log 165 Memory 414, 497
listener log 166 areas 459, 461
Listener trace 165 configuration 459
Oracle alert log 165 gap 94
SAP work process trace 165 monitor 461
session traces 165 physical 535
start and stop logs Oracle 165 swap 535
start and stop logs SAP 165 Merge 739, 741
log buffer space 427, 436 Merge patch 378
Log file 69, 70 Message server 39, 518, 675, 677
log file parallel write 436 process 49
log file switch completion 437 Metadata 735
log file sync 436 Metadata Repository 745
Log Sequence Number 552, 560 Metric 127
Log sequence number 114 Microsoft Management Console 153
Log switch 113, 114 Mirroring 97, 736
Log writer 93, 135, 550 Mirror-Log 110
LOG_DIRECTORY_LISTENER 122 MMAN 91
LOG_FILE_LISTENER 122 MMON 91
Logical optimization 128 Model concept 740
Logical read 417 Model View Controller concept (MVC)
Logical Unit of Work (LUW) 41, 47, 69 61, 672
Logical Volume Manager (LVM) 97, 446 Model-View-Controller 261
Login process 192 Modification 279
Login shell 179 Modification adjustment 400
Logon group 521 MOLAP 731, 738
Log-Writer 90 Monitoring 495, 695
Long-term storage 651 category 498
LRU algorithm 93 external 501
LRU procedure 95 hardware 496
lsnrctl 125 load caused by 500
network 498
performance 533
M Service 696
software 498
Main memory 134 table buffering 469
Mainframe 29 MOPatch 384
Maintenance Optimizer 366 msmon 519
Maintenance release 84 msprot 518
Maintenance window 264 MultiCube 761
Mass deletion 108 Multidimensional data model 728
Master controller process 735 Multidimensional Entity Relationship
Master data 469, 747, 750, 755 model 745
809
Index
Multidimensional OLAP 731 Offline

Multidimensionality 743 backup 138
Multi-pass operation 426 data backup 562
Multiple Components in One Database redo logs 444
(MCOD) 202, 347, 577, 603, 610 OLAP (Online Analytical Processing) 723
Multiple storage 67 cube 728
Multitable insert 740 engine 734
Multiuser mode 65 processor 725
OLTP (Online Transaction Processing)
89, 723
N Online
backup 138
Named pipes 120 data backup 564, 647
Native SQL 43 transaction processing 723
Net Manager 124 OPatch 381
netca 124 open resetlogs 612
netmgr 124 Open SQL 43, 57, 71
NetWeaver Operating modes 553
Developer Studio 669, 705 Operating system 173, 549
Development Infrastructure 669, 673, kernel 160, 174
705 maintenance 384
Network 413 monitor 410, 411, 445
Network performance 164 patch 174
Next extent 103 user 177, 194, 199
NoArchiveLog mode 554 Operating system group 178, 193
offline data backup 563 dba 178
Non-Unicode system 301, 337 oper 178
Normalization 73 sapsys 178
Note Assistant 375 Operating system-specific environment
Note correction 364 variable 183, 187
NTBACKUP 616 Operation 67
NULL value 76 Operation error 133
Operation mode switching 45
Operation type 355
O OPI 120
OPS$ process 194, 195, 196
Object assignment (database object) 209 Optimal execution 425
Object directory entry 316 Optimal work area size 425
copy 316 Optimization cycle 406
original 316 Optimization, internal 128
Object Navigator 59 Optimizer 92, 116, 126, 473
Object parameter (database object) 209 Optimizer hint 132
Object privilege 141 Optimizer statistics 456
Object-oriented concept 33, 82 OPTIMIZER_MODE 455
Object-relational database 82 Oracle
OCI 120 availability 503
OCSS process 97 block 567
ODM connector 779 Business Intelligence Discoverer 741
ODS object 751 Call Interface (OCI) 120
810
Index
CBO statistics 229 DB_FILE_MULTIBLOCK_READ_COUNT

character set 182 772
client 180 FILESYSTEMIO_OPTIONS 772
Client 9i 181 HASH_AREA_SIZE 775
Cluster Synchronization Service (OCSS) PARALLEL_MAX_SERVER 772
97 PGA_AGGREGATE_TARGET 774
control file 255 PGA_MAX_SIZE 774
data buffer 464 SORT_AREA_SIZE 775
data file 255 WORKAREA_SIZE_POLICY 774
Data Migration Assistant 402 Oracle parameters
Data Miner 742, 779 administration 237
Databank Optimizer 455 dynamic versus static 236
database monitor 419 Oracle process 90, 151, 157, 549
Database Upgrade Assistant 402 ARC process 151
E-Business Suite 17 CKPT 151
Enqueues 451 DB work process 151
Enterprise Manager 87 DB Writer 151
installation 548 LGWR 151
Instant Client (10g) 183 listener 151
job 543 PMON 151
latch 443 RECO 151
Listener 156, 160, 164, 200, 503, 689 SMON 151
lock mode 452 Oracle Recovery Manager 585, 622, 653
lock monitor 452 backup library 627
Managed Files (OMF) 97 BR*Tools 627
mirror log 255 external backup library 629
Optimizer 489, 739 features 622
parameter 160, 236, 416 level 0 backup 624
Program Interface (OPI) 120 level 1 backup 625
Real Application Cluster (RAC) 37, 125 SAP backup library 628
redo log 255 use 624
schema ID 205 Oracle upgrade 402
server process 92 tools 402
Session Monitor 161, 432 ORACLE_HOME 84
shadow process 159 ORACLE_SID 84
statistics 778 OracleBI
storage areas 771 Beans 742
stream 734 Discoverer 741
system ID 177 Spreadsheet 741
version number 84 Oracle-Parameter
wait events 427 PARALLEL_EXECUTION_MESSAGE_SIZ
Warehouse Builder 741 E 772
Oracle Net 89 Oracle-Upgrade
architecture 120 paths 402
Configuration Assistant 89, 124 ORADEBUG 167
Manager 89 order by 78
Services 184 Orig-Log 110
Oracle parameter OSS Note 34
CREATE_BITMAP_AREA_SIZE 775 OTR (Online Text Repository) 465
DB_CACHE_ADVICE 423
811
Index
P Prevention 485
Primary index 480
Package 58, 304 Primary key 73, 75
$TMP 308 Privilege (Oracle) 197, 201
Paging 412, 415 Process 90, 150
Paging memory 460 Process After Input (PAI) 42
PAI area (Process After Input) 42 Process Before Output (PBO) 43
Parallel processes 361 Process overview 523
Parallel query 436 Product Availability Matrix (PAM) 386
Parameter file 96 Production key 748
Parameter maintenance (Oracle) 235 Profile file 50
Parameterization file 547 Program Global Area (PGA) 94, 425, 771
Partial backup 655 Administration 777
Partial Restore and Complete Recovery administration 776
575 management 425
Partitioning 737 Project IMG 310
Password 140 CTS function 310
Password change (Oracle) 191 view 311
Patchset 378, 386 Projection 128
installation 379 Projection list 77
prerequisite 379 Protocol Support 120
PBO area (Process Before Output) 43 protocol.ora 122
pctfree 104, 441 PSAPTEMP 422, 425, 444, 777
pctincrease 104 PSPO 91
pctused 104 Punchcard stacks 29
Performance 55, 70, 126, 475
Performance problems 407
administrative 407 Q
analysis 408
program-based 407 QA approval procedure 309
user-specific 407 Quality assurance system 284
Persistent Staging Area (PSA) 755 Query 763
Personalization 279 definition 761
PGA_AGGREGATE_TARGET 425 Designer 761
Physical read 417 language 76
Pivoting 732 processing 92
Planning phase 244 rewrite 739
PMON 90, 121 Quick Sizer 250
Point-in-time recovery 577, 610
Post installation 352
Power supply 248 R
uninterruptable 247
Preliminary correction 317 R/2 system 49
PREPARE 393, 395, 396 R3trans 153, 290, 323, 333
log file 398 RAC 125
modules 397 RAID 81, 97, 110, 133, 135, 254
Prepare 116 Range partitioning 737
Prerequisites Check 346 Raw device 97, 207, 448
Presentation layer 35 Raw I/O 447
812
Index
rdbms ipc reply 441 Reorganization 211, 233

RDDIMPDP 323 online versus offline 212
read by other session 440 performance 215
Read-only tablespaces 102 process 214
Real Application Cluster (RAC) 36, 125 tools 213
Recipient system 303 Repair 316
RECO 90 Repair flag 316
Reconnect mechanism 562 Reporting 760
Recording changes 291 Repository data 282
client-dependent 291 Request ID 751
client-independent 292 Request optimization 126
Recovery 112, 133, 135, 140, 569 Request processing 126
catalog 623 RESOURCE 143
Manager (RMAN) 89, 138, 140 Resource minimized 390
training 133 Response time 537
writer 140 Restore 112, 569
Recovery method 568 RFC 62
offline data backup 572 interface 62
online data backup 573 server group 361
Recovery scenario 571, 606 RKWR 91, 140
Recycling pool 423 RMAN (Oracle Recovery Manager) 55,
redirect process 121 89, 140, 735
Redo buffer 418, 437 ROLAP 730, 738
Redo information 114, 140 Role 141
Redo log buffer 93, 114, 118, 550 Roll buffer 40
Redo log file 93, 110, 112, 118, 134, Roll memory 459
364, 438, 551, 565, 592, 650 Roll-up 732, 740
backup 557, 593 Rule-Based Optimizer (RBO) 455
loss 639, 645 runInstaller 344
loss, offline 646 Runtime analysis 481, 488
offline 554 Runtime measurement 483
online 551
split mirror database 663
status 552 S
Redo log group 113, 552, 641
Redo log switch 552 SAP
Redundancy 67, 73 application server 67
Redundancy-free storage 36 backup library 624, 628
Referential integrity 68 backup object 548
Relation 73 buffer 459, 463, 465
Relational database management system data file names 206
36 database library 186, 188
Relational OLAP 730 directory structure 174, 206
Relationship 71 LUW (logical unit of work) 47
Release upgrade 243 memory monitor 465, 469
Remote copy 359 NetWeaver 30
RemoteCube 753, 761 NetWeaver Developer Studio 60
Renaming 77 NetWeaver Portal 262
813
Index
Notes 34, 375 SAP NetWeaver Exchange Infrastructure

original client 283 (XI) 669
OS Collector 153, 173, 410 SAP patch 364
port 274 kernel files 367
process overview 414 SAP process 150, 154
Reference Implementation Guide (SAP gateway process 150
Reference IMG) 309 Internet Communication Manager 150
SAP up 393, 395, 399 message server 150
SCM 29, 765 process ID 154
SCM namespace 767 sapstart 150
Service Marketplace 34, 57 syslog collector 150
Software Change Registration 314 syslog sender 150
software development 56 work process 150
Solution Manager 501 SAP R/3 table buffer 39
Solution Manager Key 349 SAPCPE 368
standard 278 SAPDBA 544, 579
Start Service 153 SAPinst 341
system ID 177 GUI 342
system log 529 log file 351
system startup 152 SAPJup 393, 395
tablespace 202, 232 SAPOS Collector 531
Transport Tool 53 SAProuter 273, 343
user 46 SAPS 250, 405
work process 92 Scalability 36
work process overview 168 Scalable architecture 29
SAP authorization role (Oracle) 197 Scheduler 757
SAPCONN 198 Screen 465
SAPDBA role 198 SDP (Session Description Protocol) 120
SAP Basis 32 SDU (session data unit) 123
SAP Easy Access Menu 33 Secondary index 116, 488
SAP GUI 35 Secondary memory 134
HTML 259 Secure Store 688
Java 259 Security 199
Windows 259 database 271
SAP instance gap 384
memory area 459 network 272
SAP kernel 32, 185, 188, 367, 386 technical 270
double-stack system 369 Segment 102
Internet Graphics Service 367 Segment shrinking 216
parameters 189 select from 77
SAPCPE 368 Selection 77, 128
update 368 Selectivity 129
SAP NetWeaver 669 Self-tuning 736
SAP NetWeaver Application Server 60 Sequence number 135
SAP NetWeaver BI 30, 49 Server overview1 523
namespace 766 Server process 90, 91, 121
performance 766 Service 681
SAP NetWeaver Business Intelligence Service definition 123
Accelerator 780 Service level agreement (SLA) 264
814
Index
Service Manager 680 IDL 76

Service-oriented architecture 31 Native 692
SGA_TARGET 419 Open 692
Shadow instance 399 optimization 473
Shadow process 44, 92 query 116
Shadow system 390, 392 trace 485
Shape file 764 Vendor 692
Shared SQLA.Reloads/Pin 478
cursor cache 423 SQLNET.EXPIRE_TIME 455
lock 454 sqlnet.ora 122
memory 96 sqlplus 84, 137
memory segment 96 SSL 120
pool 418, 424 Stability 70
server 124 Stack processing 29
server operation 91 Staging 753
server process 92 Staging area 728
SQL pool 95 Standard
SHARED_POOL_SIZE 418, 424 job 46
Shopping cart analysis 741 software 27, 278
Short NTAB 465 transport layer 304
Size category (database object) 209 Standard business software 27
Sizing 246, 250 Standby
Slicing 732 cold 265
SMON 90 database 600, 658
Snapshot 90, 653 hot 265
Snowflake configuration 731 load balancing 267
SNPn 90 Star configuration
Socket 122 classical 746
Software extended 747
Component Archive 366 Star schema 731
Deployment Manager 365, 675, 690, Start profile 51, 153
709 startdb 153, 155, 166
error 546 startsap 153, 166
maintenance 52 Startup problem 159
Solaris 385 Statistic 127
SORT_AREA_SIZE 425 statistic_level 132
Sorting 78, 117, 487 statistics 130
Source system 753 Stop mark 327
Space management 98 stopdb 166, 172
SPFile 96, 111 stopsap 166
Split mirror database 660 Storage 103, 545
Split scenario 664 area network (SAN) 254
SPOF (Single point of failure) 517 array 254
Spool process 39 gap 79
SQL structure 90
*NetV2 119 system 97
area get ratio 478 Streams pool 418
area pin ratio 478 STREAMS_POOL_SIZE 418
cache 423 Striping 97, 736
815
Index
Structural change 610 privilege 141

Subcube 730 process 537
sum 78 R 79
Support Package statistic 516
ABAP 365 stop 171
Component 365 switch upgrade 399
Java 366 System Global Area (SGA) 94, 417
Maintenance Optimizer 366 buffer 415
memory requirements 366 dynamic 419
SAINT 365 memory 415
Software Component Archive 366 System landscape 37
SPAM 365 multisystem landscape 287
Stack 369 three-system landscape 284
system landscape 376 two-system landscape 287
types 365 System, external 331
Support package 29, 53, 364, 386
Support Package Manager
conventional mode 373 T
define queue 371
generation 373 Table
import scenario 372 generic 465
install queue 372 internal 59
system landscape 376 single 465
update 370 statistics 455
Support Package Manager (SPAM) 365, Table buffering
370 generic buffering 467
Support Package Stack (SPS) 369, 713 monitoring 469
Surrogate ID 748 single buffering 467
Swap memory 160, 412 Table definition (TTAB) 465
Swaps 466 Tablespace 564, 655
Sychronization 69 backup mode 564, 567, 647
Synchronous I/O 447 concept 98
SYS 143 dictionary managed 507
SYS tablespace 100 locally managed 506
SYSAUX tablespace 100 loss 574, 631, 636, 637
SYSDBA 100 point-in-time recovery 577
SYSDBA/SYSOPER - Connect 193 reorganization 206
SYSTEM 143 rollback 631
System system 631
analysis 408 temporary 614, 637
catalog 67 Tablespace layout 202, 568
Change Number 551 new layout 204, 212
change option 291 old layout 203
downtime 140, 545 Tablespace type 98, 207
Enqueue 452 changeover 208
error 70, 133, 135 TAF (Transport Application Failover)
file 111 125
Landscape 243 Takeover 658
Monitor 90
816
Index
Tape directory 297, 345

backup 134 domain 299, 329
pool 618 domain controller 299
virtual libraries 258 file 321
Task 295 group 299, 328
Task Handler 44 layer 303, 315
Task type 317 log 336
TCP/IP 120 management system 290
Technical terminology 32 request 58
Tertiary memory 134 route 302
Test system 655 route editor 304
Text 747, 750 step 336
Thin JDBC 119 strategy 319
Three-layer client/server architecture 35 system 134
Time stamp 135 Transport Organizer 288, 318
TIMED_STATISTICS 417 Tools 318
TNS 120, 121 Transportable tablespace 734
TNS_ADMIN 124 TREX 745, 781
tnsnames.ora 111, 122 Trigger 74, 82
Top SQL statements 479 Two Task Common (TTC) 120
Total response time 416 Two-phase data backup 657
tp 290, 301, 323, 333 Two-stage login 196
Trace
application 698
developer 684, 699 U
file 112, 570
JCo 704 Ultra Large Database 734
level 684 UML 71
performance 698 Undo information 140
single activity 699 Unicode 31, 301, 337, 347
SQL 699 Unique index 490
Training system 134 UPD (update process) 47, 48
Transaction 32, 69 UPD2 48
data 469, 755 Update 47, 76
enqueue (TX) 451 error 527
error 133 Update process 39, 527
log 81, 110, 118 type 1 48
management 92 type 2 48
number 33 Upgrade 341, 386, 402
profile 478 assistant 394
Transfer structure 753 downtime 400
Transformation 726 frontend 401
Transformation rules 754 Oracle 402
Transmission rule 754 Prepare 396
Transparent Application Failover (TAF) shadow instance 399
125 strategy 388, 390
Transparent Network Substrate (TNS) system switch 399
120 tools 393
Transparent table 59 UPS 248
Transport URL prefix table 702
817
Index
User 140 Volume 589, 600, 618

User context 44 check 620
User enqueue (UL) 451 initialization 619
User process 537 management 589, 619
user_ 101
user_role_privs 144
user_sys_privs 144 W
user_tab_privs 144
user_tablespaces 99 Wait event 422
analysis 409
classes 428
V structure 428
Waste 98
V$ view 419 Web Dynpro 670
V$DB_CACHE_ADVICE 423 ABAP 262
V$PGA_POOL_ADVICE 423 Java 261
V$SHARED_POOL_ADVICE 424 Web reporting 761
v$controlfile 112 Webservice 31, 61
V$LOCK 452 where 77
V$LOCKED_OBJECT 452 Windows 385
v$log 115 with admin option 142
v$log_history 115 Work area 425
V$PGASTAT 425, 426 Work process
V$SESSION_EVENT 427 components 169
V$SESSION_WAIT 427, 432, 442 multiplexing 40, 41
V$SGASTAT 424 PRIV mode 524
V$SQL_WORKAREA_HISTOGRAM 427 Workbench request 293
v$sysaux_occupants 101 Workload analysis 408, 417
V$SYSTEM_EVENT 427, 430 Workload repository 91
v$tablespace 107 Wrapper 62
V$-View write complete waits 441
V$PGA_TARGET_ADVICE 776
V$PGASTAT 776
V$SQL_WORKAREA_HISTOGRAM X
776
Value help 33 XML 82, 726
Variable 762
VB* tables 48
vi 85 Z
View 68
View expansion 116, 127 Zero Administration Memory Manage-
Virtual host name 342 ment 459
Virtual system 306 ZZZ_myindex_123 439
Virtual view 101 ZZZ_myindex_20 413
Visual Administrator 693 ZZZ_myindex_404 127
ZZZAdd-on 366, 374
ZZZASM instance 97
818

SAP Database Administration With Oracle: Michael Höding, André Faustmann, Gunnar Klein, Ronny Zimmermann

Uploaded by

Copyright:

Available Formats

SAP Database Administration With Oracle: Michael Höding, André Faustmann, Gunnar Klein, Ronny Zimmermann

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SAP Database Administration With Oracle: Michael Höding, André Faustmann, Gunnar Klein, Ronny Zimmermann

Uploaded by

Copyright:

Available Formats

Michael Hding, Andr Faustmann,

Gunnar Klein, Ronny Zimmermann

SAP Database Administration with Oracle

2 SAP Fundamentals .............................................................. 27

3 Oracle Fundamentals .......................................................... 65

4 SAP and Oracle ................................................................... 149

5 Planning the System Landscape ......................................... 243

6 SAP Change and Transport Management ........................... 277

7 System Lifecycle .................................................................. 341

8 Performance ........................................................................ 405

9 System Operation and Monitoring ..................................... 495

10 Backup, Restore, and Recovery .......................................... 545

11 Administrating the Java Stack ............................................ 669

12 SAP NetWeaver BI and Oracle ........................................... 723

13 Afterword and Outlook ...................................................... 785

A Flight Data Model .............................................................. 789

B General Options of the BR*Tools ........................................ 791

C References ........................................................................... 797

D Authors ............................................................................... 799

2 SAP Fundamentals ............................................................... 27

3 Oracle Fundamentals ........................................................... 65

4 SAP and Oracle .....................................................................149

4.3.2 Login Processes .......................................................... 192

5 Planning the System Landscape ..........................................243

6 SAP Change and Transport Management ............................277

6.3 Change Management for Customizing Settings ....................... 309

7 System Lifecycle ...................................................................341

8.3.2 Effects ........................................................................ 475

9 System Operation and Monitoring ......................................495

10 Backup, Restore, and Recovery ............................................545

10.4 BR*Tools for Backup, Restore, and Recovery ........................... 579

11 Administrating the Java Stack .............................................669

11.3 Java Software Logistics ........................................................... 705

12 SAP NetWeaver BI and Oracle .............................................723

"Understanding is love; that which we do not love, we do not

13 Afterword and Outlook ........................................................785

A Flight Data Model ............................................................................. 789

Index ......................................................................................................... 801

In IT, performance describes a system's ability to carry out a task within a

Performance optimization is part of the lifecycle of every system and

Small Optimization Cycle

Large Optimization Cycle

Figure 8.1 System Lifecycle and Optimization Cycles

The process of optimization is divided into the phases of analyzing, imple-

This chapter describes the analysis, implementation, and verification phases:

8.1 Administrative and Program-Based Problems

If performance problems are caused by the code of an application, the cause

Administrative performance problems are caused by the configuration of hard-

Cause\Location Program-Based Administrative

Hardware Errors in firmware Selection of inappropriate com-

Operating system Inefficient storage management by Incorrect parameterization of

Database Use of "expensive" SQL statements Insufficient parameterization

Application Incorrect use of standard SAP None

Table 8.1 Overview with Examples of Performance Optimization Problems

8.2 Analyzing Administrative Performance Problems

zation of the system components, such as hardware, operating system, data-

A similar performance analysis method is available in the context of Oracle

8.2.1 Analyzing the Hardware and Operating System

In general, the performance checks of hardware and operating system focus