Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

by Sam R. Alapati, Darl Kuhn, Bill Padfield
Released December 2013
Publisher(s): Apress
ISBN: 9781430261872

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

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

    Product information

    • Title: Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach
    • Author(s): Sam R. Alapati, Darl Kuhn, Bill Padfield
    • Release date: December 2013
    • Publisher(s): Apress
    • ISBN: 9781430261872