sap sql optimization - typical approach · sap sql optimization – typical approach hardware...

SAP SQL Optimization

- Typical Approach Kerstin Knebusch Active Global Support

Month 06, 2014 Public

© 2014 SAP AG. All rights reserved. 2

SQL Statement Optimization Why bothering?

Database guys just trying to look important, again?

What is an SAP system doing?

Selecting data

Inserting, deleting or changing data

More than 50% of the overall processing time is very often spend on the

database.

Lots of the CPU’s and a big portion of the memory are used for obtaining the

results! Expensive I/O subsystems for disk operations are needed.

Focus on optimizing SQL statements to get the most out of the investment!


SAP SQL Optimization – Typical Trigger for the Analysis

Batch run taking X hours more than available until customer or business partner

expects the result.

DB server running out of resources, but more load needs to be added.

End users complaining about response times.

Golive of:

New location with more load and “more or less” the same usage

New application area

Changed code due to transport packages, SP implementations, upgrade, …

Upgrade of SAP kernel or database version

After migration to a new datacenter

Short: We either have a problem or expect a problem!


SAP SQL Optimization – Typical Approach

Golden rules: …. Don’t panic ….. Don’t judge too quickly !

Check for hardware bottlenecks (CPU, memory, network)

Check configuration issues (SAP and Oracle parameter settings, object settings,

configured tasks any settings impacting performance)

Identify statements related to top load or time critical processing window or program

Focus on statements causing high total runtime and / or high wait time based on Oracle

workload repository integrated in SAP environment or traces of critical runs.

Understand the context!

Typical

statements:

We implemented it and now, it is slower than ever before !

Since the upgrade it does not work anymore ….

All statements might be correct,

but the conclusion might be wrong!

We need to get the facts and start the analysis from there.



Hardware Bottlenecks (CPU, Memory)

Check for hardware bottlenecks (CPU, memory):

Most of the times someone can check it somewhere.

However, often a history is not kept, even for the last week.

SAP Basis administration team does not have access to OS level tools.

You can also use ARW data for the database server.

Use the tools provided by SAP to have access and configure it for the service tools:

1147334 CPH activation for SAP Remote Services

994025 Virtualization for OS monitor on AIX

You need to maintain a schema for the CPH data collection.

http://help.sap.com/saphelp_nw2004s/helpdata/de/19/4c773dd1210968e10000000a114084/frame

set.htm

You may also use /sdf/mon for more detailed monitoring

But where

is he?

Solution manager

Based on ST06N /sdf/mon

90% CPU load

90% database time

http://help.sap.com/saphelp_nw2004s/helpdata/de/19/4c773dd1210968e10000000a114084/frameset.htm








Hardware Bottlenecks (CPU, Memory)

Scripts to select from Oracle history

1438410 script collection

Resources_CPU_CPUPerAWRInterval.txt

Resources_CPU_CPUPerAWRIntervalWithout ASH.txt

System Statistics_CPU.txt

Based on Oracle AWR

data




Hardware Bottlenecks (Network)

Check for hardware bottlenecks (network):

Most of the times no tools and no specialist is available.

What can be done first without causing high costs or effort?

Check time difference between SQL trace (ST05) and elapsed time in cache or history on database

(e.g.: ELAPSED_TIME in V$SQL)

Check network time with niping – see note for details: 500235 - Network Diagnosis with NIPING

Measurement on the same communication layer used by SAP application programs

Can be used for Round Trip Time and throughput measurements

Get long term measurements during low load, normal operation hours and peak load

times for comparison.

Sometimes it is surprising to see the RTT time difference between application servers.

Often network problems are detected too late!




Hardware Bottlenecks

Does network time matter?

Example:

Server 1: RTT: 0,500 ms,

Server 2: RTT: 1,200 ms

Server 3: RTT: 0,175 ms

Conclusion:

It depend’s, as always.

If you already have invested lots of effort in

optimization of your landscape the answer is a clear

YES, it does.

No, if you do not have the best setup of hardware in

your landscape, the old fashioned network might just

blend into it nicely.

RTT 0,500ms RTT 1,200ms





Check Memory allocation

Verify if the system is configured to over allocate memory

Checking the database memory allocation you may use best

scripts of note 1438410

Resources_Memory_Overview.txt

Resources_Paging_PagingPerAWRInterval.txt

DBACockpit – Performance – Statistical Information

The service session using the Solution Manager

Checking SAP memory allocation

ST02 – Detail – Storage

Service Session

Paging?

SAP memory allocation -> ST02

Oracle memory allocation -> script

Any other components on the system? JAVA? More systems?




I/O issues can have a significant impact on response time as well

You can use scripts (note 1438410 (SystemStatistics_LGWR.txt) ) to check or

The TPO or EWA session using the Solution Manager

(based on function module /SDF/DLD_ORA_IO_KEYFIG)



Hardware Bottlenecks – What to do?

Hardware bottlenecks identified?

Can you do anything about them quickly?

Can you reduce the load?

Turn tools off that are not needed

Any application logging that is not needed?

Any kind of logging that is not needed?

(supplemental logging?)

Autotask deactivation? Execute them less often (e.g.: monitoring).

Can you change the configuration to allocate less?

Verify the parameter settings responsible for the allocation and allocate more wisely.

Better allocate less than cause paging (e.g.: shared pool too big).

Can you change the load distribution to shift the load to other hours?

Is there some background operation that can be shifted

Use this information as guidance for planning additional load



Configuration Issues

Configuration issues?

Patches?

Note 1438410: “Configuration_Patches_SBPLevel_11g+.txt”

Parameters?

Note : 1171650: Execute attached scrip for appropriate database version

General Configuration issues?

Note 1438410: “Configuration_Overview_11g+.txt”



Configuration Issues

Example: Configuration issue causing a bottleneck

Oracle autotask: resource manager

Hinweis 1579946 - Oracle 11g: Ressourcenmanager in Version 11.2





Identify statements related to top load

Resources / tools

DBACockpit

SQLM / ATC

Service Session EWA, TPO, Self Service

RSORASTT

Scripts of note 1438410

Report: /sdf/Rsoradld_new (Transaction: /sdf/ORADLD)

Any other tool, script or source of complain




DBACockpit

Now also able to load the statements from DBA history

Set up remote connection in a system with higher SAP BASIS release and

benefit from the features for all your systems (with or without ABAP stack):

SAP _Basis 7.00 with SP18 or higher:

See note 1261329 for details

Configure database connection

Enter system in tnsnames.ora

on the DBACockpit system

Configure connection from DBAcockpit

to the target system

Make sure the authorizations are configured

as mentioned in the notes

(1456771, 1568173, 1556453, 1776682,

1727951).




SQL Monitor:

• SQLM

(shipment with SAP Basis 7.40 and

Kernel 7.21 also provided: down port

with notes)

• https://scn.sap.com/docs/DOC-47444

• /sdf/ZQLM (note 1855676)

• ST-PI SP8 July 2013

Advantage

(business case for statement

optimization)

• Identify the related main program or

transaction for statement recorded in

cache or workload repository

• What is the SQL profile of my

business process VA01?

•

https://scn.sap.com/docs/DOC-47444







Select options:

• Request type = transaction

• Tables




Program or Transaction ranked by total db time

Drill down to SQL list of

program

(98 statements in this case)

Navigate to code

It is great for

• identifying the top statements of the system or

• identifying the top statements system wide for a transaction or program,

but you cannot directly navigate to the SQL statements.




Merge info from database and ABAP kernel to identify the origin of a statement

/SDF/ZQLM

SQL Cache




ATC: (ABAP Test Cockpit integrates

with Code Inspector) http://scn.sap.com/docs/DOC-31773

ATC availability starts with NW 702 SP12 /

NW 731 SP5, enhanced with 7.40 SP3

1912445 – Inspector Variants

Limit the selection using the where clause.

Unsecure use of for all entries (start with

empty driver table)

Search specific statements…w/o ORDER BY”

check

Preferred check to find code which relies

on sorted DB content

Searches for statements like READ BINARY

SEARCH,

DELETE ADJACENT DUPLICATES, …

accessing unsorted DB content • Identify typical problems

• Even before the productive use

• Or for certain packages

http://scn.sap.com/docs/DOC-31773






RSORASTT (see note 1314689) is available for SAP customers

With pre build scripts providing charts for mainstream selections

Script collection 1438410 is integrated

Wait event analysis with RSORASTT ( or /SDF/DB_TIME_HIST_WITH_OBJ)

Database wait time with object names related to them




RSORASTT - > graphics to support better identification of bottlenecks

See the wait events color coded

Identify peak wait times easily in a chart

Check for special hours and objects of complain either here or in the function

Map yourself with SAP workload, hardware load information and SQLM if available




TPO session - > based on /SDF/DB_TIME_HIST_WITH_OBJ

See the wait events color coded (next release, depends on ST package)

With chart preview in session




Statements from Cache and History

Integration of information from

SAP DDIC

Buffering

Additional indexes (not created)

Field names

ABAP Source code

Where Clause Analysis

Oracle

All indexes

Statistic information for table and indexes

Plan History

Load History

Fieldname

Operator

Number of distinct values

Index




SE38: /sdf/RSORADLD_NEW

Main purpose

Select statements for service session (Early watch, Technical Performance Optimization)

Display of analysis details

What is interesting for daily use

Select statements from history or cache (as of ST-PI SP8)

All common details as typically known from DBACockpit 7.40 available

(but SAP Basis release independent, downward compatible to 46C)

Filter options for selection available

Check statements related to specific hours

Display

ABAP source directly from here (if applicable)

Active session history

Hourly load data

Plans from history

Bind Values




• Display statements based on SQL_ID with relevant load

data

• Same layout (as far as possible) for both sources




• Main leading columns are fixed

• Verify other load details while still having the reference to

the SQL_ID available

• Double click to see the statement

details




• Select different plan for detailed display if avialble




• Details can be used to

analyze the reason of

system behavior

• Pay attention to special

settings, changing plans

and different load history

to understand the problem




• Without having the trouble to use different tools you can display the

hourly load data in the SAP system now and verify problems and

improvements quicker than before.




• Check for which access most of the time is needed

• Don‘t forget to check if the number of samples is significant

• Check all other details

• Specifically interesting for views and joins




• ASH on plan id level with

• Filter predicates direct display

• More details displayed for index and table if available

• Compression

• Last DDL

• Estimated storage quality



Analyze / Optimize identified statements

Typical measures used currently for statement optimization

Indexes

Sorting of table data

Changing statistics (dbms_stats)

Baselines

Change Abap

Table buffering

As always: It depends!

That can for the same statement be a different answer for a different customer!

EXAMPLE: Is it better to change the statistics or to add a hint or use baselines?

If you need to get the solution quickly it might depend on what you are allowed to do faster…

Sometimes you have no developer or code changes are not allowed at all.

Sometimes it is impossible to get the outsourcer to implement anything and you have no

authorizations to implement it yourself (changing stats or using baselines) or it costs extra

money.

Although there is sometimes a good technical reason for a preferred decision, very often the

final decision is influenced rather by organizational than technical reasons.

OK, cool, but

what is the

best

method?





Analyze / Optimize identified statements : Indexes


Indexes

Create indexes

Avoid creating additional indexes with compression if the existing indexes have not been created

with compression. Rebuild all indexes with compression or do not use it.

Avoid transporting the index before it has been created on the database.

Check if the object is created AND used as desired.

Check load data of statements for improvement.

Change indexes

Shouldn‘t I rather create my own index?

No, better add fields, if it is not a unique index. Create a new index, drop the original and rename the new index, transport DDIC afterwards.

Drop indexes

I cannot just drop one of the 25 indexes of the table, or can I?

You can, if you redesign the indexes and if you make sure that a suitable index is available for the

statements using the index you want to drop. Typically you can serve lots of statements with the

same index.






Example

BALHDR – Application log: log header

SAP indexes are not all activated on Oracle

Depending on end user behavior search criteria are not supported

DBACockpit load information

Check execution plan and index support

SELECT

"LOGNUMBER"

FROM

"BALHDR"

WHERE

"MANDANT"=:A0 AND

("EXTNUMBER" LIKE :A1 OR

"EXTNUMBER"=:A2) AND

"OBJECT"=:A3 AND

("ALDATE"=:A4 AND

"ALTIME">=:A5 OR

"ALDATE">:A6) AND

("ALDATE"=:A7 AND

"ALTIME"<=:A8 OR

"ALDATE"<:A9)








Example

What is the object?

What is that for?

Conclusion:

People checking the logs: very dependent on user behavior.

Check all statements

Redesign the indexes to match end user needs

SELECT

"LOGNUMBER"

FROM

"BALHDR"

WHERE

"MANDANT"=:A0 AND

("EXTNUMBER" LIKE :A1 OR

"EXTNUMBER"=:A2) AND

"OBJECT"=:A3 AND

("ALDATE"=:A4 AND

"ALTIME">=:A5 OR

"ALDATE">:A6) AND

("ALDATE"=:A7 AND

"ALTIME"<=:A8 OR

"ALDATE"<:A9)







Possible index:

Although ALDATE and ALTIME sounds initially

like a great idea we can rule this field out,

after checking the bind variables.

CREATE INDEX "BALHDR~Z01" ON "BALHDR"

( MANDANT, OBJECT, EXTNUMBER)

TABLESPACE PSAPCRY COMPRESS 3 PCTFREE 1

STORAGE (INITIAL 64k) ONLINE;

Rebuild the other indexes with compression if not already done: alter index "<name" rebuild online compress <degree> pctfree 1 storage (initial 64k);

Other options?

Hint? Statistics?

What for? There is

currently no suitable

index support available!

Again: INDEX!!!





But…..

Why do we know that this is selective?

Should we not run an analysis on the field combination?

We could, but that would just be a waste of time and resources, specifically

if the object is big.

We know the selectivity of the combination of the fields by looking at the facts we

have.

Date and time are useless. Could be even taken out of the where clause for this used case.

The remaining fields show that we get 0 records back for this selection.

After all, one of the existing indexes would have been good enough, but without histogram

information the optimizer will not take them without hints

Finally, more choices: hint, statistic changes, baselines.

Technically the solution causing less effort should be taken.

But the hint will not work for all cases, neither will the others. For this

scenario you better get the typical requests and create suitable indexes.




Hint..

Index Hint is not an easy option as this select is the general select for the search function.

Substitute bind values? Also disadvantages (overhead, histogram, ….)

Back to index or changing statistics.





Change ABAP

Add fields to the where clause to make use of existing indexes.

Make sure the fields in the where clause are filled, otherwise they are omitted.

Do not execute the statement if control table for “For all entries” is empty.

Avoid identical executions of the same statement, buffer data in the code.

Add hints to make sure the desired index or execution path is used.

SELECT *

FROM

“<TABLE>"

WHERE

"MAND"=:A0

Empty driver table.

Not transferred by DBI.

Not only a

performance issue,

But a design question….

SELECT *

FROM

“<TABLE>"

WHERE

"MAND"=:A0

"KUNDE" = :A1

Index

(MANDT, Field1, Kunde, …)

Field1 could have been

specified,

but over looked by

programmer…..

DELETE FROM /sapapo/vordlog

WHERE vrsioid = versioid_from

%_HINTS ORACLE 'INDEX("&TABLE&"

"/SAPAPO/VORDLOG~0")'.

for details see note 130480, 129385

&table [[<block>,] <tabelle>]&

&table 0 , 1 & = first table in first block

SELECT *

FROM

“<TABLE>"

WHERE

"MAND"=:A0

“STATUS = :A1







Careful if you changed or are going to change to kernel 7.40

The default alias created by SAP changed from T_00 , T_01, … to the specified alias or the table

name.

This will make any hint invalid using the old notation with hard coded alias.

Result: statements runs with bad performance.

Solution:

Please refer to note 2029981 for details and availability of the DBSL patch.

Or change the coding (whatever is faster for or more convenient for you) to match the hint with the

code.

SELECT b.pronr b.aufnr

FROM aufk a JOIN afko b

WHERE b.mandt = a.mandt AND

b.aufnr = a.aufnr AND

b.pronr = ... AND

a.autyp = ...

/*+ leading( b ) use_nl( a ) INDEX ( b "AFKO~P" )' */

SELECT b.pronr b.aufnr

FROM aufk a JOIN afko b

WHERE b.mandt = a.mandt AND

b.aufnr = a.aufnr AND

b.pronr = ... AND

a.autyp = ...

/*+ leading(T_01) use_nl(T_00) INDEX ( T_01 "AFKO~P" )' */

The note will take care of the

required alias generation in the hint.







Table buffering

Buffer tables on application server

Check table buffer size? Small enough? Frequent accesses? Low Change rate? Content suitable

for SAP buffering?

Buffering for object can implemented if confirmed

by responsible developer. Taking a table out of the buffer

Table too big? Already causing errors? Always reading through the buffer from the database? No

more memory? Object size cannot be reduced?

Increase the buffer size if appropriate an needed.

Reduce number of entries in object.

Unbuffer the table.

In case this is a pooled table you need to take it

out to allow more efficient access on the database

and to create suitable indexes. (A017, …)







Table buffering: What you might see in the cache…

KAPOL = Pool table

A018 = condition table in the pool (default SAP buffering)

Additional indexes on pool table makes no sense.

This statement being a problem is telling us, that:

• The table buffer is probably too small or

• The table is reloaded and too big (or both)

Additional indexes can only be created if the table is taken out of the pool!

Disadvantage for the statement advisor is not knowing the application.

Keep

thinking!

Every tool

has got some

limitations.








Why? Better Data Clustering

If lots of records (at least 10 (better 50 +) are selected per execution we can save time and

energy if those records are located near by each other.

Any good criteria to check?

How to?

“SegmentStatistics_TopSegmentsPerSegmentStatistic”

“Data_ClusteringFactor_CBOStatistics”

This might be a good candidate for table sorting (e.g. based on the "sortind" option described in

SAP Note 1016172), because many records are read via the index and also the number of

records / execution is usually high. Be aware that this kind of quick automatic analysis can result

in wrong indications and further manual analysis is useful.

brspace -f tbreorg -t <tablename> -r „<indexname>«

Typical tables: JEST, DFKKOP, …

https://service.sap.com/sap/support/notes/1016172








Other options than sorting the table?

Full index for selection

IOT

Example: JEST (again)

Is it worth the effort?

• Lots of executions

• About 1bg per records

Yes,

it typically will reduce the effort to 50%







Changing statistics (dbms_stats)

Is that done in SAP environment?

Yes, 1020260 - Delivery of Oracle statistics (Oracle 10g and 11g)

Why do we change them?

To influence the index choice without an index hint.

To influence the execution order without hints.

What is typically changed:

Number of distinct values for fields or indexes

Number of btree level

Clustering factor

What is the guiding principle?

Change as less as possible to achieve the desired result.

Check for side effects on other statements.

Monitor immediately after change to revert in case of problems.







Example:

System 1: Good System 2: Bad

Difference is: AUGST / GPART

Why would

somebody

do it?







Example:








Example:


DFKKOPP~5

DFKKOP~Z02

Optimizer could only understand this, if it would

know the values at the time of determining the

execution plan.

For DFKKOP this is not a true option due to size.







Options:







Baselines

1776485 - FAQ: SQL Plan Baselines

Are there any baselines in my system?

select * from dba_sql_plan_baselines

Typical used case:

You do not want to keep the changed statistics, because you have noticed negative side effects.

You cannot add a hint, the code is to generic.

You cannot just create another index, the one you would have wanted to create is already there,

but not taken.

Now the SQL Plan Baselines come in handy:

Change the statistics

Execute the code

Create a baseline for an SQL_ID ORA-38171: Insufficient privileges for SQL management object operation#ORA-06512: at

"SYS.DBMS_SPM", line 2327#ORA-06512: at line 1

Privilege ADMINISTER SQL MANAGEMENT OBJECT should be granted (e.g. by implementing a

current version of SAP Note 706927)

You can keep the plan

but undo the changes!

https://service.sap.com/sap/support/notes/706927




Thank you

Contact information:

Kerstin Knebusch

SAP AG

Raiffeisenring 44

68789 St. Leon-Rot Germany

T +49 6227 7-46069


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 BusinessObjects Explorer, StreamWork, 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.

© 2013 SAP AG. All rights reserved

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 Software Ltd. Business Objects is an SAP company.

Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Sybase, Inc. Sybase 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.

The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express prior written permission of SAP AG.

This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains only intended strategies, developments, and functionalities of the SAP® product and is not intended to be binding upon SAP to any particular course of business, product strategy, and/or development. Please note that this document is subject to change and may be changed by SAP at any time without notice.

SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics, links, or other items contained within this material. This document is provided without a warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, or non-infringement.

SAP shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. This limitation shall not apply in cases of intent or gross negligence.

The statutory liability for personal injury and defective products is not affected. SAP has no control over the information that you may access through the use of hot links contained in these materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-party Web pages.

sap sql optimization - typical approach · sap sql optimization – typical approach hardware...

Documents