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