Contents
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-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-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-4. Understanding Wait Class Events
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
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-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-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-5. Examining a Raw SQL Trace File
10-6. Analyzing Oracle Trace Files
10-7. Formatting Trace Files with TKPROF
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-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-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
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-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-6. Handling Missing 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-2. Changing the Access Path
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-10. Directing a Distributed Query to a Specific Database
14-11. Gathering Extended Query Execution Statistics
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