You are previewing Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach.
O'Reilly logo
Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

Book Description

Performance problems are rarely "problems" per se. They are more often "crises" during which you're pressured for results by a manager standing outside your cubicle while your phone rings with queries from the help desk. You won't have the time for a leisurely perusal of the manuals, nor to lean back and read a book on theory. What you need in that situation is a book of solutions, and solutions are precisely what Oracle Database 12c Performance Tuning Recipes delivers.

Oracle Database 12c Performance Tuning Recipes is a ready reference for database administrators in need of immediate help with performance issues relating to Oracle Database. The book takes an example-based approach, wherein each chapter covers a specific problem domain. Within each chapter are "recipes," showing by example how to perform common tasks in that chapter's domain. Solutions in the recipes are backed by clear explanations of background and theory from the author team. Whatever the task, if it's performance-related, you'll probably find a recipe and a solution in this book.

  • Provides proven solutions to real-life Oracle performance problems

  • Offers relevant background and theory to support each solution

  • Gets straight to the point for when you're under pressure for results

What you'll learn

  • Optimize the use of memory and storage

  • Monitor performance and troubleshoot problems

  • Identify and improve poorly-performing SQL statements

  • Adjust the most important optimizer parameters to your advantage

  • Create indexes that get used and make a positive impact upon performance

  • Automate and stabilize using key features such as SQL Tuning Advisor and SQL Plan Baselines

Who this book is for

Oracle Database 12c Performance Tuning Recipes is aimed squarely at Oracle Database administrators. The book especially appeals to those administrators desiring to have at their side a ready-to-go set of solutions to common database performance problems.

Table of Contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. About the Technical Reviewers
  7. Acknowledgments
  8. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Downloading the Code
  9. CHAPTER 1: Optimizing Table Performance
    1. 1-1. Building a Database That Maximizes Performance
    2. 1-2. Creating Tablespaces to Maximize Performance
    3. 1-3. Matching Table Types to Business Requirements
    4. 1-4. Choosing Table Features for Performance
    5. 1-5. Selecting Data Types Appropriately
    6. 1-6. Avoiding Extent Allocation Delays When Creating Tables
    7. 1-7. Maximizing Data-Loading Speeds
    8. 1-8. Efficiently Removing Table Data
    9. 1-9. Displaying Automated Segment Advisor Advice
    10. 1-10. Manually Generating Segment Advisor Advice
    11. 1-11. Automatically E-mailing Segment Advisor Output
    12. 1-12. Rebuilding Rows Spanning Multiple Blocks
    13. 1-13. Detecting Row Chaining and Row Migration
    14. 1-14. Differentiating Between Row Migration and Row Chaining
    15. 1-15. Proactively Preventing Row Migration/Chaining
    16. 1-16. Detecting Unused Space in a Table
    17. 1-17. Tracing to Detect Space Below the High-Water Mark
    18. 1-18. Using DBMS_SPACE to Detect Space Below the High-Water Mark
    19. 1-19. Freeing Unused Table Space
    20. 1-20. Compressing Data for Direct Path Loading
    21. 1-21. Compressing Data for All DML
    22. 1-22. Compressing Data at the Column Level
  10. CHAPTER 2: Choosing and Optimizing Indexes
    1. 2-1. Understanding B-tree Indexes
    2. 2-2. Deciding Which Columns to Index
    3. 2-3. Creating a Primary Key Constraint and Index
    4. 2-4. Ensuring Unique Column Values
    5. 2-5. Indexing Foreign Key Columns
    6. 2-6. Deciding When to Use a Concatenated Index
    7. 2-7. Reducing Index Size Through Compression
    8. 2-8. Implementing a Function-Based Index
    9. 2-9. Indexing a Virtual Column
    10. 2-10. Limiting Index Contention when Several Processes Insert in Parallel
    11. 2-11. Toggling the Visibility of an Index to the Optimizer
    12. 2-12. Creating a Bitmap Index in Support of a Star Schema
    13. 2-13. Creating a Bitmap Join Index
    14. 2-14. Creating an Index-Organized Table
    15. 2-15. Monitoring Index Usage
    16. 2-16. Maximizing Index Creation Speed
    17. 2-17. Reclaiming Unused Index Space
  11. CHAPTER 3: Optimizing Instance Memory
    1. 3-1. Automating Memory Management
    2. 3-2. Managing Multiple Buffer Pools
    3. 3-3. Setting Minimum Values for Memory
    4. 3-4. Monitoring Memory Resizing Operations
    5. 3-5. Optimizing Memory Usage
    6. 3-6. Tuning PGA Memory Allocation
    7. 3-7. Configuring the Server Query Cache
    8. 3-8. Managing the Server Result Cache
    9. 3-9. Caching SQL Query Results
    10. 3-10. Caching Client Result Sets
    11. 3-11. Caching PL/SQL Function Results
    12. 3-12. Configuring the Oracle Database Smart Flash Cache
    13. 3-13. Tuning the Redo Log Buffer
    14. 3-14. Limiting PGA Memory Allocation
  12. CHAPTER 4: Monitoring System Performance
    1. 4-1. Implementing Automatic Workload Repository (AWR)
    2. 4-2. Modifying the Statistics Interval and Retention Periods
    3. 4-3. Generating an AWR Report Manually
    4. 4-4. Generating an AWR Report via Enterprise Manager
    5. 4-5. Generating an AWR Report for a Single SQL Statement
    6. 4-6. Creating a Statistical Baseline for Your Database
    7. 4-7. Managing AWR Baselines via Enterprise Manager
    8. 4-8. Managing AWR Statistics Repository
    9. 4-9. Creating AWR Baselines Automatically
    10. 4-10. Quickly Analyzing AWR Output
    11. 4-11. Manually Getting Active Session Information
    12. 4-12. Getting ASH Information from Enterprise Manager
    13. 4-13. Getting ASH Information from the Data Dictionary
  13. CHAPTER 5: Minimizing System Contention
    1. 5-1. Understanding Response Time
    2. 5-2. Identifying SQL Statements with the Most Waits
    3. 5-3. Analyzing Wait Events
    4. 5-4. Understanding Wait Class Events
    5. 5-5. Examining Session Waits
    6. 5-6. Examining Wait Events by Class
    7. 5-7. Resolving Buffer Busy Waits
    8. 5-8. Resolving Log File Sync Waits
    9. 5-9. Minimizing Read by Other Session Wait Events
    10. 5-10. Reducing Direct Path Read Wait Events
    11. 5-11. Minimizing Recovery Writer Waits
    12. 5-12. Finding Out Who’s Holding a Blocking Lock
    13. 5-13. Identifying Blocked and Blocking Sessions
    14. 5-14. Dealing with a Blocking Lock
    15. 5-15. Identifying a Locked Object
    16. 5-16. Resolving enq: TM Lock Contention
    17. 5-17. Identifying Recently Locked Sessions
    18. 5-18. Analyzing Recent Wait Events in a Database
    19. 5-19. Identifying Time Spent Waiting Because of Locking
    20. 5-20. Minimizing Latch Contention
  14. CHAPTER 6: Analyzing Operating System Performance
    1. 6-1. Detecting Disk Space Issues
    2. 6-2. Identifying System Bottlenecks
    3. 6-3. Determining Top System-Resource-Consuming Processes
    4. 6-4. Detecting CPU Bottlenecks
    5. 6-5. Identifying Processes Consuming CPU and Memory
    6. 6-6. Determining I/O Bottlenecks
    7. 6-7. Detecting Network-Intensive Processes
    8. 6-8. Mapping a Resource-Intensive Process to a Database Process
    9. 6-9. Terminating a Resource-Intensive Process
  15. CHAPTER 7: Troubleshooting the Database
    1. 7-1. Determining the Optimal Undo Retention Period
    2. 7-2. Finding What’s Consuming the Most Undo
    3. 7-3. Resolving an ORA-01555 Error
    4. 7-4. Monitoring Temporary Tablespace Usage
    5. 7-5. Identifying Who Is Using the Temporary Tablespace
    6. 7-6. Resolving the “Unable to Extend Temp Segment” Error
    7. 7-7. Resolving Open Cursor Errors
    8. 7-8. Resolving a Hung Database
    9. 7-9. Invoking the Automatic Diagnostic Repository Command Interpreter
    10. 7-10. Viewing an Alert Log from ADRCI
    11. 7-11. Viewing Incidents with ADRCI
    12. 7-12. Packaging Incidents for Oracle Support
    13. 7-13. Running a Database Health Check
    14. 7-14. Creating a SQL Test Case
    15. 7-15. Generating an AWR Report
    16. 7-16. Comparing Database Performance Between Two Periods
    17. 7-17. Analyzing an AWR Report
  16. CHAPTER 8: Creating Efficient SQL
    1. 8-1. Retrieving All Rows from a Table
    2. 8-2. Retrieve a Subset of Rows from a Table
    3. 8-3. Joining Tables with Corresponding Rows
    4. 8-4. Joining Tables When Corresponding Rows May Be Missing
    5. 8-5. Constructing Simple Subqueries
    6. 8-6. Constructing Correlated Subqueries
    7. 8-7. Comparing Two Tables to Find Missing Rows
    8. 8-8. Comparing Two Tables to Find Matching Rows
    9. 8-9. Combining Results from Similar SELECT Statements
    10. 8-10. Searching for a Range of Values
    11. 8-11. Handling Null Values
    12. 8-12. Searching for Partial Column Values
    13. 8-13. Re-using SQL Statements Within the Shared Pool
    14. 8-14. Avoiding Accidental Full Table Scans
    15. 8-15. Creating Efficient Temporary Views
    16. 8-16. Avoiding the NOT Clause
    17. 8-17. Controlling Transaction Sizes
  17. CHAPTER 9: Manually Tuning SQL
    1. 9-1. Displaying an Execution Plan for a Query
    2. 9-2. Customizing Execution Plan Output
    3. 9-3. Graphically Displaying an Execution Plan
    4. 9-4. Reading an Execution Plan
    5. 9-5. Monitoring Long-Running SQL Statements
    6. 9-6. Identifying Resource-Consuming SQL Statements That Are Currently Executing
    7. 9-7. Seeing Execution Statistics for Currently Running SQL
    8. 9-8. Monitoring Progress of a SQL Execution Plan
    9. 9-9. Identifying Resource-Consuming SQL Statements That Have Executed in the Past
    10. Comparing SQL Performance After a System Change
  18. CHAPTER 10: Tracing SQL Execution
    1. 10-1. Preparing Your Environment
    2. 10-2. Tracing a Specific SQL Statement
    3. 10-3. Enabling Tracing in Your Own Session
    4. 10-4. Finding the Trace Files
    5. 10-5. Examining a Raw SQL Trace File
    6. 10-6. Analyzing Oracle Trace Files
    7. 10-7. Formatting Trace Files with TKPROF
    8. 10-8. Analyzing TKPROF Output
    9. 10-9. Analyzing Trace Files with Oracle Trace Analyzer
    10. 10-10. Tracing a Parallel Query
    11. 10-11. Tracing Specific Parallel Query Processes
    12. 10-12. Tracing Parallel Queries in a RAC System
    13. 10-13. Consolidating Multiple Trace Files
    14. 10-14. Finding the Correct Session for Tracing
    15. 10-15. Tracing a SQL Session
    16. 10-16. Tracing a Session by Process ID
    17. 10-17. Tracing Multiple Sessions
    18. 10-18. Tracing an Instance or a Database
    19. 10-19. Generating an Event 10046 Trace for a Session
    20. 10-20. Generating an Event 10046 Trace for an Instance
    21. 10-21. Setting a Trace in a Running Session
    22. 10-22. Enabling Tracing in a Session After a Login
    23. 10-23. Tracing the Optimizer’s Execution Path
    24. 10-24. Generating Automatic Oracle Error Traces
    25. 10-25. Tracing a Background Process
    26. 10-26. Enabling Oracle Listener Tracing
    27. 10-27. Setting Archive Tracing for Data Guard
  19. CHAPTER 11: Automated SQL Tuning
    1. 11-1. Displaying Automatic SQL Tuning Job Details
    2. 11-2. Displaying Automatic SQL Tuning Advisor Advice
    3. 11-3. Generating a SQL Script to Implement Automatic Tuning Advice
    4. 11-4. Modifying Automatic SQL Tuning Features
    5. 11-5. Disabling and Enabling Automatic SQL Tuning
    6. 11-6. Modifying Maintenance Window Attributes
    7. 11-7. Creating a SQL Tuning Set Object
    8. 11-8. Viewing Resource-Intensive SQL in the AWR
    9. 11-9. Populating a SQL Tuning Set from High-Resource SQL in AWR
    10. 11-10. Viewing Resource-Intensive SQL in Memory
    11. 11-11. Populating a SQL Tuning Set from Resource-Consuming SQL in Memory
    12. 11-12. Populating a SQL Tuning Set With All SQL in Memory
    13. 11-13. Displaying the Contents of a SQL Tuning Set
    14. 11-14. Selectively Deleting Statements from a SQL Tuning Set
    15. 11-15. Transporting a SQL Tuning Set
    16. 11-16. Creating a Tuning Task
    17. 11-17. Running the SQL Tuning Advisor
    18. 11-18. Generating SQL Tuning Advice from the Automatic Database Diagnostic Monitor
  20. CHAPTER 12: Execution Plan Optimization and Consistency
    1. Background
    2. Seeing the Big Picture
    3. 12-1. Creating and Accepting a SQL Profile
    4. 12-2. Determining if a Query is Using a SQL Profile
    5. 12-3. Automatically Accepting SQL Profiles
    6. 12-4. Displaying SQL Profile Information
    7. 12-5. Selectively Testing a SQL Profile
    8. 12-6. Transporting a SQL Profile to a Different Database
    9. 12-7. Disabling a SQL Profile
    10. 12-8. Dropping a SQL Profile
    11. 12-9. Creating a Plan Baseline for a SQL Statement in Memory
    12. 12-10. Creating Plan Baselines for SQL Contained in SQL Tuning Set
    13. 12-11. Automatically Adding Plan Baselines
    14. 12-12. Altering a Plan Baseline
    15. 12-13. Determining If Plan Baselines Exist
    16. 12-14. Determining if a Query is Using a Plan Baseline
    17. 12-15. Displaying Plan Baseline Execution Plans
    18. 12-16. Manually Adding a New Execution Plan to Plan Baseline (Evolving)
    19. 12-17. Toggling the Automatic Acceptance of New Low-Cost Execution Plans
    20. 12-18. Disabling Plan Baselines
    21. 12-19. Removing Plan Baseline Information
    22. 12-20. Transporting Plan Baselines
  21. CHAPTER 13: Configuring the Optimizer
    1. 13-1. Choosing an Optimizer Goal
    2. 13-2. Enabling Automatic Statistics Gathering
    3. 13-3. Setting Preferences for Statistics Collection
    4. 13-4. Manually Generating Statistics
    5. 13-5. Locking Statistics
    6. 13-6. Handling Missing Statistics
    7. 13-7. Exporting Statistics
    8. 13-8. Restoring Previous Versions of Statistics
    9. 13-9. Gathering System Statistics
    10. 13-10. Validating New Statistics
    11. 13-11. Forcing the Optimizer to Use an Index
    12. 13-12. Enabling Query Optimizer Features
    13. 13-13. Keeping the Database from Creating Histograms
    14. 13-14. Improving Performance When Not Using Bind Variables
    15. 13-15. Understanding Adaptive Cursor Sharing
    16. 13-16. Creating Statistics on Expressions
    17. 13-17. Creating Statistics for Related Columns
    18. 13-18. Automatically Creating Column Groups
    19. 13-19. Maintaining Statistics on Partitioned Tables
    20. 13-20. Concurrent Statistics Collection for Large Tables
    21. 13-21. Determining When Statistics Are Stale
    22. 13-22. Previewing Statistics Gathering Targets
  22. CHAPTER 14: Implementing Query Hints
    1. 14-1. Writing a Hint
    2. 14-2. Changing the Access Path
    3. 14-3. Changing the Join Order
    4. 14-4. Changing the Join Method
    5. 14-5. Changing the Optimizer Version
    6. 14-6. Choosing Between a Fast Response and Overall Optimization
    7. 14-7. Performing a Direct-Path Insert
    8. 14-8. Placing Hints in Views
    9. 14-9. Caching Query Results
    10. 14-10. Directing a Distributed Query to a Specific Database
    11. 14-11. Gathering Extended Query Execution Statistics
    12. 14-12. Enabling Query Rewrite
    13. 14-13. Improving Star Schema Query Performance
  23. CHAPTER 15: Executing SQL in Parallel
    1. 15-1. Enabling Parallelism for a Specific Query
    2. 15-2. Enabling Parallelism at Object Creation
    3. 15-3. Enabling Parallelism for an Existing Object
    4. 15-4. Implementing Parallel DML
    5. 15-5. Creating Tables in Parallel
    6. 15-6. Creating Indexes in Parallel
    7. 15-7. Rebuilding Indexes in Parallel
    8. 15-8. Moving Partitions in Parallel
    9. 15-9. Splitting Partitions in Parallel
    10. 15-10. Enabling Automatic Degree of Parallelism
    11. 15-11. Examining Parallel Explain Plans
    12. 15-12. Monitoring Parallel Operations
    13. 15-13. Finding Bottlenecks in Parallel Processes
    14. 15-14. Getting Detailed Information on Parallel Sessions
  24. Index