Index

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Index

A note on the digital index

A link in an index entry is displayed as the section title in which that entry appears. Because some sections have multiple index markers, it is not unusual for an entry to have several links to the same section. Clicking on any link will take you directly to the place in the text in which the marker appears.

A

Adams, Steve, Responding to the Diagnosis

ALTER SESSION SET EVENTS command, Time Scope Errors at Trace Activation

Amdahl’s Law, Amdahl’s Law, Glossary

analyzing response time, Tools for Analyzing Response Time–Resource Profile

resource profiles, Resource Profile–Resource Profile
sequence diagrams, Sequence Diagram–Sequence Diagram

application identification, SQL trace data files, Application identification

applications

client-server, finding trace files for, Simple Client-Server Applications
connection-pooling, finding trace files for, Connection-Pooling Applications–Some Good News
packaged software, convincing vendors to improve performance of, Eliminating Wasteful Work
scalable, attributes of, Attributes of a Scalable Application

architecture, multitier application, Parse Optimization

B

BACKGROUND_DUMP_DEST parameter, Finding Your Trace File(s)

directory, Trace Writing
MTS and, Oracle Multi-Threaded Server

bind variables

avoiding hard parses with, Parse Optimization
SQL trace data files, Bind variables

buffer busy wait events, “Too Much Data” Is Really Not Enough Data, Logical I/O Optimization

C

cache hit ratio (see CHR)

capacity upgrades, Upgrade Capacity

case studies, performance improvement projects, Case Studies–Conclusion

large CPU service duration, Case 2: Large CPU Service Duration–Lessons Learned

diagnosis/response, Diagnosis and Response
lessons learned, Lessons Learned
results, Results
targeting response time, Targeting–Targeting

large read event duration, Case 4: Large Read Event Duration–Lessons Learned

diagnosis/repair, Diagnosis and Repair
lessons learned, Lessons Learned
results, Results
targeting response time, Targeting

large SQL*Net event duration, Case 3: Large SQL*Net Event Duration–Lessons Learned

diagnosis/response, Diagnosis and Response–Diagnosis and Response
lessons learned, Lessons Learned
results, Results
targeting response time, Targeting

misleading systemwide data, Case 1: Misled by System-Wide Data–Lessons Learned

diagnosis/response, Diagnosis and Response–Diagnosis and Response
lessons learned, Lessons Learned
results, Results
targeting response time, Targeting

CBO (cost-based query optimizer), Resource Profile

CDF (cumulative distribution function), Cumulative distribution function (CDF) of response time, Glossary

CHR (cache hit ratio), Resource Profile

optimizing, Optimizing Your Database Buffer Cache Hit Ratio

client-server applications, finding trace files for, Simple Client-Server Applications

collecting

diagnostic data

aggregating, “Too Much Data” Is Really Not Enough Data
expectations about, Expectations About Data Collection–Expectations About Data Collection
for long-running user actions, Long-Running User Actions
scope, Data Scope–“Too Much Data” Is Really Not Enough Data
scoping errors, Scoping Errors–Scoping Errors

extended SQL trace data, Collecting Extended SQL Trace Data–Exercises

collection errors, Eliminating Collection Error–Incomplete Recursive SQL Data
understanding application architectures, Understanding Your Application–Understanding Your Application

commercial approach, to repairing response time problems, How to Work a Resource Profile

Compulsive Tuning Disorder (see CTD)

connection-pooling applications, finding trace files for, Connection-Pooling Applications–Connection-Pooling Applications

cost constraint, improvement projects, Forecasting Project Cost

cost-based query optimizer (CBO), Resource Profile

CPU consumption, CPU Consumption

bad targets, Response time fluctuations

double-counting, CPU Consumption Double-Counting–CPU Consumption Double-Counting

measuring, complications in, Complications in Measuring CPU Consumption–How getrusage works

how getrusage works, How getrusage works–How getrusage works
how gettimeofday works, How gettimeofday works

CPU service events, Response Time Focus, “Too Much Data” Is Really Not Enough Data, Approximating a Session’s Resource Profile, The denominator problem, Queueing Visualized, Response Time Components, CPU service

creating specifications for performance improvement projects, Making a Good Specification–Determining Who Will Execute Each Action and When

determining who executes user actions, Determining Who Will Execute Each Action and When
identifying user actions, User Action–Identifying the Right User Actions and Contexts
prioritizing user actions, Prioritizing the User Actions

CTD (Compulsive Tuning Disorder), Expectations About Data Collection, Glossary

lack of termination condition in conventional Oracle tuning methods, How to Tell When Your Work Is Done

cumulative distribution function (see CDF)

cursor identification, SQL trace data files, Cursor identification

cursor numbers, SQL trace data files, Cursor numbers

customer care, new standard of, A New Standard of Customer Care

D

data

diagnostic (see diagnostic data)

extended SQL trace (see extended SQL trace data)

Oracle fixed view, Oracle Fixed View Data–Exercises

deficiences of, Deficiencies of Fixed View Data–Lack of Read Consistency

database buffer cache, Logical I/O Optimization

hit ratio, Glossary

optimizing, Optimizing Your Database Buffer Cache Hit Ratio
unreliability of, Optimizing Your Database Buffer Cache Hit Ratio

database calls, Glossary

between-call events, Responding to large SQL*Net response time contributions

duration data, lack of, with Oracle fixed view data, Lack of Database Call Duration Data

eliminating unnecessary when working resource profiles, Eliminate Unnecessary Calls–Thinking in a bigger box

supply/demand in technology stack, Supply and demand in the technology stack

LIO count, Logical I/O Optimization

parent-child relationships, Parent-child relationships

recursive statistics, Recursive statistics–Recursive statistics

SQL trace data files, Database calls

recursive SQL, Recursive SQL Double-Counting–Recursive statistics
response time accounting, Response Time Accounting–Recursive statistics

time between, Time Between Database Calls

forward attribution, Forward Attribution for Between-Call Events
missing, Effect

time within, Time Within a Database Call

forward attribution, Forward Attribution for Within-Call Events
missing, Effect

db file scattered read events, Difficulty of Proper Time-Scoping, The denominator problem, unaccounted-for

db file sequential read events, Forward Attribution for Within-Call Events, Oracle Kernel Timings, Infinite capacity for waiting

db scattered read events, “Too Much Data” Is Really Not Enough Data

DBMS_SUPPORT package, Activating trace from a third-party session

DBMS_SYSTEM package, Activating trace from a third-party session

determinism, Expectations About Data Collection

diagnosis, responding to, Responding to the Diagnosis–Attributes of a Scalable Application

case study of large CPU service duration, Diagnosis and Response

case study of large read event duration, Diagnosis and Repair

case study of large SQL*Net event duration, Diagnosis and Response–Diagnosis and Response

case study of systemwide data, Diagnosis and Response–Diagnosis and Response

eliminating wasteful work, Eliminating Wasteful Work–Write Optimization

LIO optimization, Logical I/O Optimization–How to optimize SQL
parse optimization, Parse Optimization–Parse Optimization
SQL optimization, How to optimize SQL
why LIO problems are common, Why LIO problems are so common
write optimization, Write Optimization

response time components, Response Time Components–Responding to large response time contributions from other events

diagnostic data, Interpreting Extended SQL Trace Data

(see also extended SQL trace data)

collecting

aggregating, “Too Much Data” Is Really Not Enough Data
expectations about, Expectations About Data Collection–Expectations About Data Collection
for long-running user actions, Long-Running User Actions
Oracle’s operational timing data, Oracle Diagnostic Data Sources–Oracle Diagnostic Data Sources
scope, Data Scope–“Too Much Data” Is Really Not Enough Data
scoping errors, Scoping Errors–Scoping Errors

making sense of, Making Sense of Your Diagnostic Data–Making Sense of Your Diagnostic Data

targeting the right, Targeting the Right Diagnostic Data–For More Information

documentation

Oracle database product, Responding to the Diagnosis
Oracle9i release 2 performance, Response Time Components

E

E-Business Suite, newer releases of, Expectations About Data Collection

enqueue event, V$SESSION_WAIT, Approximating a Session’s Resource Profile

Ensor, Dave, Overcoming Common Objections, How to Work a Resource Profile

Erlang C formula, Queueing delay and response time

Erlang, Agner, Queueing delay and response time, Using the pdf, Non-M/M/m Systems, Poisson-exponential relationship, Glossary

errors

collecting extended SQL trace data, Eliminating Collection Error–Incomplete Recursive SQL Data

incomplete recursive SQL data, Incomplete Recursive SQL Data–Exercises

missing time, at trace deactivation, Missing Time at Trace Deactivation–Missing Time at Trace Deactivation

overflow, Glossary

Oracle fixed view data, susceptibility to, Susceptibility to Overflow and Other Errors

quantization, Quantization Error–Bounds of Quantization Error, Glossary

bounds of, Bounds of Quantization Error–Bounds of Quantization Error
CPU consumption, complications in measuring, Complications in Measuring CPU Consumption–How getrusage works
definition of, Definition of Quantization Error–Definition of Quantization Error
detecting, Detection of Quantization Error–Detection of Quantization Error
measurement resolution, Measurement Resolution–Measurement Resolution

time scope, at trace activation, Time Scope Errors at Trace Activation–Excess database call data at trace activation

event counts, Response Time Focus

events, Oracle wait, unaccounted-for, Glossary

(see also idle events)
attributing, Glossary
buffer busy, “Too Much Data” Is Really Not Enough Data, Logical I/O Optimization
CPU service, Response Time Focus, “Too Much Data” Is Really Not Enough Data, Approximating a Session’s Resource Profile, The denominator problem, Queueing Visualized, Response Time Components, CPU service
db file scattered read, “Too Much Data” Is Really Not Enough Data, Difficulty of Proper Time-Scoping, The denominator problem, unaccounted-for
db file sequential read, Forward Attribution for Within-Call Events, Oracle Kernel Timings, Infinite capacity for waiting
enqueue, V$SESSION_WAIT, Approximating a Session’s Resource Profile
free buffer, Logical I/O Optimization, Write Optimization
latch free, User Action Focus, “Too Much Data” Is Really Not Enough Data, Difficulty of Proper Action-Scoping, How to Tell When Your Work Is Done, Logical I/O Optimization, Parse Optimization, Case 1: Misled by System-Wide Data
log file switch completion, Finding Where a System Is Stuck, Logical I/O Optimization
log file sync, V$SESSION_WAIT, Responding to the Diagnosis, Logical I/O Optimization
pmon timer, The “idle events” problem, Idle events in background sessions
PX Deq Credit: need buffer, unaccounted-for
PX events, Responding to large response time contributions from other events
rdbms ipc message, The “idle events” problem, The denominator problem, Idle events in background sessions
single-task message, Responding to large SQL*Net response time contributions
smon timer, The “idle events” problem, Idle events in background sessions
SQL*Net message from client, Response Time Focus, Forward Attribution for Between-Call Events, Missing wait event data at trace activation, Approximating a Session’s Resource Profile, The “idle events” problem–The denominator problem, No Event Is Inherently “Unimportant”, No Event Is Inherently “Unimportant”, Parse Optimization, Targeting, Targeting
SQL*Net message to client, No Event Is Inherently “Unimportant”, No Event Is Inherently “Unimportant”
SQL*Net more data from client, Responding to large SQL*Net response time contributions, Parse Optimization, Diagnosis and Response
START_SQL_TRACE_IN_SESSION, Activating trace from a third-party session
write complete, Logical I/O Optimization

extended SQL trace data, Evolution of the Response Time Model

activating, Activating Extended SQL Trace

collecting, Collecting Extended SQL Trace Data–Exercises

understanding application architectures, Understanding Your Application–Understanding Your Application

collection errors, eliminating, Eliminating Collection Error–Incomplete Recursive SQL Data

extracting response time information from, Walking the Clock–Clock Walk Formulas

files

application identification, Application identification
bind variables, Bind variables
confidentiality/sensitivity of, Collecting Extended SQL Trace Data
cursor identification, Cursor identification
cursor numbers, Cursor numbers
database calls, Database calls
element definitions, Trace File Element Definitions–Reference summary
file names, Trace File Names
finding, Finding Your Trace File(s)–Some Good News
recursive relationships among database calls, Recursive SQL Double-Counting–Recursive statistics
response time accounting, Response Time Accounting–Recursive statistics
row source operations, Row source operations
session identification, Session identification and timestamps
time between database calls, Time Between Database Calls
time within a database call, Time Within a Database Call
timestamps, Session identification and timestamps
transaction end markers, Transaction end markers
wait events, Wait events
walk-through, Trace File Walk-Through–Trace File Walk-Through, Detailed Trace File Walk-Through–Detailed Trace File Walk-Through
writing to, Trace Writing

incomplete recursive SQL data, Incomplete Recursive SQL Data–Exercises

interpreting, Interpreting Extended SQL Trace Data–Exercises

missing time errors at trace deactivation, Missing Time at Trace Deactivation–Missing Time at Trace Deactivation

from Oracle8i and earlier kernels, walking the clock through, Oracle Release 8 and Prior

from Oracle9i kernel, walking the clock through, Oracle Release 9

time scope errors at trace activation, Time Scope Errors at Trace Activation–Excess database call data at trace activation

tracing someone else’s source code, Tracing Someone Else’s Source Code–Activating trace from a third-party session

tracing your own source code, Tracing Your Own Source Code–Tracing Your Own Source Code

underutilization of, reasons for, Why I Wrote This Book

F

file names, extended SQL trace files, Trace File Names

files, extended SQL trace

application identification, Application identification
bind variables, Bind variables
confidentiality/sensitivity of, Collecting Extended SQL Trace Data
cursor identification, Cursor identification
cursor numbers, Cursor numbers
database calls, Database calls
element definitions, Trace File Element Definitions–Reference summary
file names, Trace File Names
finding, Finding Your Trace File(s)–Some Good News
identification, Session identification and timestamps
from Oracle8i and earlier kernels, Oracle Release 8 and Prior
from Oracle9i kernel, Oracle Release 9
recursive relationships among database calls, Recursive SQL Double-Counting–Recursive statistics
response time accounting, Response Time Accounting–Recursive statistics
row source operations, Row source operations
time between database calls, Time Between Database Calls
time within a database call, Time Within a Database Call
timestamps, Session identification and timestamps
transaction end markers, Transaction end markers
wait events, Wait events
walk-through, Trace File Walk-Through–Trace File Walk-Through, Detailed Trace File Walk-Through–Detailed Trace File Walk-Through
writing to, Trace Writing

finding

economically optimal performance improvement activity, How to Find the Economically Optimal Performance Improvement Activity

extended SQL trace files, Finding Your Trace File(s)–Some Good News

for client-server applications, Simple Client-Server Applications
for connection-pooling applications, Connection-Pooling Applications–Some Good News
MTS and, Oracle Multi-Threaded Server

fixed view definition, Finding a Fixed View Definition

inefficient SQL, Finding Inefficient SQL–Finding Inefficient SQL

quantization errors, Detection of Quantization Error–Detection of Quantization Error

where session is stuck, Finding Where a Session Is Stuck

where system is stuck, Finding Where a System Is Stuck

focusing on

response time, Response Time Focus
user actions, User Action Focus

forecasting improvement projects, How to Forecast Improvement

benefits of, Forecasting Project Benefits–If you can’t monetize the benefits
cost of, Forecasting Project Cost
net payoff of, Forecasting Project Net Payoff–Forecasting Project Risk
risk of, Forecasting Project Risk

formulas

M/M/m queueing model, M/M/m Queueing Theory Formulas
queueing theory, use of Greek letters, Service channels, utilization, and stability
for walking the clock, Clock Walk Formulas

forward attribution, Forward Attribution for Within-Call Events, Glossary

free buffer wait events, Logical I/O Optimization, Write Optimization

G

geek approach, to repairing response time problems, How to Work a Resource Profile
getrusage operation, how it works, How getrusage works–How getrusage works
gettimeofday operation, how it works, How gettimeofday works
Greek alphabet, with English equivalents, Greek Alphabet

H

hardware upgrades, performance degradation and, A Better Way to Optimize
Heisenberg Uncertainty principle, performance analysis and, Measurement Resolution
Hotsos Profiler program, Why I Wrote This Book

I

idle events, The “idle events” problem, Glossary

in background sessions, Idle events in background sessions
importance of, No Event Is Inherently “Unimportant”–Responding to large response time contributions from other events
problem, The “idle events” problem

improvement activity

economically optimal, finding, How to Find the Economically Optimal Performance Improvement Activity
targeting the right, Targeting the Right Improvement Activity–Forecasting Project Risk

input/output, measuring, V$SESS_IO

inter-process competition, eliminating, when working resource profiles, Eliminate Inter-Process Competition

attacking latency problems, How to attack a latency problem
finding competing workload, How to find competing workload

interpreting extended SQL trace data, Interpreting Extended SQL Trace Data–Exercises, Trace File Walk-Through

(see also extended SQL trace data)

interrupt transition, The interrupt Transition

K

kernel mode, Operating System Process Management
“knee” of performance curve, The knee–The knee
Kolk, Anjo, Responding to the Diagnosis

L

latch contention, User Action Focus, Resource Profile

logical I/O optimization and, Logical I/O Optimization

latch free events, User Action Focus, “Too Much Data” Is Really Not Enough Data, Difficulty of Proper Action-Scoping, How to Tell When Your Work Is Done, Logical I/O Optimization, Parse Optimization, Case 1: Misled by System-Wide Data

latency, Glossary

problems with, attacking, How to attack a latency problem

Linux, M/M/m model and, Suitability for modeling with M/M/m

LIO (logical I/O), Resource Profile, Glossary

optimizing, Logical I/O Optimization–How to optimize SQL
problems, why they commonly occur, Why LIO problems are so common

log file switch completion events, Finding Where a System Is Stuck, Logical I/O Optimization

log file sync events, V$SESSION_WAIT, Responding to the Diagnosis, Logical I/O Optimization

logical I/O (see LIO)

loops, extracting parse calls from, Parse Optimization

M

M/M/m queueing model, The M/M/m Queueing Model–Sensitivity analysis, Glossary

behaviors of, Behavior of M/M/m Systems–Parameter sensitivity

multichannel scalability, Multi-channel scalability–Multi-channel scalability
parameter sensitivity, Parameter sensitivity–Parameter sensitivity
response time fluctations, Response time fluctuations
“knee” of performance curve, The knee–The knee

exponential distribution, Exponential Distribution

Poisson-expontential relationship, Poisson-exponential relationship
program to test for, A program to test for exponential distribution
testing for fit to, Testing for fit to exponential distribution

formulas, M/M/m Queueing Theory Formulas

M/M/m queueing systems, M/M/m Systems

non-M/M/m queueing systems, Non-M/M/m Systems

perspective on, Perspective

using M/M/m, worked example, Using M/M/m: Worked Example–Sensitivity analysis

checking for stability, Suitability for modeling with M/M/m–Suitability for modeling with M/M/m
computing required number of CPUs, Computing the required number of CPUs–Computing the required number of CPUs
negotiating negotiable parameters, Negotiating the negotiable parameters–Negotiating the negotiable parameters
perspective on, Perspective
sensitivity analysis, Sensitivity analysis
usefulness of optimistic model, What we can learn from an optimistic model
using Goal Seek in Microsoft Excel, Using Goal Seek in Microsoft Excel

mailing lists, optimizing SQL, How to optimize SQL

mean utilization per channel, Service channels, utilization, and stability

measurement intrusion effect, Glossary

Oracle kernel timing, Measurement Intrusion Effect–Measurement Intrusion Effect
of polling on Oracle fixed view data, Measurement Intrusion Effect of Polling

measuring

CPU consumption, complications in, Complications in Measuring CPU Consumption–How getrusage works

how getrusage works, How getrusage works–How getrusage works
how gettimeofday works, How gettimeofday works

input/output, V$SESS_IO

software, How Software Measures Itself–CPU Consumption

CPU consumption, CPU Consumption
elapsed time, Elapsed Time

memory

accesses, eliminating, Resource Profile
database buffer cache, Logical I/O Optimization
system global area, Logical I/O Optimization

Method C performance-tuning method, “You’re Doing It Wrong”

Method R performance-tuning method, Method R–Evaluation of Effectiveness

methods, Glossary

performance-tuning (see performance-tuning methods)

MTS (Multi-Threaded Server), Oracle Multi-Threaded Server

Multi-Threaded Server (MTS), Oracle Multi-Threaded Server

O

OCI (Oracle Call Interface), Parse Optimization

operating system process management, Operating System Process Management–Other States and Transitions

interrupt transition, The interrupt Transition
sys call transition, The sys call Transition

optimization, A Better Way to Optimize–Evaluation of Effectiveness, Glossary

database buffer cache hit ratio, Optimizing Your Database Buffer Cache Hit Ratio

LIO, Logical I/O Optimization–How to optimize SQL

parse, Parse Optimization–Parse Optimization

SQL, How to optimize SQL

technology, advances in, User Action Focus–All Together Now

Amdahl’s Law, Amdahl’s Law
response time focus, Response Time Focus

write operations, Write Optimization

Oracle

database product documentation, Responding to the Diagnosis

kernel

obtaining diagnostic data for code paths, V$SESSION_EVENT
timing (see Oracle kernel timing)

MetaLink support bulletins/bug reports, Responding to the Diagnosis

operation timing data sources, Oracle Diagnostic Data Sources–Oracle Diagnostic Data Sources

pseudoevents, Oracle Pseudoevents–unaccounted-for

unaccounted for, unaccounted-for

wait times, components of, Oracle wait times

“wait interface” [Oracle

wait interface (see wait interface)

Oracle Call Interface (OCI), Parse Optimization

Oracle fixed view data, Oracle Fixed View Data–Exercises

deficiencies of, Deficiencies of Fixed View Data–Lack of Read Consistency

difficulty of proper action-scoping, Difficulty of Proper Action-Scoping
difficulty of proper time-scoping, Difficulty of Proper Time-Scoping
lack of database call duration data, Lack of Database Call Duration Data
lack of detail, Lack of Detail
lack of read consistency, Lack of Read Consistency–Lack of Read Consistency
measurement intrusion effect of polling, Measurement Intrusion Effect of Polling
susceptibility to overflow/other errors, Susceptibility to Overflow and Other Errors
too many data sources, Too Many Data Sources

Oracle “wait interface”, The Oracle “Wait Interface”

SQL queries, Useful Fixed View Queries–Targeting revisited

approximating session’s resource profile, Approximating a Session’s Resource Profile–Approximating a Session’s Resource Profile
finding fixed view definition, Finding a Fixed View Definition
finding inefficient SQL, Finding Inefficient SQL–Finding Inefficient SQL
finding where session is stuck, Finding Where a Session Is Stuck
finding where system is stuck, Finding Where a System Is Stuck
test harness, Tom Kyte’s Test Harness
viewing waits systemwide, Viewing Waits System-Wide–Targeting revisited

Oracle kernel timing, Oracle Kernel Timings–Exercises

unaccounted-for time, Unaccounted-for Time–Trace Writing

CPU consumption double-counting, CPU Consumption Double-Counting–CPU Consumption Double-Counting
measurement intrusion effect, Measurement Intrusion Effect–Measurement Intrusion Effect
quantization errors, Quantization Error–Bounds of Quantization Error
time spent not executing, Time Spent Not Executing–Process States and Transitions Revisited
un-instrumented kernel code, Un-Instrumented Oracle Kernel Code–Trace Writing

Oracle practitioners, queueing theory for, Queueing Theory–Sensitivity analysis

model input/out values, Model Input and Output Values–Cumulative distribution function (CDF) of response time
random variables, Random Variables–A program to test for exponential distribution

Oracle-L email list, Why I Wrote This Book, How to optimize SQL

Oracle8i and earlier kernels, walking the clock through trace files emitted by, Oracle Release 8 and Prior

Oracle9i

release 2 performance documentation, Response Time Components
trace files emitted by kernel, walking the clock through, Oracle Release 9

oraus.msg, Activating Extended SQL Trace

overflow errors, Glossary

Oracle fixed view data, susceptibility to, Susceptibility to Overflow and Other Errors

P

Parallel Execution (PX), Simple Client-Server Applications

parse optimization, Parse Optimization–Parse Optimization

PDF (probability density function), Probability density function (pdf), Glossary

performance

analysis, Heisenberg Uncertainty principle and, Measurement Resolution
analysts, optimal, The optimal performance analyst–The optimal performance analyst
improvement activity (see improvement activity)
improvement projects (see performance improvement projects)
models, Performance Models

performance improvement projects, A Better Way to Optimize