Analyzing

SQL Statements

Analyzing
SQL
Statements

© SAP AG EWB10 Analyzing SQL Statements - 1

 Copyright 2003 SAP AG. All Rights Reserved

n © Copyright 2003 SAP AG. All rights reserved.

n 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.
n Some software products marketed by SAP AG and its distributors c ontain proprietary software components of other
software vendors.
n Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of
Microsoft Corporation.
n IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®, OS/390®,
OS/400®, iSeries , pSeries, xSeries , zSeries , z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity ®, Tivoli®, Informix
and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA and/or other countries.
n ORACLE® is a registered trademark of ORACLE Corporation.
n UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.
n Citrix®, the Citrix logo, ICA ®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® and
other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
n HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium,
Massachusetts Institute of Technology.
n JAVA® is a registered trademark of Sun Microsystems, Inc.
n JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and
implemented by Netscape.
n SAP, SAP Logo, R/2, RIVA, R/3, SAP ArchiveLink, SAP Business Workflow, WebFlow , SAP EarlyWatch, BAPI,
SAPPHIRE, Management Cockpit, mySAP, [Link], 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 in several other
countries all over the world. MarketSet and Enterprise Buyer are jointly owned trademarks of SAP Markets and
Commerce One. All other product and service names mentioned are the trademarks of their respective owners.

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 2

© SAP AG EWB10 Analyzing SQL Statements - 2

 Analyzing
Unit: Analyzing SQL Statements SQL Statements

Contents:
1. Recognizing expensive statements

2. Analyzing the Shared SQL Area

3. Using the Explain function

4. Table statistics

5. Using the ABAP Dictionary

Objectives:
1. At the end of this unit you will be able to:
2. Determine if and why a statement is expensive, using the:
1. Shared SQL Area
2. Explain Function

3. Use the ABAP Dictionary

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 3

n This unit describes how to:

• Recognize the different types of expensive SQL statements
• Use the Explain function
• Check table statistics
• Use the ABAP Dictionary
n Once you have completed this unit, you will be able to use the analysis tools to determine if and
why statements are expensive.

© SAP AG EWB10 Analyzing SQL Statements - 3

 SQL Statements
Analyzing
SQL Statements

SELECT MANDT, VBELN, OBJNR FROM ZVBAK

WHERE MANDT = 001 AND ERNAM = ERNIE

MANDT VBELN OBJNR ERNAM

001 000013245 654875454 ERNIE Records specified by

001 000013246 697935435 FRED the WHERE clause
001 000013247 673225747 JAMES
001 000013248 216356543 GUSS
001 000013249 687535435 ERNIE
001 000013250 258674802 KURT
001 000113245 983231365 MAX
Search area
001 000113251 522112486 BERT
(the data that is
001 000113252 325348654 BERT
searched through for
002 000013249 241567685 HUGO
the requested data)
002 000013250 968351332 FRED
002 000013251 874352321 MAX
002 000013252 544546546 GUSS

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 4

n The WHERE clause specifies which records are searched from these tables. In the case of joins,
these records are also restricted by the join condition. The fie lds specified in the WHERE clause
also determine which indexes are used.
n The search area is the set of data (records) that are searched to evaluate the query. This set is not
explicitly specified in the query. It is determined by the optimizer when the statement is evaluated,
using the indexes of the tables.
n The goal of the database optimizer is to reduce the search area as much as possible. This can be
achieved by SQL statement tuning or by technical tuning such as creating indexes or changing
view definitions.

© SAP AG EWB10 Analyzing SQL Statements - 4

 Expensive SQL Statements
Analyzing
SQL Statements

SELECT MANDT, VBELN, OBJNR FROM ZVBAK

WHERE MANDT = 001 AND ERNAM = ERNIE

Type 1: The optimizer chooses an unsuitable access path

Symptom: Many buffer gets but only a few records per execution

MANDT VBELN OBJNR ERNAM

001 000013245 654875454 ERNIE Records specified by

001 000013246 697935435 FRED the WHERE clause
001 000013247 673225747 JAMES
001 000013248 216356543 GUSS
001 000013249 687535435 ERNIE
001 000013250 258674802 KURT
001 000113245 983231365 MAX
Search area
001 000113251 522112486 BERT
(the data that is
001 000113252 325348654 BERT
searched through for
002 000013249 241567685 HUGO
the requested data)
002 000013250 968351332 FRED
002 000013251 874352321 MAX
002 000013252 544546546 GUSS

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 5

n When you analyze a statement, classify the statement first, to find the possible tuning methods.
n There are two different types of statements:
Type 1 statements search through a lot of data for only a few qualified records. Here the
optimizer chooses an unsuitable path to access the data.
Type 2 statements request a lot of records. Here the optimizer chooses a suitable access
path.
n In this example, the application requests the records of table ZVBAK where MANDT (the
client) equals 001 and the field ERNAM (the name of person who created the record or object)
is 'ERNIE'. Many records are read, although only two are requested. Therefore, this statement is
of Type 1.

© SAP AG EWB10 Analyzing SQL Statements - 5

 Analyzing
Expensive SQL Statements SQL Statements

SELECT MANDT, VBELN, OBJNR FROM ZVBAK

WHERE MANDT = 001 AND ERNAM = ERNIE

Type 1: The optimizer chooses an unsuitable access path

Symptom: Many buffer gets but only a few records per execution

Statements of type 1 may be accelerated by:

n Update of optimizer statistics

n Change of the ABAP code

n Optimizing user input

n Creating an index

n Extending an existing index

n Droping an existing index

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 6

n Statements of type 1 may be accelerated by one of the following:

• Update of optimizer statistics
• Changing the ABAP code, for example specify additional fields in the WHERE clause
so that an existing index is chosen by the optimizer, read the data from a different table
where an appropriate index exists, or avoid reading the data.
• Optimizing the user input
• Create a new index
• Extend an existing index
• Drop an existing index
Caution: The performance of other statements may suffer if an index is created, extended, or
dropped.

© SAP AG EWB10 Analyzing SQL Statements - 6

 Expensive SQL Statements
Analyzing
SQL Statements

SELECT MANDT, VBELN, OBJNR FROM ZVBAK

WHERE MANDT = 001

Type 2: The optimizer chooses a suitable access path

Symptom: Many buffer gets and many records per execution

MANDT VBELN OBJNR ERNAM

001 000013245 654875454 ERNIE Records specified by

001 000013246 697935435 FRED the WHERE clause
001 000013247 673225747 JAMES
001 000013248 216356543 GUSS
001 000013249 687535435 ERNIE
001 000013250 258674802 KURT
001 000113245 983231365 MAX
Search area
001 000113251 522112486 BERT
(the data that is
001 000113252 325348654 BERT
searched through for
002 000013249 241567685 HUGO
the requested data)
002 000013250 968351332 FRED
002 000013251 874352321 MAX
002 000013252 544546546 GUSS

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 7

n In this example, the application requests all records of table ZVBAK where MANDT = 001.
Because the database returns all the records that are read, this statement is of type 2.

© SAP AG EWB10 Analyzing SQL Statements - 7

 Expensive SQL Statements
Analyzing
SQL Statements

SELECT MANDT, VBELN, OBJNR FROM ZVBAK

WHERE MANDT = 001

Type 2: The optimizer chooses a suitable access path

Symptom: Many buffer gets and many records per execution

Statements of type 2 may be accelerated by:

n Change of the ABAP code

n Tuning of the business process

n Optimizing user input

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 8

n A statement of type 2, that returns many records, cannot be accelerated by using an index. To
accelerate this type of statement, you can:
• Tune the ABAP report
• Tune the business process
• Optimize the user input

© SAP AG EWB10 Analyzing SQL Statements - 8

 Analyzing the Shared SQL Area
Analyzing
SQL Statements

Check database activity since

startup
Sort for the following criteria: Database server
n Buffer gets
Expensive statements for DBMS Database
overall performance processes buffer
n Disk reads
Expensive statements
for I/O performance Buffer gets
n Records
Expensive statements for Records
database and application
server performance

Every statement has its own Disk reads

hit ratio
Application
server

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 9

n Before you analyze the Shared SQL Area, check that there have been well over a million user
calls. Then the database has processed enough statements to make the analysis meaningful.
n A statement that causes a lot of buffer gets is expensive with respect to system resource
consumption. Such statements can reduce overall database performance.
n A statement that causes a lot of disk reads can critically affect the system I/O performance and
reduce overall system performance.
n A statement that reads a lot of records can reduce the performance of both the database and
application servers. Statements of this type may be coded inefficiently.
n Every SQL statement has its own hit ratio.
n Terminology:
• Buffer gets are called reads in the Database Overview Monitor
• Disk reads are called physical reads in the Database Overview Monitor

© SAP AG EWB10 Analyzing SQL Statements - 9

 Analyzing
Buffer Gets SQL Statements

Check statements for which the number of

buffer gets exceeds 5% of the total reads

Note: The load on

the data buffer
depends only on
the number of
buffer gets. It is
Database Database
buffer independent from buffer
the number of
executions

Few executions
Many with many buffer
executions gets per execution

Application Application
server server

Requested record Buffer get Database request

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 10

n To display the statements that cause the highest database load, sort by buffer gets.
n The statements at the top of the list cause the highest consumption of database resources. These
statements are either executed very often, with a small number of buffer gets per execution, or
they have a very high number of buffer gets per execution. These statements can reduce the
global database performance.
n An R/3 System has expensive statements if:
• The number of buffer gets for the topmost statement exceeds 5% of the total number of
reads.
• The ratio of reads to user calls is greater than 15.
n Check any statements for which the number of buffer gets exceeds 5% of the total reads.
n If the ratio of reads to user calls is much greater than 15, check at least the topmost statements.
n If there are many expensive statements, each statement has only a small fraction of the total
number of reads. In this case, the ratio of user calls to reads is the best indicator of expensive
statements.

© SAP AG EWB10 Analyzing SQL Statements - 10

 Analyzing
Buffer Gets per Execution SQL Statements

Statements with many buffer

gets per execution
Many buffer gets
n Have high consumption of per execution
system resources during
execution
n Should have a low number
of executions
n Should not be executed
during peak hours
Database
buffer

Application
server

Requested record Buffer get Database request

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 11

n The statements that cause a high database load while being executed have a high number of
buffer gets per execution. These statements can reduce the performance of all other statements
running at the same time. If these statements are are executed during background operation,
when online users are not logged on to the system, they are less critical.
n To determine when the statement was executed for the first time, choose Next info from the
Shared SQL Area and check column 1. Load Time.

© SAP AG EWB10 Analyzing SQL Statements - 11

 Analyzing
Buffer Gets per Record SQL Statements

High number of buffer gets per record

→ No appropriate index

Records specified by
the WHERE clause Database
buffer

Search area
(the data that is
searched through
for the requested
data)

Application
server

Requested record Buffer get Database request

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 12

n Statements with a high number of buffer gets per record (compared to the size of a record) use
an unsuitable access path. This means that the search area is unnecessarily large.
n However, if the records returned by the statement are often zero, the number of buffer gets per
record can be very large although the statement is executed with a suitable access path.
Therefore, always compare the number of records processed to the number of executions. If the
buffer gets per execution is low, this statement uses a suitable access path.
n Our experience shows that statements, which often return no records, can be avoided by coding
changes.

© SAP AG EWB10 Analyzing SQL Statements - 12

 Analyzing
Records per Execution SQL Statements

Statements with a high number of records

per execution may use suboptimal logic

Example:
ABAP coding:

SELECT * FROM MARA.

Database
CHECK MARA-MATNR = 12345. buffer
...
ENDSELECT.

Application
server

Requested record Buffer get Database request

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 13

n Statements with a high number of records per execution may be coded inefficiently.
n In this example, the complete table MARA is read from the database to the application server
when only one record is required.
n Check if column SQL Sort is greater than 0. Sort operations must be avoided for statements that
process many records.

© SAP AG EWB10 Analyzing SQL Statements - 13

 Analyzing
Disk Reads SQL Statements

Check statements where the number of disk reads

exceeds 2% of the total physical reads

Symptoms:
n High number of disk reads
→ Degraded I/O performance Database server

n Hit rate for the statement is low DBMS processes Database buffer

Causes:
n Large tables
n Low number of rows per block
n Data is aged out of the buffer
n Many full table scans performed for this table
Disk reads

Requested record Buffer get Database request

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 14

n To display the statements that critically affect system I/O performance, sort by disk reads.
n If the statement cannot be tuned by any other means, it may be advantageous to create
additional tablespaces for the tables that have expensive disk accesses and to locate these
tablespaces on separate disks.

© SAP AG EWB10 Analyzing SQL Statements - 14

 Analyzing
Statement Details SQL Statements

Full SQL Bind variable

Statement
 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 15

n To display the full SQL statement, double -click the statement in the Shared SQL Area.
n Bind variables are passed from R/3 to Oracle during the parsing of the statement. The values
for the statement are passed to the database at a later point in time when the data is retrieved.
Using bind variables allows Oracle to reuse statements in the Shared SQL Area for queries with
the same structure but different values. If the values of the query were passed to the database
without using bind variables, the statement could not be reused.

© SAP AG EWB10 Analyzing SQL Statements - 15

 Display the Execution Plan for an Analyzing

SQL Statement
SQL Statements

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 16

n To display the execution plan of an SQL statement, choose Explain.

n Estimated Costs indicates the costs calculated by the optimizer for this access path. The number
represents either the expected number of buffer gets that must be performed, or the expected
number of disk accesses. The CPU time necessary to process the statement does not greatly
influence the estimated costs.
n Before you analyze a statement further, classify it:
Type 1 statements read records inefficiently.
Before trying to tune these statements, check if the table statistics are current.
Type 2 statements return many records per execution.
For these statements, you cannot tune the database access path. To tune them, you must
change the ABAP reports.

© SAP AG EWB10 Analyzing SQL Statements - 16

 The ABAP Dictionary (Transaction SE12)
Analyzing
SQL Statements

Tables accessed by ABAP

statements are declared in the
ABAP Dictionary

The ABAP Dictionary displays

information about tables, views,
and indexes

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 17

n To display table fields or indexes, use the ABAP Dictionary (Transaction SE12) or choose DDIC
Info from the Shared SQL Area screen.

© SAP AG EWB10 Analyzing SQL Statements - 17

 Summary of this Unit
Analyzing
SQL Statements

Now you are able to:

Recognize expensive statements

Analyze the Shared SQL Area

Use the Explain function

Use the ABAP Dictionary

 SAP AG 2003, SQL Statement Optimization for SAP Systems on Oracle / 18

Now you are able to:

n Recognize expensive statements

n Analyze the Shared SQL Area
n Use the Explain function
n Use the ABAP Dictionary

© SAP AG EWB10 Analyzing SQL Statements - 18