Optimizing Oracle Performance

Optimizing Oracle Performance dispenses with database- centric metrics and instead focuses on the issue of analyzing the database's response time to all of the different sorts of requests made of it. Former VP of Oracle's System Performance Group, Cary Millsap, clearly shows how eliminating requests for unneeded work improves performance dramatically. Cary also shows how the mathematical discipline of queuing theory can be applied to remove the guesswork, frustration, and expense associated with determining the true root cause of any type of performance problem.

Cary Millsap is the former Vice President of Oracle's System Performance Group and the cofounder of Hotsos, a company dedicated to Oracle system performance. Hotsos provides performance-improvement tools for Oracle environments and also delivers training in the form of clinics and symposiums. Cary is also a founding member of the Oak Table Network (http://www.oaktable.net), an informal association of "Oracle Scientists" well known throughout the Oracle community.

Jeff Holt is one of the world's most productive Oracle performance optimization specialists. He has tremendous experience in constructing training programs and software tools to optimize the system performance management process. He is a former support analyst and consultant at Oracle Corporation, where he served as a technology leader in the System Performance Group. He is the Hotsos Tools lead designer and developer, the author of several technical papers, a Hotsos founding employee, and a Hotsos Clinic principal developer.

Foreword

xi

Preface

xiii

Part I. Method

1. A Better Way to Optimize

3

(30)

"You're Doing It Wrong"

5

(1)

Requirements of a Good Method

6

(2)

Three Important Advances

8

(4)

Tools for Analyzing Response Time

12

(7)

Method R

19

(14)

2. Targeting the Right User Actions

33

(12)

Specification Reliability

33

(5)

Making a Good Specification

38

(5)

Specification Over-Constraint

43

(2)

3. Targeting the Right Diagnostic Data

45

(13)

Expectations About Data Collection

45

(3)

Data Scope

48

(7)

Oracle Diagnostic Data Sources

55

(2)

For More Information

57

(1)

4. Targeting the Right Improvement Activity

58

(15)

A New Standard of Customer Care

58

(1)

How to Find the Economically Optimal Performance Improvement Activity

59

(1)

Making Sense of Your Diagnostic Data

60

(2)

Forecasting Project Net Payoff

62

(11)

Part II. Reference

5. Interpreting Extended SQL Trace Data

73

(36)

Trace File Walk-Through

73

(3)

Extended SQL Trace Data Reference

76

(10)

Response Time Accounting

86

(8)

Evolution of the Response Time Model

94

(4)

Walking the Clock

98

(4)

Forward Attribution

102

(2)

Detailed Trace File Walk-Through

104

(2)

Exercises

106

(3)

6. Collecting Extended SQL Trace Data

109

(32)

Understanding Your Application

109

(3)

Activating Extended SQL Trace

112

(7)

Finding Your Trace File(s)

119

(7)

Eliminating Collection Error

126

(14)

Exercises

140

(1)

7. Oracle Kernel Timings

141

(36)

Operating System Process Management

141

(3)

Oracle Kernel Timings

144

(1)

How Software Measures Itself

145

(4)

Unaccounted-for Time

149

(1)

Measurement Intrusion Effect

150

(3)

CPU Consumption Double-Counting

153

(2)

Quantization Error

155

(15)

Time Spent Not Executing

170

(3)

Un-Instrumented Oracle Kernel Code

173

(2)

Exercises

175

(2)

8. Oracle Fixed View Data

177

(43)

Deficiencies of Fixed View Data

178

(9)

Fixed View Reference

187

(7)

Useful Fixed View Queries

194

(23)

The Oracle "Wait Interface"

217

(1)

Exercises

218

(2)

9. Queueing Theory for the Oracle Practitioner

220

(65)

Performance Models

221

(1)

Queueing

222

(3)

Queueing Theory

225

(18)

The M/M/m Queueing Model

243

(36)

Perspective

279

(1)

Exercises

280

(5)

Part III. Deployment

10. Working the Resource Profile

285

(20)

How to Work a Resource Profile

286

(14)

How to Forecast Improvement

300

(2)

How to Tell When Your Work Is Done

302

(3)

11. Responding to the Diagnosis

305

(21)

Beyond the Resource Profile

306

(1)

Response Time Components

307

(9)

Eliminating Wasteful Work

316

(8)

Attributes of a Scalable Application

324

(2)

12. Case Studies

326

(29)

Case 1: Misled by System-Wide Data

327

(6)

Case 2: Large CPU Service Duration

333

(4)

Case 3: Large SQL*Net Event Duration

337

(7)

Case 4: Large Read Event Duration

344

(7)

Conclusion

351

Part IV. Appendixes

A. Glossary

355

(8)

B. Greek Alphabet

363

(2)

C. Optimizing Your Database Buffer Cache Hit Ratio

365

(7)

D. M/M/m Queueing Theory Formulas

372

(2)

E. References

374

(7)

Index

381

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Optimizing Oracle Performance

Summary

Author Biography

Table of Contents

Supplemental Materials

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Optimizing Oracle Performance

Summary

Author Biography

Table of Contents

Supplemental Materials

Digital License