expert oracle exadata978-1-4302-3393-0/1.pdf · expert oracle exadata #opyrightÚ by+erry/sborne...
TRANSCRIPT
Expert Oracle Exadata
Kerry Osborne Randy Johnson Tanel Pöder
Expert Oracle Exadata
(eBook)
Contents at a Glance
About the Authors................................................................................................ xvi
About the Technical Reviewer ............................................................................ xvii
Acknowledgments ............................................................................................. xviii
Introduction ......................................................................................................... xix
Chapter 1: What Is Exadata?...................................................................................1
Chapter 2: Offloading / Smart Scan ......................................................................23
Chapter 3: Hybrid Columnar Compression............................................................65
Chapter 4: Storage Indexes.................................................................................105
Chapter 5: Exadata Smart Flash Cache...............................................................125
Chapter 6: Exadata Parallel Operations ..............................................................143
Chapter 7: Resource Management......................................................................175
Chapter 8: Configuring Exadata ..........................................................................237
Chapter 9: Recovering Exadata...........................................................................275
Chapter 10: Exadata Wait Events........................................................................319
Chapter 11: Understanding Exadata Performance Metrics.................................345
Chapter 12: Monitoring Exadata Performance....................................................379
Chapter 13: Migrating to Exadata.......................................................................419
Chapter 14: Storage Layout ................................................................................467
Chapter 15: Compute Node Layout .....................................................................497
Chapter 16: Unlearning Some Things We Thought We Knew.............................511
Appendix A: CellCLI and dcli ...............................................................................535
Appendix B: Online Exadata Resources ..............................................................545
Appendix C: Diagnostic Scripts...........................................................................547
Index ...................................................................................................................551
Contents
About the Authors................................................................................................ xvi
About the Technical Reviewer ............................................................................ xvii
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
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
DML Performance................................................................................................................................... 83
Expected Compression Ratios .........................................................................................91
Compression Advisor.............................................................................................................................. 91
Real World Examples.............................................................................................................................. 94
Restrictions/Challenges.................................................................................................100
Moving Data to a non-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 as Cache ............................................................................................................. 128
How ESFC is Created ............................................................................................................................ 132
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
Auto DOP........................................................................................................................146
Operation and Configuration................................................................................................................. 146
I/O Calibration....................................................................................................................................... 149
Auto DOP 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 IORM Works.................................................................................................................................. 206
Methods for Managing I/O on Exadata ................................................................................................. 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 ( ) ............................................................................................................ 258
Step 5: Firstboot ................................................................................................................................... 260
Step 6: Staging the Installation Media.................................................................................................. 262
Step 7: Running OneCommand............................................................................................................. 263
Upgrading Exadata.........................................................................................................268
Creating a New RAC Cluster ................................................................................................................. 268
Upgrading the Existing Cluster ............................................................................................................. 270
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 the Database 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
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 table scan ............................................................................................................................ 323
cell smart index scan ........................................................................................................................... 326
cell single block physical read ............................................................................................................. 328
cell multiblock 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
cell smart incremental 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
resmgr:cpu quantum ............................................................................................................................ 341
resmgr:pq 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 ............................................................................................ 356
Understanding SQL Statement Performance .................................................................374
Summary .......................................................................................................................377
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 Metrics to Use?................................................................................................................... 405
Monitoring Exadata Storage Cell OS-level Metrics............................................................................... 406
Summary .......................................................................................................................418
Chapter 13: Migrating to Exadata.......................................................................419
Migration Strategies ......................................................................................................420
Logical Migration ...........................................................................................................421
Extract and Load................................................................................................................................... 422
Copying Data over 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
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
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-Intensive OLTP Workloads .......................................................................................................... 513
DW-Oriented Workloads ................................................................................................514
Enabling Smart Scans .......................................................................................................................... 514 Things that Can Cripple Smart Scans................................................................................................... 516 Other Things to Keep in Mind ............................................................................................................... 526
Mixed Workloads ...........................................................................................................528
To Index or Not to Index?...................................................................................................................... 529 The Optimizer Doesn’t Know ................................................................................................................ 530 Using Resource Manager ..................................................................................................................... 533
Summary .......................................................................................................................534
Appendix A: CellCLI and dcli ...............................................................................535
CellCLI Command Syntax...............................................................................................535
Getting Familiar with CellCLI ................................................................................................................ 536 Sending Commands from the Operating System ................................................................................. 540 Configuring and Managing the Storage Cell ......................................................................................... 540
dcli 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
About the Author
Kerry Osborne
Randy Johnson
Tanel Pöder
About the Technical Reviewer
Kevin Closson
Acknowledgments
Introduction
The Intended Audience
How We Came to Write This Book
A Moving Target
Thanks to the Unofficial Editors
Who Wrote That?
Online Resources
A Note on “Kevin Says”
How We Tested
Schemas and Tables
Good Luck