An overview of using the SAP R/3 Trace tool and how it can assist in Crystal Reporting
This article discusses how R/3’s SQL Trace feature may be of assistance when developing reports via the Crystal Integration Kit for SAP (the Open SQL driver feature).
Knowledge learned in this article can be put to use when:
Users require an understanding of how the SQL Trace tool is used within SAP R/3
Users require an understanding of the various methods and features of the SAP SQL tool
Users require an understanding of how to use the SAP R/3 SQL Trace tool in concert with Crystal Reports.
SQL Trace Tool Basics
The SQL Trace feature is another of R/3’s ABAP Workbench suite of tools - the Performance Trace tool. Its common usage is by Oracle DBAs and ABAP programmers for Performance Tracing (database access, locking activities and remote calls of reports and transactions) and for error/fault investigations.
ABAP programmers employ it primarily for ABAP report performance tuning and table Index verification. Our area of concern is the SQL Trace portion of the Performance Trace tool.
You can use the SQL Trace tool to perform several important tasks:
Reverse-engineer an existing SAP report to assist in data mining for Crystal Reports
Test timing and table links, vis-à-vis database hits, for performance tuning of a Crystal Report in development
Ensure top-down data retrieval from database from the Crystal Report (Crystal reads the data requests from the report from the top of the report all the way down to the bottom – it’s a top-down operation)
Ensure “read records” and “print records’ features of Crystal are properly employed
De-bugging of incorrect table links (recursive/endless loops).
As you can see, judicious usage of the SQL Trace feature can be a powerful aid toward developing lean and efficient reports – whether using Crystal alone (as in an Open SQL report) or when using a Function or an ABAP Query within your reports.
Beginning in version 3.0G, SAP released the first version of the Performance Trace tool. From Release 4.0B onward, the Performance Trace contains the following tracing features:
SQL Trace: This allows monitoring of the database access from reports and transactions
Enqueue Trace: This allows monitoring of the transaction locking system
RFC Trace: This provides information about Remote Function Calls between instances.
Note: The release of SAP R/3 -- SAP Enterprise (v4.7) contains a new feature: Memory Trace
You access the SQL Trace tool via T/C ST05.
By default, the SQL Trace tool is de-activated, or turned off. We recommend the following procedures be followed to effectively use this tool:
Start another SAP session
Within the new session, enter T/C ST05 ~ the Trace Requests screen is displayed
Return to your original session and prepare to execute your report Note: if variables are required within the report, execute the report, and have the Parameter screen appear and ready for your input(s)
Return to your other SAP session (the Trace Requests screen)
Select SQL Trace from the Trace Modes area and activate the trace by clicking on the “Trace on” button
Return to your original session (with the Crystal Report) and either execute the report (if no variables required) or enter the necessary variables and then execute the report
Once the report has successfully executed and returned with data, switch to the other session and click on the “Trace off” button
Click on the “List Trace” button to view the results of your SQL Trace for the report
Once you click the “Trace on” button, a Trace File (specifically a Ring File) is automatically created. The Trace File is what is viewed when performing a Trace Analysis.
To compare sequential traces, you must save the Trace File to your PC – otherwise it will be overwritten by each new trace you perform.
As previously mentioned, the SQL Trace tool is part of a larger suite of tools that comprise the ABAP Workbench’s Performance Trace feature. Here is a technical overview of how these tools interact:
Understanding SAP’s SQL Trace Feature
Once you have run your SQL Trace, you can immediately analyze the results of the trace.
As mentioned, the trace results are available until you execute the next trace (trace records are overwritten within the trace file). You can ensure this does not occur by saving the current trace file to your PC.
The Filter Trace List pop-up box allows you to specify which trace and filter your trace results.
When you display your trace results, you may choose between a Basic list and an Extended list. Both lists will display an overview of the logged actions and performance data.
When you use the SQL Trace tool, the results from either list type permit analysis of the following:
The SQL statements executed by the Crystal drivers
The values that the R/3 system uses for particular database access and any changes to the database (Note: Crystal does not change the database)
How the R/3 system converts ABAP Open SQL statements (such as SELECT) into Standard SQL statements for database Reads and Fetches
Where the Crystal driver may execute a COMMIT statement
Where the design of your Crystal report mandates the repetition of identical database accesses (an area of interest for us)
The total number of database accesses and any changes that occur in the update part of the Crystal application (for Crystal, this = zero updates or changes).
As mentioned, you display your SQL Trace results in one of two list types:
Basic List
Extended List
For our needs with Crystal Reports, the Basic List serves our purposes – the Extended List contains all the elements of the Basic List and has additional detailed information such as the exact time the trace was executed (to the millisecond) and the name of the program(s) that executed the trace. Therefore, we will limit our discussion to the Basic List.
Basic List —Trace Analysis
Above is the Basic List Trace Analysis screen – this was derived from an Open SQL Crystal Report executed on an SAP 4.6D System. The top line displays the following information:
The name of the Transaction (i.e., SESS)
The Process Identification number ~ PID ~ (i.e., 2240)
The Process Type (i.e., DIA = Dialog)
The SAP Client
The user’s Name
From left-to-right, here is an explanation of what the columns display:
Duration ~ runtime for the statement in milliseconds & microseconds ~ if you see a listing highlighted, this means this operation exceed the pre-set limit of 10000 milliseconds
Object ~ for an SQL Trace: Name of the database table
Op. ~ for an SQL Trace: Name of the operation performed in the database
Rec ~ for an SQL Trace: Number of records retrieved or processed and passed between the R/3 System and the database
RC ~ the Return Code of the statement
Statement ~ an abbreviated listing of the complete statement as logged
From this Basic List, we can perform the following types of analysis:
Sorting the list into meaningful collections
Display the entire formatted logged SQL Statements
Definition of the corresponding ABAP Dictionary object for SQL and any Enqueue traces
Display the logged statement "in situ" or as it appears within the program code
Display the access plan that was used for a logged SQL Statement
List identical select statements in the trace list ~ very handy
Summarize the trace list ~ good for memorialization and trace comparison
Switch between the two list types
As can be seen within the OP. column, the operation type performed is critical to our understanding of how Crystal is accessing and retrieving the SAP data (especially when using the Open SQL driver) – therefore, here is a brief overview of what each SQL Statement means as used within this context:
Next, we examine how these SQL Statements operate. To begin, R/3 database application operations are not only related, but always occur in the same logical order:
The DECLARE function defines a Cursor ID and assigns a number to it. DECLARE is always followed by PREPARE
PREPARE takes an SQL Statement and determines the appropriate database access method to use ~ therefore, it prepares the SQL Statement for passing to the database. At this stage, the R/3 System is restricted to the consideration of the structure of the SQL Statement (valid or not) and not with the values that it contains
The OPEN function takes the prepared SQL Statement and adds the relevant values to it
FETCH passes the entries from the database application to the database interface of the R/3 System. All database operations required to execute an SQL statement are always linked via the Cursor ID
If the SQL statement makes changes in the database (INSERT, UPDATE, DELETE), PREPARE is followed by EXEC, which executes the statement (Note: Crystal will not do this, whereas an ABAP program could)
If the system can refer back to an SQL Statement that has already been prepared, there will not be a repeat PREPARE operation ~ therefore, the SQL Statement is executed using the REOPEN or REEXEC Statements where appropriate.
Looking through a typical SQL Trace can be confusing – however, SAP has provided some helpful tools to assist us with this chore ~ specifically, the Summarization pop-up box and ABAP Dictionary information regarding an object and Identical Selects identification. Each is self explanatory in its operation.
As with many reports provided by SAP, you’ll have to scroll-across the screen to see all of the information it provides:
You can also view replaced variables.
By using the Selection for objects feature, you can filter the results even further – allowing just the objects of interest to you to be viewed. For example, from the Detailed Statement screen above, we will select one R/3 object to focus on ~ in this example; the object is the transparent table VBAK:
The list is restricted to this one object and sorted in output sequence – take notice the multiple REOPENS (this is only a partial screenshot). We should investigate the links being used within the Open SQL driver query (Left, Equal or Right) – perhaps a sub-report should be employed to capture this data and increase the report’s performance – or perhaps using an ABAP Query for specific portions of the report instead of the Open SQL for the entire report?
These are just some of the insights the SQL Trace tool offers you. Experimentation and investigation will illustrate other uses.
Written by Geoffry C. Houze, Data Management Group SAP Practice Manager
Call 888.394.1664 to find out how Data Management Group can help you with all your business intelligence needs.
To find out how we can help you solve your information challenges, visit our professional services pages:
