Characterizing Workload for the Oracle E-Business Suite

gruesomebugscuffleΛογισμικό & κατασκευή λογ/κού

25 Νοε 2013 (πριν από 3 χρόνια και 8 μήνες)

125 εμφανίσεις


www.hotsos.com

Slide
1

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Characterizing Workload

for the Oracle E
-
Business Suite

Larry Klein (larry.klein@hotsos.com)

Hotsos Enterprises, Ltd.

Charlotte Oracle Users Group

Thursday May 10, 2007


www.hotsos.com

Slide
2

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Presenter’s Background

Larry Klein


3 years, VP of Consulting, Hotsos Enterprises, Ltd.


10 years, Oracle Consulting and Development


database and application performance and architecture


17 years, IBM and Candle Corp’s


mainframe diagnostics, performance, and capacity planning



Patrick Robbins, VP of Sales, Hotsos Enterprises, Ltd.





www.hotsos.com

Slide
3

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Presentation Agenda



Workload and Characterization Concepts


Data Sources


Case Studies


Questions?


www.hotsos.com

Slide
4

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

What is Workload?

The Exercise of Application Functionality for Business Benefit

Application

Functions

Users

Oracle

E
-
Business

Suite


www.hotsos.com

Slide
5

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Workload has a “Heaviness” or Cost

Application Functions

Light

Heavy

Users

Few

Workload

Light

Workload

Medium

Many

Workload

Medium

Workload

Heavy


www.hotsos.com

Slide
6

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Why is “Workload” Important?

Workload

Database

Operating System

CPU, Memory,

Disk, Network


www.hotsos.com

Slide
7

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Why is “Workload” Important?

Workload

Database

Operating System

CPU, Memory,
Disk, Network

Hotsos sees Others do much
“Downstream” tuning



database



ratios



init.ora



disk speeds







www.hotsos.com

Slide
8

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Why is “Workload” Important?

Workload

Database

Operating System

CPU, Memory,
Disk, Network

Downstream efforts often
are unfocused, wasted


like rearranging deck
chairs on a certain luxury
liner


At Hotsos we believe
there is much to be
gained by focusing at the
Workload level



www.hotsos.com

Slide
9

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Why is “Workload” Important?

Workload

Tech

Stack

Workload Drives



Resource Utilizations



Operating Costs



Good or Bad Behavior


www.hotsos.com

Slide
10

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

http://www.angelsandearthlythings.com/atlas.html

Do You Remember Atlas?



Greek god



Lost battle against Zeus



Punished by being made to
carry on his shoulders the
weight of the heavens

So, Now that we Know Workload,

What is Workload Characterization?


www.hotsos.com

Slide
11

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

http://www.angelsandearthlythings.com/atlas.html

Poor Atlas…

Ouch!!!

I’m Really
Sore!!!


www.hotsos.com

Slide
12

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

http://www.angelsandearthlythings.com/atlas.html

Atlas Visits a “Traditional Tuning” Doctor…

Ouch!!!

I’m Really
Sore!!!

The Flex in your Knees
exceeds a comfortable range



wear some knee braces

The Flex in your Back
exceeds a comfortable range



wear a back brace


www.hotsos.com

Slide
13

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

http://www.angelsandearthlythings.com/atlas.html

Atlas Visits a “Workload Characterizing” Doctor…

Ouch!!!

I’m Really
Sore!!!

That’s a Pretty Big Rock
you’ve got there


Let’s find a way to do
Something about that Rock


www.hotsos.com

Slide
14

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Workload Characterization?


What is it?


The process of finding in an Application System


The big rocks


Many other very useful, powerful things


Why do you care?


The big rocks impact other, more important workloads


Resource consumption, contention, latching


Every system has big rocks


Wasting resources


Causing contention


Causing one or more unplanned, unbudgeted hardware
upgrades





www.hotsos.com

Slide
15

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

When a Customer Calls Hotsos for Help


What they say

“Help, my Whole System is Slow”



What they mean

“Help, one or more individual workloads is slower than I need it to be”



We help them by


Characterizing the workload


Looking for the big rocks


Considering their other, important issues


Putting together a game plan to analyze and fix



www.hotsos.com

Slide
16

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

What Happens in our Hotsos Consulting work?

We use the Hotsos Method R*


Listen to the Customer


Understand then compile an Issues List


Order Entry form OEXOEMOE takes 60 seconds to book an
order, need it to be 5 seconds


Custom report XYZ takes 50 hours to run, need it to be 2 hrs
because it’s on the critical path of month end processing


Holy cow, Big Rock DiscoverABC consumed 52% of
yesterday’s total database processing costs


Trace (10046 level 12)


Profile


Analyze


Test and Resolve


*R = Response Time


www.hotsos.com

Slide
17

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

E
-
Business Customers Almost Always

get into Workload Trouble, Particularly Big Rock Trouble

Project Implementation Timeline

Buy

Software

Go

Live and
Pray

Guess

#Users,

Features,

Sizing

Order

Hardware

Map &

Gap

Install

Hardware

CRP’s

? More
Users,
Features!

Build
1000

Custom
-
izations
per the
Gap,

Uncon
-
strained
batch
schedule

We’re out
of time.


We don’t
need no
stinking
load
testing


www.hotsos.com

Slide
18

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

As a result, the Initial Server Sizing could be off…

Congratulations! Your Project



has generated 10 (or maybe 100?) pounds of workload



for a 5 pound server


www.hotsos.com

Slide
19

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

For E
-
Business Customers

We Often find 2 Categories of Issues…

Issue Distribution
Foundation
Workload

www.hotsos.com

Slide
20

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Common E
-
Business

Foundation Issues

Issue

Action

Not running certified
releases

Check Metalink for Certification status

Creative init.ora

Must follow legal settings (especially CBO) per Metalink
Support Note 216205.1

(Under)sizing

See sizing table at end of Note 216205.1

No, inconsistent, or
stale statistics

Run FNDGSCST or equivalent, be careful of interaction with
10g rdbms GATHER_STATS job

No pinning

Pin at database startup, frequently executed packages,
functions, triggers

No purging



Run FNDWFPR to purge workflow tables



Run FNDCPPUR, FNDSCPRG to purge FND tables






www.hotsos.com

Slide
21

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Common E
-
Business

Workload Issues

Issue

Action

Micro

The thing I traced is its own problem. There is inefficient
SQL or whatever. Go fix the SQL or whatever…

Macro

The thing I traced, in and of itself, is good.



it’s SQL is efficient



on a no
-
load system it runs great



under load it runs poorly



who is (are) the culprits



consuming resource



contending with this task

The thing I traced is good



it’s a batch job



the user is complaining about turnaround time



runtime actually was quite good



waittime before run is quite high


hmmmm…


www.hotsos.com

Slide
22

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

To find out the Culprits

You Need Workload Characterization



Who



What



When



Where



How



How Much?


www.hotsos.com

Slide
23

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Workload Management Process

Detect Workload Issues

Measure and Characterize
Workload

Fix/Manage Workload


www.hotsos.com

Slide
24

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

How do you get the Data for

Workload Characterization?

Oracle

Apps

Attribute

Data Source

Who

OS Username, DB Username, Apps Username

What

Program, Module, Action

When

Logon time, Logoff time (if Conc Mgr job, Waittime too)

Where

Host, Terminal, Node

How Much Cost

Logical reads, Physical reads, cpu

How Many
Transactions

Application Transaction Tables


www.hotsos.com

Slide
25

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Hotsos Method/Product Terminology for

Workload Characterization?

Oracle

Apps

Attribute

Entity

Who

Workers

What

When

Where

How Much Cost

Cost by Worker, detail then summary

How Many Xactions

Widgets


www.hotsos.com

Slide
26

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

For a given Workload, Both Response Time and Cost are Important

AND Related… (AND Need to be Managed…)

A Workload’s response time R = function(cost, contention)

Cost

Contention

Response Time

Low

Low

Fast

Low

High

Not so Fast

High

Low

Not so Fast

High

High

Certainly Slow


www.hotsos.com

Slide
27

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Eliminating unnecessary workload often results in
better
-
than
-
linear performance improvement.


Eliminating work…


Saves top
-
line response time


Eliminates dependant work


Reduces queue lengths


Makes remaining calls faster


Helps everyone




Eliminating unnecessary work
reverses

exponential performance degradation

From Hotsos Problem Determination Class, Chapter 6 Day 3


www.hotsos.com

Slide
28

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Said another way…

Like Grocery store Checkout Lines,
Computer Systems can be described
by a “Queueing Model”


The shape of the “hockey stick” is
based on the number of clerks (cpu’s)
and other factors


With respect to the “knee” of curve



left is “good”



right is “bad”


Utilization

Response Time


www.hotsos.com

Slide
29

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Said another way…

If you have R pain, you might be
“right” of the knee


How do you go left to reduce
utilization?



Utilization

Response Time

Add Capacity (at what cost$$$?)

Reduce Workload


www.hotsos.com

Slide
30

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“The Whole ERP System is Slow”

Hotsos Consulting, recent project

Logical Reads by Day
0
2000000000
4000000000
6000000000
8000000000
10000000000
12000000000
14000000000
16000000000
Sun
Mon
Tues
Wed
Thurs
Fri
Sat
Sun
Mon
Tues
Day of Week
Logical Reads
non-APPS
APPS

www.hotsos.com

Slide
31

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“The Whole ERP System is Slow”

Logical Reads by Day
0
2000000000
4000000000
6000000000
8000000000
10000000000
12000000000
14000000000
16000000000
Sun
Mon
Tues
Wed
Thurs
Fri
Sat
Sun
Mon
Tues
Day of Week
Logical Reads
non-APPS
APPS
Ms. Customer,



On any one work day 80% of your
ERP daily cost is going to “non
-
APPS” workloads



As it turns out, all of “non
-
APPS” is
custom Cognos reporting



ERP is for transaction processing



Your options are:



shutdown or identify and tune
the costly Cognos reports



implement a reporting
environment separate from
transaction processing


www.hotsos.com

Slide
32

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“Order Entry Meltdown”

Challenge

Approach



Client XYZ Company



Custom Order Entry Application



Application not meeting


needs of the business



Database Server max’ed out




Tune Logical Reads



Identify/Trace Work



Measure and report progress



5 week effort




www.hotsos.com

Slide
33

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Progress Report to the Client’s CIO

Total Daily Logical Read 86% Reduction, over 5 Weeks


www.hotsos.com

Slide
34

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Client Feedback…

Client CIO, “I Guess that’s good work,

but What’s a Logical Read?”



“How do I know you didn’t reduce costs by:



shutting down the database



sending users home?




www.hotsos.com

Slide
35

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study’s Alternative Ways to Report Success

Daily Order Volume
0
1000
2000
3000
4000
5000
6000
Aug-95
Sep-95
Cost per Order
0
10
20
30
40
50
Aug-95
Sep-95
500% Increase
in Widget Production

Over 5 weeks

40:1 “Betterment”
In Cost per Order

Over 5 weeks

Client bought me dinner!!!


www.hotsos.com

Slide
36

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“My CPU’s are maxed out already?



How did my Sizing Guestimates compare to Actual Usage?


www.hotsos.com

Slide
37

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“My CPU’s are maxed out already?



How do my Sizing Guestimates compare to Actual Usage?


www.hotsos.com

Slide
38

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“Performance is Highly Variable, day to day”


www.hotsos.com

Slide
39

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“Performance is Highly Variable, day to day”


www.hotsos.com

Slide
40

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“OE really slows down during Month End”

Hotsos Consulting project, March 2007

Mr. Customer,



during your OE window of
6am to 6pm on month end
days



you are incurring 80% of
your day’s batch cost too



let’s identify expensive
day
-
time running batch



move it to nighttime



tune it


www.hotsos.com

Slide
41

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


“Month End Batch runs too long”



Mr. Customer, on your busiest month end day



(6444+1750)/13360 = 60% of your STANDARD jobs ran in 5 seconds or less



The STANDARD queue has a sleep setting of 30 seconds



Let’s setup a FAST queue, and a SLOW queue, and reassign jobs…


www.hotsos.com

Slide
42

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Case Study


How “Valid” is the Stress Test???


Clone PROD to TEST, then Upgrade TEST


Load test TEST as if it were PROD


Determine important PROD user activity from 2
-
4pm


Induce similar user activity in TEST for 2 hours, monitor


But


No automated test scripts


No orchestrated, manual test scripts


Select PROD users to log in to TEST and “do their thing”


Determine if TEST workload came close to PROD


Determine if TEST suffered latching, too, or problem solved


Decide Upgrade go
-
live based on TEST “closeness” and TEST
system performance

Large E
-
Business Customer Needs to Upgrade
because of serious latching issues due to rdbms bug

Will the Upgraded System Sustain PROD Volumes?


www.hotsos.com

Slide
43

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Measuring TEST compared to PROD
-

Workers

Workers Batch

Workers Online

Workers
Total

Count

Run
Mins

Count

Distinct

Count

Prod

2
-
4pm

2546

397

3134

294

9650

TEST

1990

1663

2669

162

6628

Hmmm


TEST



light on Workers



heavy on Batch Runtime Minutes


www.hotsos.com

Slide
44

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Measuring TEST compared to PROD
-

Widgets

Order
Lines


Journal
Lines

...

Total

Xactions

Prod

2
-
4pm

41670

1162

55982

TEST

10496

841

12212

Hmmm


TEST was light on Widgets


www.hotsos.com

Slide
45

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Measuring Day in Life TEST


Assembly Line Costs

Logical Reads
(millions)

Physical Reads
(millions)

APPS

Non
-
APPS

APPS

Non
-
APPS

Prod

2
-
4pm

486

8

11

1

TEST

1038

2

17

0

Hmmm


TEST



much higher Costs


www.hotsos.com

Slide
46

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

How Much like PROD was TEST?


Probably, Manual Stress TEST users



“underloaded” transaction processing



“overloaded” reporting

TEST was not valid!!!

“Upon review, I think that many TEST users must have”



used TEST as “pedal to the metal”



disregarded instructions and pacing



loaded up TEST with favorite longrunning month
-
end reports


www.hotsos.com

Slide
47

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

How Much like PROD was TEST?




Customer realized the TEST was invalid



But hey, TEST had no latching problems



Customer decided to upgrade anyway



Argh…


www.hotsos.com

Slide
48

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Summary

Workload Characterization is a process


To measure and understand the details of


Workers


Costs


Widgets (worker outputs)


that occur in your E
-
Business or any Application System


To apply different views against the details


To look for details, anomalies, summaries, and trends


To help you reduce utilization by knowing the right workloads to
optimize


To help you improve performance without hardware upgrades




www.hotsos.com

Slide
49

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Summary

Workload Characterization


Has been practiced for 40 years in the mainframe world


Is not popular nor widely applied in the Oracle/UNIX world


Consequently, many customers “take the default” and buy more
hardware in response to Workload Performance issues


It’s simple and very cheap, if you know how, to


Slice and dice and dissect all the work in your system


Look for and “big rock”, costly work or other bad workload
behavior


Reduce utilization and “buy back” capacity quickly and easily



www.hotsos.com

Slide
50

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Questions???


www.hotsos.com

Slide
51

Copyright © 1999
-
2007 by Hotsos Enterprises, Ltd.

Thank You!