Expert Oracle Exadata

Kerry

Osborne

Randy

Johnson

Tanel Poder

Contents

J

m

About

the Authors

xvi

About the Technical Reviewer

xvii

a

_{Acknowledgments}

xviii

Introduction

xix

Chapter

1:

What Is

Exadata?

1

An Overview of Exadata

2

History

of Exadata

3

Alternative Views of What Exadata Is

5

Data Warehouse

_Appliance

5

OLTP Machine 6

Consolidation Platform 6

Configuration Options

7

Exadata Database Machine X2-2 7 Exadata Database Machine X2-8 8

Hardware

_Components

9

Operating Systems

10 Database Servers 11

Storage

Servers 11

InfiniBand

11 Flash Cache 12 Disks 12

Bits and Pieces 12

Software

_Components

12

Database Server Software 12

Storage

Server

Software 16

Software Architecture

18

Summary

21

Chapter

2:

_Offloading

/ Smart Scan

23

Why

Offloading

Is

_Important

23

What

_Offloading

Includes

27

Column

_Projection

27

Predicate

_Filtering

32

Storage

Indexes 33

Simple

Joins

_{(Bloom Filters)}

34 Function

_Offloading

37

Compression/Decompression

39

Encryption/Decryption

40

Virtual Columns 41

Data

_Mining

Model

Scoring

43 Non-Smart Scan

_Offloading

44

Smart Scan

_{Prerequisites}

45

Full

Scans

45

Direct

Path Reads 45

Exadata

_Storage

46

Smart

Scan Disablers

49

Simply

Unavailable 49

Reverting

to Block

_Shipping

49

Skipping

Some

_Offloading

50 vi

CONTENTS

How to

_Verify

That Smart Scan

is

_Happening

50

10046 Trace 51

Performance Statistics

_(v$sessstat)

52 Offload

_{Eligible Bytes}

54

SQL

Monitoring

58

Parameters

61

Summary

64

Chapter

3:

_Hybrid

Columnar

_Compression

65

Oracle

_Storage

Review

65

Oracle

_Compression

Mechanisms

68

BASIC 68 OLTP 68 HCC 69

HCC

Mechanics

72

HCC Performance

73

Load Performance 73

Query

Performance 78 DMLPerformance 83

Expected Compression

Ratios

91

Compression

Advisor 91 Real World

Examples

94

Restrictions/Challenges

100

Moving

Data toanon-Exadata Platform 100

Disabling

Serial Direct Path

Reads

100

Locking

Issues 101

Single

Row Access 102

Common

_Usage

Scenarios

102

Summary

104

Chapter

4:

_Storage

Indexes

105

Structure

105

Monitoring Storage

Indexes

107

Database Statistics 107

Tracing

108

Monitoring Wrap Up

111

Controlling Storage

Indexes

111

_kcfis_storageidx_disabled

111

_kcfis_storageidx_diag_mode

112

_cell_storidx_mode

112

Storage

Software Parameters 112

Behavior

113

Performance

114

Special Optimization

for Nulls 116

Physical

Distribution of Values 117

Potential Issues

118

Incorrect Results 119

Moving Target

119

Partition Size

122

Incompatible Coding Techniques

122

Summary

124

Chapter

5: Exadata Smart

Flash Cache

125

Hardware

125

Cache

vs.

Flash

Disk

127

Using

Flash

_Memory

asCache 128

HowESFCis Created 132

CONTENTS

Controlling

ESFC

_Usage

134

Monitoring

135

At the

_{Storage Layer}

135

At the

Database

Layer

139

Performance

140

Summary

141

Chapter

6: Exadata Parallel

_Operations

143

Parameters

143

Parallelization at the

_Storage

Tier

145

AutoDOP

146

Operation

and

_{Configuration}

146

I/O Calibration 149

AutoDOP

_{Wrap Up}

152

Parallel

Statement

Queuing

152

The Old

_Way

153

The New

_Way

153

Controlling

Parallel

Queuing

157

Parallel Statement

_{Queuing Wrap}

Up

165

In-Memory

Parallel

Execution

165

Summary...

175

Chapter

7: Resource

_Management

175

Database Resource

_Manager

176

Consumer

Groups

178

Plan Directives 181

Resource Plan 183

Resource

_Manager

Views 184 The Wait Event: resmgr: cpu

quantum

185

DBRM

_Example

185

Testing

a

Resource

Plan 190

Instance

_Caging

198

Configuring

and

_Testing

Instance

_Caging

199

Over-Provisioning

204

I/O

Resource

_Manager

204

How I0RM Works 206

Methods for

_Managing

I/OonExadata 209

Bringing

It All

_Together

215 IORM

_Monitoring

and Metrics 219

Summary

235

Chapter

8:

_Configuring

Exadata

237

Exadata Network

_Components

237

The

_Management

Network 237

The Client

Access

Network

238 The

Private

Network 239

About the

_{Configuration}

Process

240

Configuring

Exadata

241

Step

1: The

_{Configuration}

Worksheet 242

Step

2: The DBM

_Configurator

253

Step

3:

_Upload

Parameter and

_Deployment

Files 254

Step

4:

CheckIP

_(checkip.

sh)

258

Step

5: Firstboot 260

Step

6:

_Staging

the

Installation

Media 262

Step

7:

_Running

OneCommand

263

Upgrading

Exadata

268

Creating

aNew RAC Cluster 268

Upgrading

the

_Existing

Cluster 270

*CONTENTS

Summary

273

Chapter

9:

_Recovering

Exadata

275

Exadata

_Diagnostic

Tools

275

Sun

_{Diagnostics: sundiag.sh}

276

HealthCheck 278

CellCLI 279

Backing

Up

Exadata

284

Backing Up

theDatabase Servers 284

Backing Up

the

_Storage

Cell 287

Backing

Up

the Database

293

Disk-Based

_Backups

293

Tape-Based Backups

293

Backup

from

_Standby

Database 294

Exadata

_{Optimizations}

for RMAN 295

Recovering

Exadata

296

Restoring

the Database Server 296

Recovering

the

_Storage

Cell 299

Summary

318

m

_Chapter

10: Exadata Wait Events

319

Events

_Specific

to

Exadata

319

The "cell" Events 320 Plan

_Steps

That

_Trigger

Events 321

Exadata Wait Events in the User I/O Class

323

cell smart tablescan 323

cellsmartindexscan 326

cell

_single

block

_physical

read 328 cellmultiblock

_physical

read 330

cell list of blocks

_physical

read 331

cell

smart file

creation

332 cell

statistics gather

333

Exadata Wait Events

in the

_System

I/O Class

333

cellsmartincremental

_backup

334

cell smart restore from

backup

335

Exadata Wait Events

in the Other and Idle Classes

337

cell smart flash

_unkeep

337

Old Events

338

direct

path

read 338 enq: KO—fast

object

checkpoint

339 reliable message 340

Resource

_Manager

Events

341

resmgrxpu

quantum

341

resmgnpq queued

342

Summary

343

Chapter

11:

_{Understanding}

Exadata Performance Metrics

...345

Measuring

Exadata's Performance

Metrics

345

Revisiting

the

_{Prerequisites}

for

Exadata Smart

Scans

346

Exadata Smart Scan Performance 346

Understanding

Exadata Smart

Scan Metrics

and

Performance Counters

350

Exadata

_Dynamic

Performance

Counters

350

When and How to Use Performance Counters 351 The

_Meaning

and

Explanation

of Exadata Performance Counters 354 Exadata Performance Counter Reference i 356

Understanding

SQL

Statement Performance

374

Summary

377

CONTENTS

Chapter

12:

_Monitoring

Exadata Performance..

379

A

_{Systematic Approach}

379

Monitoring

SQL

Statement

_Response

Time

380

Monitoring

SQL

Statements

with Real-Time

SQL

Monitoring Reports

381

Monitoring

SQL Statements

using

V$SQL

and

V$SQLSTATS

393

Monitoring

the

_Storage

Cell

_Layer

395

Accessing

Cell Metrics In the Cell

_{Layer Using}

CellCLI 395

Accessing

Cell Metrics

_Using

the Grid Control Exadata

_Storage

Server

_Plug-In

396

Which

Cell MetricstoUse? 405

Monitoring

Exadata

Storage

CellOS-level Metrics 406

Summary

418

Chapter

13:

_Migrating

to

Exadata

419

Migration Strategies

420

Logical Migration

421

Extract and Load 422

Copying

Dataover a

Database

Link 427

Replication-Based Migration

443

Logical Migration Wrap

Up

451

Physical Migration

451

Backup

and Restore

452

Full

_Backup

and Restore 452 Incremental

Backup

454

Transportable

Tablespaces (and XTTS)

455

Physical

Standby

458

ASM Rebalance 460

Post-Migration

Tasks 463

Wrap Up

Physical Migration

Section 464 xiii

Summary

465

Chapter

14:

_{Storage Layout}

467

Exadata Disk Architecture

467

Failure

_Groups

469

Grid

Disks

471

Storage

Allocation

473

Creating

Grid Disks

477

Creating

Grid Disks 478 Grid Disk

_Sizing

479

Creating

FlashDisk-Based

Grid

Disks 483

Storage Strategies

484

Configuration Options

485

Isolating Storage

Cell

Access

485

Cell

_Security

487

Cell

_{Security Terminology}

488 Cell

_Security

Best Practices 489

Configuring ASM-Scoped Security

489

Configuring Database-Scoped Security

490

Removing

Cell Security

493

Summary

495

Chapter

15:

_Compute

Node

_Layout

497

Provisioning

Considerations

497

Non-RAC

_{Configuration}

500

RAC

Clusters

504

Typical

Exadata

_{Configuration}

506

Exadata Clusters

507

Summary

509

«CONTENTS

Chapter

16:

_Unlearning

Some

_Things

We

_Thought

We Knew

511

A Tale of Two

_Systems

511

OLTP-Oriented Workloads

512

Exadata Smart

Flash

Cache

_(ESFC)

512

Scalability

513 Write-lntensive OLTP Workloads 513

DW-Oriented Workloads

514

Enabling

SmartScans 514

Things

that

Can

_Cripple

Smart

Scans 516 Other

_Things

to

_Keep

in Mind

526

Mixed

Workloads

528

To IndexorNot to Index? 529

The

_Optimizer

Doesn't

Know . 530

Using

Resource

_Manager

533

Summary

534

Appendix

A: CellCLI and deli

535

CellCLI Command

_Syntax

535

Getting

Familiar

with CellCLI

536

Sending

Commands from

the

Operating

System

540

Configuring

and

_Managing

the

_Storage

Cell 540

deli Command

_Syntax

542

Summary....

544

Appendix

B: Online Exadata Resources

545

Exadata MOS

_Support

Notes

545

Helpful

Exadata MOS

_Support

Notes

545

Exadata Bulletins

and

_Blogs

546

Appendix

C:

_Diagnostic

_Scripts

547

Index

551