Forecasting Oracle Performance

294 Pages·2007·6.58 MB·English

cyan yellOW
MaGenTa Black
PanTOne 123 c
BOOks fOR PROfessIOnals By PROfessIOnals® The eXPeRT’s VOIce® In ORacle
Companion
eBook Available
Forecasting Oracle Performance
F
o
Dear Reader,
r
Contained in this book are, dare I say, secrets. There is mystery surrounding
e
Oracle forecasting, performance modeling, and capacity planning. In the pages
of this book are the secrets I’ve uncovered and discovered through more than 20 c Forecasting
years of working with literally thousands of IT professionals around the world.
a
My goal is to expose these secrets as plainly and completely as I possibly can.
I wrote this book to be a kind of training course or “how-to” book. It’s packed s
full of examples to transform the theory and mathematics into actions that you t
can apply practically and immediately. Plus I focus on how to communicate i
n
and translate the numbers into management information.
Oracle
Years of experience, my course materials, and mountains of information g
and notes have been boiled down and placed into this single book. Here, you

will learn how to:
O
• Choose the data collection strategy that will work best for you
• Select the most appropriate forecast model r
• Characterize complex Oracle workloads a
• Apply scalability limitations to your forecasts c Performance
• Methodically forecast to ensure quality and consistency
l
• Identify resources at risk of being overutilized and causing service-level e
breaches

• Develop multiple risk mitigation strategies to ensure service levels are met
P
• Effectively communicate forecast results to management
e
Forecasting is serious business, but it’s also just plain fun and very satisfy-
ing. My hope is that this book will excite you, propel you, prepare you, and give r
you a passion for forecasting Oracle performance. f
o
Sincerely,
r
Use the methods in this book to ensure that your
Craig Shallahamer
m
Oracle server can satisfy performance requirements
a now and into the future.
THE APRESS ROADMAP n
Companion eBook c
Expert Oracle Mastering The Art and Science Forecasting
Database 10g Oracle SQL of Oracle Oracle e
Administration and SQL*Plus Performance Tuning Performance
S
See last page for details h
on $10 eBook version a
l
la Craig Shallahamer
ISBN-13: 978-1-59059-802-3 h
ISBN-10: 1-59059-802-4 a
m
90000
www.apress.com e
r
Shelve in
Databases/Oracle
User level:
Intermediate–Advanced 9 781590 598023
this print for content only—size & color not accurate 7" x 9-1/4" / CASEBOUND / MALLOY
(0.75 INCH BULK -- 296 pages -- 60# Thor)
802400FMFINAL.qxd 3/23/07 1:26 PM Page i
Forecasting Oracle
Performance
Craig Shallahamer
802400FMFINAL.qxd 3/23/07 1:26 PM Page ii
Forecasting Oracle Performance
Copyright © 2007 by Craig Shallahamer
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 : 978-1-59059-802-3
ISBN-10 : 1-59059-802-4
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use atrademark symbol with every occurrence
of atrademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Jonathan Gennick
Technical Reviewers: Jody Alkema, Tim Gorman, and Jared Still
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan Gennick,
Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Jeff Pepper, Paul Sarknas, Dominic
Shakeshaft, Jim Sumser, Matt Wade
Project Manager: Richard Dal Porto
Copy Edit Manager: Nicole Flores
Copy Editor: Marilyn Smith
Assistant Production Director: Kari Brooks-Copony
Production Editor: Katie Stence
Compositor: Kinetic Publishing Services, LLC
Proofreader: Linda Seifert
Indexer: John Collin
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA
94710. Phone [phone], fax [phone], e-mail [email protected], or visit http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty. Although every precaution
has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to
any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly
by the information contained in this work.
802400FMFINAL.qxd 3/23/07 1:26 PM Page iii
Sometimes the wind blows
the majestic across my face.
And when Iopen my eyes,
I see wonders
of truth,of depth,
and mysteries
unveiled.
802400FMFINAL.qxd 3/23/07 1:26 PM Page iv
802400FMFINAL.qxd 3/23/07 1:26 PM Page v
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
■ CHAPTER 1 Introduction to Performance Forecasting. . . . . . . . . . . . . . . . . . . . . . . . 1
■ CHAPTER 2 Essential Performance Forecasting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
■ CHAPTER 3 Increasing Forecast Precision. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
■ CHAPTER 4 Basic Forecasting Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
■ CHAPTER 5 Practical Queuing Theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
■ CHAPTER 6 Methodically Forecasting Performance. . . . . . . . . . . . . . . . . . . . . . . . 139
■ CHAPTER 7 Characterizing the Workload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
■ CHAPTER 8 Ratio Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
■ CHAPTER 9 Linear Regression Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
■ CHAPTER 10 Scalability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
v
802400FMFINAL.qxd 3/23/07 1:26 PM Page vi
802400FMFINAL.qxd 3/23/07 1:26 PM Page vii
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
■ CHAPTER 1 Introduction to Performance Forecasting. . . . . . . . . . . . . . . . . . 1
Risk:A Four-Letter Word. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Service-Level Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Modeling:Making the Complex Simple. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Model Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Mathematical Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Benchmark Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Simulation Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Differences Between Benchmarks and Simulations. . . . . . . . . . . . . . 8
Challenges in Forecasting Oracle Performance. . . . . . . . . . . . . . . . . . . . . . . 9
■ CHAPTER 2 Essential Performance Forecasting. . . . . . . . . . . . . . . . . . . . . . . . 13
The Computing System Is Alive. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Transactions Are Units of Work. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
The Arrival Rate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
The Transaction Processor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
The Queue. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Transaction Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
The Response Time Curve. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
CPU and IO Subsystem Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Method Is a Must. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Data Collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Essential Mathematics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
The Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
The Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
What Management Needs to Know . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
vii
802400FMFINAL.qxd 3/23/07 1:26 PM Page viii
viii ■ CONTENTS
Risk Mitigation Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Tuning the Application and Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Buying More CPU Capacity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Balancing Existing Workload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
■ CHAPTER 3 Increasing Forecast Precision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Forecasting Gotchas! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Model Selection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Questions to Ask . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Fundamental Forecasting Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Baseline Selection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Response Time Mathematics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Erlang C Forecasting Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Contrasting Forecasting Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Average Calculation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
The Right Distribution Pattern. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
How to Average Diverse Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Case Study:Highlight Company . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Determine the Study Question. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Gather and Characterize Workload. . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Select the Forecast Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Forecast and Validate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
What We Tell Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
■ CHAPTER 4 Basic Forecasting Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
What Is Statistics?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Sample vs.Population . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Describing Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Numerically Describing Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Visually Describing Data Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Fully Describing Sample Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Making Inferences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Precision That Lies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
802400FMFINAL.qxd 3/23/07 1:26 PM Page ix
■ CONTENTS ix
■ CHAPTER 5 Practical Queuing Theory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Queuing System Notation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Little’s Law. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Kendall’s Notation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
The Queuing Theory Workbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Queuing Configurations and Response Time Curve Shifts. . . . . . . . . . . . 114
Observing the Effects of Different Queuing Configurations . . . . . . 114
Moving the Response Time Curve Around. . . . . . . . . . . . . . . . . . . . . 119
Challenges in Queuing Theory Application . . . . . . . . . . . . . . . . . . . . . . . . . 124
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
■ CHAPTER 6 Methodically Forecasting Performance . . . . . . . . . . . . . . . . . . 139
The Need for a Method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
The OraPub Forecasting Method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Determine the Study Question. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Gather the Workload Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Characterize the Workload. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Develop and Use the Appropriate Model. . . . . . . . . . . . . . . . . . . . . . 145
Validate the Forecast. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Forecast. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
■ CHAPTER 7 Characterizing the Workload. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
The Challenge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Gathering the Workload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Gathering Operating System Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Gathering Oracle Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Defining Workload Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Modeling the Workload. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Simple Workload Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Single-Category Workload Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Multiple-Category Workload Model. . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Selecting the Peak. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Selecting a Single Sample. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Summarizing Workload Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184