Optimizing Response Time

1
Optimizing Response Time

• Exchange 2004
• Las Vegas Nevada

Tom Bascom Greenfield Technologies tom_at_greenfieldt
ech.com http//www.greenfieldtech.com
2
Performance Tuning

• Hypothesize that X has an unacceptable value
  which represents a bottleneck.
• Make changes, one at a time, with the intent of
  improving X.
• Undo changes that dont improve X.
• When X has an acceptable value move to the next
  bottleneck.

3
Whats wrong with that?

• It doesnt work very well.
• It takes too long.
• It costs too much.
• It isnt what the person paying the bill cares
  about.

4
Why doesnt it work well?

• Youre treating symptoms in random order
• You cannot extrapolate detail from aggregate
  data.
• You cannot tell how long something took by
  counting how many times it happened.
• Users dont care about database internals.
• Users care about response time.

5
How long does it take?

• How long have you got?
• How many variables are there?
• Whats your tolerance for experimentation?
• Have you ever heard the term combinatorial
  explosion???

6
How much does it cost?
Wiring instructions available after the
presentation
7
Details from the Aggregate?

• Has your System Administrator ever said
• Network utilization is less than 25
• Disks are only 50 busy
• Memory is 100 utilized
• CPU usage is 80

8
Details from the Aggregate?

• Over what period?
• Which devices?
• Allocated to what?
• Broken down how?
• What was user X doing?
• And how much time did user X spend on each
  component?

9
Counting Events

• How long does it take to read 1,000,000 records?
• Self-service or Remote clients?
• Cached or from disk?
• Indexed query or WHOLE-INDEX?
• Why are you reading 1,000,000 records?

10
Limited Room for Improvement
You want to improve the runtime of a report that
takes 8 hours to run. In this period it does
100,000 disk reads
an obvious target for performance improvement!
11
Amdahls Law
The performance enhancement possible with a given
improvement is limited by the fraction of the
execution time that the improved feature is used.

• 8 hours 28,000 seconds
• 100,000 disk reads is (probably) 1,000 sec
• Room for Improvement 1,000 seconds, 3

12
Improvement Activities

• Make the system go faster.
• Distribute disk IO.
• Ensure x unused CPU capacity.
• Improve hit ratio to x
• Eliminate all Table Scans

13
Characteristics ofReal Improvement Activities

• Informed Consent.
• Alignment with Business Priorities.
• Forecasting Payoff.
• Dollars and Cents.
• Dont fall into the trap!
• ROI.

14
What Makes A Good Method?

• Impact
• Efficiency
• Measurability
• Predictive Capacity
• Reliability
• Determinism
• Finiteness
• Practicality

15
A Better Method

• Select Appropriate User Actions to Improve.
• Collect Properly Scoped Data.
• Execute an Appropriate Optimization Activity.

Target the largest response time component of the
most important Business process first.
16
Selecting User Actions

• Pay Attention to the Business.
• Target What is Important.
• Prioritization Based on Impact.

Users want the right answer, with the best
response time at the lowest cost.
17
Collecting the Right Data

• Aggregate Data Isnt Helpful.
• Actually its dangerous.
• We need RESPONSE TIME
• Response time needs to be scoped to discrete
  components of the system.

18
What Is Response Time?
19
Tools

• Progress Profiler
• -profile or sessionprofiler in v9
• Line by line execution timing at microsecond
  granularity
• ProTop
• V9
• VST based view of db performance metrics
• Readprobe
• Calibration of db CPU cost

20
Case Study

• DEMO

21
?
Any Questions
Tom Bascom Greenfield Technologies tom_at_greenfieldt
ech.com http//www.greenfieldtech.com
22
Resources
Optimizing Oracle Performance Millsap Holt,
OReilly Associates ISBN 0-596-00527-X The
Goal Goldratt Cox, North River Press ISBN
0884270610 http//www.greenfieldtech.com/downloa
ds http//www.greenfieldtech.com/articles