Oracle Performance Troubleshooting With Dictionary Internals SQL & Tuning Scripts

Cover......Page 1
Publication Information......Page 4
Table of Contents......Page 6
Using the Online Code Depot......Page 9
Conventions Used in this Book......Page 10
Acknowledgements......Page 11
Preface......Page 12
Modeling Peak Efficiency......Page 13
Modeling Availability......Page 14
Resource Availability......Page 15
Modeling Speed......Page 16
Access Efficiency......Page 17
Code Efficiency......Page 18
Model Dependencies......Page 20
The Impact of Availability......Page 22
The Impact of Speed......Page 24
Conclusion......Page 25
Applying Performance Methodologies......Page 27
Ratio-Based Analysis......Page 28
Accurate Ratio Analysis Technniques......Page 29
The Dangers of Blanket Ratios......Page 31
Prerequisites for Bottleneck Analysis......Page 32
Ratio-based analysis......Page 41
Performance Area: Memory......Page 42
Performance Area: Space......Page 43
Performance Area: User Activity......Page 44
Workload Analysis......Page 45
Conclusion......Page 46
Correcting Foundational Flaws......Page 48
Why is Physical Design Overlooked?......Page 49
The Number One Performance Myth......Page 51
The Link between Performance Monitoring and Physical Design......Page 52
Making the Biggest Performance Impact......Page 55
Spotting Physical Design Flaws......Page 58
Determine Cardinality......Page 61
Frequency of Update......Page 62
Conclusion......Page 64
The Contribution of Storage to Performance......Page 66
Hardware/Disk Planning......Page 68
The Coming Physical I/O Crisis......Page 70
Locally-managed Tablespace (LMTs)......Page 71
Multiple Blocksizes......Page 73
The Oracle9i Undo Tablespaces......Page 74
Object Planning......Page 75
Automatic Growth......Page 76
Unlimited Object Extents......Page 81
Preventing Transactional Failures......Page 87
Operating System Checks......Page 88
Fragmentation Issues......Page 89
Object Fragmentation......Page 90
Detecting Tablespace Fragmentation......Page 91
Eliminating Trablespace Fragmentation......Page 96
Detecting Object Fragmentation......Page 98
Correcting Ojbect Fragmentation......Page 100
Detecting Space-related Object Performance Problems......Page 101
Correcting Space-related Object Performance Problems......Page 110
Conclusion......Page 112
Maximizing Memory......Page 113
Getting a Handle on Memory Usage......Page 114
Understanding the SGA......Page 117
Gaining insight into Memory Use......Page 118
The Buffer Cache Hit Ratio - Still Worthwhile?......Page 121
Getting Advice on the Buffer Cache......Page 125
How to Keep Data Where You Want it......Page 128
Exploiting the New 9i Data Caches......Page 129
Other Interesting Buffer Cache Metrics......Page 131
Looking into the Shared Pool......Page 133
When Does Less Become More?......Page 134
Getting More Details on Shared Pool Usage......Page 135
How to Keep Code Where You Want it......Page 138
More Shared Pool Metrics......Page 139
Examining the Log Buffer......Page 141
Investigating Session Memory Usage......Page 142
Investigating Sorts......Page 144
Oracle9i Sorting......Page 145
Buffer Busy Waits......Page 146
Latches......Page 147
Conclusion......Page 150
Pinpointing I/O Hotspots......Page 152
Global Basic Queries......Page 153
Determine Global Object Access Patterns......Page 157
Examine Storage-level Statistics......Page 160
Locating Hot I/O Objects......Page 163
Find the Current I/O Session Bandits......Page 166
Examining Background Processes......Page 172
Monitoring Rollback Activity......Page 174
Conclusion......Page 176
Uncovering Security Holes......Page 177
Finding Storage Hogs......Page 181
Locating Top Resource Sessions......Page 184
Pinpointing Sessions with Problem SQL......Page 196
Conclusion......Page 199
Workload Analysis Part 2 - Identifying Problem SQL......Page 200
What is 'Bad SQL'?......Page 201
Historical SQL Analysis......Page 202
New Techniques for Analyzing SQL Execution......Page 207
SQL Tuning Roadmap......Page 211
Understand the Query and Dependent Objects......Page 212
Look for SQL Rewrite Possibilities......Page 216
Look for Object-based Solutions......Page 220
Conclusion......Page 222