Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning,9780072227291

Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

by ; ;
Edition: 1st
ISBN13: 9780072227291
ISBN10: 007222729X
Format: Paperback
Pub. Date: 2004-07-16
Publisher(s): McGraw-Hill Education
List Price: $65.74

Buy New

Usually Ships in 2-3 Business Days
$62.61
Add to Cart

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

Rent Digital

Rent Digital Options
Online:1825 Days access
Downloadable:Lifetime Access
$85.50
*To support the delivery of the digital material to you, a non-refundable digital delivery fee of $3.99 will be charged on each digital item.
$85.50*
Add to Cart

Used Book

We're Sorry
Sold Out

Buy from our Marketplace starting at $5.50

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

Troubleshoot, tune, and optimize your Oracle database efficiently and successfully every time. This book explains how to take full advantage of the revolutionary Oracle Wait Interface to quickly pinpoint--and solve--core problems and bottlenecks, and increase productivity exponentially.

Table of Contents

Foreword xvii
Acknowledgments xxi
Introduction xxv
Introduction to Oracle Wait Interface
1(14)
The Old Fashion of Oracle Performance Optimization
3(1)
Why Are Cache-Hit Ratios Grossly Inefficient?
3(2)
The New Fashion of Oracle Performance Optimization
5(1)
The OWI Philosophy
6(2)
Database Response Time Tuning Model
8(3)
Paradigm Shift
11(2)
In a Nutshell
13(2)
Oracle Wait Interface Components
15(34)
What Is a Wait Event?
16(1)
OWI Components
17(19)
V$Event_Name View
18(2)
V$System_Event View
20(4)
V$Session_Event View
24(3)
V$Session_Wait View
27(3)
Trace Event 10046---The Extended SQL Trace
30(6)
New OWI Views in Oracle Database 10g Release 1
36(9)
V$Session_Wait_History View
37(1)
V$System_Wait_Class View
38(1)
V$Session_Wait_Class View
39(1)
V$Event_Histogram View
40(2)
Types of Wait Events
42(3)
OWI Limitations
45(3)
No CPU Statistics
45(1)
No End-to-End Visibility
45(1)
No Historical Data
46(1)
Inaccuracies
46(2)
In a Nutshell
48(1)
Common Wait Events
49(28)
Introduction to Common Wait Events
50(16)
buffer busy waits
50(1)
control file parallel write
51(2)
db file parallel read
53(1)
db file parallel write
53(1)
db file scattered read
54(1)
db file sequential read
55(1)
db file single write
55(1)
direct path read
56(1)
direct path write
57(1)
enqueue
57(2)
free buffer waits
59(1)
latch free
59(1)
library cache pin
60(1)
library cache lock
61(1)
log buffer space
62(1)
log file parallel write
62(1)
log file sequential read
63(1)
log file switch (archiving needed)
63(1)
log file switch (checkpoint incomplete)
63(1)
log file switch completion
64(1)
log file sync
64(1)
SQL*Net message from client
65(1)
SQL*Net message to client
65(1)
Common Wait Events in Oracle Real Application Clusters Environment
66(6)
global cache cr request
66(2)
buffer busy global cache
68(1)
buffer busy global cr
68(1)
global cache busy
69(1)
global cache null to x
69(1)
global cache null to s
70(1)
global cache s to x
70(1)
global cache open x
71(1)
global cache open s
71(1)
row cache lock
71(1)
Tracking CPU and Other Statistics
72(2)
In a Nutshell
74(3)
OWI Monitoring and Collection Methods
77(26)
Why Is Historical Performance Data Important?
78(1)
Fast and Accurate Root Cause Analysis
79(1)
Why Trace Event 10046 Is Not a Suitable Data Collector
80(1)
Why Statspack Is Not a Suitable Data Collector
81(1)
Database Logoff Trigger as a Data Collector
82(4)
Sampling for Performance Data Using PL/SQL Procedure
86(15)
Data Source
87(1)
Sampling Frequency
88(2)
Repositories
90(2)
Events to Monitor
92(8)
Pros and Cons
100(1)
Sampling for Performance Data with SQL-less SGA Access
101(1)
In a Nutshell
101(2)
Interpreting Common I/O Related Wait Events
103(38)
db file sequential read
104(8)
Common Causes, Diagnosis, and Actions
105(7)
db file scattered read
112(8)
Common Causes, Diagnosis, and Actions
113(3)
Why Does db file sequential read event Show Up in a Full Table Scan Operation
116(2)
Why Does a Full Scan Operation Request Fewer Blocks than the MBRC
118(1)
Setting the DB_File_Multiblock_Read_Count (MBRC)
118(2)
Why Physical I/Os Are Expensive
120(1)
direct path read
120(8)
Common Causes, Diagnosis, and Actions
121(5)
Initialization Parameters of Interest
126(2)
direct path write
128(2)
Common Causes, Diagnosis, and Actions
128(2)
db file parallel write
130(5)
Common Causes, Diagnosis, and Actions
130(5)
log file parallel write
135(3)
Common Causes, Diagnosis, and Actions
135(3)
control file parallel write
138(2)
Common Causes, Diagnosis, and Actions
138(2)
In a Nutshell
140(1)
Interpreting Locks-Related Wait Events
141(56)
Latch Free
142(28)
What Is a Latch?
142(1)
Differences between a Latch and a Lock
143(1)
Latch Family
144(1)
Latch Acquisition
145(1)
Latch Classification
146(2)
What Does the Latch Free Wait Event Tell You?
148(1)
Latch Miss Locations
149(1)
Latches in Oracle Database 10g Release 1
149(1)
Common Causes, Diagnosis, and Actions
150(1)
Shared Pool and Library Cache Latches
151(8)
Cache Buffers Chains Latches
159(8)
Cache Buffers Lru Chain Latches
167(2)
Row Cache Objects Latches
169(1)
Enqueue
170(14)
What Is an Enqueue?
170(1)
What Is an Enqueue Resource?
170(2)
What Is an Enqueue Lock?
172(2)
Enqueue Architecture
174(1)
Decoding Enqueue Type and Mode
174(3)
Common Causes, Diagnosis, and Actions
177(7)
Buffer Busy Waits
184(11)
Common Causes, Diagnosis, and Actions
185(10)
In a Nutshell
195(2)
Interpreting Common Latency-Related Wait Events
197(18)
log file sync
198(6)
Common Causes, Diagnosis, and Actions
198(6)
log buffer space
204(2)
Common Causes, Diagnosis, and Actions
205(1)
free buffer waits
206(4)
Common Causes, Diagnosis, and Actions
206(4)
write complete waits
210(1)
Common Causes, Diagnosis, and Actions
210(1)
log file switch completion
211(1)
Common Causes, Diagnosis, and Actions
211(1)
log file switch (checkpoint incomplete)
212(1)
Common Causes, Diagnosis, and Actions
212(1)
In a Nutshell
213(2)
Wait Events in a Real Application Clusters Environment
215(22)
What's So Special About Waits in Real Application Clusters (RAC)?
216(7)
Global Buffer Cache in Real Application Clusters
217(6)
Global Cache Waits
223(7)
global cache cr request
223(5)
global cache busy
228(2)
RAC Wait Event Enhancements in Oracle Database 10g
230(1)
Enqueue Waits
231(5)
Most Common Enqueues
232(4)
In a Nutshell
236(1)
Performance Management in Oracle Database 10g
237(38)
Database Statistics
238(3)
Time Model Statistics
238(2)
Wait Model Statistics
240(1)
Operating System Statistics
240(1)
Additional SQL Statistics
241(1)
Database Metrics
241(1)
New Background Processes
241(1)
Automatic Workload Repository
242(10)
Repository Snapshots
243(1)
Snapshot Baselines
244(1)
Using EM to Manage AWR
244(3)
Manually Managing AWR
247(5)
Active Session History
252(7)
What Is an Active Session?
253(1)
Components of ASH
253(6)
Automatic Database Diagnostic Monitor (ADDM)
259(14)
ADDM Setup
260(1)
Using EM to Access ADDM
261(9)
Manually Running ADDM Report
270(3)
ADDM Views
273(1)
In a Nutshell
273(2)
A Oracle Database 10g Diagnostic Events
275(14)
Oracle Diagnostic Events
276(13)
Types of Diagnostic Events
276(3)
Setting Diagnostic Events
279(6)
Internal Workings of the Events
285(4)
B Enqueue Waits in Oracle Database 10g
289(12)
C Oracle Dumps and Traces
301(16)
Oradebug: The Ultimate Utility for Traces and Dumps
302(2)
Data Block Dump
304(1)
Syntax
304(1)
Buffers Dump
305(2)
Syntax
306(1)
Controlling the Dump Information
306(1)
Buffer Dump
307(1)
Syntax
307(1)
Controlling the Dump Information Using Levels
308(1)
File Headers Dump
308(1)
Syntax
308(1)
Controlling the Dump Information Using Levels
308(1)
Control File Dump
309(1)
Syntax
310(1)
Controlling the Dump Information Using Levels
310(1)
Heap Dump
310(2)
Syntax
311(1)
Controlling the Dump Information Using Levels
311(1)
Library Cache Dump
312(1)
Syntax
312(1)
Controlling the Dump Information Using Levels
312(1)
Processstate Dump
313(1)
Syntax
313(1)
Shared Server State Dump
314(1)
Syntax
314(1)
Controlling the Dump Information Using Levels
314(1)
Systemstate Dump
315(1)
Syntax
315(1)
Controlling the Dump Information Using Levels
315(1)
Redo Log Dump
315(2)
Syntax
316(1)
Controlling the Dump Information Using Levels
316(1)
D Direct SGA Access
317(16)
Overhead
318(1)
Security
318(1)
Speed
318(1)
Concurrency
319(1)
Get Hidden Information
319(1)
Introduction to X$ Views
319(3)
The Necessary Ingredients
322(5)
Find SGA ID
322(1)
Find SGA Base Address
323(1)
Find the Starting Address of X$KSUSECST
324(1)
Find the Record Size of the X$KSUSECST Structure
324(1)
Find Number of Records in the X$KSUSECST Structure
325(1)
Find the X$KSUSECST View Columns Offsets
325(2)
Attaching the SGA to a C Program
327(6)
E References
333(6)
Chapter 1
334(1)
Chapter 2
334(1)
Chapter 3
334(1)
Chapter 4
335(1)
Chapter 5
335(1)
Chapter 6
335(1)
Chapter 7
336(1)
Chapter 8
336(1)
Chapter 9
336(1)
Appendix A
337(1)
Appendix B
337(1)
Appendix C
337(1)
Appendix D
337(2)
Index 339

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.