Optimizing Oracle Performance

Oracle DBAs and developers are all too familiar with the outlay of time and resources, blown budgets, missed deadlines, and marginally effective performance fiddling that is commonplace with traditional methods of Oracle performance tuning. In Optimizing Oracle Performance, Cary Millsap, former VP of Oracle's System Performance Group, clearly and concisely explains how to use Oracle's response time statistics to diagnose and repair performance problems. Cary also shows how "queueing theory" can be applied to response time statistics to predict the impact of upgrades and other system changes. The price of this essential book will be paid back in hours saved the first time its methods are used.

Table of Contents

Foreword

Preface

Part I. Method

1. A Better Way to Optimize

     "You're Doing It Wrong"

     Requirements of a Good Method

     Three Important Advances

     Tools for Analyzing Response Time

     Method R

2. Targeting the Right User Actions

     Specification Reliability

     Making a Good Specification

     Specification Over-Constraint

3. Targeting the Right Diagnostic Data

     Expectations About Data Collection
     Data Scope

     Oracle Diagnostic Data Sources

     For More Information

4. Targeting the Right Improvement Activity

     A New Standard of Customer Care

     How to Find the Economically Optimal Performance Improvement Activity

     Making Sense of Your Diagnostic Data

     Forecasting Project Net Payoff

Part II. Reference

5. Interpreting Extended SQL Trace Data

     Trace File Walk-Through

     Extended SQL Trace Data Reference

     Response Time Accounting

     Evolution of the Response Time Model

     Walking the Clock

     Forward Attribution

     Detailed Trace File Walk-Through

     Exercises

6. Collecting Extended SQL Trace Data

     Understanding Your Application

     Activating Extended SQL Trace

     Finding Your Trace File(s)

     Eliminating Collection Error

     Exercises

7. Oracle Kernel Timings

     Operating System Process Management

     Oracle Kernel Timings

     How Software Measures Itself

     Unaccounted-for Time

     Measurement Intrusion Effect

     CPU Consumption Double-Counting

     Quantization Error

     Time Spent Not Executing

     Un-Instrumented Oracle Kernel Code

     Exercises

8. Oracle Fixed View Data

     Deficiencies of Fixed View Data

     Fixed View Reference

     Useful Fixed View Queries

     The Oracle "Wait Interface"

     Exercises

9. Queueing Theory for the Oracle Practitioner

     Performance Models

     Queueing

     Queueing Theory

     The M/M/m Queueing Model

     Perspective

     Exercises

Part III. Deployment

10. Working the Resource Profile

     How to Work a Resource Profile

     How to Forecast Improvement

     How to Tell When Your Work Is Done

11. Responding to the Diagnosis

     Beyond the Resource Profile

     Response Time Components

     Eliminating Wasteful Work

     Attributes of a Scalable Application

12. Case Studies

     Case 1: Misled by System-Wide Data

     Case 2: Large CPU Service Duration

     Case 3: Large SQL*Net Event Duration

     Case 4: Large Read Event Duration

     Conclusion

Part IV. Appendixes

A. Glossary

B. Greek Alphabet

C. Optimizing Your Database Buffer Cache Hit Ratio

D. M/M/m Queueing Theory Formulas

E. References

Index