Analyse Problem - SAP Tools

Analyzing Database Access

Active Global Support

March 2009

1
8. Analyzing Database Access

Analyzing
Introduction
ABAP Messages

Analyzing
Basic Tools
Authorization Errors

Analyzing
Screens & Interfaces
Database Access

Data Objects Analyzing

ABAP Program Terminations

Program Modularization New ABAP Debugger

2
8. Analyzing Database Access:
Contents/Objectives

Contents
„ SQL-Trace – Overview
„ Tracing Database Access
„ Basic Analysis of SQL Traces

Objectives
At the end of this unit, you will be able to:
„ Record an SQL trace
„ Analyze your program with respect to
„ Accessed tables
„ Involved ABAP programs
„ Long running statements
„ Database commits

3
SQL-Trace (ST05)

ST05 traces
„ every SQL-statement that is executed on DB
„ statements that cannot be analyzed using the debugger

ST05 answers questions like:

„ Where is my data written to / read from ?
„ Where is the ABAP Statement I am looking for?
„ When is the COMMIT being executed?
„ How does the DB execute my ABAP statement ?
„ Why is a table access slow ?

4
 How to Start the SQL-Trace

Hint: Keep the trace small !

The SQL Trace is started using transaction ST05

The trace records every database access that occurs on the Instance.

Only one trace can be performed at a time.

To ensure readability of the recorded trace it is a MUST to keep the recording as short as
possible:
„ Use Filter settings, such as User name
„ If possible use two different users, one for tracing, the other for executing the transaction
that is to be traced
„ Switch the trace on directly before you perform the relevant action. Do not trace a
complete Transaction.

A new trace will overwrite the trace file. To keep the trace you should either download the
file or change to a new trace file in transaction ST01.

5
 How to Stop And List the SQL Trace

1) Stop it

2) List it

3) Specify your list options

Note:
If you want to list the trace you need to be on the same server where the
trace has been started!

Note the time, the server and the file for later listing.

6
 The SQL-Trace Output

Complete SQL-Statement
and Where-Clause

Database Table Number of Records

Duration
Time stamp

To get the full trace information use the Extended List. In contrast to the Basic List the
Extended List also contains Information on e.g. the program name.

The trace list includes information on: Transaction, Process ID (same as in SM50 or
SM66), Client and User

Time stamp off the statements and their duration in micro seconds

Program name and table name

Type of operation (technical; this does not correspond to Insert, Select, etc, but to the
operation at DB level)

Number of records that has been transferred between DB and ABAP program

Detailed Information about the Statement. Note that usually you find more than one entry
for each statement. This is for technical reasons.

A sort is possible with respect to

„ Output sequence (recommended)
„ Process IDs

7
Demo: Perform and Analyze an SQL-Trace

Q: What table is storing the Tcode of your

Favorites in the
Easy Access Menu!

Create an SQL trace and find out!

8
 Procedure: Perform and Analyze an SQL-Trace

1. Switch on the SQL-Trace for your user and create a new Favorite

2. Search for INSERT

3. Find the statement

4. Double-click to verify
the details of the
statement

Using a Double-click or the corresponding field in the Application Toolbar allows you to
navigate
„ To the corresponding statement in the ABAP program (ABAP display)
„ To the definition of the database table in the ABAP Dictionary (DDIC info)
„ To the details of the execution at DB level (Explain)

9
Search Strategies

Other Search Strategies:

Search for Object or Statement

Use a filter Edit – Selection for Objects

Use the Statement Summary Go to – Statement Summary

10
Analyzing Database Access: Summary

Now you are able to:

„ Record an SQL trace

„ Analyze an Application with respect to
„ Accessed tables
„ Involved ABAP programs
„ Long running statements
„ Database commits

11
Copyright 2009 SAP AG
All Rights Reserved

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein
may be changed without prior notice.
Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries,
eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+,
POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex,
MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.
Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other
countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
Java is a registered trademark of Sun Microsystems, Inc.
JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos
are trademarks or registered trademarks of SAP AG in Germany and other countries.
Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and
services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries.
Business Objects is an SAP company.
All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only.
National product specifications may vary.
These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only,
without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group
products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed
as constituting an additional warrant.

12