PART Ⅰ OCP Tuning Exam Guide 3
1 Database Tuning Overview 3
How This Book Is Organized 4
Topics Covered in the OCP DBA Tuning Exam 5
The Oracle Performance Tuning Methodoiogy 5
Diagnostics and Tuning Tools 5
Sizing the Shared Pool 6
Sizing the Buffer Cache 6
Sizing Other SGA Structures 7
Database Configuration and I/O Issues 7
Optimizing Sort Operations 8
Diagnosing Contention for Latches 8
Tuning Rollback or UNDO Segments 9
Monitoring and Detecting Lock Contention 9
Tuning Oracle Shared Server 9
Application Tuning 10
Using Oracle Blocks Efficiently 10
SQL Statement Tuning 11
OS Considerations 12
Tuning Overview 12
Why Do We Performance Tune? 12
What Is Performance Tuning? 13
Where Do I Tune? 13
How Do I Performance Tune? 14
When Do I Tune? 14
How Do I Know If My Performance Tuning Efforts Are Actually Working? 14
To Advance Your Tuning Knowledge 15
2 Oracle Performance Tuning Methodology 17
Oracle Performance Tuning Method 18
Describe the Roles Associated with the Database Tuning Process 21
Describe the Dependency Between Tuning in Different Development Phases 22
Describe SLAs 25
Describe the Tuning Goals 26
Describe the Most Common Tuning Problems 27
Describe Tuning Goals Associated with Different Types of Applications 29
Performance and Safety Trade-Offs 30
Chapter Summary 31
Two-Minute Drill 31
Chapter Questions 33
Answers to Chapter Questions 34
3 Diagnostic and Tuning Tools 37
Diagnostic and Tuning Tools 38
The Data Dictionary and Dynamic Performance Views Related to Tuning 39
Data Dictionary Views 39
How the ANALYZE Command Is Used with Data Dictionary Views 43
Dynamic Performance Views 44
Current State Views 44
Accumulator Views 44
Informational Views 44
Statistics in the Dynamic Performance Views 45
Instance/Database Performance Views 45
Memory 46
Disk 48
Contention 48
Session-Related 49
What Is Statspack? 51
Installing and Configuring Statspack 51
Interactive or Batch Installations 52
Statspack Maintenance 52
Configuring Statspack Statistics Gathering 52
Collect Statistics Using Statspack 53
Take a Snapshot 53
Automating Snapshots 53
Producing a Performance Report 53
Contents of the Statspack Performance Report 54
Other Tools That Can Be Used for Tuning 55
UTLBSTAT/UTLESTAT 55
OEM Oracle Expert 56
Custom Tools 56
The Alert Log and Trace Files 56
Instance Alert Log 56
Trace User SQL 56
Chapter Summary 57
Two-Minute Drill 57
Chapter Questions 58
Answers to Chapter Questions 62
4 Sizing the Shared Pool 65
Sizing the Shared Pool 66
Shared Pool Concepts 66
Measure and Tune the Library Cache Hit Ratio 68
Size and Pin Objects in the Shared Pool 72
Tune the Shared Pool Reserve Space 74
Measure and Tune the Dictionary Cache Hit Ratio 75
Describe UGA and Session Memory Considerations 79
Setting the Large Pool 80
Chapter Summary 83
Two-Minute Drill 85
Chapter Questions 87
Answers to Chapter Questions 90
5 Sizing the Buffer Cache 93
Sizing the Buffer Cache 94
Describe How the Buffer Cache Is Used by Different Oracle Processes 95
Describe the Tuning Issues Related to the Buffer Cache 96
Getting Advice about the Buffer Cache Size 99
Implement Dynamic SGA Allocation 101
Monitor the Use of the Buffer Cache and the Different Pools Within the Buffer Cache 103
Create and Size Multiple Buffer Pools 106
Make Appropriate Use of Table Caching 109
Diagnose LRU Latch Contention 110
Diagnose Freelist Contention 110
Deprecated Buffer Cache Parameters 112
Chapter Summary 112
Two-Minute Drill 113
Chapter Questions 116
Answers to Chapter Questions 120
6 Sizing Other SGA Structures 123
Sizing Other SGA Structures 124
Monitor and Size the Redo Log Buffer 124
Monitor and Size the Java Pool 129
Limit the Amount of Java Session Memory Used by a Session 130
Configuring I/O Slaves 131
Configuring Multiple DBW Processes 132
Chapter Summary 132
Two-Minute Drill 133
Chapter Questions 135
Answers to Chapter Questions 137
7 Database Configuration and I/O Issues 139
Database Configuration and I/O Issues 140
Describe Reasons for Distributing Different Oracle File Types 140
Diagnose Inappropriate Use of Tablespaces 146
Tune Full Table Scan Operations 148
Describe How Checkpoints Work 150
Monitor and Tune Checkpoints 151
Monitor and Tune Redo Logs 153
Chapter Summary 157
Two-Minute Drill 159
Chapter Questions 160
Answers to Chapter Questions 163
8 Optimize Sort Operations 167
Optimize Sort Operations 168
Describe How Sorts Are Performed in Oracle 168
Set Old and New Sort Parameters 169
Describe the Operations That Cause Sorting 171
Differentiate Between Disk and Memory Sorts 172
Create and Monitor Temporary Tablespaces 174
Describe Ways to Reduce Total Sorts and Disk Sorts 177
Chapter Summary 178
Two-Minute Drill 179
Chapter Questions 180
Answers to Chapter Questions 182
9 Diagnosing Contention for Latches 185
Diagnosing Contention for Latches 186
Describe the Purpose of Latches 186
Describe the Different Types of Latch Requests 188
Describe How to Diagnose Contention for Latches 188
Identify the Resources to Be Tuned to Minimize Latch Contention 193
Chapter Summary 194
Two-Minute Drill 195
Chapter Questions 196
Answers to Chapter Questions 199
10 Tuning Rollback or Undo Segments 201
Tuning Rollback/Undo Segments 202
Use the Dynamic Performance Views to Check Rollback Segment Performance 206
Define the Number and Size of Manual Rollback Segments 210
Appropriately Allocate Rollback Segments to Transactions 212
Understand and Explain the Concept of Automatic Undo Management 216
Create and Maintain Automatically Managed Undo Tablespace 217
Chapter Summary 219
Two-Minute Drill 221
Chapter Questions 224
Answers to Chapter Questions 227
11 Monitoring and Detecting Lock Contention 231
Monitoring and Detecting Lock Contention 232
Define Levels of Locking 235
Describe Possible Causes of Contention 240
Use Oracle Utilities to Detect Lock Contention 240
Resolve Contention in an Emergency 243
Prevent Locking Problems 244
Recognize Oracle Errors Arising from Deadlocks 245
Chapter Summary 247
Two-Minute Drill 249
Chapter Questions 251
Answers to Chapter Questions 254
12 Tuning Oracle Shared Server 257
Tuning Oracle Shared Server 258
Identifying Issues Associated with Managing Users in a Shared Server Environment 258
Diagnosing and Resolving Performance Issues with Shared Server Processes 260
Configure the Shared Server Environment to Optimize Performance 265
Chapter Summary 267
Two-Minute Drill 269
Chapter Questions 270
Answers to Chapter Questions 272
13 Application Tuning 275
Application Tuning 276
Describe the Role of the DBA in Tuning Applications 277
Explain Different Storage Structures 277
Explain and Describe Clustering Options 278
Explain the Different Types of Indexes 280
Explain IOTs 282
Describe Materialized Views and the Use of Query Rewrites 284
List Requirements for OLTP,DSS，and Hybrid Systems 286
Chapter Summary 289
Two-Minute Drill 291
Chapter Questions 292
Answers to Chapter Questions 294
14 Using Oracle Blocks Efficiently 297
Using Oracle Blocks Efficiently 298
Describe the Correct Usage of Extents and Oracle Blocks 298
Explain Space Usage and the High Watermark 302
Determine the High Watermark 303
Recover Space from Sparsely Populated Segments 303
Describe the Use of Oracle Block Parameters 306
Describe and Detect Chaining and Migration of Oracle Blocks 309
Perform Index Reorganization 311
Monitor Indexes to Determine Usage 314
Chapter Summary 314
Two-Minute Drill 317
Chapter Questions 321
Answers to Chapter Questions 326
15 SQL Statement Tuning 329
SQL Statement Tuning 330
Describe How the Optimizer Is Used 331
Explain the Concept of Plan Stability 335
Use of Stored Outlines 336
Describe How Hints Are Used 339
Collect Statistics on Indexes and Tables 340
Describe the Use of Histograms 342
Copy Statistics Between Databases 343
OLTP and DSS Considerations 344
Use SQL Trace and TKPROF 345
Chapter Summary 348
Two-Minute Drill 350
Chapter Questions 353
Answers to Chapter Questions 356
16 OS Considerations and Oracle Resource Manager 359
OS Considerations 360
Describe Different System Architectures 360
Understand Virtual Memory and Paging 361
Describe the Primary Steps of OS Tuning 362
Identify Similarities Between OS and DB Tuning 363
Explain the Difference Between a Process and a Thread 364
Database Resource Manager 364
Configuring Resource Management 366
Administer Resource Manager 368
Chapter Summary 372
Two-Minute Drill 373
Chapter Questions 377
Answers to Chapter Questions 378
PART Ⅱ Practice Exams 383
17 Practice Exam 1 and 2 383
Practice Exam 1 Questions 384
Answers to Practice Exam 1 Questions 401
Practice Exam Two 413
Practice Exam 2 Questions 413
Answers to Practice Exam 2 Questions 431
Glossary 446
Index 457