troubleshooter´s top 5: real-world performance tuning of ifs applications

REAL-WORLD PERFORMANCE Troubleshooters Top 5—Real-world performance tuning of IFS Applications

© 2013 IFS

3

Evolution

Configuration

Toolbox

Top 5—performance issues

Questions

AGENDA

© 2013 IFS

4

JONAS GÖRANSSON Principal Systems Engineer Worked for IFS since 1993 IFS Consulting, IFS R&D

STEFAN LÖVGREN Principal Systems Engineer Worked for IFS R&D since 1999

WHO WE ARE

© 2013 IFS

5

IFS BENCHMARK TEAM Sizing of IFS Applications (IFS Sizing Guides) Performance testing (development & customer-initiated tests) Customer performance issues (support)

WHAT WE DO

© 2013 IFS

6

EVOLUTION

ERP SYSTEM EVOLUTION

© 2013 IFS

8

Different architectures, more complex, more functionality and more integrations.

IFS APPLICATIONS EVOLUTION

© 2013 IFS

9

Easy installation with less configuration

Automatic and self-managed RDBMS

No magic initialization parameters

ORACLE EVOLUTION

© 2013 IFS

10

16-bit 32-bit 64-bit technology, Memory KB TB

Clock frequency has increased from some MHz to several GHz

Low-end servers cover high-end demands

HARDWARE EVOLUTION

© 2013 IFS

11

Hardware – Low-end servers cover high-end demands

Software – Increased robustness

Architecture complexity – Distributed systems makes troubleshooting harder

EVOLUTION SUMMARY

© 2013 IFS

12

CONFIGURATION

HARDWARE

© 2013 IFS

14

CPU Memory

Disk Network

Computer performance

CONFIGURATION

© 2013 IFS

15

Web server App server DB server

Database processes Server threads

Integration queues Connection threads

Clients

Work load

TOOLBOX

Get an overall picture from the complete system under normal conditions

MONITORING

© 2013 IFS

17

DB

WEB

OPTIONAL PRINT

APP

Hardware resources as well as application

Keep historical monitoring data as reference

Specific tools for every part of the system

From overview to detail

MONITOR

© 2013 IFS

18

System resources

Application server

Database

Oracle Enterprise Manager (OEM)

Oracle Workload Repository (AWR)

Free Oracle tools: SQL trace, trcsess, tkprof, Trace Analyzer

3rd party programs: Spotlight, Insider, MyOra

Open Source

In-house admin scripts

TOOLS

© 2013 IFS

19

Holistic approach

Be open-minded

Don’t be biased—the problem can be the unexpected

Don't change more than one thing at the time

Top-down problem-solving

METHODOLOGY

© 2013 IFS

20

Problem definition—define not solve

Identify the problem

Trace to source—trail backwards

Is this the root cause of what is generating the problem?

Do the solution have unforeseen consequences?

Don’t assume that problem is solved until verified

TOP-DOWN PROBLEM-SOLVING

© 2013 IFS

21

TOP-5 PERFORMANCE ISSUES

Code

Index

Locks

Misconfiguration

Parameters

TOP-5 PERFORMANCE ISSUES

© 2013 IFS

23

Non-optimal SQL and PL/SQL code

IN clauses with sub-queries

Loops

Excessive commits

TOP-5 PERFORMANCE ISSUES CODE

© 2013 IFS

24

TOP-5 PERFORMANCE ISSUES CODE

© 2013 IFS

25

Queries not using index

Queries using wrong index

Too many indexes on table

TOP-5 PERFORMANCE ISSUES INDEX

© 2013 IFS

26


© 2013 IFS

27

Use the Advanced button to show which columns

are indexed.

Green arrow = indexed Red arrow = not indexed


© 2013 IFS

28

The Match Case checkbox can be unchecked if you

don’t know whether capitals or lower case letters are

used, but…

…the upper() SQL function in the SQL clause prevents use

of indexes.


© 2013 IFS

29

Count of ”Cancelled historical order

lines”


© 2013 IFS

30

Hot parts / blocks

Number generation, sequences

Legal demands, non-broken number series

TOP-5 PERFORMANCE ISSUES LOCKS

© 2013 IFS

31

TOP-5 PERFORMANCE ISSUES LOCKS—HOT PARTS

© 2013 IFS

32


© 2013 IFS

33

1, Purchase order scenario

3, User load profile

2, User load details


© 2013 IFS

34

Database Locks

Increasing user load


© 2013 IFS

35

TOP-5 PERFORMANCE ISSUES LOCKS—HOT PARTS—COMPARE COLD PARTS

© 2013 IFS

37

Purchase Orders using 2000 parts total Purchase Orders using 10 parts total

CPU % Response time

Unbalanced configuration Planning/purchasing new infrastructure Adding infrastructure Extreme load profile

TOP-5 PERFORMANCE ISSUES MISCONFIGURATION

© 2013 IFS

38

shared_pool_size

db_cache_size

sga_max & sga_target

pga_aggregate_target

processes

optimizer_index_cost_adj

undo_retension

TOP-5 PERFORMANCE ISSUES—PARAMETERS

© 2013 IFS

45

TOP-5 PERFORMANCE ISSUES PARAMETERS—OPTIMIZER_INDEX_COST_ADJ

© 2013 IFS

46

optimizer_index_cost_adj=10

optimizer_index_cost_adj=1000

SUMMARY

Balance infrastructure against workload

Monitor all components in the system

Have a prepared toolbox

SUMMARY

© 2013 IFS

48

© 2013 IFS

49

THIS DOCUMENT MAY CONTAIN STATEMENTS OF POSSIBLE FUTURE FUNCTIONALITY FOR IFS’S SOFTWARE PRODUCTS AND TECHNOLOGY. SUCH STATEMENTS OF FUTURE FUNCTIONALITY ARE FOR INFORMATION PURPOSES ONLY AND SHOULD NOT BE

INTERPRETED AS ANY COMMITMENT OR REPRESENTATION. IFS AND ALL IFS PRODUCT NAMES ARE TRADEMARKS OF IFS. THE NAMES OF ACTUAL COMPANIES AND PRODUCTS MENTIONED HEREIN MAY BE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

www.IFSWORLD.com

© 2013 IFS

troubleshooter´s top 5: real-world performance tuning of ifs applications

Technology