8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning Browse Get Certified My Learning Subscribe Explore SAP
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
69 mins
Measuring Runtime Consumption
Analyzing
with ABAP Profiling Database Access
with SQL Trace
67 mins
Analyzing Database Access with SQL
Trace
37 mins
Objectives
QuizAfter completing this lesson, you will be able to:
Start the SQL trace
Analyze SQL trace results
Using Data Types and Type
Conversions Correctly
The SQL Trace
Processing Character Fields
The SQL Trace is used to centrally monitor the entire traffic between the ABAP
system and the database. You can activate and deactivate the trace from
inside ABAP development tools. but to analyze trace results, you have to start
Using Code Pushdown
the Technical in ABAP
Monitor Cockpit, SQL
a Browser-based application.
The SQL Trace is not restricted to a single user, client, or server instance. If
there is more than one database connected to the system, it traces the traffic
Improving Internal
for all these Table
databases.
Performance
Implementing Authorization Checks
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 1/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning Subscribe
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
In the Trace Result, you can display information about individual SQL
statements, like the time consumption on the database or the number of
records accessed. You can also analyze the native SQL syntax, that is, the
statement in the form in which it reached the database.
Some restrictions apply when working with the SQL Trace. Only one user can
switch the trace on at same time. And when you activate the trace from ABAP
Development Tools there is no way to add recording filters.
SQL Trace Results
In this video, you will see how to start an SQL Trace and analyze the result.
Note
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 2/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
We will discuss the Prepared Plan and Executed Plan tabs later in
Learning Subscribe
this course.
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
Work with the SQL Trace Tool
By profiling your ABAP code you discovered that access to the database is a
major contributor to the overall runtime. You now use the SQL Trace tool to
analyze the database access on your program in more details and to further
improve the performance of your code.
Template:
/LRN/CL_S4D401_ATS_PROFILING (Global Class)
Solution:
/LRN/CL_S4D401_ATS_SQL_TRACE (Global Class)
Task 1: Copy Template (Optional)
Copy the template class. If you finished the previous exercise, you can skip
this task and continue editing your class ZCL_##_SOLUTION .
Steps
1. Copy class /LRN/CL_S4D401_ATS_PROFILING to a class in your own package
(suggested name: ZCL_##_SOLUTION , where ## stands for your group
number).
a. In the Project Explorer view, right-click class /LRN/CL_S4D401_ATS_PROFILING
to open the context menu.
b. From the context menu, choose Duplicate ....
c. Enter the name of your package in the Package field. In the Name field,
enter the name ZCL_##_SOLUTION , where ## stands for your group
number.
d. Adjust the description and choose Next.
e. Confirm the transport request and choose Finish.
2. Activate the copy.
a. Press Ctrl + F3 to activate the class.
Task 2: Perform an SQL Trace
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 3/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
To further analyze the performance issues, you activate the SQL Trace and
Learning Subscribe
execute your ABAP class ZCL_##_SOLUTION as a console app.
Within the same ABAP instance, only one user can switch on the SQL Trace at
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
a time. To also give other users a chance, make sure you switch of the SQL
Trace as soon as you finish executing the console app.
Steps
1. If it is not open, open your ABAP class ZCL_##_SOLUTION in the editor.
a. Press Ctrl + Shift + A and enter the name of your ABAP class.
b. Choose OK.
2. Open the SQL Trace State window for your ABAP cloud project.
a. In the Project Explorer view on the left, right-click your cloud project to
open the context menu.
b. Choose SQL Trace ....
3. Check the current SQL Trace State. If the trace is switched on, close the
dialog window and come back in a few minutes.
a. Choose OK to close the dialog window.
4. If the trace is currently switched off, switch it on. Then close the dialog
window.
a. Choose Activate to switch on the SQL trace.
b. Choose OK to close the dialog window.
5. Execute your global class ZCL_##_SOLUTION as console app.
a. Press F9 to execute the ABAP class as console app.
6. As soon as the application has finished, switch off the SQL trace and open
the trace directory.
a. As soon as the green progress indicator in the bottom right corner
disappears, right-click your cloud project in the Project Explorer and
choose SQL Trace ....
b. Choose Deactivate to swithc off the trace.
c. Choose View Trace Directory to display a list of your SQL traces.
Task 3: Analyze the Trace Result
Analyze the SQL Trace result. Filter for database accesses that originate from
your ABAP class and find out which database tables have been accessed for
how often.
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 4/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Steps
Learning Subscribe
1. On the Technical Monitor Cockpit, display the trace records of the newest
trace.
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
Hint
By default, the list of traces is sorted descending by column
Time Stamp.
a. Choose the topmost list entry.
b. Choose Trace Records.
2. Analyze the list of trace records.
a. You find the displayed number of rows above the header of column
Time Stamp.
b. You find the maximum number of rows that are displayed next to the Go
button.
3. Filter the list of trace records. Only display records that originate from your
ABAP class ZCL_##_SOLUTION.
Hint
We recommend that you use the selection filter left from the Go
button and not the display filter that you can set by choosing the
header of column Program.
a. Choose Filter (...) from the toolbar on the left of the Go button.
b. Choose Program from the drop down list at the top of the dialog
window.
c. Make sure that the Like option is selected.
d. Replace the input prompt Enter value with the name of your ABAP class
( Z00_SOLUTION ).
e. Choose OK.
f. Choose Go to refresh the selection.
4. Sort to the list of trace records. Find the SQL statements that accessed the
most records.
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 5/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning Subscribe
Note
By default, the list of trace records is sorted by the Time Stamp
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
column. If you want to sort it using the Duration column, you
have to remove the existing sorting first.
a. Choose the three horizontal dots on the right of the Go button.
b. Choose Reset Sort.
c. Choose Sort(0) on the left of the Go button.
d. Find the Accessed Records [#] column. Tick the box on the left and
choose the button with the downward arrow.
e. Choose OK to close the dialog window.
f. Choose Go.
5. Remove the sorting by the Accessed Records column and sort the list
descending by the Duration column.
a. Choose the three horizontal dots on the right of the Go button.
b. Choose Reset Sort.
c. Choose Sort(0) on the left of the Go button.
d. Find the Duration [µs] column. Tick the box on the left and choose the
button with the downward arrow.
e. Choose OK to close the dialog window.
f. Choose Go.
6. For the most expensive single record statement, navigate to the SQL
statement display.
a. Place the cursor on the row with value 1 in the Accessed Records
column and the largest value in the Duration column.
b. Choose SQL Statement from the navigation pane on the top.
c. You find the table name in the FROM clause.
7. Open the SELECT statement in the ABAP development tools.
a. Choose Show ABAP Source in ADT.
Task 4: Improve the Code
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 6/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
To further improve the performance of your code, eliminate the repeated
Learning Subscribe
SELECT SINGLE access to database table /LRN/CONNECTION. You know that
table /LRN/CONNECTION contains a relatively small number of records.
/ Browse Therefore,
/ Learningyou decide/ toAcquiring
Journeys buffer the entire
Core ABAPcontent
Skills / ofAnalyzing
the database table
Database in a…
Acces
static attribute (suggested name: connections_buffer ). You fill the buffer in the
static constructor (method class_constructor ) and replace the SELECT
SINGLE statement in the instance constructor with a read access to the buffer.
Hint
The SELECT SINGLE access to database table /LRN/CONNECTION
is located in the constructor method of local class
lcl_passenger_flight .
Steps
1. Navigate to the definition of attribute connection_details and from there to
the definition of structure type st_connection_details .
a. In the FROM clause of the SINGLE SELECT statement, place the cursor
on connection_details and press F3 . Alternatively, you can hold down
the Ctrl key and choose connection_details .
b. In the DATA statement, place the cursor on st_connection_details and
press F3 . Alternatively, you can hold down the Ctrl key and choose
st_connection_details .
2. Copy public structure type st_connection_details to a new private structure
type (suggested name: st_connections_buffer ).
a. Select the entire TYPES: statement (including row END OF
st_conection_details. ) and press Ctrl + C .
b. Scroll down to the private section, place the cursor above the
ENDCLASS statement and press Ctrl + V to insert the copied code.
c. In the inserted code, replace st_connection_details with
st_connections_buffer .
3. At the beginning of the component list of st_connections_buffer , add the
key fields of database table /LRN/CONNECTION.
a. Adjust the code as follows:
Code Snippet
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 7/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning 1 Subscribe
2 TYPES:
3
/ Browse / Learning Journeys / BEGIN OF st_connections_buffer,
Acquiring Core ABAP Skills / Analyzing Database Acces…
4 carrier_id TYPE /dmo/carrier_id,
5 connection_id TYPE /dmo/connection_id,
6 airport_from_id TYPE /dmo/airport_from_id
7 airport_to_id TYPE /dmo/airport_to_id,
8 departure_time TYPE /dmo/flight_departur
9 arrival_time TYPE /dmo/flight_departur
10 END OF st_connections_buffer.
11
4. Declare a private static attribute connections_buffer as an internal table
with st_connections_buffer as row type.
a. At the end of the class definition, add the following code:
Code Snippet
1
2 CLASS-DATA connections_buffer TYPE TABLE OF st_c
3
5. Use a quick fix to generate a static constructor (class constructor) for class
lcl_passenger_flight .
a. Scroll up to statement CLASS lcl_passenger_flight DEFINITION. .
b. Place the cursor on lcl_passenger_flight and press Ctrl + 1 to display
a list of available quick fixes.
c. From the list, choose Generate class constructor.
6. Edit the implementation of method class_constructor . Implement a SELECT
statement that reads all records from database table /LRN/CONNECTION
into the connections_buffer attribute.
a. Adjust the code as follows:
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 8/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning
Code Snippet
Subscribe
1
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
2 METHOD class_constructor.
3
4 SELECT
5 FROM /lrn/connection
6 FIELDS carrier_id, connection_id,
7 airport_from_id, airport_to_id, depart
8 INTO TABLE @connections_buffer.
9
10 ENDMETHOD.
11
7. In the implementation of method constructor , replace the SELECT SINGLE
statement with a read access to the connections_buffer .
Note
Keep in mind that attribute connection_details has fewer
components than the row type of the connections_buffer
attribute.
a. Select the entire SELECT SINGLE statement and choose Ctrl + < to
add a comment sign in front of each row.
b. After the commented SELECT SINGLE statement, add the following
code:
Code Snippet
1
2 * SELECT SINGLE
3 * FROM /lrn/connection
4 * FIELDS airport_from_id, airport_to_id,
5 * WHERE carrier_id = @carrier_id
6 * AND connection_id = @connection_id
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376cec34 9/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
7 * INTO @connection_details .
Learning Subscribe
8
9 connection_details = CORRESPONDING #( co
/ Browse / Learning10
Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
11
12 ).
13
14
Task 5: Re-check the Code
Activate and test your global class. Execute the class as console app. Repeat
the profiling to measure the performance improvement.
Note
If you want, you can re-run the unit test and the ABAP Test Cockpit
analysis to make sure you did not introduce any new issues.
Steps
1. Activate your global class and execute it as console app.
a. Press Ctrl + F3 to activate the class.
b. Press F9 to execute the class as console app.
2. Start the runtime measurement with the ABAP Profiler.
a. Right-click in the editor and choose Profile As → ABAP Application
(Console).
b. In the Trace Parameters dialog box, choose Finish without changing any
of the settings.
3. Switch to the Profiler perspective and analyze the trace overview.
a. Choose Window → Perspective → Open Perspective → ABAP Profiling.
b. In the ABAP traces view, choose Refresh. Alternatively, you can place the
cursor anywhere in the ABAP Traces view and press F5 to re-load the
list of trace results.
c. Double-click the most recent trace (this is the topmost entry under your
project).
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376ce… 10/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
d. The overall runtime and the distribution between ABAP and Database
Learning
are displayed in the lower section of Subscribe
the Overview.
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
Learning
Continue to quiz
Quick Links
Download Catalog (CSV, JSON, XLSX, XML)
Was this lesson helpful? Yes No
SAP Learning Hub
SAP Training Shop
SAP Developer Center
SAP Community
Newsletter
Learning Support
Get Support
Share Feedback
About SAP
Company Information
Copyright
Trademark
Worldwide Directory
Careers
News and Press
Site Information
Privacy
Terms of Use
Legal Disclosure
Do Not Share/sell My Personal Information (us Learners Only)
Cookie Preferences
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376ce… 11/12
8/15/25, 10:18 AM Analyzing Database Access with SQL Trace
Learning Subscribe
/ Browse / Learning Journeys / Acquiring Core ABAP Skills / Analyzing Database Acces…
https://learning.sap.com/learning-journeys/acquire-core-abap-skills/analyzing-database-access-with-sql-trace_bb8cab9a-aacc-435f-9dfd-c9da376ce… 12/12
