Anthony Cecchini is the President of Information Technology 麻豆TV网站 (ITP), an SAP consulting company headquartered in Pennsylvania. ITP offers comprehensive planning, resource allocation, implementation, upgrade, and training assistance to companies. Anthony has over 17 years of experience in SAP R/3 business process analysis and SAP systems integration. His areas of expertise include SAP NetWeaver integration; ALE development; RFC, BAPI, IDoc, Dialog, and Web Dynpro development; and customized Workflow development. You can reach him at [email protected].
Performance Problems听Come in a Variety of Flavors
There are many reasons for slow execution of a transaction or a听report. Sometimes there are general system problems. Sometimes users听use the program in a way it was not designed for. Sometimes the nature听of the application and workload calls for parallel processing. And听sometimes the source of the performance issue can be traced back to your ABAP code, mainly the construction of your OPEN SQL Definition.
While there are many reasons other than database performance that could be causing the performance problem, this blog series will focus on just this aspect. 听If you’ve ruled out deficiencies in the system setup, mishandling by听users, or the need for parallel processing, then you need to revisit your听code and see if the source of your performance problem is hiding there in your SQL commands.
Lets agree now that听all programs perform numerous accesses听to the database, so this听should be considered thoroughly.听For example, selections that are not supported听by a suitable index can have long (very long!)听runtimes. This not only potentially impacts the听running programs themselves, but is also an encumbrance听to all users, as they have only limited access听to the database if it is blocked by some long-running听selections.
Analyzing Database Access听Activities with Performance Trace
If you found (or have assumed) that the long runtime听is caused by database accesses, use the Performance听Trace tool (transaction ST05) for further analysis.听With Performance Trace you can record all accesses听to the database coming from the instance.
As you can see in the steps below, you typically start听the trace in one session (1), start the program you听want to test in another session (2), switch off the trace听after the end of the program (3), and then analyze the听recorded data (4).
听Let’s take a look at听the start screen of the Performance听Trace tool.听Before we examine the details of听using this tool, it is important to note the following听considerations:
– Performance Trace records only the information听coming from the work processes on the same听instance. Consequently, if you are using Remote听Function Calls (RFCs) or asynchronous updates听in your program, the database accesses from these听modules will not be recorded if they are executed听on other instances.
– Performance Trace can be used by only one听user at a time on each instance. Before using听the trace, please make sure that it is currently听switched off and not already in use. You can see this by the “Trace Status“听at the bottom of the start screen.
– The trace information is written to a file of a听fixed size. If the size limit of the file is reached,听the oldest data is overwritten. Therefore, it听is possible that the trace information of a long running听program will be incomplete and you will听only see the most recent data. Data from older traces听remain in the trace file only until the space is听required for new trace information.
As you can see in the screen shot above, there are five different听trace modes for the performance trace:
SQL Trace covers all database accesses.
Enqueue trace covers all enqueue operations.
RFC Trace covers all RFC communications from听and to the instance
Buffer trace covers all accesses to the table听buffers on the instance.
HTTP Trace covers听response time end to end, time spent for execution on the server, Bytes sent and recieved, and听time for data transfer both sent and received. (e.g. If you were connected via CITRIX and wanted to trace)
For the analysis of database accesses, it is sufficient听to activate just the SQL trace. The enqueue,听RFC, and buffer traces are useful for a performance听analysis if you know (or suspect) that enqueue听requests, RFCs, or accesses to the table buffers are听taking more time than they should.听When you start the trace, you can select whether听or not you want to record data from all programs听running under your user name on the instance (Activate听Trace button) or if you want to define some filters听(Activate Trace with filter button).
The screen shot below听shows your options for narrowing the听data to be traced. You can select data from programs听running under another user鈥檚 name, select or exclude听certain tables, restrict the data to a specific work听process (e.g., if you start the trace when the program听is already running), etc…
By pressing the Deactivate Trace button in the start听screen of ST05 the trace is stopped. With听Display Trace,听your options for filtering the data听to be displayed is shown. You can select the type of information听to be displayed (SQL, enqueue, RFC, buffer);听you can specify the time听period in which the data was recorded (by default it is听the period of the last recorded trace); you can select听the name of the user whose data should be displayed;听and you can select the tables you want to see (or听not see). You can select the number of trace records to display (default is usually 5,000). There are many restrictions you can enforce besides the ones I am describing. Please consult the SAP help.
Now with a click of the green check听
听we see the results below.
Let鈥檚 take a look at how to interpret all of听this performance data.听The columns shown (from left to听right) have the following meanings and the items highlighted in red, are the most important when discussing performance.
HH:MM:SS.MS 鈥� The time at which the听operation was executed
Duration听鈥� The duration of the operation
Program听鈥� The name of the ABAP program
ObjectName听鈥� The name of the affected table
Op.听鈥� The database operation
Curs听鈥� The used cursor
Array听鈥� The number of records that can be听transferred in one step from the database
Recs听鈥� The number of records that were read or听sent to the database (e.g., when inserting data)
RC听鈥� The return code from the database
Conn听鈥� The name of the database connection
Statement听鈥� The statement as it arrives at the听database
Sometimes the line that indicates the duration of听the operation is shaded in red, which means that the听duration time exceeds 100 ms. Please understand that this does听not necessarily mean there is a performance problem听with this access, as you always have to take the number听of processed records into consideration for context. For听example, reading 1,000 records in 200 ms is a good result, but reading only 5 records in 80 ms is not very good. That said, in the above听case, where the听duration time is high although the response time per听record is not, you should still check whether this high听number of records is really required 鈥� perhaps you听do not really all these records returned and can reduce the result set returned, thus boosting performance as well.
As a rule of thumb, a processing time of听up to 2-5 ms per record is okay, and thus need not听be a major concern to you at this time, even if it can be听potentially be faster.
In the column Program, you see the name of the听ABAP program where the access was coming from.听In order to navigate to the code belonging to听it, place the cursor on the line with the statement in听question and select the ABAP display button 
听or use the menu path 听Goto 鈫� Display ABAP Source, or even hit F5 key.
The column ObjectName contains the name of听the database table affected. If you want to see some听information from the DDIC (the Data dictionary) for a听particular table, place the cursor on a line containing听that table and select the DDIC info button 
, or use the menu path Goto 鈫扗DIC information, or hit F6 key.
The column Rec tells you how many database听records were read and transferred to the application听server in one step (FETCH operation). Sometimes听the result of the query consists of more records than听can be transferred in one step. In this case, you听would see several lines with repeated FETCH operations. In the example below, you can see I was executing a VA03 (Sales Order Display) for S/O # 4988. You would expect to see a REC of 1 and we do, as this Sales Order does exist.
In the final column (Statement) you see the听statement as it was handed over to the database.听This usually looks a little different from the original听ABAP statement because it was transformed by听the Open SQL layer on the instance. The complete听statement is displayed when you place the cursor听on the line with the statement in question and select听the details button , or follow the menu path Edit 鈫� Choose, or hit the F2 key. At the bottom of the statement you can see the values that were used for each variable in the WHERE statement, as well as their data type and length within the parenthesis (e.g CH, 3 is data type CHAR and a length of 3).
Summary and what’s next…
OK, we learned about the Database Performance Trace. We learned how to execute it via the transaction ST05, and the normal sequence of events when taking a trace snapshot. We saw how to filter what we wanted to trace, as well as filter the display once a trace has been taken. We also took a deep dive into the Trace results, learned about the columns and some nice shortcuts into the code, DDIC information, and SQL statement passed to the back-end DBMS. In the next Blog, we dig in and see how to find a performance issue with this trace tool.
