Expert Oracle Exadata

Expert

Oracle

_Exadata

Second Edition

Martin

Bach

Karl Arao

Andy

Colvin

Frits

_Hoogland

Kerry

Osborne

Randy

Johnson

Tanel

Poder

Contents

J

AbouttheAuthors xxi

Acknowledgments.

xxiii

Introduction xxv

Chapter

1: What Is Exadata? 1

An Overview of Exadata 1

History

of Exadata 2

Alternative ViewsofWhat Exadata Is 4

DataWarehouseAppliance 4 OLTP Machine 5

Consolidation Platform 5

Configuration Options

6

Exadata Database Machine X5-2 6 ExadataDatabase MachineX4-8 7 Exadata_{Storage Expansion}Rack X5-2 7

Hardware

_Components

9 Operating Systems 10 Database Servers 10 StorageServers 10 InfiniBand 10 FlashCache 11 Disks 11

CONTENTS

Software

_Components

11

Database Server Software 11

StorageServer Software 14

SoftwareArchitecture 16

Summary

20

Chapter

2:

_Offloading

/ Smart Scan 21

Why Offloading

Is

_Important

21 What

_Offloading

Includes 26

Column _Projection 27

Predicate_Filtering 31

StorageIndexes and Zone _Maps 33

SimpleJoins_{(Bloom Filters)} 35

Function_Offloading 38

Compression/Decompression 41

Encryption/Decryption 42

Virtual Columns 42

Supportfor LOB_offloading 45

JSON _Supportand _Offloading 46

Data_MiningModel_Scoring 47

Non-Smart ScanOffloading 48

Smart Scan

_{Prerequisites}

49

Full Scans 49

Direct Path Reads 50 Exadata_Storage 53

Smart Scan Disablers 54

SimplyUnavailable 54

Revertingto Block_Shipping 55

SkippingSome_Offloading 56

SkippingOffloading silently 56

Howto

_Verify

That Smart Scan Is

_Happening

57

10046 Trace 57

Session Performance Statistics 58

OffloadEligible Bytes 59

SQLMonitoring 63

Parameters 65

Summary

66

Chapter

3:

_Hybrid

Columnar

_Compression

67

Oracle Storage Review 67

Disassemblingthe Oracle Block 70

Compression

Mechanics 73

BASIC_Compression 73

OLTP_Compression 74

HybridColumnar_Compression 76

HCC Internals 80

What Happens WhenYou CreateaHCC_CompressedTable? 83

HCC Performance 86

LoadPerformance 86

QueryPerformance 87

DML Performance 90

Expected

Compression

Ratios 97

CompressionAdvisor 97

Real-World_Examples 99

Restrictions/Challenges

105

MovingDatatoaNon-Exadata Platform 105

DisablingSerial Direct Path Reads 106

LockingIssues 106

iCONTENTS

Common Usage Scenarios 111

Automatic Data_Optimization 112

ExampleUse CasesforADO 114

Summary

120

Chapter

4:

_Storage

Indexes 121

Structure 121

Monitoring Storage Indexes 122

Database Statistics 123

Tracing 124

Monitoring Wrap-Up 126

Controlling Storage

Indexes 126

_kcfis__storageidx_disabled 127

_kcfis_storageidx_diag_mode 127

_celLstoridx^mode 127

_cell_storidxjTiinrnax_enabled 128

StorageSoftwareParameters 128

Behavior 129

Performance 130

Special Optimizationfor Nulls 132

PhysicalDistribution of Values 133

Potential Issues 134

IncorrectResults 134

Moving Target 135

PartitionSize 138

Incompatible Coding Techniques 138

Summary 139

Chapter

5: Exadata Smart Flash Cache 141

Hardware 142

FlashMemoryin Exadata X4-2_StorageServers 142 Flash_Memoryin Exadata X5-2_StorageServers 144

Flash Cache vs. Flash Disk 145

Using Flash_MemoryasCache 146

Mixed Workload andOLTP_{Optimizations} 150

Using Flash_MemoryforDatabase_Logging 151

Using FlashMemorytoAccelerate Writes 153

MiscellaneousOtherWBFC-relatedOptimizations 155 How ESFC and ESFL Are Created 156

Enablingthe Write-back Flash Cache 158

FlashCache_Compression 162

ControllingESFC_Usage 163

Monitoring

164

At the_{Storage Layer} 164

Atthe Database_Layer 170

Summary

176

Chapter 6: Exadata Parallel

_Operations

177

Parameters 177

Parallelizationat the _StorageTier 180

Auto DOP ₁₈₀

Operationand _{Configuration} 181

I/OCalibration 184

Auto DOP_Wrap-Up 186

ParallelStatement

_Queueing

186

The Old_Way 187 The New_Way 187

ControllingParallel_Queueing 190 ParallelStatement_{Queueing Wrap-Up} 197

In-Memory

Parallel Execution 197

Troubleshooting

Parallel Execution 206

lCONTENTS

Chapter

7: Resource

_Management

• 209

Consolidation 210

Typesof Database Consolidation 210

Instance

_Caging

211

Configuring Instance_Caging 212

SettingCPU_C0UNT 213

Instance_{Caging Usage}and Results 213 Instance_Cagingand_Multitenancy 214

Over-Provisioning 214

Binding

Instances to

_Specific

CPUs

_{Using Cgroups}

215

Installation and_{Configuration}ofCgroups 215

Oracle 12c

_{THREADED_EXECUTION}

217

Managing

PGA

_Memory

218

Database Resource

_Manager

221

CreatingaCDB Resource Plan 222

Creatinga(Pluggable)Database Resource Plan 224

UsingtheSchedulerto_ChangetheResourcePlan 227

The Wait Event:_{resmgr: cpuquantum} 228 Where to Go from Here 228 Resource_MappingPriorities 229 ResourceLimiting 229 OtherLimitingParameters 230 Consumer_{Group Mappings Using ORACLE_FUNCTION} 231

Monitoringthe Resource_Manager 232

Resource_ManagerViews 233

I/O Resource

_Manager

234

IORM Methods 235

How IORMWorks 236 IORM Architecture 236

IORM_Objective 238

ConfiguringInterdatabase IORM 238

CategoryIORM 241 I/O Resource_Managerand_PluggableDatabases 243 I/O Resource_ManagerProfiles 243

Resource

_Management

Directives Matrix 244

IORMMonitoringand Metrics 245

Summary 250

Chapter8:

_Configuring

Exadata 251

Exadata Network

_Components

251

TheManagementNetwork 252

The Client Access Network 252 The Private Network 252

About the

_{Configuration}

Process 254

Configuring

Exadata 256

Step1:_GatheringInstallation_Requirements 256

Step2: Run Oracle Exadata_DeploymentAssistant 257

Step3: Create Network VLANs and DNS Entries for Hostnames 285

Step4: Run CheckIP toVerify Network Readiness 285

Step5:Run Cables and Power to Exadata Racks 288

Step6: Perform HardwareInstallation 289

Step7:_StageOneCommand Files and Oracle Software 289

Step8:_Configurethe_{Operating System} 291

Step9: Run OneCommand 294

Upgrading

Exadata 297

CreatingaNew RAC Cluster 298

Upgradingthe_Existing Cluster 299

Summary

301

Chapter

9:

_Recovering

Exadata • 303

Exadata

_Diagnostic

Tools 303

Sun_{Diagnostics: sundiag.sh} 304

CONTENTS

Backing Up

Exadata 308

BackingUp the Database Servers 308

Backing Upthe_StorageCell 312

Backing

Upthe Database 316 Disk-BasedBackups 316

Tape-Based Backups 317

Backupfrom_StandbyDatabase 318

ExadataOptimizationsforRMAN 318

Recovering

Exadata 319

Restoringthe Database Server 320

Recoveringthe_StorageCell 323

Summary 339

Chapter

10: Exadata Wait Events 341

Events

_Specific

toExadata 342 The "cell" Events 343

PlanStepsThat_TriggerEvents 344

Exadata Wait Eventsin the User I/O Class 346

cell smart tablescan 346

cell smart indexscan 350

cell_singleblock_physicalread 352

cell multiblockphysicalread 354

cell listofblocksphysicalread 355 cell smart file creation 356

cell statistics_gather 356 Minor EventsintheUser/10 Class 357

Exadata Wait Eventsinthe

_System

I/OClass 358

cell smart incrementalbackup 358

cellsmart restore from_backup 360

Exadata Wait Events in the Other and Idle Classes 361

cell smart flash unkeep 361 Event_Meaning 362 xiv

Non-Exadata-Specific

Events 363 directpathread 363

Enq:KO—fast_{object checkpoint} 364 reliablemessage 365

Resource

_Manager

Events 366

resmgr.becomeactive 366

resmgncpuquantum 368

resmgr:pqqueued 369

Summary

370

Chapter

11: Exadata Performance Metrics 371

Measuring

Exadata's Performance Metrics 371

Revisiting

the

_{Prerequisites}

for Exadata Smart Scans 374

Exadata SmartScan Performance 374

UnderstandingExadata Smart Scan Metrics and Performance Counters 378 Exadata _Dynamic Performance Counters 378

When and How to Use Performance Counters 379 The_Meaningand Explanationof Exadata Performance Counters 383

Performance CounterReferenceforaSelected Subset 386

Understanding

SQLStatementPerformance 411

Querying

cellsrv Internal

_Processing

Statistics 414

TheVSCELLFamilyofViews 415

The cellsrvstatutility 419

Summary

421

Chapter

12:

_Monitoring

Exadata Performance 423 A _Systematic

_Approach

423

Monitoring

SQLStatement

_Response

Time 424

MonitoringSQL Statements with Real-TimeSQLMonitoring Reports 425

mCONTENTS

Monitoring

the

_Storage

Cell

_Layer

441 AccessingCell Metrics intheCell_{Layer Using}CellCLI 442

AccessingCell Metrics_Usingthe_{Enterprise Manager}Exadata_StorageServerPlug-In 443 Which Cell Metricsto Use? 449

MonitoringExadataStorageCellOS-Level Metrics 450

Summary

461

Chapter 13:

_Migrating

toExadata 463

Migration

Strategies

464

Logical Migration

465

Extract andLoad 466

Copying Dataover aDatabase Link 472

Replication-Based Migration 486

Logical Migration Wrap Up 492

Physical

Migration

492

Backup

and Restore 493

FullBackupand Restore 493

Incremental_Backup 495

Transportable Tablespaces 497

Cross-Platform TTS with IncrementalBackups 500

Physical Standby ;..503

Wrap Up Physical MigrationSection 505

Summary

506

Chapter

14:

_{Storage Layout}

507

Exadata Disk Architecture 507

FailureGroups 509

Grid Disks 512

StorageAllocation 514

Creating

Grid Disks 518

CreatingGrid Disks 519

Grid DiskSizing.... 520

CreatingFlashDisk-Based Grid Disks 524

Storage Strategies

525

Configuration Options 525

Isolating StorageCell Access 526

Cell

_Security

528

Cell_{Security Terminology} .529

Cell_SecurityBest Practices 529

Configuring ASM-Scoped Security 530

Configuring Database-Scoped Security 531

RemovingCell _Security 534

Summary

536

Chapter

15:

_Compute

Node

_Layout

537

Provisioning

Considerations 538

Non-RAC

_{Configuration}

539

Split-Rack

Clusters 541

Typical

Exadata

_{Configuration}

543

Multi-RackClusters 544

Summary

546

Chapter

16:

_Patching

Exadata 547

Types

ofExadata Patches 548

Quarterly

Database Patch for Exadata 549

ApplyingaQDPE in Place 550

ApplyingaQDPEby_CloningHomes 553

Exadata

_Storage

Server Patches 556

Applyingan ExadataStorageServerPatch 559

Upgrading Compute Nodes 565

UpgradingInfiniBandSwitches 568

ApplyingPatchesto_{Standby Systems} 569

CONTENTS

Chapter

17:

_Unlearning

Some

_Things

We

_Thought

We Knew 571

A Tale of Two_{Systems 571}

OLTP-Oriented Workloads ₅₇₂

Exadata Smart Flash Cache_(ESFC) 572

Scalability 573

Write-lntensiveOLTP Workloads 573

DW-OrientedWorkloads 574

EnablingSmart Scans 574

ThingsThat Can_Cripple Smart Scans 576

Other_Thingsto_KeepinMind 583

Mixed Workloads 590

To IndexorNot to Index? 591

The0ptimi2erDoesn'tKnow 594

Using ResourceManager 598

Summary

598

Appendix

A:CELLCLI and DCLI ₅₉₉

An Introductionto CellCLI 599

Invokingcellcli 600

GettingFamiliar withcellcli 602

SendingCommands fromthe_{OperatingSystem} 607

UsingcellcliXML_Outputin the Database 607

Configuringand_Managingthe StorageCell 609

An Introduction to deli 610

Summary

612

AppendixB: Online Exadata Resources 613

My Oracle

Support

Notes 613

The Authors'

_Blogs

615

Appendix

C:

_{Diagnostic Scripts}

617

Appendix

D: exachk 621

An Introduction to exachk 621

Running exachk 622

SavingPasswords for exachk 625

Automatingexachk Executions 627

Summary 629