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

, by Shee, Richmond; Deshpande, Kirtikumar; Gopalakrishnan, K.

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780072227291 | 007222729X
Cover: Paperback
Copyright: 7/16/2004

Buy New

In Stock Usually Ships in 24-48 Hours

$59.68
eBook

Available Instantly

Online: 1825 Days

Downloadable: Lifetime Access

$59.00

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.

Foreword

xvii

Acknowledgments

xxi

Introduction

xxv

Introduction to Oracle Wait Interface

(14)

The Old Fashion of Oracle Performance Optimization

(1)

Why Are Cache-Hit Ratios Grossly Inefficient?

(2)

The New Fashion of Oracle Performance Optimization

(1)

The OWI Philosophy

(2)

Database Response Time Tuning Model

(3)

Paradigm Shift

(2)

In a Nutshell

(2)

Oracle Wait Interface Components

(34)

What Is a Wait Event?

(1)

OWI Components

(19)

V$Event_Name View

(2)

V$System_Event View

(4)

V$Session_Event View

(3)

V$Session_Wait View

(3)

Trace Event 10046---The Extended SQL Trace

(6)

New OWI Views in Oracle Database 10g Release 1

(9)

V$Session_Wait_History View

(1)

V$System_Wait_Class View

(1)

V$Session_Wait_Class View

(1)

V$Event_Histogram View

(2)

Types of Wait Events

(3)

OWI Limitations

(3)

No CPU Statistics

(1)

No End-to-End Visibility

(1)

No Historical Data

(1)

Inaccuracies

(2)

In a Nutshell

(1)

Common Wait Events

(28)

Introduction to Common Wait Events

(16)

buffer busy waits

(1)

control file parallel write

(2)

db file parallel read

(1)

db file parallel write

(1)

db file scattered read

(1)

db file sequential read

(1)

db file single write

(1)

direct path read

(1)

direct path write

(1)

enqueue

(2)

free buffer waits

(1)

latch free

(1)

library cache pin

(1)

library cache lock

(1)

log buffer space

(1)

log file parallel write

(1)

log file sequential read

(1)

log file switch (archiving needed)

(1)

log file switch (checkpoint incomplete)

(1)

log file switch completion

(1)

log file sync

(1)

SQL*Net message from client

(1)

SQL*Net message to client

(1)

Common Wait Events in Oracle Real Application Clusters Environment

(6)

global cache cr request

(2)

buffer busy global cache

(1)

buffer busy global cr

(1)

global cache busy

(1)

global cache null to x

(1)

global cache null to s

(1)

global cache s to x

(1)

global cache open x

(1)

global cache open s

(1)

row cache lock

(1)

Tracking CPU and Other Statistics

(2)

In a Nutshell

(3)

OWI Monitoring and Collection Methods

(26)

Why Is Historical Performance Data Important?

(1)

Fast and Accurate Root Cause Analysis

(1)

Why Trace Event 10046 Is Not a Suitable Data Collector

(1)

Why Statspack Is Not a Suitable Data Collector

(1)

Database Logoff Trigger as a Data Collector

(4)

Sampling for Performance Data Using PL/SQL Procedure

(15)

Data Source

(1)

Sampling Frequency

(2)

Repositories

(2)

Events to Monitor

(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

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

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

Summary

Table of Contents

Supplemental Materials