ibm as/400 performance tuning (session s400 04) ron schmerbauch

2000IBMCorp.Session S400 04

IBM

AS/400 Performance Tuning(Session S400 04)

Ron Schmerbauch


Session Preview How do I know when there's a performance problem?

What can I do about it? General performance overview of your AS/400 General performance overview of your R/3 system Where do I look for DB performance info? How can I improve DB performance?


Session Focus - DB2 for AS/400

DB performance influences and is influenced by your system as a whole

Performance needs to be viewed from different perspectives

All work

Specific SQL Request All work

Specific SQL Request

Complex SQL Request



Performance is a moving target Many factors to consider Analysis and tuning is a mixture of art and science

All work



Complex SQL Request


How do I know when there's a performance problem?

Other than the user complaints..... You will see a difference in your performance data Checking the "good" days is just as important as checking the "bad" days

Every system is different, only your experience and historical data will be your best reference


What actions might I take?

Here are the options... Get more computing resources Move workload to a different time or day Adjust use of computing resources Change the workload, or influence the implementation

Change the performance expectations


How do I decide which action to take?

Analyze the situation using performance tools Rely on history and experience Experiment More than one action may be necessary



Look at the big picture first Understand the high level tools and measurements

All work



Complex SQL Request


Performance tuning - general tips

Scope the problem - is everything slow or is it something specific?

Keep records of what and when you change things Adjust one piece at a time Dynamic environments make tuning difficult Test and production environments may respond differently to "the same" thing

Tuning is an iterative process


Performance tuning - general tips

History indicates most performance problems are caused by:

Locally defined tables and reports Poor queries (often using SELECT *, LIKE %)Queries on tables with no index

Third party applications not factored in R/3 sizingsharing R/3 memory pool

Basic work management errors Suspect these areas first!


Checking AS/400 performance

Examine computing resources - CPU, Memory, Disk

Use system commands or ST06 Work with system status(WRKSYSSTS) Work with disk status (WRKDSKSTS) Work system activity (WRKSYSACT) or Work Active Job (WRKACTJOB)


WRKSYSSTS


WRKSYSSTS Check CPU % Maximize memory pool size, set Paging Option to *CALC

The lower the DB Faults the better, don't worry about paging numbers much

Make "Max Act" as low as possible, but high enough to keep Wait->Inel and Act->Inel at 0.

Set Sysval QPFRADJ=0 once you settle on values


WRKDSKSTS


WRKDSKSTS

Look at % utilization and % used Utilization should be no more than 40% over time. % used should be roughly equal across arms, use STRASPBAL or SAV/DLT/RSTLIB to respread data evenly.


WRKSYSACT


WRKSYSACT

Check most active jobs Look for interactive jobs CFINTnn tasks taking up significant CPU


Checking general performance of your R/3 system

ST03 - Workload ST02 - Buffers ST04 - DB ST06 - Operating system SM50 - Work processes SM51 - Servers


Checking DB performance

Use ST04 and/or SM50 to find the report and query

Use ST04, ST05 and OS/400 commands for detailed analysis

All work



Complex SQL Request


ST04


ST04 - Detailed Analysis


Where does ST04 data come from?

Memory-based DB Monitor summarizes info on a statement basis and collects the information in memory

Scheduled SAP job calls OS/400 API to dump data to files in R3<SID>400 library

ST04 reads the files See 4.6 SAP Documentation CD

Basis Components --> CCMS --> CCMS --> CCMS Monitoring --> Database Monitor


ST04 - 50 Slowest Queries


ST05


ST05 - trace output


ST05 - Explain SQL


What are these things and where do they come from?

Prepared Statement Explain Host Variable Access path Access plan Reusable ODP


XDARECVR

Static Dynamic Extended Dynamic

Compiled embedded statements

Prepare every time

Prepare once and then reference

Optimizer

DB2 UDB

Native(Record

I/O)

SQL

Network

(Data Storage & Management)

DB2 UDB for AS/400

Architecture

XDAEDRS

SAP 3-tier app server wp

XDAEDRS

SAP 2-tier central server WP job


DynamicSQL

statement

SQL Package (*SQLPKG)

AccessPlan

Each Dynamic SQL PREPARE is -Parsed-Validated for syntax-Optimized

as access plan createdfor the statement

Extended Dynamic SQL View

Has this Dynamic request been

previously executed?

Generic plan quickly generated on PrepareComplete, optimized plan on Execute/Open


STATEMENT NAME: OlBAAAEYCASELECT "BIASSTEP" , "NOCOL" FROM R3TSTDATA/"SSQLR" WHERE "REP_NAME" = ? WITH UR SQL4021 Access plan last saved on 05/16/00 at 11:20:26. SQL4020 Estimated query run time is 1 seconds. SQL402D Query attributes overridden from query options file QAQQINI in library QUSRSYS. SQL4027 Access plan was saved with DB2 UDB Symmetric Multiprocessing installed on the system. SQL4017 Host variables implemented as reusable ODP. SQL4008 Index SSQLR used for table 1. SQL4011 Index scan-key row positioning used on table 1.

SQL Package Contents:

Statement name Statement text Statement parse

tree Access Plan

PRTSQLINF output

ST05 report tells you the library, package and statement

SQL explain shows you slightly different info than whatis included in the SQL package

SQL Explain vs. SQL packages


ACCESSPLAN

Internal Structures

OPEN DATA PATH(ODP)

Executable code for allrequested I/O operations

CREATE

Create process is EXPENSIVE– Longer execution time the first time an SQL statement is executed

Emphasizes the need of REUSABLE ODPs

Access Plan to ODP


To minimize the number of ODPs that have to be created, DB2 UDB leaves the ODP open and reuses the ODP if the statement is run again in job (if possible)

Reusable ODPs consume 10 to 20 times less CPU resources than a new ODP

Two executions of statement needed to establish reuse pattern

Reusable ODPs


ST05 - trace output

ODP reused


Reuse Considerations

Reusable ODP's do have one shortcoming... once reuse mode has started access plan is NOT rebuilt when the environment changes

What if a table that started out empty and is now 5X in size since the last execution?

What if selectivity of host variable or parameter marker is greatly different on nth execution of a statement?

What if an index is added for tuning after 5th execution of statement in the job?


ST05 - trace output

Why no check here?

Check for already

prepared stmt


DBSL statement caching

Dynamic (table or field name variables in abap) statements are checked against a cache in DBSL.

Cache misses need to be prepared as usual - into lib R3<SID>X0000, pkg <tablename>

Cache hits are reused, no need to check or prepare another statement


ST05 - Explain SQL


Prepared Statement - SQL package Explain - reasons behind the plan Host Variable - "fill in the blank" for selection Access path - fancy name for an index Reusable ODP - faster data access

Now you know...

How are all of the access plan decisions made?


Server configurationServer attributes

Version/Release/ModificationLevel

SMP

Database design

Table sizes, number of rows

Views and Indexes (Radix, EVI)Work

management

StaticDynamic

Extended DynamicInterfaces

SQL Request

Job, Query attributes

Server performance

The Plan

Optimization...the intersection of various factors


What data access methods does the optimizer have to consider?

Non-Keyed Data Access Methods Table Scan Parallel Table Scan Parallel Pre-fetch Parallel Table Pre-load Skip Sequential with dynamic bitmap Parallel Skip Sequential


These are the most common for R/3

Keyed Data Access Methods Key Positioning and Parallel Key Positioning Dynamic Bitmaps / Index ANDing ORing Key Selection and Parallel Key Selection Index-From-Index Index-Only Access Parallel Index Pre-load


More complex queries, also common in R/3

Joining, Grouping, Ordering Nested Loop Join Hash Join Index Grouping Hash Grouping Index Ordering Sort


Data Access Methods

Keyed Skip Sequential Table Scan

Re

spo

nse

Tim

e /

Sys

tem

Re

sou

rce

s

Few Many


Selection criteria are applied to ranges of index entries before the table is processed.

Advantages: Only those index entries that are within a selected range

are processed Can process both join and selection processing within a

single operation if the correct index exists Potential disadvantages:

Can perform poorly when a large number of rows are selected

Used when: Less than ~20% of the keys are selected Ordering, grouping, or join operation requires the use of

an index Selection columns match the leading ix key fields

Key Positioning


Creating the Perfect Index

A "perfect" index is a radix index that is permanent and can provide:

Good, useful statistics to the optimizerIndex contains appropriate selection, joining, grouping, ordering fields

Applicable key fields are contiguousEqual predicate fields first, one non-equal predicate field last


CREATE INDEX X1 ON EMPLOYEE(LASTNAME, WORKDEPT, SALARY):

SELECT * FROM EMPLOYEEWHERE WORKDEPT BETWEEN 'A01' AND 'E01'

AND LASTNAME IN ('SMITH', 'JONES', 'PETERSON')AND SALARY BETWEEN 50000 AND 100000

LASTNAME WORKDEPT SALARY

... ...

... ...

... ...

Jones A01 35000

Jones C01 51000

Jones D01 45000

... ...

Peterson C01 60000

Peterson E01 100000

Peterson E01 120000

... ...

Smith B01 47000

Smith C01 59000

Smith F01 62000

... ...

... ...

Think of processing a set of ranges...

JonesA0150000 - JonesE01100000PetersonA0150000 - PetersonE01100000SmithA0150000 - SmithE01100000

Art - The Perfect Index

Early elimination of rows is the key Narrow range(s)


CREATE INDEX X1 ON EMPLOYEE(LASTNAME, WORKDEPT, SALARY):

SELECT * FROM EMPLOYEEWHERE WORKDEPT BETWEEN 'A01' AND 'E01'

AND LASTNAME LIKE 'PETER%'AND SALARY BETWEEN 50000 AND 100000

LASTNAME WORKDEPT SALARY

... ...

... ...

... ...

Jones A01 35000

Jones C01 51000

Jones D01 45000

... ...

Peterson C01 60000

Peterson E01 100000

Peterson E01 120000

... ...

Smith B01 47000

Smith C01 59000

Smith F01 62000

... ...

... ...

Think of processing a set of ranges...

Peter(-infinity) - Peter(+infinity)

With this index, WORKDEPT and SALARYare of no use.

Art - The Perfect Index


CREATE INDEX IX1 on TABLE1 (YEAR, MONTH, CUSTOMER, ORDERNO)

CREATE INDEX IX2 on TABLE2 (ORDERNO, QUANTITY, SALES_AMOUNT)

SELECT A.YEAR, A.MONTH, A.CUSTOMER, SUM(B.QUANTITY), SUM(B.SALES_AMOUNT)FROM TABLE1 A, TABLE2 BWHERE A.YEAR = 1998 and A.MONTH in (10, 11, 12) and A.CUSTOMER = 'SMITH'and A.ORDERNO = B.ORDERNOGROUP BY A.YEAR, A.MONTH, A.CUSTOMERORDER BY A.YEAR, A.MONTH, A.CUSTOMER

"Perfect" radix indexes...

Selection, grouping, ordering

Joining

StatisticsMulti key selectionIndex only accessNested loop joinIndex groupingIndex ordering


Join Optimization Tips

Remember that there is no such thing as a hard and fast optimization rule since the optimizer is data dependent.

For every "rule of thumb" that you learn, there will always be at least one exception.

The single most important thing that will effect the performance of join queries is the Join Order.


Join Optimization Tips

At a minimum, make sure there are radix indexes built over all the join columns.

May have indexes built over both join columns and selection columns, this allows for multi-key joins.

Create multiple, single key indexes over selection columns to take advantage of dynamic bitmaps, then create radix index over join columns.

Create multiple, single key EVIs over foreign key columns to take advantage of dynamic bitmaps and star schema join.

Indexes are used to determine the average number of duplicate values for the join fields.


Indexing Strategies - Basic Approach

You must create some indexes Statistics Implementation

In general: equal selection fields first, then join fields -or- group-by and order-by fields

Be aware of limitations when creating variable length and null capable primary and foreign key columns

May have to play around with key order based on the queries, the data and selectivity of the columns


Indexing Strategies - More details

Consider Index Only Access All columns in the SELECT clause as keys

Consider dynamic bitmaps and index ANDing/ORing

Simple indexes can be combined together for selection Consider EVIs for stats, dynamic bitmaps, and star

schema join Single key, low number of unique values Fact table foreign key Over temporary results file to provide stats

Check for messages and iterate


Creating Indexes Do not create lots of permanent indexes trying to

cover every combination Create one or two you feel will be good ones, run job again (or run STRSQL for single query) and see if they are used

If they are used and run time is noticeably helped, consider them for permanent use

If they aren't used, delete them and try a different combination

Don't create indexes just to solve single instance of full open or query unless it accounts for a significant amount of time


What does another index cost?

Each additional index created for a table will cause overhead when:

Updates to the table include the index keysRows are inserted or deleted for the tableFull opens occur for that file (index evaluation)

Extra disk space Extra save, restore time




SMP

Database design



management

StaticDynamic


SQL Request


Server performance

The Plan

Optimization...the intersection of various factors


When should I delete packages? When factors influencing the optimizer have

changed significantly CPU/Memory changes DB PTFs Big changes in database...client copy, client delete, language import

After R/3 install, before and after upgrades Selected packages for new indexes

Don't forget...your system will need to build them again

Safest to delete when R/3 is down


What is the DLTOLDPKGS job?

One other reason that you would deleted packages....application changes

Runs at STARTSAP time Attempts to improve performance by deleting

packages related to ABAPs that have changed since the package was built.


Deleted records and performance

Space for deleted rows is not reclaimed automatically

Deleted rows are reused by new inserts Random "holes" can increase paging Use RGZPFM to compress the tables Check DB02 - Deleted row analysis

Deleted byte count may be wrong for tables with variable length fields


DB02 - Deleted Records


Misc... Memory... if you have it, the system will use it

Separate memory pools as appropriate Test and tune queries before going live

Team up with power users to understand their queries Set realistic response time expectations Test applications and queries using the "real" interface

Interactive SQL on Server models Use Operations Navigator, RUNSQLSTM or Query Manager in batch instead


Review - What actions might I take to improve performance?

Get more computing resources Move workload to a different time or day Adjust use of computing resources Change the workload, or influence the implementation

Change the performance expectations




SMP

Database design



management

StaticDynamic


SQL Request


Server performance

The Plan

Work with the system and the optimizer

Hardware issues

CHGJOB, CHGQRYA, QAQQINI file

Optimizer version - DB ptfs

DB02 for sizes, deleteds, indexes

WRKSYSSTS - pools, paging, maxactWRKACTJOB - cpu

Select *, LIKE '%'

STRSQL/SQLUTIL plan may be different than from R/3


Reporting performance problems

Check CPU, memory, disk Provide Query, SQL package and/or explain Table stats

size, num rows, num deleteds Any known changes to environment Prior performance, current performance What you have tried already


Additional Resources R/3 on AS/400 info, education, services

www.as400.ibm.com/service/bms/support.htm

DB2 Query Optimization Workshop www.as400.ibm.com/db2/db2educ_m.htm www.as400.ibm.com/developer/education/ibcs.html

Much of this session came directly from this class...Credits and thanks to Mike Cain and Kent Milligan of IBM PartnerWorld for Developers


Additional Resources

DB2 UDB for AS/400 - www.as400.ibm.com/db2/db2main.htm

IBM Redbook for SAP on AS/400 - www.redbooks.ibm.com

SAP Basis courses BC370, BC525 IBM Pubs - www.as400.ibm.com/infocenter

DB2 for AS/400 SQL ProgrammingDB2 for AS/400 SQL ReferenceDB2 for AS/400 Database Programming


Please complete your session evaluation and drop it in the

box on your way out.

Be courteous — deposit your trash, and do not take the handouts for

the following session.

The SAP TechEd 2000 Staff

ibm as/400 performance tuning (session s400 04) ron schmerbauch

Documents

performance data

performance tools

performance needs

performance problems

performance expectations

checking as400 performance

wrkdsksts slide

wrksyssts slide