OCP Oracle9i Database Performance Tuning考试指南 英文版【2025|PDF|Epub|mobi|kindle电子书版本百度云盘下载】

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

PART Ⅰ OCP Tuning Exam Guide3

1 Database Tuning Overview3

How This Book Is Organized4

Topics Covered in the OCP DBA Tuning Exam5

The Oracle Performance Tuning Methodoiogy5

Diagnostics and Tuning Tools5

Sizing the Shared Pool6

Sizing the Buffer Cache6

Sizing Other SGA Structures7

Database Configuration and I/O Issues7

Optimizing Sort Operations8

Diagnosing Contention for Latches8

Tuning Rollback or UNDO Segments9

Monitoring and Detecting Lock Contention9

Tuning Oracle Shared Server9

Application Tuning10

Using Oracle Blocks Efficiently10

SQL Statement Tuning11

OS Considerations12

Tuning Overview12

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 Knowledge15

2 Oracle Performance Tuning Methodology17

Oracle Performance Tuning Method18

Describe the Roles Associated with the Database Tuning Process21

Describe the Dependency Between Tuning in Different Development Phases22

Describe SLAs25

Describe the Tuning Goals26

Describe the Most Common Tuning Problems27

Describe Tuning Goals Associated with Different Types of Applications29

Performance and Safety Trade-Offs30

Chapter Summary31

Two-Minute Drill31

Chapter Questions33

Answers to Chapter Questions34

3 Diagnostic and Tuning Tools37

Diagnostic and Tuning Tools38

The Data Dictionary and Dynamic Performance Views Related to Tuning39

Data Dictionary Views39

How the ANALYZE Command Is Used with Data Dictionary Views43

Dynamic Performance Views44

Current State Views44

Accumulator Views44

Informational Views44

Statistics in the Dynamic Performance Views45

Instance/Database Performance Views45

Memory46

Disk48

Contention48

Session-Related49

What Is Statspack?51

Installing and Configuring Statspack51

Interactive or Batch Installations52

Statspack Maintenance52

Configuring Statspack Statistics Gathering52

Collect Statistics Using Statspack53

Take a Snapshot53

Automating Snapshots53

Producing a Performance Report53

Contents of the Statspack Performance Report54

Other Tools That Can Be Used for Tuning55

UTLBSTAT/UTLESTAT55

OEM Oracle Expert56

Custom Tools56

The Alert Log and Trace Files56

Instance Alert Log56

Trace User SQL56

Chapter Summary57

Two-Minute Drill57

Chapter Questions58

Answers to Chapter Questions62

4 Sizing the Shared Pool65

Sizing the Shared Pool66

Shared Pool Concepts66

Measure and Tune the Library Cache Hit Ratio68

Size and Pin Objects in the Shared Pool72

Tune the Shared Pool Reserve Space74

Measure and Tune the Dictionary Cache Hit Ratio75

Describe UGA and Session Memory Considerations79

Setting the Large Pool80

Chapter Summary83

Two-Minute Drill85

Chapter Questions87

Answers to Chapter Questions90

5 Sizing the Buffer Cache93

Sizing the Buffer Cache94

Describe How the Buffer Cache Is Used by Different Oracle Processes95

Describe the Tuning Issues Related to the Buffer Cache96

Getting Advice about the Buffer Cache Size99

Implement Dynamic SGA Allocation101

Monitor the Use of the Buffer Cache and the Different Pools Within the Buffer Cache103

Create and Size Multiple Buffer Pools106

Make Appropriate Use of Table Caching109

Diagnose LRU Latch Contention110

Diagnose Freelist Contention110

Deprecated Buffer Cache Parameters112

Chapter Summary112

Two-Minute Drill113

Chapter Questions116

Answers to Chapter Questions120

6 Sizing Other SGA Structures123

Sizing Other SGA Structures124

Monitor and Size the Redo Log Buffer124

Monitor and Size the Java Pool129

Limit the Amount of Java Session Memory Used by a Session130

Configuring I/O Slaves131

Configuring Multiple DBW Processes132

Chapter Summary132

Two-Minute Drill133

Chapter Questions135

Answers to Chapter Questions137

7 Database Configuration and I/O Issues139

Database Configuration and I/O Issues140

Describe Reasons for Distributing Different Oracle File Types140

Diagnose Inappropriate Use of Tablespaces146

Tune Full Table Scan Operations148

Describe How Checkpoints Work150

Monitor and Tune Checkpoints151

Monitor and Tune Redo Logs153

Chapter Summary157

Two-Minute Drill159

Chapter Questions160

Answers to Chapter Questions163

8 Optimize Sort Operations167

Optimize Sort Operations168

Describe How Sorts Are Performed in Oracle168

Set Old and New Sort Parameters169

Describe the Operations That Cause Sorting171

Differentiate Between Disk and Memory Sorts172

Create and Monitor Temporary Tablespaces174

Describe Ways to Reduce Total Sorts and Disk Sorts177

Chapter Summary178

Two-Minute Drill179

Chapter Questions180

Answers to Chapter Questions182

9 Diagnosing Contention for Latches185

Diagnosing Contention for Latches186

Describe the Purpose of Latches186

Describe the Different Types of Latch Requests188

Describe How to Diagnose Contention for Latches188

Identify the Resources to Be Tuned to Minimize Latch Contention193

Chapter Summary194

Two-Minute Drill195

Chapter Questions196

Answers to Chapter Questions199

10 Tuning Rollback or Undo Segments201

Tuning Rollback/Undo Segments202

Use the Dynamic Performance Views to Check Rollback Segment Performance206

Define the Number and Size of Manual Rollback Segments210

Appropriately Allocate Rollback Segments to Transactions212

Understand and Explain the Concept of Automatic Undo Management216

Create and Maintain Automatically Managed Undo Tablespace217

Chapter Summary219

Two-Minute Drill221

Chapter Questions224

Answers to Chapter Questions227

11 Monitoring and Detecting Lock Contention231

Monitoring and Detecting Lock Contention232

Define Levels of Locking235

Describe Possible Causes of Contention240

Use Oracle Utilities to Detect Lock Contention240

Resolve Contention in an Emergency243

Prevent Locking Problems244

Recognize Oracle Errors Arising from Deadlocks245

Chapter Summary247

Two-Minute Drill249

Chapter Questions251

Answers to Chapter Questions254

12 Tuning Oracle Shared Server257

Tuning Oracle Shared Server258

Identifying Issues Associated with Managing Users in a Shared Server Environment258

Diagnosing and Resolving Performance Issues with Shared Server Processes260

Configure the Shared Server Environment to Optimize Performance265

Chapter Summary267

Two-Minute Drill269

Chapter Questions270

Answers to Chapter Questions272

13 Application Tuning275

Application Tuning276

Describe the Role of the DBA in Tuning Applications277

Explain Different Storage Structures277

Explain and Describe Clustering Options278

Explain the Different Types of Indexes280

Explain IOTs282

Describe Materialized Views and the Use of Query Rewrites284

List Requirements for OLTP,DSS，and Hybrid Systems286

Chapter Summary289

Two-Minute Drill291

Chapter Questions292

Answers to Chapter Questions294

14 Using Oracle Blocks Efficiently297

Using Oracle Blocks Efficiently298

Describe the Correct Usage of Extents and Oracle Blocks298

Explain Space Usage and the High Watermark302

Determine the High Watermark303

Recover Space from Sparsely Populated Segments303

Describe the Use of Oracle Block Parameters306

Describe and Detect Chaining and Migration of Oracle Blocks309

Perform Index Reorganization311

Monitor Indexes to Determine Usage314

Chapter Summary314

Two-Minute Drill317

Chapter Questions321

Answers to Chapter Questions326

15 SQL Statement Tuning329

SQL Statement Tuning330

Describe How the Optimizer Is Used331

Explain the Concept of Plan Stability335

Use of Stored Outlines336

Describe How Hints Are Used339

Collect Statistics on Indexes and Tables340

Describe the Use of Histograms342

Copy Statistics Between Databases343

OLTP and DSS Considerations344

Use SQL Trace and TKPROF345

Chapter Summary348

Two-Minute Drill350

Chapter Questions353

Answers to Chapter Questions356

16 OS Considerations and Oracle Resource Manager359

OS Considerations360

Describe Different System Architectures360

Understand Virtual Memory and Paging361

Describe the Primary Steps of OS Tuning362

Identify Similarities Between OS and DB Tuning363

Explain the Difference Between a Process and a Thread364

Database Resource Manager364

Configuring Resource Management366

Administer Resource Manager368

Chapter Summary372

Two-Minute Drill373

Chapter Questions377

Answers to Chapter Questions378

PART Ⅱ Practice Exams383

17 Practice Exam 1 and 2383

Practice Exam 1 Questions384

Answers to Practice Exam 1 Questions401

Practice Exam Two413

Practice Exam 2 Questions413

Answers to Practice Exam 2 Questions431

Glossary446

Index457