You are previewing Expert Oracle Exadata, Second Edition.
O'Reilly logo
Expert Oracle Exadata, Second Edition

Book Description

Expert Oracle Exadata, 2nd Edition opens up the internals of Oracle's Exadata platform so that you can fully benefit from the most performant and scalable database hardware appliance capable of running Oracle Database. This edition is fully-updated to cover Exadata 5-2 and Oracle Database 12c. If you're new to Exadata, you'll soon learn that it embodies a change in how you think about and manage relational databases. A key part of that change lies in the concept of offloading SQL processing to the storage layer. In addition there is Oracle's engineering effort in creating a powerful platform for both consolidation and transaction processing. The resulting value proposition in the form of Exadata has truly been a game-changer.

Expert Oracle Exadata, 2nd Edition provides a look at the internals and how the combination of hardware and software that comprise Exadata actually work. Authors include Martin Bach, Andy Colvin, and Frits Hoogland, with contributions from Karl Arao, and built on the foundation laid by Kerry Osborne, Randy Johnson, and Tanel Poder in the first edition. They share their real-world experience gained through a great many Exadata implementations, possibly more than any other group of experts today. Always their goal is toward helping you advance your career through success with Exadata in your own environment. This book is intended for readers who want to understand what makes the platform tick and for whom—"how" it does what it is does is as important as what it does. By being exposed to the features that are unique to Exadata, you will gain an understanding of the mechanics that will allow you to fully benefit from the advantages that the platform provides.

This book changes how you think about managing SQL performance and processing. It provides a roadmap to successful Exadata implementation. And it removes the "black box" mystique. You'll learn how Exadata actually works and be better able to manage your Exadata engineered systems in support of your business.

This book:

  • Changes the way you think about managing SQL performance and processing
  • Provides a roadmap to successful Exadata implementation
  • Removes the "black box" mystique, showing how Exadata actually works
  • Table of Contents

    1. Cover
    2. Title
    3. Copyright
    4. About IOUG Press
    5. Contents at a Glance
    6. Contents
    7. About the Authors
    8. Acknowledgments
    9. Introduction
    10. Chapter 1: What Is Exadata?
      1. An Overview of Exadata
      2. History of Exadata
      3. Alternative Views of What Exadata Is
        1. Data Warehouse Appliance
        2. OLTP Machine
        3. Consolidation Platform
      4. Configuration Options
        1. Exadata Database Machine X5-2
        2. Exadata Database Machine X4-8
        3. Exadata Storage Expansion Rack X5-2
      5. Hardware Components
        1. Operating Systems
        2. Database Servers
        3. Storage Servers
        4. InfiniBand
        5. Flash Cache
        6. Disks
        7. Bits and Pieces
      6. Software Components
        1. Database Server Software
        2. Storage Server Software
      7. Software Architecture
      8. Summary
    11. Chapter 2: Offloading / Smart Scan
      1. Why Offloading Is Important
      2. What Offloading Includes
        1. Column Projection
        2. Predicate Filtering
        3. Storage Indexes and Zone Maps
        4. Simple Joins (Bloom Filters)
        5. Function Offloading
        6. Compression/Decompression
        7. Encryption/Decryption
        8. Virtual Columns
        9. Support for LOB offloading
        10. JSON Support and Offloading
        11. Data Mining Model Scoring
        12. Non-Smart Scan Offloading
      3. Smart Scan Prerequisites
        1. Full Scans
        2. Direct Path Reads
        3. Exadata Storage
      4. Smart Scan Disablers
        1. Simply Unavailable
        2. Reverting to Block Shipping
        3. Skipping Some Offloading
        4. Skipping Offloading silently
      5. How to Verify That Smart Scan Is Happening
        1. 10046 Trace
        2. Session Performance Statistics
        3. Offload Eligible Bytes
        4. SQL Monitoring
      6. Parameters
      7. Summary
    12. Chapter 3: Hybrid Columnar Compression
      1. Oracle Storage Review
      2. Disassembling the Oracle Block
      3. Compression Mechanics
        1. BASIC Compression
        2. OLTP Compression
        3. Hybrid Columnar Compression
        4. HCC Internals
        5. What Happens When You Create a HCC Compressed Table?
      4. HCC Performance
        1. Load Performance
        2. Query Performance
        3. DML Performance
      5. Expected Compression Ratios
        1. Compression Advisor
        2. Real-World Examples
      6. Restrictions/Challenges
        1. Moving Data to a Non-Exadata Platform
        2. Disabling Serial Direct Path Reads
        3. Locking Issues
        4. Single Row Access
      7. Common Usage Scenarios
        1. Automatic Data Optimization
        2. Example Use Cases for ADO
      8. Summary
    13. Chapter 4: Storage Indexes
      1. Structure
      2. Monitoring Storage Indexes
        1. Database Statistics
        2. Tracing
        3. Monitoring Wrap-Up
      3. Controlling Storage Indexes
        1. _kcfis_storageidx_disabled
        2. _kcfis_storageidx_diag_mode
        3. _cell_storidx_mode
        4. _cell_storidx_minmax_enabled
        5. Storage Software Parameters
      4. Behavior
      5. Performance
        1. Special Optimization for Nulls
        2. Physical Distribution of Values
      6. Potential Issues
        1. Incorrect Results
        2. Moving Target
        3. Partition Size
        4. Incompatible Coding Techniques
      7. Summary
    14. Chapter 5: Exadata Smart Flash Cache
      1. Hardware
        1. Flash Memory in Exadata X4-2 Storage Servers
        2. Flash Memory in Exadata X5-2 Storage Servers
      2. Flash Cache vs. Flash Disk
        1. Using Flash Memory as Cache
        2. Mixed Workload and OLTP Optimizations
        3. Using Flash Memory for Database Logging
        4. Using Flash Memory to Accelerate Writes
        5. Miscellaneous Other WBFC-related Optimizations
        6. How ESFC and ESFL Are Created
        7. Enabling the Write-back Flash Cache
        8. Flash Cache Compression
        9. Controlling ESFC Usage
      3. Monitoring
        1. At the Storage Layer
        2. At the Database Layer
      4. Summary
    15. Chapter 6: Exadata Parallel Operations
      1. Parameters
      2. Parallelization at the Storage Tier
      3. Auto DOP
        1. Operation and Configuration
        2. I/O Calibration
        3. Auto DOP Wrap-Up
      4. Parallel Statement Queueing
        1. The Old Way
        2. The New Way
        3. Controlling Parallel Queueing
        4. Parallel Statement Queueing Wrap-Up
      5. In-Memory Parallel Execution
      6. Troubleshooting Parallel Execution
      7. Summary
    16. Chapter 7: Resource Management
      1. Consolidation
        1. Types of Database Consolidation
      2. Instance Caging
        1. Configuring Instance Caging
        2. Setting CPU_COUNT
        3. Instance Caging Usage and Results
        4. Instance Caging and Multitenancy
        5. Over-Provisioning
      3. Binding Instances to Specific CPUs Using Cgroups
        1. Installation and Configuration of Cgroups
      4. Oracle 12c THREADED_EXECUTION
      5. Managing PGA Memory
      6. Database Resource Manager
        1. Creating a CDB Resource Plan
        2. Creating a (Pluggable) Database Resource Plan
        3. Using the Scheduler to Change the Resource Plan
        4. The Wait Event: resmgr: cpu quantum
        5. Where to Go from Here
        6. Resource Mapping Priorities
        7. Resource Limiting
        8. Other Limiting Parameters
        9. Consumer Group Mappings Using ORACLE_FUNCTION
        10. Monitoring the Resource Manager
        11. Resource Manager Views
      7. I/O Resource Manager
        1. IORM Methods
        2. How IORM Works
        3. IORM Architecture
        4. IORM Objective
        5. Configuring Interdatabase IORM
        6. Category IORM
        7. I/O Resource Manager and Pluggable Databases
        8. I/O Resource Manager Profiles
      8. Resource Management Directives Matrix
        1. IORM Monitoring and Metrics
      9. Summary
    17. Chapter 8: Configuring Exadata
      1. Exadata Network Components
        1. The Management Network
        2. The Client Access Network
        3. The Private Network
      2. About the Configuration Process
      3. Configuring Exadata
        1. Step 1: Gathering Installation Requirements
        2. Step 2: Run Oracle Exadata Deployment Assistant
        3. Step 3: Create Network VLANs and DNS Entries for Hostnames
        4. Step 4: Run CheckIP to Verify Network Readiness
        5. Step 5: Run Cables and Power to Exadata Racks
        6. Step 6: Perform Hardware Installation
        7. Step 7: Stage OneCommand Files and Oracle Software
        8. Step 8: Configure the Operating System
        9. Step 9: Run OneCommand
      4. Upgrading Exadata
        1. Creating a New RAC Cluster
        2. Upgrading the Existing Cluster
      5. Summary
    18. Chapter 9: Recovering Exadata
      1. Exadata Diagnostic Tools
        1. Sun Diagnostics: sundiag.sh
        2. Cell Alerts
      2. Backing Up Exadata
        1. Backing Up the Database Servers
        2. Backing Up the Storage Cell
      3. Backing Up the Database
        1. Disk-Based Backups
        2. Tape-Based Backups
        3. Backup from Standby Database
        4. Exadata Optimizations for RMAN
      4. Recovering Exadata
        1. Restoring the Database Server
        2. Recovering the Storage Cell
      5. Summary
    19. Chapter 10: Exadata Wait Events
      1. Events Specific to Exadata
        1. The “cell” Events
        2. Plan Steps That Trigger Events
      2. Exadata Wait Events in the User I/O Class
        1. cell smart table scan
        2. cell smart index scan
        3. cell single block physical read
        4. cell multiblock physical read
        5. cell list of blocks physical read
        6. cell smart file creation
        7. cell statistics gather
        8. Minor Events in the User/IO Class
      3. Exadata Wait Events in the System I/O Class
        1. cell smart incremental backup
        2. cell smart restore from backup
      4. Exadata Wait Events in the Other and Idle Classes
        1. cell smart flash unkeep
        2. Event Meaning
      5. Non-Exadata-Specific Events
        1. direct path read
        2. Enq: KO—fast object checkpoint
        3. reliable message
      6. Resource Manager Events
        1. resmgr:become active
        2. resmgr:cpu quantum
        3. resmgr:pq queued
      7. Summary
    20. Chapter 11: Exadata Performance Metrics
      1. Measuring Exadata’s Performance Metrics
      2. Revisiting the Prerequisites for Exadata Smart Scans
        1. Exadata Smart Scan Performance
        2. Understanding Exadata Smart Scan Metrics and Performance Counters
      3. Exadata Dynamic Performance Counters
        1. When and How to Use Performance Counters
        2. The Meaning and Explanation of Exadata Performance Counters
        3. Performance Counter Reference for a Selected Subset
      4. Understanding SQL Statement Performance
      5. Querying cellsrv Internal Processing Statistics
        1. The V$CELL Family of Views
        2. The cellsrvstat utility
      6. Summary
    21. Chapter 12: Monitoring Exadata Performance
      1. A Systematic Approach
      2. Monitoring SQL Statement Response Time
        1. Monitoring SQL Statements with Real-Time SQL Monitoring Reports
        2. Monitoring SQL Statements Using V$SQL and V$SQLSTATS
      3. Monitoring the Storage Cell Layer
        1. Accessing Cell Metrics in the Cell Layer Using CellCLI
        2. Accessing Cell Metrics Using the Enterprise Manager Exadata Storage Server Plug-In
        3. Which Cell Metrics to Use?
        4. Monitoring Exadata Storage Cell OS-Level Metrics
      4. Summary
    22. Chapter 13: Migrating to Exadata
      1. Migration Strategies
      2. Logical Migration
        1. Extract and Load
        2. Copying Data over a Database Link
        3. Replication-Based Migration
        4. Logical Migration Wrap Up
      3. Physical Migration
      4. Backup and Restore
        1. Full Backup and Restore
        2. Incremental Backup
        3. Transportable Tablespaces
        4. Cross-Platform TTS with Incremental Backups
        5. Physical Standby
        6. Wrap Up Physical Migration Section
      5. Summary
    23. Chapter 14: Storage Layout
      1. Exadata Disk Architecture
        1. Failure Groups
        2. Grid Disks
        3. Storage Allocation
      2. Creating Grid Disks
        1. Creating Grid Disks
        2. Grid Disk Sizing
        3. Creating FlashDisk-Based Grid Disks
      3. Storage Strategies
        1. Configuration Options
        2. Isolating Storage Cell Access
      4. Cell Security
        1. Cell Security Terminology
        2. Cell Security Best Practices
        3. Configuring ASM-Scoped Security
        4. Configuring Database-Scoped Security
        5. Removing Cell Security
      5. Summary
    24. Chapter 15: Compute Node Layout
      1. Provisioning Considerations
      2. Non-RAC Configuration
      3. Split-Rack Clusters
      4. Typical Exadata Configuration
      5. Multi-Rack Clusters
      6. Summary
    25. Chapter 16: Patching Exadata
      1. Types of Exadata Patches
      2. Quarterly Database Patch for Exadata
        1. Applying a QDPE in Place
        2. Applying a QDPE by Cloning Homes
      3. Exadata Storage Server Patches
        1. Applying an Exadata Storage Server Patch
        2. Upgrading Compute Nodes
        3. Upgrading InfiniBand Switches
        4. Applying Patches to Standby Systems
      4. Summary
    26. Chapter 17: Unlearning Some Things We Thought We Knew
      1. A Tale of Two Systems
      2. OLTP-Oriented Workloads
        1. Exadata Smart Flash Cache (ESFC)
        2. Scalability
        3. Write-Intensive OLTP Workloads
      3. DW-Oriented Workloads
        1. Enabling Smart Scans
        2. Things That Can Cripple Smart Scans
        3. Other Things to Keep in Mind
      4. Mixed Workloads
        1. To Index or Not to Index?
        2. The Optimizer Doesn’t Know
        3. Using Resource Manager
      5. Summary
    27. Appendix A: CELLCLI and DCLI
      1. An Introduction to CellCLI
        1. Invoking cellcli
        2. Getting Familiar with cellcli
        3. Sending Commands from the Operating System
        4. Using cellcli XML Output in the Database
        5. Configuring and Managing the Storage Cell
      2. An Introduction to dcli
      3. Summary
    28. Appendix B: Online Exadata Resources
      1. My Oracle Support Notes
      2. The Authors’ Blogs
    29. Appendix C: Diagnostic Scripts
    30. Appendix D: exachk
      1. An Introduction to exachk
        1. Running exachk
        2. Saving Passwords for exachk
        3. Automating exachk Executions
      2. Summary
    31. Index
    32. General