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

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

• ISBN13: 9780072227291
• ISBN10: 007222729X
• Edition: 1st
• Format: Paperback
• Copyright: 2004-07-16
• Publisher: McGraw-Hill Education

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