Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability

Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability

by Jim Czuprynski, Deiby Gomez, Bert Scalzo
Released October 2017
Publisher(s): McGraw-Hill
ISBN: 9781260025972

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master Oracle Database 12c Release 2 testing and tuning

Seamlessly transition to Oracle Database 12c Release 2 and achieve peak performance using the step-by-step instruction and best practices contained in this Oracle Press guide. Written by a team of Oracle ACEs, Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability clearly explains how to identify, investigate, and resolve performance issues. You will discover how to use troubleshooting tools and test rigs, optimize code and queries, evaluate database performance, perform realistic application testing, capture and replay actual production workloads, and employ Oracle Database In-Memory.

•Establish benchmarks and evaluate application workload performance

•Configure and deploy SQL Tuning Advisor and SQL Access Advisor

•Maximize efficiency using Oracle Database In-Memory and In-Memory Advisor

•Identify and repair poorly running code with SQL Monitor

•Uncover database problems using Real-Time ADDM and Emergency Monitoring

•Work with database workload capture and replay

•Analyze third-party code with Workload Intelligence

•Identify database objects that will benefit most from In-Memory Column Store (IMCS)

•Monitor and manage IMCS objects with In-Memory Central

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. About the Authors
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. PART I: Tools and Test Rigs
    1. 1 Presenting Tools for Tuning
      1. An Oracle DBA’s Tool Belt: Tools for Tuning
      2. Chapter Roadmap
        1. Part I—Tools and Test Rigs
        2. Part II—Digging Deeper: SQL Performance Management Tools
        3. Part III—It’s Running Slow: Database Performance Evaluation
        4. Part IV—Testing to Destruction
        5. Part V—A Brave New World: Database In-Memory
        6. Part VI— Appendixes, with Other Tuning Tools, Code Examples, and References
      3. Summary
    2. 2 Testing Methodology
      1. TPC-DS: An Appropriate Benchmark for Testing
        1. TPC-DS Database Schema
        2. TPC-DS Application Workloads
      2. Swingbench: Our Tool of Choice for Workload Generation
        1. Swingbench Overview
        2. Swingbench Improvements in Version 2.6.1
      3. Summary
  10. PART II: Digging Deeper: SQL Performance Management Tools
    1. 3 SQL Tuning Sets and Tuning Advisor
      1. SQL Tuning Sets
        1. Constructing SQL Tuning Sets
        2. Showing SQL Tuning Set Information
        3. Transporting SQL Tuning Sets to Another Database
      2. SQL Tuning Advisor
        1. SQL Tuning Advisor: Analysis Targets
        2. Example: Tuning a SQL Statement with SQL Tuning Advisor
        3. Explain Plan Section
        4. Using SQL Tuning Advisor in Enterprise Manager Cloud Control
      3. Summary
    2. 4 SQL Access Advisor
      1. Using SQL Access Advisor
        1. Analysis Sources
        2. Analysis Recommendations
        3. Limiting the Analysis
        4. Modes of Execution
        5. Analysis Scope
      2. Example: Tuning a SQL Statement with SQL Access Advisor
        1. Creating a SQL Tuning Set
        2. Creating a SQL Tuning Task
        3. Linking the SQL Access Advisor Task with the STS
        4. Configuring the Task
        5. Executing a SQL Tuning Task
        6. Monitoring a SQL Tuning Task
        7. Displaying the Results of a SQL Tuning Task
        8. Creating a Script with the Recommendations
      3. Using SQL Access Advisor in Enterprise Manager Cloud Control
      4. Summary
    3. 5 SQL Plan Management
      1. What Affects Execution Plans
      2. Evolution of Execution Plan Improvements
      3. Understanding Plan Stability Internals
        1. Plan Management Storage
        2. Plan Management Internal Processing Logic
        3. Plan Management Cleanup
      4. SQL Plan Management Example Using TPC-DS
      5. Summary
  11. PART III: It’s Running Slow: Database Performance Evaluation
    1. 6 SQL Monitor
      1. Which SQL Statements Are Monitored?
        1. SQL Monitor: Division of Labor
      2. Interacting with SQL Monitor
        1. Invoking SQL Monitor via PL/SQL
        2. Invoking SQL Monitor from SQL Developer
        3. Invoking SQL Monitor from OEM Express
        4. Invoking SQL Monitor from OEM Cloud Control
      3. Interpreting SQL Monitor Performance Data
      4. Automating SQL Monitor
      5. Summary
    2. 7 Real-Time Operations Monitoring
      1. Defining a Simple Operation: The +MONITOR Hint
      2. Defining a Composite Operation
        1. Examining a Composite Operation via PL/SQL
        2. Examining a Composite Operation via OEM Express
      3. Summary
    3. 8 Real-Time ADDM and Emergency Monitoring
      1. Real-Time ADDM
        1. Standard ADDM: A Review
        2. Real-Time ADDM to the Rescue
        3. Real-Time ADDM via Scripts
      2. Real-Time ADDM via OEM Cloud Control 13cR2
      3. Emergency Monitoring: When All Else Fails
      4. Summary
    4. 9 ASH Analytics
      1. It’s All a Matter of Time
      2. ASH Data Warehouse Design
      3. ASH Analytics in Action
      4. Summary
  12. PART IV: Testing to Destruction: Real Application Testing
    1. 10 SQL Performance Analyzer
      1. Using SQL Performance Analyzer
        1. SPA vs. DWC/R: What’s the Difference?
      2. SQL Performance Analyzer Inputs
      3. Testing the Impact of a Change
      4. Using SPA to Estimate the Impact of a Change with a CLI
      5. Using SPA to Estimate the Impact of a Change with OEM Cloud Control
      6. Summary
    2. 11 Database Workload Capture and Workload Intelligence
      1. Database Workload Capture and Replay
        1. Database Workload Capture and Replay: Suggested Use Cases
      2. Database Workload Capture
        1. DWC: Concepts and Architecture
      3. Capturing Application Workloads with DWC
        1. DWC: Capturing an Application Workload Using OEM Cloud Control
        2. DWC: Capturing Application Workloads via Scripts
        3. DWC: Workload Reporting
      4. Workload Intelligence: Making the Invisible Visible
        1. Workload Intelligence: Concepts
        2. WI: A Practical Example
        3. Workload Intelligence: Findings
        4. Workload Intelligence: Metadata
      5. Summary
    3. 12 Database Workload Replay
      1. DWR Concepts and Architecture
        1. DWR Operations: A Checklist
      2. Analyzing DWR Results
        1. DWR Improvements in Oracle 12c
      3. Leveraging Oracle Enterprise Manager Cloud Control
        1. Preprocessing DWC Capture Files
        2. Replaying the Preprocessed Workload
      4. Analyzing Performance Impacts
        1. DWR Workload Replay Report
        2. DWR Comparison Reporting
      5. Consolidated Database Replay
        1. Gathering the New Workload
        2. DWR: Replaying a Consolidated Workload
        3. Consolidated Replay: Analyzing Results
      6. DWR: Employing Workload Scale-Up
      7. Summary
  13. PART V: A Brave New World: Database In-Memory
    1. 13 Database In-Memory Advisor
      1. Database In-Memory Concepts: A Quick Review
        1. DBIM: In-Memory Column Store
        2. DBIM: Advantages for Analytics
      2. Introducing the In-Memory Advisor (IMADV)
        1. IMADV: A Brief History
        2. IMADV 2.0: Installation
        3. Leveraging IMADV: A Practical Example
      3. Summary
    2. 14 In-Memory Central
      1. Oracle Database 12cR2: Database In-Memory Enhancements
        1. DBIM: Architectural Enhancements
        2. DBIM: Faster Population, Wider Availability
      2. Overview: In-Memory Central
        1. Activating Database In-Memory
        2. Leveraging In-Memory Central
        3. In-Memory Central in Action: A Practical Example
      3. Summary
  14. PART VI: Appendixes
    1. A Other Performance Tuning and Testing Tools
      1. Tools Intrinsic to Oracle Database
        1. STATSPACK
        2. Optimizer Statistics Advisor (OSA)
        3. Segment Advisor
      2. Other Home-Grown Toolsets
        1. TUNAs360
        2. eDB360
        3. SQLd360
        4. Pathfinder
        5. MOATS
        6. SLOB
        7. HammerDB
    2. B Code Examples and Listings
      1. Chapter 11: Database Workload Capture and Workload Intelligence
      2. Chapter 12: Database Workload Replay
    3. C References
      1. Chapter 3: SQL Tuning Sets and Tuning Advisor
      2. Chapter 4: SQL Access Advisor
      3. Chapter 5: SQL Plan Management
      4. Chapter 6: SQL Monitor
      5. Chapter 7: Real-Time Operations Monitoring
      6. Chapter 8: Real-Time ADDM and Emergency Monitoring
      7. Chapter 9: ASH Analytics
      8. Chapter 10: SQL Performance Analyzer
      9. Chapter 11: Database Workload Capture and Workload Intelligence
      10. Chapter 12: Database Workload Replay
      11. Chapter 13: Database In-Memory Advisor
      12. Chapter 14: In-Memory Central
  15. Index

Product information

  • Title: Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability
  • Author(s): Jim Czuprynski, Deiby Gomez, Bert Scalzo
  • Release date: October 2017
  • Publisher(s): McGraw-Hill
  • ISBN: 9781260025972