OCP Oracle9i Database Performance Tuning考试指南 英文版PDF电子书下载

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