tuning sap oracle aix v.2.0

7/30/2019 Tuning SAP Oracle AIX v.2.0

1/97

IBM Americas ATS

2013 International Business Machines, Inc.

IIBBMMSSAAPPTTeecchhnniiccaallBBrriieeff

TTuunniinngg SSAAPP wwiitthh OOrraaccllee oonn AAIIXX

MMaarrkkGGoorrddoonn

IIBBMM SSoolluuttiioonnss AATTSS

VVeerrssiioonn:: 22..00

DDaattee:: MMaayy 33,, 22001133


2/97

IBM Americas ATS


Page 2

1. ACKNOWLEDGEMENTS ................................................................................................................ 5

1.1. VERSION 2.0 ................................................................................................................................... 51.2. VERSION 1.2 ................................................................................................................................... 51.3. INITIAL VERSION............................................................................................................................. 5

2. DISCLAIMERS ................................................................................................................................... 5

3. TRADEMARK..................................................................................................................................... 5

4. FEEDBACK ......................................................................................................................................... 6

5. VERSION UPDATES.......................................................................................................................... 6

6. INTRODUCTION................................................................................................................................ 7

7. ORACLE CONCEPTS........................................................................................................................ 9

7.1. LOCAL AND JOIN PREDICATES ........................................................................................................ 97.2. EXPLAIN ......................................................................................................................................... 97.3. ACCESS AND FILTERPREDICATES................................................................................................. 107.4. CHECK FOR EXECUTION PLAN IN CACHE........................................................................................ 12

8. ANALYZING A PROBLEM WITH A SPECIFIC PROGRAM OR TRANSACTION ............ 14

8.1. COMPONENTS OF SAP RESPONSE TIME ......................................................................................... 148.2. MAJORITY OF TIME IS CPU ON APPLICATION SERVER................................................................... 15

8.2.1. Performance problem is high CPU time.............................................................................. 158.2.1.1. Other common ABAP problems.................................................................................. 208.2.1.2. Summary of Performance problem is high CPU time ................................................. 20

8.3. MAJORITY OF TIME IS DATABASE REQUEST TIME ......................................................................... 208.3.1. Types of problems causing high DB request time................................................................ 20

8.3.1.1. Indexes do not support predicates................................................................................ 20

8.3.1.2. Misuse of SAP Data Model ......................................................................................... 218.3.1.3. SELECT in LOOP instead of FOR ALL ENTRIES.................................................... 218.3.1.4. Data skew causes wrong execution plan to be chosen................................................. 228.3.1.5. Invalid or missing Oracle statistics.............................................................................. 228.3.1.6. Range Predicates cause wrong access plan to be chosen............................................. 228.3.1.7. Unnecessary SQL......................................................................................................... 238.3.1.7.1. Table could be buffered on application server but is not buffered .............................. 238.3.1.7.2. SAP instance buffers are too small .............................................................................. 238.3.1.7.3. FOR ALL ENTRIES with empty internal table .......................................................... 238.3.1.7.4. Invalid parameters in ABAP SQL Call........................................................................ 238.3.1.7.5. SQL is not needed for business process....................................................................... 23

8.3.2. FBL3N indexes do not support local predicates............................................................... 248.3.2.1. Summary of FBL3N .................................................................................................... 28

8.3.3. Program performs too many SQL operations...................................................................... 298.3.3.1. Summary of Program performs too many SQL operations ......................................... 30

8.3.4. Invalid parameters cause unnecessary SQL........................................................................ 30


3/97

IBM Americas ATS


Page 3

8.3.4.1. Summary of Invalid parameters cause unnecessary SQL............................................ 328.3.5. MB51 Index does not support business process requirements ......................................... 32

8.3.5.1. Summary of MB51 Index does not support business process requirements ............ 358.4. MAJORITY OF TIME IS NOT CPU ORDB REQUEST TIME................................................................. 36

9. SYSTEM PERFORMANCE PROBLEMS..................................................................................... 37

9.1. PERFORM SQLCACHE ANALYSIS................................................................................................. 379.1.1. Indicator of inefficient SQL ................................................................................................. 379.1.2. SQL Bget ratios that are generally not problems ................................................................ 389.1.3. Local predicates do not match indexes ................................................................................ 399.1.4. Reasons that the predicates do not match indexes............................................................... 409.1.5. Actions when predicates do not match indexes.................................................................... 419.1.6. High Impact SQL caused by missing indexes on custom table............................................ 42

9.1.6.1. Summary of missing custom index.............................................................................. 449.1.7. High-impact SQL caused by incorrect use of SAP data model ........................................... 45

9.1.7.1. Summary of incorrect use of data model ..................................................................... 479.1.8. High-impact SQL when optimizer chooses wrong execution plan ...................................... 47

9.1.8.1. Summary of wrong choice of access path.................................................................... 529.1.9. High-impact SQL is a symptom of SAP buffer problem ...................................................... 52

9.1.9.1. Summary of High-impact SQL is a symptom of SAP buffer problem........................ 579.1.10. High Impact Unnecessary SQL............................................................................................ 58

9.1.10.1. Summary of High Impact Unnecessary SQL............................................................... 599.1.11. High Impact SQL is a symptom of Oracle statistics problem.............................................. 59

9.1.11.1. Summary of access path problem caused by statistics................................................. 629.1.12. High impact SQL caused by inefficient ABAP programming.............................................. 62

9.1.12.1. Summary of High impact SQL caused by inefficient ABAP programming ............... 649.1.13. High impact SQL caused by range predicate estimate........................................................ 65

9.1.13.1. Summary of High impact SQL caused by range predicate estimate ........................... 689.1.14. SQL not needed because of business customization ............................................................ 69

9.1.14.1. Summary of SQL not needed because of business customization............................... 729.2. CREATE CANDIDATE LIST FROM ST03N........................................................................................ 73

9.2.1. ST03N with table statistics................................................................................................... 73

10. SYSTEM HEALTH CHECK ....................................................................................................... 74

10.1. CPU ACTIVITY .......................................................................................................................... 7410.2. I/O ACTIVITY ............................................................................................................................ 77

10.2.1. Good I/O performance in Oracle......................................................................................... 7810.2.2. Symptom of I/O hotspot on disk ........................................................................................... 78

10.3. ORACLE REVIEW....................................................................................................................... 79

10.3.1. Concurrent I/O..................................................................................................................... 8010.3.2. Database Hit Rate................................................................................................................ 8010.3.3. Database delays ................................................................................................................... 81

10.4. SAP BUFFERED TABLE STATISTICS ............................................................................................ 8310.4.1. Not buffered but could be buffered ...................................................................................... 83

10.5. EVALUATE MEMORY IN AIX..................................................................................................... 84


4/97

IBM Americas ATS


Page 4

10.5.1. 64K page use........................................................................................................................ 8410.5.2. AIX Restricted Tunables concept ......................................................................................... 8410.5.3. AIX Alternative Memory Management ................................................................................ 8410.5.4. AIX paging ........................................................................................................................... 8510.5.5. Evaluating increasing memory for Oracle or SAP.............................................................. 88

11. FOUR GUIDELINES FOR AVOIDING PERFORMANCE PROBLEMS............................. 91

11.1. USE THE SAP DATA MODEL ...................................................................................................... 9111.2. USE ARRAY OPERATIONS ON THE DATABASE............................................................................. 9111.3. CHECK WHETHER THE DATABASE CALL CAN BE AVOIDED......................................................... 9111.4. WRITE ABAP PROGRAMS THAT ARE LINE-ITEM SCALABLE ...................................................... 91

12. APPENDIX: REFERENCE MATERIALS................................................................................. 93


5/97

IBM Americas ATS


Page 5

1. Acknowledgements

1.1. Vers ion 2.0

Thank you to Damir Rubic and Steve Poon, who reviewed and proposed improvements.

1.2. Vers ion 1.2

Thank you to Damir Rubic and Steve Poon, who contributed new material on AIX and Oracle.

1.3. Initial Vers ion

Thank you to Walter Orb of IBM Germany, who was for several years the pSeries SAP performancelead for IBM Americas Solutions ATS, and who showed me many of the processes used in this paper.

Thank you to Marty Carangelo, Dale Martin, and Ralf Schmidt-Dannert, for their contributions onOracle and pSeries performance.

Thank you to Phil Hardy and Damir Rubic, who reviewed the paper and offered many improvements.

2. DisclaimersIBM has not formally reviewed this paper. While effort has been made to verify the information, thispaper may contain errors. IBM makes no warranties or representations with respect to the content hereofand specifically disclaims any implied warranties of merchantability or fitness for any particular purpose.IBM assumes no responsibility for any errors that may appear in this document. The informationcontained in this document is subject to change without any notice. IBM reserves the right to make anysuch changes without obligation to notify any person of such revision or changes. IBM makes nocommitment to keep the information contained herein up to date.

Most examples in this paper are based on ECC 6 with Oracle 11.

The processes and guidelines in this paper are the compilation of experiences analyzing performance on avariety of SAP systems. Your results may vary in applying them to your system.

Examples have been created and/or edited to clarify points for the paper.

3. Trademark ABAP, SAP, R/3, and SAP Netweaver are the trademark(s) or registered trademark(s) of

SAP AG in Germany and in several other countries.

IBM, RS/6000, System p, pSeries and AIX, AIX/L, AIX/L, Total Storage, PowerPC, Power VM, are registered trademarks of IBM Corporation.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group.


6/97

IBM Americas ATS


Page 6

4. FeedbackPlease send comments or suggestions for changes to [email protected].

5. Version Updates

Version 1.0 initial version Version 1.1 add SM04 Sessions Version 1.2

o Rename document to Tuning SAP with Oracle on AIXo added Section 8.3.1 (and sub-sections) types of DB access problemso added SQL cache example Section 9.1.6o added invalid statistics example Section 9.1.11o removed BDCPV example of access path choice problem, and replaced with Section 9.1.8

and LTAP/LTAK exampleo added ST06N and AIX virtualization CPU display in Section 10.1o Added Section 9.1.10 empty FOR ALL ENTRIES internal tableo Added SAP memory management (Sections 10.5.1 and 10.5.3)o Added Oracle related information in Sections 10.3.1.

Version 2.0 major updates for ECC6 and Oracle 11o Added Section 7 Oracle Concepts.o Updated Section 8.2.1 - Performance problem is high CPU time.o Updated Section 8.3.2 with FBL3N example to replace MIRO.o Updated Section 8.3.3 - Program performs too many SQL operations.o Added Section 8.3.4- Invalid parameters cause unnecessary SQL.o Added Section 8.3.5 - MB51 Index does not support business process requirements.o Updated Section 9.1.7 with new VBRP example.o Added Section 9.1.8- High-impact SQL when optimizer chooses wrong execution plan.o Updated Section 9.1.10- High Impact Unnecessary SQL.o Added Section 9.1.12 - High impact SQL caused by inefficient ABAP programming.o Added Section 9.1.13 - High impact SQL caused by range predicate estimate.o Added Section 9.1.14 - SQL not needed because of business customization.o Added Section 9.2.1- ST03N with table statistics.o Removed sections on summaries of performance tools.
mailto:[email protected]:[email protected]


7/97

IBM Americas ATS


Page 7

6. IntroductionThere are two intended audiences for this paper Oracle DBAs and SAP BASIS administrators. Eithermay be doing performance analysis on an SAP system with Oracle on AIX. The goal of this paper is toprovide each audience with material that is useful and new: An SAP Basis administrator experienced with

other databases should find the ORACLE specific tuning tools and techniques helpful, while theexperienced ORACLE administrator is presented with SAP specific tuning tools and techniques.

This paper covers the two most common types of performance problems database performance andinefficient ABAP coding. While there are other causes of problems in SAP (e.g. network performance,external RFC interfaces, SAP instance configuration, SAP sort, etc), database and ABAP performance arethe most common and generally have the biggest impact. In order to provide the most benefit in thesmallest paper, these other issues are not included in this paper.

This paper has a process-based approach, where different goals are pursued via different processes andtools.

To fix a problem reported for a specific program, we will perform elapsed time analyses ofprograms, determine where time is spent, and optimize these long running parts. This includesinterpretation of ST03N and STAT/STAD records, and using ST05 and SAT. The paper willdemonstrate how to use the SAP stats to obtain database performance statistics, identify I/Obottlenecks and SAP problems, etc. The benefit of this approach is that it is focused on an areathat has been identified as a business problem.

To check for inefficient use of DB resources and improve overall database serverperformance, we will use ST04 statement cache analysis. The value of this approach is that itoffers a benefit in reducing resource usage and increasing system efficiency. The disadvantage isthat one may be finding and solving problems that no end-user cares about. For example, if wecan improve the elapsed time of a batch job from 2 hours to 10 minutes, but the job runs at 2:00

AM, and nobody needs the output until 8:00 AM, it may not really be a problem. Even if it is not abusiness problem, there may still be a benefit to addressing a problem of this type as part ofoptimizing resource consumption, in order to reduce the computing resources required to supportbusiness requirements.

To do a system health check, review AIX paging and CPU usage, Oracle I/O statistics, ST10 andST02 buffering. The operating environment needs to be running well for good performance, butproblems in these areas can be symptoms of other problems. For example, inefficient SQL cancause high CPU usage or high I/O activity. Therefore, a health check should be done together withanalysis of SQL and ABAP problems.

This paper has many examples, and it describes what is good or bad in each example. There are not

always specific rules given on what is good or bad, such as Database request time over 40% of elapsedtime is bad and under 40% is good. Rather, this paper tries to focus on an opportunity-based approach,such as:

Look for where a program (or the SAP and database system) spends time. Ask If I fix a problem in this area, will people notice and care that it has been fixed?


8/97

IBM Americas ATS


Page 8

It will discuss how to estimate the impact of solving a problem. System wide performance analysis (suchas a statement of cache analysis, or ST03 analysis) will generally turn up several candidates. Byestimating the impact of fixing these problems, one can decide which to address first.

When doing this analysis, it is important to identify and track specific issues. Often, a performance issue

may not have enough impact to merit a new index, or an ABAP change. In this case, we want to track thatwe have analyzed it, and chosen not to do anything, so that we dont waste time discovering it again nextyear.

This paper refers to a number of SAP Notes. An OSS userid, or userid that allows access toservice.sap.com, is a prerequisite for anyone doing performance analysis on an SAP system, whether theperson is an Oracle DBA, AIX administrator, or SAP BASIS Administrator.

This paper breaks performance management into three parts, which are discussed in Sections 8, 9, and 10:

Analyzing a problem with a specific program or transaction System performance problems System Health Check.

Since AIX-level symptoms such as paging, excessive CPU use, and high I/O rates can be symptoms ofapplication and SQL problems, one should start by reviewing the SAP and Oracle indicators, before takingaction based on the AIX level indicators.


9/97

IBM Americas ATS

7. Oracle ConceptsHere, we have a brief review of some of the concepts and terms that will be used for Oracle.

7.1. Lo cal and Jo in Predicates

Local predicates on columns qualify the rows to be returned to the application. Join predicatesspecify the relationship between tables used to join rows in the tables.

Figure 1: SQL with local and join predicates

In Figure 1, T_00.MANDT=:A0 is a local predicate, and T_00.MBLNR=T_01.MBLNR is ajoin predicate.

Local predicates are called filtering or selective when they return few rows. Selecting a singledocument by its document number (where BELNR = ) would be return few rows. Selecting allthe rows in a client (where MANDT = ) is not selective, since there is generally only one client ona system.


7.2. Explain

We can explain SQL to evaluate whether it is executed efficiently. The explain output will providesome clues that we can use to determine whether the SQL can be improved.

Figure 2: Explain example

Page 9


10/97

IBM Americas ATS

In Figure 2, note that the estimated cost is much higher than #rows and the estimated CPU cost isalso very high. These are Oracles estimate of the cost of executing the statement, not the actualruntime statistics. When the cost is very high relative to the rows, it can point to a problem whereOracle cannot retrieve the result efficiently. An example is when the indexes do not support thelocal or join predicates.

7.3. Ac cess and Fil ter Predicates

Access Predicates are evaluated in the index. The more selective they are, the fewer index andtable pages to be retrieved. Filter predicates are applied for index or table rows on the buffer pagesretrieved for the access predicates. It is much more efficient to apply local predicates as accesspredicates than as filter predicates.

In order to find out why the statement cost estimate is high, in Figure 2 drill into the AccessPredicates field or the Filter Predicates field under INDEX SKIP SCAN RFBLG~0 to displaythe Oracle access and filter predicates used on the index.

Figure 3: Access and Filter Predicates

We can see that the reason that this statement has a high cost is that the local predicates on BELNRand GJAHR are applied as filter predicates on the index pages that have been retrieved for theMANDT access predicate.

In order to determine why local predicates are processed as access predicates or filter predicates,we need to check the indexes on the table. Drill into the RFBLG~0 field in Figure 2 to displaythe columns in the index.

Figure 4: RFBLG~0 Index

When we compare the index columns in Figure 4 with the local predicates in Figure 2, we see thatthe local predicates match the first, third, and fourth columns of the index. Since there is no localpredicate on the second column, Oracle used index skip scan, and has to apply GJAHR= andBELNR= as filter predicates.


Page 10


11/97

IBM Americas ATS

This example is actually a logic error in the program. As we can see from the columns in theunique index RFBLG~0 shown in Figure 4, BELNR is unique only when combined with BUKRSand BELNR. If BUKRS is not specified in the SQL, it is possible to retrieve more than onedocument.

If the SQL has local predicates on MANDT, BUKRS, BELNR, GJAHR as shown in Figure 5, thenOracle uses index range scan and processes all the local predicates as access predicates, and thecost estimate is much lower than in Figure 2. There are no Filter Predicates. Performance willbe better.

Figure 5: Explain with added local predicate on BUKRS


Page 11


12/97

IBM Americas ATS

7.4. Check for execut ion plan in cache

When you explain a statement, Oracle will attempt to display the execution plan that is being used.If that is not possible, you will see the following message:

Figure 6: Explain cannot display cached plan

If Oracle cannot show the cached execution plan, it is possible that the plan you are seeing in the

explain output is different from the plan that was used when the statement was executed.


Page 12


13/97

IBM Americas ATS

If Oracle can display the cached execution plan, you will see a message in the explain outputsimilar to the following:

Figure 7: Explain with cached execution plan


Page 13


14/97

IBM Americas ATS

8. Analyzing a problem with a specific program or transactionIf a problem has been identified, then the first step is to determine where most of the response time isspent. Based on this first step, there are different tools that are used to monitor the ABAP, database,RFCs, etc.

8.1. Components of SAP response t ime

SAP note 364625 describes the different components of SAP dialog step response time. This paper isfocused on programs that have high database request time, or high CPU time. These are the two mostfrequent symptoms of performance problems.

Figure 8: STAD transaction display of time components


Page 14


15/97

IBM Americas ATS

8.2. Major i ty of t ime is CPU on appl icat ion server

When the majority of time is CPU on the application server, we will use SAT (formerly SE30) toanalyze the ABAP program.

8.2.1. Performance problem is high CPU time

As an example, assume that we have been asked to investigate the performance of ZSUMMARY.We check ST03N as shown in Figure 9.

Figure 9: ST03N ZSUMMARY

Average times are somewhat slow (0 Time column is 174 seconds per dialog step), with CPU onthe application server (0 CPU column) over ten times length of database request time (0 DBTime column).

Since CPU time is the majority of elapsed time in the example in Figure 9, use SAT to trace theABAP runtime. We arrange for a user to run ZSUMMARY so we can trace it.

Run SAT and click the Own Standard Variant button and Change.

2013 International Business Machines, Inc.Figure 10: SAT own standard variant selected

Page 15


16/97

IBM Americas ATS

Then press change (the pencil) in Figure 10 to change the own standard variant options to setdefault settings.

Figure 11: SAT gather stats on internal tables

Enable statistics for internal tables as shown in Figure 11 By default these are off, but inefficientinternal table accesses are a very common source of high CPU use in custom ABAP programs.

In the Duration/Type tab, select Per Call Position.

Figure 12: SAT Aggregation level

In Figure 12, select Aggregation level by call, so that different calls on the same table are reportedseparately. Aggregation level None will cause the trace to grow very big very quickly, but isused if you need to determine the hierarchy relationships between calling and called programs.


Page 16


17/97

IBM Americas ATS

Save and go back to the main screen shown in Figure 10. Then press Switch On/Off on theSAT main screen to show the list of work processes here in Figure 13.

Figure 13: SAT start/end measurement

Select the process to be traced, and press Start measurement, trace a while, then press Endmeasurement.

Return back to the main SAT screen shown in Figure 10. Press Format performance data, andselect trace to be formatted. After the trace is formatted, you can evaluate it.

Figure 14: SAT analysis

Display the trace selected in the Evaluate tab.

Figure 15 : ZSUMMARY Desktop

As shown in Figure 15, the program spent over 98% of elapsed time processing Internal Tables onthe application server.


Page 17


18/97

IBM Americas ATS

Press the Times tab and then sort the list by Net to see where the time was spent.

Figure 16: SAT sorted by Net time

Select the slow Read Table statement in Figure 16 and press ABAP to see the ABAP.

Figure 17: ZSUMMARY source code

This is custom code it starts with Z. We will send it to the developers to fix it. The problemwith this ABAP is shown below in Figure 19.

You can calculate the time per READ TABLE using the Hit Lists button in Figure 14.

Figure 18: Hit Lists

In Figure 18, 308,697,854 uSec / 9645 calls = 32,005 uSec (or 32 ms) per call, which is very slow.


Page 18


19/97

IBM Americas ATS

READ TABLE calls should be a few microseconds. Inefficient access reading internal tables is avery common performance and scalability issue. In the SAT transaction, press the Tips andtricks button to see various suggestions on ways to improve ABAP programming. Tips andtricks contains several examples of common problems with internal tables.

Figure 19: SAT tips and tricks

One can review Tips and tricks for suggestions on ways to improve ABAP programs. In thiscase, the problem is that the ABAP ZSUMMARY shown in Figure 17 is doing a linear search ofthe internal table.

Figure 20: SAT binary search test

When a large internal table is read without the BINARY SEARCH option, the program becomesmuch slower as the number of items processed grows and the table grows.

Hashed internal tables can also be used to improve the performance of READ TABLE.


Page 19


20/97

IBM Americas ATS

8.2.1.1. Other common ABAP problems

Sorting an internal table too frequently, rather than using a sorted or hashed ITAB is anothercommon performance problem that causes high CPU.

Figure 21: Frequent ABAP SORTs

High CPU use and slow performance of LOOP AT statements can also be improved by usingSORTED or HASHED internal tables.

Figure 22: Slow LOOP AT

8.2.1.2. Summary ofPerformance problem is high CPU time

Check the response time components of the program. If a majority of time is spent in CPUtime on the application server, use SAT to trace the program. We find that the majority of

ABAP time is on a READ TABLE statement.

This is a custom program, so we would send this to the development team to investigate waysto speed up processing the internal tables, such as using a hashed itab or sorted itab withBINARY SEARCH option on READ TABLE.

8.3. Major i ty of t ime is Database Request t ime

If the majority of program or transaction elapsed time is DB time, the first question we need to ask iswhether the DB time is high because of a problem in retrieving the data. There are a number ofdifferent problems that manifest themselves as high elapsed DB time.

8.3.1. Types of problems causing high DB request time

8.3.1.1. Indexes do not support predicates


Local predicates are the column operator value clauses in SQL. In order to execute theseefficiently, the selective local predicate column must be in an index. If the column is not inan index, then Oracle must read data pages from the table to check whether the row containsthe value which is sought.

Page 20


21/97

IBM Americas ATS

Join predicates are the table1.columnA = table2.columnB clauses in SQL. Just as with localpredicates, selective join predicates must be indexed for good performance.

In many cases, the correct way to address this is to modify the ABAP so that it uses the

indexes correctly. There is an example of this in Section 9.1.12.

In some cases, new indexes are needed to support a business process that is not part oforiginal SAP functionality. There are example of this in Sections 8.3.2, 8.3.5, and 9.1.6.

8.3.1.2. Misuse of SAP Data Model

SAP often stores data redundantly in more than one table. For example, a delivery documentmight contain the order number. And an order document might contain the associateddelivery number. The delivery tables are indexed by delivery number, and order tables areindexed by order number, so if a program has the value of an order number and wants toretrieve the associated delivery, the program could theoretically use either table. But, if the

program selects from the delivery table using order number, there is no index for ordernumber and the select is slow. If the program selects from the order table, there is an indexand the select is fast.

There are three SAP notes (185530, 191492, and 187906) that describe common errors in useof the data model and how to fix them.

There is an example of this problem in Section 9.1.7.

8.3.1.3. SELECT in LOOP instead of FOR ALL ENTRIES

If an ABAP program has a list of keys in an internal table, there are two ways to use the tableto select rows from the database:

LOOP AT internal_tableSELECT from DB_TABLE where COLUMN = internal_table-columnENDLOOP

Figure 23: LOOP with select

Each SELECT call in the LOOP will make a call between the application server, and thedatabase server.

For better performance, an array operation should be used:

SELECT from DB_TABLE for all entries in internal_tableWHERE COLUMN=internal_table-column


Page 21


22/97

IBM Americas ATS


Page 22

The LOOP AT will make one call to the DB for each value, the FOR ALL ENTRIES willgroup the values together, and SAP will create an IN list (there is one column=value in theWHERE) or an OR (if there is more than one column=value clause in the WHERE.

There is an example of this in Section 8.3.3.

8.3.1.4. Data skew causes wrong execution plan to be chosen

When Oracle estimates the number of rows that will be returned on an ABAP select, it usesthe number of distinct values in a column and the number of rows in the table to estimate thenumber of rows with each column value. For example if a column AUFNR has 1,000distinct values, and its table has 10,000 rows, then Oracle assumes that each value specifiedfor AUFNR will return 10 rows.

However, if many rows contain the same value, then the data is skewed -- that is the

distribution of values in rows is not uniform. If data is skewed, Oracle requires additionalhistogram statistics, combined with an ABAP HINT, in order to use values at execution timeto determine the skew, and choose a better execution plan.


8.3.1.5. Invalid or missing Oracle statistics

Cost based optimizers collect statistics about tables and indexes (number of rows, distinctvalues of columns, etc) that are used to optimize SQL statements. If the statistics aremissing, or not collected in the correct way, the optimizer may choose the wrong executionplan.

There is an example of this issue in Section 9.1.11.

8.3.1.6. Range Predicates cause wrong access plan to be chosen

Similar to the problem with skew, prepare with parameters can cause problems with rangepredicates. When preparing with parameters, Oracle uses estimates of filtering for rangepredicates, such as


23/97

IBM Americas ATS


Page 23

8.3.1.7. Unnecessary SQL

8.3.1.7.1.Table could be buffered on application server but is not buffered

Tables that are seldom changed, and where the application can tolerate a small intervalbetween when rows are changed and the changes are available on all application servers

can be buffered on the SAP application server to offload the database.

There is an example of this issue in Section 10.4.1.

8.3.1.7.2.SAP instance buffers are too small

If the SAP generic or single record buffers are too small, then tables that could be bufferedon the application cannot fit into buffer, and must be read from the DB server.


8.3.1.7.3.FOR ALL ENTRIES with empty internal table

The ABAP FOR ALL ENTRIES statement uses an internal table with a list of keys to beretrieved for a column or columns. If the internal table is empty, the local predicates on thecolumns are not generated in the SQL, which generally causes the program to retrieve rowsthat it does not need.


8.3.1.7.4.Invalid parameters in ABAP SQL Call

If the ABAP does not correctly validate the inputs and SQL results at runtime, it is possibleto have invalid parameters in the DB calls selection conditions that would never retrieve a

row.


8.3.1.7.5.SQL is not needed for business process

Based on customization and business processes, some table columns may be used, or not,by SAP. If they are not used, they will contain no data, so selections from the table basedon the column will never retrieve data.



24/97

IBM Americas ATS

8.3.2. FBL3N indexes do not support local predicates

We have been asked to investigate the performance of the FBL3N transaction. As a first step,check the response time characteristics of FBL3N using ST03N.


Page 24

Figure 24: MIRO ST03N times

Figure 24 shows that the average FBL3N dialog step runs about 1 second (0 Time column), andabout 90% of the elapsed time (0 DB Time column) is database request time. The 0 Timeand 0 DB Time units are milliseconds. The T Response Time unit is seconds.

While the average response time is good, there may be some long running dialog steps that haveslow response time. In Figure 25 we use STAD transaction to display statistics records forFBL3N. Note that most have very short response time, but there is one that runs over two minutes(126,265 ms), where almost all the elapsed time is DB time.

Figure 25: STAD FBL3N


25/97

IBM Americas ATS

Since DB request time is the majority of time (see Figure 25), and individual SQL calls may belong, arrange to have the transaction run by a user who is experiencing long response times, andtrace the transaction with ST05. Here is the output of an ST05 SQL trace.

Figure 26: FBL3N ST05 trace

In Figure 26, note that there are some slow SELECTs on BSIS each takes over one second andreturn just a few rows. (The duration is in microseconds.) In general, a statement that is executedefficiently using indexes will take at most few milliseconds per row.

Press the ABAP display button, which is the sheet of paper button in Figure 26, in order to seethe ABAP source code.

Figure 27: FB3N ABAP display

The cursor will be positioned at the source line in the ABAP. In Figure 27, the ABAP is SAPcode. You can also display the program attributes to confirm the owner of the program.


Page 25


26/97

IBM Americas ATS

Figure 28: Program Attributes

Review the execution plan used by Oracle -- press the Explain button in Figure 26.

Figure 29: FBL3N BSIS


Page 26


27/97

IBM Americas ATS

Drill into Access predicates for BSIS~0 in Figure 29, to see which local predicates are applied inthe index.

Figure 30: BSIS Access Predicates

MANDT, BUKRS, and HKONT are processed in the index.

Drill into the table name BSIS in Figure 29, to display the indexes and indexed columns.

Figure 31: BSIS~0 Index

Comparing the local predicates in Figure 29 with the index columns in Figure 31, we see that noneof the other local predicates on BUDAT or BLART can be evaluated in the index.

Drill into Filter Predicates for BSIS table in Figure 29, to verify that BUDAT and BLART areevaluated on the table rows.

Figure 32: BSIS Filter Predicates


Page 27


28/97

IBM Americas ATS

Since the statement runs very slowly and returns few rows, we can infer that one or both of the thelocal predicates on BUDAT or BLART provides some filtering if MANDT, BUKRS or HKONThad provided all the filtering, the statement would run quickly, since evaluating access predicatesin the index is very efficient. With efficient index access, it would have taken a few ms per row,instead of taking about one second per row.

In Figure 29, compare #Rows on the index and table, to see Oracles estimate of the selectivityof the Filter Predicates on the table Oracle estimates that 697 rows will qualify from the index,but only one will qualify after the filter predicates on the table are evaluated.

So, the problem seems to be that some of the local predicates in the SQL are not processedefficiently in this SQL. Since Figure 27 showed that the program is SAP code, we would:

First, check the SAP data dictionary definitions Second, search SAP notes for this problem Third, open an OSS message

In this case, we search SAP SMP and find that there is a SAP note that seems to address this issue it proposes an index on BSIS for (MANDT, BUKRS, HKONT, and BUDAT).

Figure 33: SAP note for FBL3N BSIS

8.3.2.1. Summary ofFBL3N

The transaction was identified as a candidate to review. We checked the components ofresponse time using ST03N, and found that the majority of time was DB time. The transactionwas traced with ST05, and slow SQL was found. The slow SQL statement was initiated froman SAP ABAP program. We checked SAP SMP, and found a note that recommended a newindex. We can either create the proposed index, or open a message to SAP to review this issue.


Page 28


29/97

IBM Americas ATS

8.3.3. Program performs too many SQL operations

A different example of inefficient SQL is when a program uses single row selects instead of arrayselects. SAP has a construct (FOR ALL ENTRIES) that uses an internal table in an ABAP tospecify the key values to be fetched from a table in Oracle. The FOR ALL ENTRIES (FAE) canbe executed as an IN list (if there is only one variable column in the internal table) or as an OR (if

there are two or more variable columns in the internal table.

In this example, we have been asked to review the performance of a program that is slow. Inchecking the stats for the program, we see that database request time (0 DB column) is long,nearly 40 minutes, but the average time for each sequential read is short, 2.5 ms.

Figure 34: ZFB001 program - lots of fast DB accesses

Average CPU time (0 CPU) is about of database request time (0 DB), so we need to look atthe database accesses with ST05 to investigate.

We trace the transaction with ST05, and then list the trace.

Figure 35: ZFB001 ST05

The program behavior is interesting. It repeatedly selects rows from the same table, BKPF.Display the ABAP source.

2013 International Business Machines, Inc.Figure 36: ZFB001 program source

Page 29


30/97

IBM Americas ATS

The program is looping through an internal table (LOOP AT t_bel). For each entry in the table, theprogram selects from BKPF. If it is possible to change the program to do a single FOR ALLENTRIES select on BKPF from the internal table, the program would probably run much faster,since reading N rows at once from Oracle is generally faster than reading 1 row N times.

Since the program is custom code (Zxx), we send it back to the ABAP team, to review whether theprogram could be restructured to use SAP array fetch operations.

8.3.3.1. Summary ofProgram performs too many SQL operations

ST03N statistics showed long database time and relatively small CPU time, but per-callsequential DB request times were good (2.5 ms). We used ST05 to trace the SQL and alsoconfirm the SQL response times. The trace showed that the same table was repeatedlyaccessed. The ABAP program is looping through an internal table, fetching one row at a timefrom the database. The program needs to be re-evaluated, to determine if the single-row accessto the database can be converted into an array operation, such as FOR ALL ENTIRIES.

8.3.4. Invalid parameters cause unnecessary SQLLogic errors, such as not validating input parameters or the results of SQL can create the situationwhere SQL is executed with parameters that will never retrieve a row, since the parameters specifykeys that cannot exist. This type of problem can be found when analyzing an SQL trace, or by

examining the Bind variables (e.g. Figure 70) when a statement is explained in the Shared CursorCache.

In this example, we have been asked to analyze the performance of the program ZCOITEMS, a customprogram that runs slowly. The majority of elapsed time was DB request time, so we use ST05 SQLtrace.

Figure 37: ZCOITEMS ST05

Note that Hits is zero on all lines no rows are retrieved. The value being sought is (space).


Page 30


31/97

IBM Americas ATS

We summarize the trace (Trace list > Summarize by SQL statement), to see if this occursfrequently.

Figure 38: ZCOITEMS summarize

And we see that 98% of the calls to LFA1 are identical.

Figure 39: ZCOITEMS summary

Press the page icon (Display Call Positions in ABAP Program) to display the ABAP source

location.

Figure 40: ZCOITEMS ABAP

And we find the location in the ABAP which makes the calls. Last, we use SE11 to review thetable (or call the developers), to see if space is a reasonable value to use in the program.


Page 31


32/97

IBM Americas ATS

Figure 41: ZCOITEMS LFA1 display

LIFNR is the vendor or creditor account number. It does not seem to make sense to specifyLIFNR = , since there is no vendor or creditor with the account number . If this had been astatus flag, then it could make sense to search for a status of space.

We return the program to the ABAP developers, to review how the data is retrieved and checkedbefore the call to LFA1 is made.

8.3.4.1. Summary ofInvalid parameters cause unnecessary SQL

The program ZCOITEMS spends the majority of its time in DB calls. We trace with ST05,and see that the program frequently selects from LFA1 where LIFNR = (space). Summaryof the trace shows that duplicate selects on LFA1 are over 90% of the calls on LFA1. SE11

shows that LIFNR is a customer or vendor number, so it is unlikely that space is valid. Theprogram is returned to development to review.

8.3.5. MB51 Index does not support business process requirements

In this example, we have been asked to review performance of MB51, which has been reported tohave long runtimes.

Figure 42: MB51 performance statistics

Based on the ST03N data in Figure 42, the performance seems fine an average of 3.6 seconds.Well have one of the users run the transaction, and trace it. Since DB time is the large majorityof time, we use ST05 SQL trace.


Page 32


33/97

IBM Americas ATS

Figure 43: MB51 ST05 summarized trace

We run the ST05 SQL trace and summarize the trace, and find in Figure 43 that there are someslow selects on MKPF. The first line shows 47 rows retrieved in 464 seconds. 10 rows persecond is a very slow rate. If the indexes support the SQL, it should take at most a fewmilliseconds per row.


Page 33

Display the ABAP source, and we see that it is SAP standard code.

Figure 44: MB51 ABAP Source

The ABAP already contains the &SUBSTITUTE VALUES& HINT, so that the statement willbe prepared with values each time it is executed. When preparing a statement with values,histograms must also be gathered on the table, for best access plan choice.


34/97

IBM Americas ATS

Explain the statement in Figure 44, and note that the Oracle estimate for the MSEG branch of thejoin is much higher than the MKPF branch of the join, even though the #Rows estimate is similar.A custom index (Z00) is used on MSEG. We need to evaluate the index, to see if it has beencreated correctly to optimize access to the table.

Figure 45: MB51 with custom index used

First, drill into the Access Predicates on MSEG~Z00, to see which local predicates are applied inthe index (Access Predicates) and which are applied on table rows in the data pages (FilterPredicates).

Figure 46: MB51 Access and Filter Predicates

The local predicates BWART BETWEEN and WERKS = are Access Predicates in the index,but WERKS = is applied again as a Filter Predicate after the table is read. We need to look atthe definition of the index to see why this is happening. If the index was designed to support this


Page 34


35/97

IBM Americas ATS

SQL, all three local predicates on MANDT, WERKS, and BWART would be Access Predicatesand there would be no Filter Predicates.

Figure 47: MB51 Custom Z00 Index

In Figure 47, we can see the problem the BWART column (which has a range predicate -BETWEEN) is ahead of WERKS in the index. For the most efficient index access, columns withequals predicates, such as MANDT and WERKS in this example, should be before columns withrange predicates.

In this example, the index does not efficiently support the local predicates the range predicate onBWART causes the WERKS = local predicate to be a Filtering Predicate so it is applied onindex pages after accessing the index b-tree

Assuming that this index is designed to optimize the business process of reporting on movementtypes (BWART) for a plant (WERKS), and that the end-user will generally specify a single plant,the best design for the index would be (MANDT, WERKS, BWART, MATNR). We want tomove the column that has the range predicate (BWART) after MANDT and WERKS in the index.

In all the other examples in this paper, we try to avoid creating custom indexes. The table inSection 9.1.5 shows the decision matrix. But, in some cases custom indexes are necessary tosupport a business process that is not supported by the default indexes. The business processreporting on all the materials (MATNR) in a plant (WERKS) is not supported by the defaultMSEG indexes. This index would enable that process to be done efficiently.

8.3.5.1. Summary ofMB51 Index does not support business processrequirements

We are asked to review MB51. ST03N statistics show good average response times, but whenwe trace execution for a user, we see long running selects on MKPF that return few rows.Explain shows that a custom index is being used, and that one of the local predicates is appliedas a Filter Predicate, which is not as efficient as having all local predicates processed as Access

Predicates. We review the index design and local predicates, and find that there is a rangepredicate on the second column of the index. We review the purpose of the index, and changethe column order of the index to have the columns with equals predicates in the lead, followedby columns with range predicates.


Page 35


36/97

IBM Americas ATS


Page 36

8.4. Major i ty of t ime is no t CPU or DB request t ime

There are a number of SAP response time categories that can also indicate performance problems thatare not covered in this paper. SQL and ABAP, which are covered in this paper, are the source of thevast majority of performance problems.

The paper Tuning SAP on DB2 for z/OS on System z on IBM Techdocs(http://www.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100287 ) contains detailedexplanations about additional SAP sources of program delays (such as ENQ delay, SAP sort, local I/O,commit work and wait, RFC, GUI, network, etc), their symptoms and possible fixes.
http://www.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100287http://www.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100287


37/97

IBM Americas ATS

9. System performance problems

9.1. Perform SQL Cache Analysis

In order to find inefficient SQL that may have a system-wide impact, because of using excessive CPU

or doing excessive I/O, review the SQL cache. SAP note 766349 describes the columns in the ST04Shared Cursor Cache display.

9.1.1. Indicator of inefficient SQL

The primary indicator shown in ST04 of inefficient SQL is when both Bgets/exec and Bgets/row

are high. A Bget (buffer get) is when oracle references a page in Oracle database buffer. A highratio for both of these indicators is a sign that Oracle must search a large amount of data to find theresults. Searching many pages will consume extra CPU and may cause extra I/O operations.Normally, it might take 5 buffer gets to retrieve a single row from a single table, and it can bemuch fewer when performing array operations that retrieve many rows with a single call to theDB.

SAP and Oracle display CPU and elapsed time statistics, as well as the efficiency (Bgets/row andBgets/exec) statistics. The CPU and elapsed time data is useful for estimating the impact of thestatement on the system or program.

Figure 48: ST04 display of SQL cache for Oracle 11

Note that if no rows are returned by a statement, Bgets/row is 0. When Bgets/row andBgets/Exec are high, it is a sign of inefficient SQL. If Bgets/row is high, and Bgets/Exec is low, itmeans the statement is executed efficiently, but seldom returns a row.


Page 37


38/97

IBM Americas ATS

Figure 49: ST04 SQL cache - problem example

In Figure 49, the first statement in the list looks like it is very inefficient each execution takes onthe average 3.5 seconds to return 4.9 rows. There are 11,456 Bgets/exec and 2,239 Bgets/row,which is much higher than the 5 Bgets/row that one might see if the SQL were efficientlyaccessing the data.

Figure 50: ST04 SQL cache - moderately inefficient SQL

Even when the Bgets/row is lower than the example shown in Figure 49, if a statement is executedfrequently enough it can cause an important impact on the system. By addressing and fixingseveral of these problems, one can make a measurable decrease in CPU usage on the system.

The higher Bgets/row and Bgets/exec are, the more likely that the SQL is inefficient.

9.1.2. SQL Bget ratios that are generally not problems

When Bgets/exec is high and Bgets/row is low, SAP is doing array operations, which affectmany rows per database call. This is generally an efficient way to access the database.


Page 38


39/97

IBM Americas ATS

When Bgets/exec is low and Bgets/row is high, this shows that the SQL seldom retrievesrows. If Proc rows is 0, check whether the table contains data, and if the statement isneeded in the program.

When Bgets/exec is low, and Bgets/row is low, then the data has been retrieved efficiently.9.1.3. Local predicates do not match indexesAs described above in Section 8.3.1, one of the main causes of inefficient SQL is that the SQLlocal predicates generated from the ABAP do not match the available indexes.

Local predicates are the WHERE COLUMN operator value clauses in the SQL. If thepredicates contain columns that are not in indexes, or if the indexed columns are not selective (thatis they return many rows) then Oracle generally cannot access the data efficiently.

Figure 51: Sample explain

In Figure 51, the local predicates are:

MANDT = VGBEL =


Page 39


40/97

IBM Americas ATS

Drill into Filter Predicates on Figure 51, and we see in Figure 52 that all the local predicates areapplied on the table. This is to be expected, since Oracle is using TABLE ACCESS FULL, ratherthan using an index.

Figure 52: VPRB Filter Predicates

One can drill into the table or indexes shown in Figure 51, to see the indexes on the table.

Figure 53: Display indexes from explain

Now that we have predicates and index columns, one can compare to determine if the localpredicate columns are contained in indexes. Comparing Figure 51 and Figure 53, we see that theonly indexed predicate column is MANDT. Since there is only one value of MANDT, Oraclemust look at all the rows of the table, in order to find the rows matching the VGBEL = predicate.

9.1.4. Reasons that the predicates do not match indexes

There are three basic scenarios:

The program is not using the SAP data model correctly, The ABAP needs to be changed to match the available indexes, and The business process may require a new index on the table


Many kinds of information are redundantly stored in SAP. For instance, the transaction data for abilling document may contain columns with the document number for the delivery or sales orderbeing billed. But just because the information is present does not mean that is the right way toretrieve it. Billing documents, to continue the example above, are indexed to support lookup by

Page 40


41/97

IBM Americas ATS

billing document number. If a program tries to read the billing tables using the invoice column, inorder to find the associated invoice document, the column with the invoice number will not beindexed, and access will be slow. I f thi s is the situation, the solution is not to create an index,but to use the SAP data model corr ectly.

There is an example of incorrect use of the SAP data model below in section 9.1.6.

SAP has several SAP notes that describe wrong and right ways to use the SAP data model toretrieve application information:

MM SAP note 191492 SD SAP note 185530 PP SAP note 187906

And if we look at SAP note 185530, we find this SQL and a solution:

Figure 54: Note 185530 - VBRP where VGBEL

9.1.5. Actions when predicates do not match indexes

It can occur that specific business processes require new indexes on tables. Here are someguidelines on how to approach the problem when you find a program where the predicates do notmatch the indexes:

ABAP Creator Table Creator Action when predicates do not match indexes

SAP SAP Check data dictionary for standard indexes whichare not active in DB

Check SAP notes Open a message to SAP

Custom Custom Rewrite program if possible Evaluate table access patterns, whether table can be

buffered

Create indexCustom SAP Check data dictionary for standard indexes that are

not active in DB

Review use of data model (will frequently fixproblem) Change program Create index (should very seldom be necessary)


Page 41


42/97

IBM Americas ATS

9.1.6. High Impact SQL caused by missing indexes on custom table

In this example, the SQL cache is sorted by CPU time, to bring the statements with longest totalexecution time to the top. Note that the top statement performs over 12,000 Bgets for each rowprocessed, which is inefficient. With effective indexes, selecting a single row from a table will

take 4-5 Bgets. Each execution is over 3 seconds (the time is in microseconds), and with effectiveindexes, selecting a row would normally take a few milliseconds.

Figure 55: Custom table SQL

We select the statement and press the explain button to see the execution plan.

Figure 56: Custom table explain


It is SELECT FOR UPDATE, so part of the long averaged elapsed time could be row lockcontention. But in Figure 55 each execution used over 1 CPU second to process over 16K bufgets,so the statement is also very inefficient.

Page 42


43/97

IBM Americas ATS

We see in Figure 56 that Oracle is using INDEX SKIP SCAN, which means that not all the localpredicates match the leading index columns some local predicates are applied on the index pagesas Filter Predicates. The cost is very high, relative to estimated rows.

Drill into Access Predicates or Filter Predicates in Figure 56 to see how the local predicates

are applied.

Figure 57: Custom Table Access and Filter Predicates

The entire index is read on each execution SERNR is a Filter Predicate, and there is only oneMANDT, as shown in the index statistics in Figure 58.

Figure 58: Custom Table index

From the SQL list in Figure 55, we can also display the ABAP text by pressing the ABAPSource button.

Figure 59: Custom program for custom table


Page 43


44/97

IBM Americas ATS

Goto > Attributes to check who wrote it.

Figure 60: Custom Program Attributes

The ABAP is custom development it was not created by SAP. If created and modified bySAP, it would standard ABAP.

We compare the local predicates in the SQL statement

MANDT = SERNR =

With the columns contained in the index MANDT WERKS MATNR ZOX_RUECK SERNR ZCONF_CNT

And now we can see the source of the problem the local predicates in the SQL do not match theleading columns of the index.

At this time, we need to determine what action should be taken. From the table in Section 9.1.5,we see that possible actions might be to rewrite the SQL, or buffer the table in SAP, or create anindex. In this case, let us assume that we cannot re-write the SQL to match the available index.So, we will create a new index (MANDT, SERNR) on the table.

9.1.6.1. Summary of missing custom index

The statement was identified by sorting the SQL cache by CPU time. The top statementperformed over 12,000 bufgets per row, which is inefficient. Each execution took over 1second, which was about 1,000 times as long as it would take with a proper index. When weexplained the statement, and compared the local predicates to the available indexes, we found

that there is no index that supported the ABAP selection for SERNR = i_update_status-sernr.The solution was to create a new index (MANDT, SERNR) for this program.


Page 44


45/97

IBM Americas ATS

9.1.7. High-impact SQL caused by incorrect use of SAP data model

Sort the SQL cache by CPU Time, to bring the high impact statements to the top.

Figure 61: VBRP high impact statement

The first statement in the list is very, very inefficient it performs over 9,000 Buffer gets per row,and retrieves only 35 rows per execution. When all the local predicates in an index are evaluatedefficiently in an index, there are at most only a few Buffer gets per row.

Explain the statement to see what execution plan is used.

Figure 62: VBRP explain

It is TABLE ACCESS FULL the entire table is read without an index.

Note the local predicates, which we will compare with indexes.

MANDT = VGBEL = VGPOS =


Page 45


46/97

IBM Americas ATS

Drill into VPRB in Figure 62 to see the indexes on the table. There is only one index, and itdoes not contain VGBEL or VGPOS.

Figure 63: VBRP index

The SQL has a local predicate on MANDT, but Figure 63 shows that MANDT has only one value,so Oracle chose to read the entire table (TABLE ACCESS FULL) as shown in Figure 62.

Use the ABAP Source button in Figure 61 to look at the source code.

Figure 64: Program accessing VBRP

The program name starts with Z this is custom code. From the table in Section 9.1.5, we know

that the first action is to examine if the program is using the data model correctly.

SAP note 185530 describes several incorrect and correct lookups for SD. This problem iscontained in the note:

Figure 65: SAP note with examples of using the SD data model


Page 46


47/97

IBM Americas ATS

The action for this problem is to send it back to the developer with reference to SAP note 185530.In this proposed fix, for SD documents there is a table VBFA (document flow) that contains thepredecessor and successor documents for sales documents. For example, given a sales ordernumber, one can find all the subsequent documents related to the sales order, or given an invoicenumber, one can find all the predecessor documents that lead to that invoice document.

9.1.7.1. Summary of incorrect use of data model

Statement is found in ST04 SQL cache with very high Bgets/exec and high Bgets/row.Explain the statement, the entire table is read. Check the indexes, there is no index thatsupports the local predicates. Check the owner of the program. It is not SAP, so we suspectthat the programmer is looking in the wrong place for the information. Check SAP note185530, since the problem here is in use of the SD tables, and find the proposed fix. Send theprogram back to developer to re-work.

9.1.8. High-impact SQL when optimizer chooses wrong execution plan

While the Oracle costbased optimizer generally does a good job of determining the best way to

access tables based on the predicates in the SQL, it can go wrong. Here the SQL cache has beensorted by elapsed time, to find statements with a high impact on response time.

Figure 66: ST04 SQL cache for skew example

The statement performs about 30 buffer gets per row, which is high for an array operation of 3,000rows per execution. With efficient indexes, a single row read is about 5 bufgets, and for arrayoperations there are fewer bufgets per row, often less than one bufget per row.

A high ratio of Bgets per row usually means that the local predicates that filter well are not beingapplied in the index, of if the statement is a join, they are not being applied on the first tableaccessed by Oracle.


Page 47


48/97

IBM Americas ATS

Explain the statement, to see what it is doing.

Figure 67: BKPF explain

From Figure 57, get the local predicates, which will be needed later:

MANDT = BUKRS= BSTAT = XBLNR = BUDAT = STBLG =

The ST04 statistics in Figure 66 tell us that the current execution plan is not an efficient way toaccess the data, since each execution takes over 33 buffer gets for each row.

Drill into the BKPF~1 index in Figure 67, and we see that this index matches the columns(MANDT, BUKRS, BSTAT, and XBLNR) in the local predicates.


Figure 68: BKPF~1

Page 48


49/97

IBM Americas ATS

Drill into the BKPF table in Figure 67 to get the row count for the table.

Figure 69: BKPF rows

There are 6.2 M rows, and 3.6M distinct values of XBLNR, so without histograms, Oracle willestimate that there are about 2 rows per XBLNR value. If there is skew in the distribution ofvalues in XBLNR, this estimate will be wrong. We press the Bind Variables button in Figure67 to see history of variables used at runtime. Review the Bind Variables to see if there arepatterns or trends in the variables that users specify.

Figure 70: BKPF Bind variables

Comparing with the SQL in Figure 67, we see that the value for XBLNR is space (:A3 is space).

We can use ST04 SQL Command Editor to check for skew in the columns.

Figure 71: BKPF SQL to count XBLNR

The transaction TAANA can also be used to do counts of rows with a value.


Page 49


50/97

IBM Americas ATS

The result in Figure 72 shows that the value (space) occurs very frequently over three millionrows in the table have the value XBLNR = . The index BKPF~1 is not a good choice in thissituation, since there are so many rows with value XBLNR= . If XBLNR were a value otherthan space, then BKPF~1 would have been a good choice.

Figure 72: BKPF XBLNR count

Next, check the indexes on BKPF to see if there is an index that would support the BUDAT =local predicate. BKPF~2 matches MANDT, BUKRS, BSTAT, and BUDAT. You can use thequery in Figure 71, modified for BUDAT column, to confirm that the data in BUDAT is not

skewed, and that it would be a better choice for Oracle to use.

Figure 73: BKPF~2 index

Figure 73 shows that there is already an index on BUDAT. Oracle is not choosing it. When thereis skew in data values, or when range predicates are used to select very small or very largepercentages of the rows, the default SAP prepare method (prepare with parameters) may causeOracle to choose a bad execution plan, since Oracle cannot make a good estimate of cost in thesesituations. In this case, Oracle is over-estimating the selectivity of the XBLNR = localpredicate.

The default way that statements are prepared by SAP is that parameters are used. This enables astatement to be shared by many different threads, but it has the disadvantage that Oracle cannotcompare the execution-time values to the Oracle histogram statistics, to determine when aninfrequently occurring (or frequently occurring) value is being sought. SAP provides an option to

use an ABAP hint to change this default. See SAP note 129385 regarding how to prepare astatement with literals or values.

For earlier versions of SAP and Oracle that do not have the Bind Variables option in ST04explain, it is generally necessary to trace the SQL using ST05 to get the parameters.


Page 50


51/97

IBM Americas ATS

Using the ABAP Source button in Figure 66, we can display the ABAP source. It is customcode, since the name starts with Z.

Figure 74: BKPF source

Since there is skew, we need to check if histograms are being collected on the table, since Oraclewill need the histograms in conjunction with parameter values to determine when the program isselecting frequently-occurring values or seldom-occurring values. Use DB02 Storage tab to viewcolumns with histograms. Figure 75 shows that there are currently none.

Figure 75: DB02 display columns with histograms

If you use SAP to control statistics, then update DBSTATC for BKPF to collect statistics usingEH Method for Estimate Histograms The options for DBSTATC are described in SAP note106047.

Figure 76: DBSTATC


Page 51


52/97

IBM Americas ATS

Last, the ABAP must be modified to add the Open SQL HINT &SUBSTITUTE VALUES&, asdescribed in SAP note 130480. &SUBSTITUTE VALUES& is used when the access planshould vary based on the runtime values of the SQL, such as when there is skew in the distributionof a column used in the statement. By passing the values of the parameters to Oracle, Oracle cancompare the value with the histograms, and use that to choose the best execution plan.

SAP note 176754 discusses a variety of issues that may impact the Oracle Cost Based Optimizer inchoosing the best execution plan.

I t is necessary that two steps are done to f ix thi s problem the histogram statistics gather the

data that shows Oracle that there is skew in values of a column, and the ABAP H INT passes the

value used when the statement is executed, so that Oracle can compare the table statistics to the

execution value, to determine whether a fr equentl y occurr ing or infr equentl y occurr ing value is

being retrieved.

9.1.8.1. Summary of wrong choice of access pathStatement is found in SQL cache with many buffer gets per row. Explain the statement, andwe see that four of the six local predicates are evaluated in index BKPF~1. We check the bindvalues, and then look at the distribution of values in XBLNR, and we determine that there isskew in XBLNR, and the program is fetching the commonly occurring value (space).There is another index that contains BUDAT, and since there are over 5,000 distinct values ofBUDAT, we modify the program to use the &SUBSTITUTE VALUES& HINT so that SAPwill pass the values to Oracle when the statement is executed. We also gather histogramstatistics on BKPF, so that Oracle will know that XBLNR has data skew.

9.1.9. High-impact SQL is a symptom of SAP buffer problemWe have ST05 traced and summarized the trace for a program with long DB time, in order to findthe SQL that is slow.

Figure 77: A611 in SQL cache

Note in Figure 77 that the selects on A611 take the largest part of DB request time, but A611 is setto be fully buffered (BfTp = ful).


Page 52


53/97

IBM Americas ATS

Display the ABAP source using the ABAP Source button in Figure 77, and it is SAP code.

Figure 78: A611 ABAP source SD_COND_ACCESS

Transaction ST02 shows SAP buffer management activity generic key DB accs is much higherthan for other buffer types, and there are swaps.

Figure 79: ST02 Buffer Display


Page 53


54/97

IBM Americas ATS

In Figure 79, drill into the Generic Key line.

Figure 80: ST02 Generic Key

In Figure 80, press Buffered Objects. Figure 81 shows that the table with the highest number ofDB calls, A611, is in error state.

Figure 81: A611 Error State

Place the cursor on a buffer state, and press F1 to display the help for Buffer State. Error meansthe buffer could not be loaded.


Page 54


55/97

IBM Americas ATS

Figure 82: ST02 Buffer Status

The table is configured as buffered on the application server, but there must be some sort ofproblem, since the call statistics in Figure 81 show over 1 million calls. When a table is bufferedon the application server, there should be very few calls to the database server to retrieve rowsfrom the table.

It may be that the table is frequently changed and invalidated and then re-loaded or that the tabledoes not fit in the application server buffer. These can be checked in Figure 81 there are nochanges. The table may also be read by the ABAP in a way that bypasses buffering on theapplication server. See SAP note 47239 regarding buffered table behavior. Since BYPASSINGBUFFER is not used in the ABAP above, and the table is in error state, we can infer that the

problem is that the SAP Generic Buffer area needs to be enlarged.

We can check the size of the table to verify that it can be buffered, and then increase the genericbuffer parameters zcsa/table_buffer_area and zcsa/db_max_buftab. As an alternative, one canchange the table to be buffered on generically on some of the key columns, rather than fullybuffering the table. Each generic range will require less space, and the most active parts of thetable would be loaded into SAP buffer.


Page 55


56/97

IBM Americas ATS

Figure 83: DB05 A611

One can use DB05 to evaluate the number of generic ranges to choose. In Figure 83, note thatthere are 250 distinct values of WERKS, but only one distinct value of MANDT, KAPPL, andKSCHL. If we were to choose to buffer this table generically, then buffering on 4 columns wouldbe a reasonable choice, each WERKS would have its own range in the SAP buffer.


Page 56


57/97

IBM Americas ATS

As shown in Figure 84, use SE13 to change the number of columns in the generic key, if you wantto buffer in smaller ranges.

Figure 84: SE13 A611

9.1.9.1. Summary ofHigh-impact SQL is a symptom of SAP bufferproblem

ST05 summarized trace shows that a buffered table is top contributor to DB time. Look at theprogram source, and it is SAP code. Check the buffers on the application server, see that thetable is in error state. This usually means that the generic buffer is too small. The frequentcalls to A611 are a symptom of another problem, the small generic buffer size. Either increasethe size of the generic buffer, or change to buffer the table in smaller segments.


Page 57


58/97

IBM Americas ATS

9.1.10. High Impact Unnecessary SQL

In this example, we have ordered the SQL cache by rows per execution, in order to search forstatements that retrieve large numbers of rows. The top statement looks OK, based on theefficiency metrics the ratio of Bget/row is low. But, the question we ask is why would aprogram need to retrieve 16 million rows on each execution of an SQL statement?

Figure 85: EKET 16 million rows

We explain the SQL to see what the program is doing.

Figure 86: Explain EKET

In the explain output, we see something unusual. The only local predicate is MANDT =, so allthe rows in the table for the productive MANDT are being selected. Usually we would only seeSQL with MANDT= and no other local predicates in SQL that loads fully buffered tables. Weneed to check the program that is issuing the SQL.


Page 58


59/97

IBM Americas ATS

Figure 87: EKET program source

Figure 87 shows that the program is a custom program (the name starts with Z)Note that the ABAP has selection conditions (WHERE EBELN = ) that are not in the OracleSQL why does this happen?

ABAP SQL works like a template for building the DB SQL. When the WHERE condition isconverted to SQL local predicates, if there is no value specified for a variable or if an internal tableused with FOR ALL ENTRIES is empty, that column is not included in the SQL. Here, theproblem is that the internal table I_EKPO is empty, so the DBSL does not create local predicateson EKET-EBELN and EKET-EBELP. Since MANDT is implicitly added to the SQL, we get anSQL statement with only MANDT =.

The internal table used in FOR ALL ENTRIES places restrictions on the rows to be retrieved, so if

the internal table is empty, then all rows are retrieved.

In this case, the fix is to modify the ABAP to check for whether I_EKPO is empty, and if it is

empty to skip the SELECT.

9.1.10.1.Summary of High Impact Unnecessary SQL

The SQL in cache is efficient (low Bgets / row) but many rows are processed. We check theSQL, and the only local predicate is MANDT = there are no other selection conditions.Checking the ABAP, there is a WHERE clause with EKET columns referencing an internaltable. If the internal table is empty, then the DBSL does not create a local predicate onEKET. The fix is to modify the ABAP to check whether the table is empty, and skip theSELECT if it is empty.

9.1.11. High Impact SQL is a symptom of Oracle statistics problem

In this example, we have ordered the SQL cache by total elapsed time, in order to find statementsthat have the largest impact on runtime (at the system level). Note that the top statement has over6,000 bufgets per execution, but 0 bufgets per row. This is because no rows were returned. SeeSection 9.1.2 regarding ratios of bufgets to executions and rows.


Page 59


60/97

IBM Americas ATS

This example is taken from a test system. In general, problems with statistics on productivesystems are encountered soon after new tables or new SAP functionality are transported into prod,or when errors have been made in existing procedures for gathering DB statistics.

Figure 88: D010INC high bgets/Exec

When we explain the statement, we see in Figure 89 that the table D010INC is being read withoutan index, using TABLE ACCESS FULL.

Figure 89:D010INC explain


Page 60


61/97

IBM Americas ATS

Click on the table name (D010INC) in Figure 89 to see the table and its indexes and indexstatistics.

Figure 90:D010INC indexes

SAP retrieves the statistics here from Oracle. At the time the statistics were analyzed, the tablewas empty. But the table is clearly no longer empty, since each execution of the SQL (see Figure88) currently reads over 6,000 pages of data from the table.

We can analyze the table to generate new statistics (note the Analyze button in Figure 90), andafter the statistics are generated, when we explain the statement, the index is used.


Page 61


62/97

IBM Americas ATS

Figure 91: D010INC read with index

If this were a productive system and the table were an SAP business table, we would need toreview the statistics gathering process for the table, to verify that it was correct, and we wouldneed to analyze statistics for the table, in order for Oracle to choose a better execution plan.

9.1.11.1.Summary of access path problem caused by statistics

We sorted the SQL cache to find statement with high elapsed time. The top statement did over6,000 bufgets per execution, and never returned a row. This is inefficient. We explained thestatement and examined the table and index statistics, and found that the table had statisticsgathered when it was empty, and the table had grown.

See SAP notes 932975 and 1020260 (and other note they reference) regarding Oracle tablesthat require special statistics to ensure proper optimization.

9.1.12. High impact SQL caused by inefficient ABAP programming

In this example, we have a statement that is high in total elapsed time, and where the statement isinefficiently executed. The statement performs 39 bufgets per row returned.

Figure 92: CRCO ST04 Shared Cursor Cache


Page 62


63/97

IBM Americas ATS

Explain the statement, and we see that there are both Access Predicates (predicates applied in theindex) and Filter Predicates (predicates applied after the index rows are read) on the CRCO~0index.

Figure 93: CRCO Explain

Drill into the Access Predicates line in Figure 93, and we see that OBJID is applied as a FilterPredicate, which is less efficient than when it is an Access Predicate.

Figure 94: CRCO Predicates


Page 63


64/97

IBM Americas ATS

If we drill into the CRCO~0 index in Figure 93, we can see the problem there is no localpredicate on the OBJTY column, which is the second column in the index. Local predicates oncolumns after OBJTY, such as OBJID, are processed as Filter Predicates.

Figure 95: CRCO~0 index

In Figure 95, note that there is only one value for OBJTY. Since there is only one value, it shouldbe possible for the ABAP program to include the value, so that the first three columns of CRCO~0

can be used as access predicates. We check the ABAP source using the ABAP Source buttonin Figure 92.

Figure 96: CRCO program

This is a custom program, so our action is to send it back to the developer, to review how tospecify OBJTY = as part of the ABAP SQL.

9.1.12.1.Summary ofHigh impact SQL caused by inefficient ABAPprogramming

SQL in the shared cursor cache has symptom of inefficiency high bufgets per row. Weexplain the statement, and see that the only access predicate is MANDT=. The local predicateon OBJID is a filtering predicate, which is evaluated by oracle after the index rows areretrieved by MANDT. There is only one value of OBJTY, the second column in the index, so

it may be possible to add OBJTY to the ABAP SQL. The program is a custom program, sowe send it to development to review how to modify the ABAP to match the CRCO~0 indexbetter.


Page 64


65/97

IBM Americas ATS

9.1.13. High impact SQ

tuning sap oracle aix v.2.0

Documents