Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

by Sam R. Alapati, Darl Kuhn, Bill Padfield
Released August 2011
Publisher(s): Apress
ISBN: 9781430236627

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 11g Performance Tuning Recipes delivers.

Oracle Database 11g 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

  • Written by a team of experienced database administrators successful in their careers

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 11g 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
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. About the Technical Reviewer
  7. Acknowledgments
    1. Personal Acknowledgments
  8. 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. Avoiding Extent Allocation Delays When Creating Tables
    6. 1-6. Maximizing Data Loading Speeds
    7. 1-7. Efficiently Removing Table Data
    8. 1-8. Displaying Automated Segment Advisor Advice
    9. 1-9. Manually Generating Segment Advisor Advice
    10. 1-10. Automatically E-mailing Segment Advisor Output
    11. 1-11. Rebuilding Rows Spanning Multiple Blocks
    12. 1-12. Freeing Unused Table Space
    13. 1-13. Compressing Data for Direct Path Loading
    14. 1-14. Compressing Data for All DML
    15. 1-15. Compressing Data at the Column Level
    16. 1-16. Monitoring Table Usage
  9. 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 Index
    4. 2-4. Creating a Unique Index
    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. Avoiding Concentrated I/O for Index
    11. 2-11. Adding an Index Without Impacting Existing Applications
    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
  10. 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
  11. 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
  12. 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 Due to Locking
    20. 5-20. Minimizing Latch Contention
    21. 5-21. Managing Locks from Oracle Enterprise Manager
    22. 5-22. Analyzing Waits from Oracle Enterprise Manager
  13. Chapter 6: Analyzing Operating System Performance
    1. 6-1. Detecting Disk Space Issues
    2. 6-2. Identifying System Bottlenecks (vmstat)
    3. 6-3. Identifying System Bottlenecks (Solaris)
    4. 6-4. Identifying Top Server-Consuming Resources (top)
    5. 6-5. Identifying CPU and Memory Bottlenecks (ps)
    6. 6-6. Identifying I/O Bottlenecks
    7. 6-7. Identifying Network-Intensive Processes
    8. 6-8. Troubleshooting Database Network Connectivity
    9. 6-9. Mapping a Resource-Intensive Process to a Database Process
    10. 6-10. Terminating a Resource-Intensive Process
  14. 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
  15. 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 Finding Missing Rows
    8. 8-8. Comparing Two Tables to Finding 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
  16. 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. 9-10. Comparing SQL Performance After a System Change
  17. 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
  18. Chapter 11: Automated SQL Tuning
    1. 11-1. Displaying Automatic SQL Tuning Job Details
    2. 11-2. Displaying Automatic SQL Tuning 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. Viewing Resource-Intensive SQL in Memory
    10. 11-10. Populating SQL Tuning Set from High-Resource SQL in AWR
    11. 11-11. Populating a SQL Tuning Set from Resource-Consuming SQL in Memory
    12. 11-12. Populating 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. Manually Running SQL Tuning Advisor
    18. 11-18. Getting SQL Tuning Advice from the Automatic Database Diagnostic Monitor
  19. Chapter 12: Execution Plan Optimization and Consistency
    1. 12-1. Creating and Accepting a SQL Profile
    2. 12-2. Automatically Accepting SQL Profiles
    3. 12-3. Displaying SQL Profile Information
    4. 12-4. Disabling a SQL Profile
    5. 12-5. Dropping a SQL Profile
    6. 12-6. Moving a SQL Profile
    7. 12-7. Automatically Adding Plan Baselines
    8. 12-8. Creating a Plan Baseline for One SQL Statement
    9. 12-9. Creating Plan Baselines for SQL Contained in SQL Tuning Set
    10. 12-10. Altering a Plan Baseline
    11. 12-11. Determining If Plan Baselines Exist
    12. 12-12. Displaying Plan Baseline Execution Plans
    13. 12-13. Adding a New Plan to Plan Baseline (Evolving)
    14. 12-14. Disabling Plan Baselines
    15. 12-15. Removing Plan Baseline Information
    16. 12-16. Transporting Plan Baselines
  20. 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. 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
  22. 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
  23. Index

Product information

  • Title: Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach
  • Author(s): Sam R. Alapati, Darl Kuhn, Bill Padfield
  • Release date: August 2011
  • Publisher(s): Apress
  • ISBN: 9781430236627