book

Optimizing Oracle Performance

by Cary Millsap, Jeff Holt

September 2003

Intermediate to advanced

416 pages

14h 1m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Foreword
Preface
Why I Wrote This BookAudience for This BookStructure of This BookWhich Platform and Version?What This Book Is and Is NotAbout the Tools, Examples, and ExercisesCitationsConventions Used in This BookComments and QuestionsAcknowledgments
I. Method
1. A Better Way to Optimize
1.1. “You’re Doing It Wrong”1.2. Requirements of a Good Method1.3. Three Important Advances1.3.1. User Action Focus1.3.2. Response Time Focus1.3.3. Amdahl’s Law1.3.4. All Together Now1.4. Tools for Analyzing Response Time1.4.1. Sequence Diagram1.4.2. Resource Profile1.5. Method R1.5.1. Who Uses the Method1.5.1.1. The abominable smokestack1.5.1.2. The optimal performance analyst1.5.1.3. Your role1.5.2. Overcoming Common Objections1.5.2.1. “But my whole system is slow”1.5.2.2. “The method only works if the problem is the database”1.5.2.3. “The method is unconventional”1.5.3. Evaluation of Effectiveness
2. Targeting the Right User Actions
2.1. Specification Reliability2.1.1. The System2.1.2. Economic Constraints2.2. Making a Good Specification2.2.1. User Action2.2.2. Identifying the Right User Actions and Contexts2.2.3. Prioritizing the User Actions2.2.4. Determining Who Will Execute Each Action and When2.3. Specification Over-Constraint
3. Targeting the Right Diagnostic Data
3.1. Expectations About Data Collection3.2. Data Scope3.2.1. Scoping Errors3.2.2. Long-Running User Actions3.2.3. “Too Much Data” Is Really Not Enough Data3.3. Oracle Diagnostic Data Sources3.4. For More Information
4. Targeting the Right Improvement Activity
4.1. A New Standard of Customer Care4.2. How to Find the Economically Optimal Performance Improvement Activity4.3. Making Sense of Your Diagnostic Data4.4. Forecasting Project Net Payoff4.4.1. Forecasting Project Benefits4.4.1.1. Monetizing the benefits4.4.1.2. If you can’t monetize the benefits4.4.2. Forecasting Project Cost4.4.3. Forecasting Project Risk
II. Reference

5. Interpreting Extended SQL Trace Data
5.1. Trace File Walk-Through5.2. Extended SQL Trace Data Reference5.2.1. Trace File Element Definitions5.2.1.1. Cursor numbers5.2.1.2. Session identification and timestamps5.2.1.3. Application identification5.2.1.4. Cursor identification5.2.1.5. Database calls5.2.1.6. Wait events5.2.1.7. Bind variables5.2.1.8. Row source operations5.2.1.9. Transaction end markers5.2.1.10. Reference summary5.2.2. Oracle Time Units5.3. Response Time Accounting5.3.1. Time Within a Database Call5.3.2. Time Between Database Calls5.3.3. Recursive SQL Double-Counting5.3.3.1. Parent-child relationships5.3.3.2. Recursive statistics5.4. Evolution of the Response Time Model5.5. Walking the Clock5.5.1. Oracle Release 8 and Prior5.5.2. Oracle Release 95.5.3. Clock Walk Formulas5.6. Forward Attribution5.6.1. Forward Attribution for Within-Call Events5.6.2. Forward Attribution for Between-Call Events5.7. Detailed Trace File Walk-Through5.8. Exercises
6. Collecting Extended SQL Trace Data
6.1. Understanding Your Application6.2. Activating Extended SQL Trace6.2.1. Tracing Your Own Source Code6.2.2. Tracing Someone Else’s Source Code6.2.2.1. Triggering a session to activate its own trace6.2.2.2. Activating trace from a third-party session6.3. Finding Your Trace File(s)6.3.1. Trace File Names6.3.2. Simple Client-Server Applications6.3.3. Oracle Parallel Execution6.3.4. Oracle Multi-Threaded Server6.3.5. Connection-Pooling Applications6.3.6. Some Good News6.4. Eliminating Collection Error6.4.1. Time Scope Errors at Trace Activation6.4.1.1. Missing wait event data at trace activation6.4.1.2. Missing database call data at trace activation6.4.1.3. Excess database call data at trace activation6.4.2. Missing Time at Trace Deactivation6.4.3. Incomplete Recursive SQL Data6.5. Exercises
7. Oracle Kernel Timings
7.1. Operating System Process Management7.1.1. The sys call Transition7.1.2. The interrupt Transition7.1.3. Other States and Transitions7.2. Oracle Kernel Timings7.3. How Software Measures Itself7.3.1. Elapsed Time7.3.2. CPU Consumption7.4. Unaccounted-for Time7.5. Measurement Intrusion Effect7.6. CPU Consumption Double-Counting7.7. Quantization Error7.7.1. Measurement Resolution7.7.2. Definition of Quantization Error7.7.3. Complications in Measuring CPU Consumption7.7.3.1. How gettimeofday works7.7.3.2. How getrusage works7.7.4. Detection of Quantization Error7.7.5. Bounds of Quantization Error7.8. Time Spent Not Executing7.8.1. Instrumenting the Experiment7.8.2. Process States and Transitions Revisited7.9. Un-Instrumented Oracle Kernel Code7.9.1. Effect7.9.2. Trace Writing7.10. Exercises
8. Oracle Fixed View Data
8.1. Deficiencies of Fixed View Data8.1.1. Too Many Data Sources8.1.2. Lack of Detail8.1.3. Measurement Intrusion Effect of Polling8.1.4. Difficulty of Proper Action-Scoping8.1.5. Difficulty of Proper Time-Scoping8.1.6. Susceptibility to Overflow and Other Errors8.1.7. Lack of Database Call Duration Data8.1.8. Lack of Read Consistency8.2. Fixed View Reference8.2.1. V$SQL8.2.2. V$SESS_IO8.2.3. V$SYSSTAT8.2.4. V$SESSTAT8.2.5. V$SYSTEM_EVENT8.2.6. V$SESSION_EVENT8.2.7. V$SESSION_WAIT8.3. Useful Fixed View Queries8.3.1. Tom Kyte’s Test Harness8.3.2. Finding a Fixed View Definition8.3.3. Finding Inefficient SQL8.3.4. Finding Where a Session Is Stuck8.3.5. Finding Where a System Is Stuck8.3.6. Approximating a Session’s Resource Profile8.3.7. Viewing Waits System-Wide8.3.7.1. The “idle events” problem8.3.7.2. The denominator problem8.3.7.3. Infinite capacity for waiting8.3.7.4. Idle events in background sessions8.3.7.5. Targeting revisited8.4. The Oracle “Wait Interface”8.5. Exercises
9. Queueing Theory for the Oracle Practitioner
9.1. Performance Models9.2. Queueing9.2.1. Queueing Economics9.2.2. Queueing Visualized9.3. Queueing Theory9.3.1. Model Input and Output Values9.3.1.1. Arrivals and completions9.3.1.2. Service channels, utilization, and stability9.3.1.3. Service time and service rate9.3.1.4. Queueing delay and response time9.3.1.5. Maximum effective throughput9.3.1.6. Cumulative distribution function (CDF) of response time9.3.2. Random Variables9.3.2.1. Expected value9.3.2.2. Probability density function (pdf)9.3.2.3. Using the pdf9.3.2.4. Why understanding distribution is important9.3.3. Queueing Theory Versus the “Wait Interface”9.3.3.1. Oracle wait times9.3.3.2. Differences in queueing theory notation9.4. The M/M/m Queueing Model9.4.1. M/M/m Systems9.4.2. Non-M/M/m Systems9.4.3. Exponential Distribution9.4.3.1. Poisson-exponential relationship9.4.3.2. Testing for fit to exponential distribution9.4.3.3. A program to test for exponential distribution9.4.4. Behavior of M/M/m Systems9.4.4.1. Multi-channel scalability9.4.4.2. The knee9.4.4.3. Response time fluctuations9.4.4.4. Parameter sensitivity9.4.5. Using M/M/m: Worked Example9.4.5.1. Suitability for modeling with M/M/m9.4.5.2. Computing the required number of CPUs9.4.5.3. What we can learn from an optimistic model9.4.5.4. Negotiating the negotiable parameters9.4.5.5. Using Goal Seek in Microsoft Excel9.4.5.6. Sensitivity analysis9.5. Perspective9.6. Exercises
III. Deployment
10. Working the Resource Profile
10.1. How to Work a Resource Profile10.1.1. Work in Descending Response Time Order10.1.1.1. Why targeting is vital10.1.1.2. Possible benefits of low-return improvements10.1.2. Eliminate Unnecessary Calls10.1.2.1. Why workload elimination works so well10.1.2.2. Supply and demand in the technology stack10.1.2.3. How to eliminate calls10.1.2.4. Thinking in a bigger box10.1.3. Eliminate Inter-Process Competition10.1.3.1. How to attack a latency problem10.1.3.2. How to find competing workload10.1.4. Upgrade Capacity10.2. How to Forecast Improvement10.3. How to Tell When Your Work Is Done
11. Responding to the Diagnosis
11.1. Beyond the Resource Profile11.2. Response Time Components11.2.1. Oracle Pseudoevents11.2.1.1. CPU service11.2.1.2. unaccounted-for11.2.2. No Event Is Inherently “Unimportant”11.2.2.1. Responding to large SQL*Net response time contributions11.2.2.2. Responding to large response time contributions from other events11.3. Eliminating Wasteful Work11.3.1. Logical I/O Optimization11.3.1.1. Why LIO problems are so common11.3.1.2. How to optimize SQL11.3.2. Parse Optimization11.3.3. Write Optimization11.4. Attributes of a Scalable Application
12. Case Studies
12.1. Case 1: Misled by System-Wide Data12.1.1. Targeting12.1.2. Diagnosis and Response12.1.3. Results12.1.4. Lessons Learned12.2. Case 2: Large CPU Service Duration12.2.1. Targeting12.2.2. Diagnosis and Response12.2.3. Results12.2.4. Lessons Learned12.3. Case 3: Large SQL*Net Event Duration12.3.1. Targeting12.3.2. Diagnosis and Response12.3.3. Results12.3.4. Lessons Learned12.4. Case 4: Large Read Event Duration12.4.1. Targeting12.4.2. Diagnosis and Repair12.4.3. Results12.4.4. Lessons Learned12.5. Conclusion
IV. Appendixes
Glossary
A. Greek Alphabet
B. Optimizing Your Database Buffer Cache Hit Ratio
C. M/M/m Queueing Theory Formulas
D. References
Index
About the Authors
Colophon
Copyright

Content preview from Optimizing Oracle Performance

Chapter 11. Responding to the Diagnosis

To improve Oracle performance, you must of course understand the technology of each response time component that contributes significantly to your targeted user action’s response time. The place you should begin your research is the Oracle Database Concepts guide at http://technet.oracle.com. The response time components that show up in your resource profiles associate directly to instrumented Oracle kernel actions described in the Concepts guide. For example, it describes how the Oracle LGWR process copies content from the redo log buffer to an online redo log file. An Oracle kernel process accounts for the time it spends waiting on LGWR to perform this particular action with the wait event called log file sync.

There are lots of other such events. The number of wait events inside the Oracle kernel has grown with each new release, as shown in Table 11-1. Thankfully, it is not important for you to know a lot of details about every Oracle wait event. You usually don’t need the gory details in your brain for any more than a couple of wait events at a time—the ones that are dominating your targeted user action at the moment. This is excellent news, because some of the events require some study time to understand. Rather than learn and try to retain a lot of details about dozens of events, I think it is more important to focus on the following:

Know how to target the events that are important to you right now. I describe how to do this in Part ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 059600527XErrata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Optimizing Oracle Performance

by Cary Millsap, Jeff Holt

Chapter 11. Responding to the Diagnosis

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.