Home Page Icon
Home Page
Table of Contents for
Title
Close
Title
by Bill Padfield, Darl Kuhn, Sam R. Alapati
Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach
Title
Dedication
Contents at a Glance
Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Personal Acknowledgments
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. Avoiding Extent Allocation Delays When Creating Tables
1-6. Maximizing Data Loading Speeds
1-7. Efficiently Removing Table Data
1-8. Displaying Automated Segment Advisor Advice
1-9. Manually Generating Segment Advisor Advice
1-10. Automatically E-mailing Segment Advisor Output
1-11. Rebuilding Rows Spanning Multiple Blocks
1-12. Freeing Unused Table Space
1-13. Compressing Data for Direct Path Loading
1-14. Compressing Data for All DML
1-15. Compressing Data at the Column Level
1-16. Monitoring Table Usage
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 Index
2-4. Creating a Unique Index
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. Avoiding Concentrated I/O for Index
2-11. Adding an Index Without Impacting Existing Applications
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
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 Due to Locking
5-20. Minimizing Latch Contention
5-21. Managing Locks from Oracle Enterprise Manager
5-22. Analyzing Waits from Oracle Enterprise Manager
Chapter 6: Analyzing Operating System Performance
6-1. Detecting Disk Space Issues
6-2. Identifying System Bottlenecks (vmstat)
6-3. Identifying System Bottlenecks (Solaris)
6-4. Identifying Top Server-Consuming Resources (top)
6-5. Identifying CPU and Memory Bottlenecks (ps)
6-6. Identifying I/O Bottlenecks
6-7. Identifying Network-Intensive Processes
6-8. Troubleshooting Database Network Connectivity
6-9. Mapping a Resource-Intensive Process to a Database Process
6-10. 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 Finding Missing Rows
8-8. Comparing Two Tables to Finding 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
9-10. 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 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. Viewing Resource-Intensive SQL in Memory
11-10. Populating SQL Tuning Set from High-Resource SQL in AWR
11-11. Populating a SQL Tuning Set from Resource-Consuming SQL in Memory
11-12. Populating 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. Manually Running SQL Tuning Advisor
11-18. Getting 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. Automatically Accepting SQL Profiles
12-3. Displaying SQL Profile Information
12-4. Disabling a SQL Profile
12-5. Dropping a SQL Profile
12-6. Moving a SQL Profile
12-7. Automatically Adding Plan Baselines
12-8. Creating a Plan Baseline for One SQL Statement
12-9. Creating Plan Baselines for SQL Contained in SQL Tuning Set
12-10. Altering a Plan Baseline
12-11. Determining If Plan Baselines Exist
12-12. Displaying Plan Baseline Execution Plans
12-13. Adding a New Plan to Plan Baseline (Evolving)
12-14. Disabling Plan Baselines
12-15. Removing Plan Baseline Information
12-16. 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
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
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset