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