Table of Contents
- Title and Copyright Information
- Preface
-
Part I SQL Performance Fundamentals
-
1
Introduction to SQL Tuning
- About SQL Tuning
- Purpose of SQL Tuning
- Prerequisites for SQL Tuning
- Tasks and Tools for SQL Tuning
- About Automatic Error Mitigation
- 2 SQL Performance Methodology
-
1
Introduction to SQL Tuning
-
Part II Query Optimizer Fundamentals
- 3 SQL Processing
-
4
Query Optimizer Concepts
- Introduction to the Query Optimizer
- About Optimizer Components
- About Automatic Tuning Optimizer
- About Adaptive Query Optimization
- About Approximate Query Processing
- About SQL Plan Management
- About Quarantined SQL Plans
- About the Expression Statistics Store (ESS)
- 5 Query Transformations
-
Part III Query Execution Plans
-
6
Explaining and Displaying Execution Plans
- Introduction to Execution Plans
- Generating Plan Output Using the EXPLAIN PLAN Statement
-
Displaying Execution Plans
- About the Display of PLAN_TABLE Output
- Displaying Execution Plans: Basic Steps
- Displaying Adaptive Query Plans: Tutorial
-
Display Execution Plans: Examples
- Customizing PLAN_TABLE Output
- Displaying Parallel Execution Plans: Example
- Displaying Bitmap Index Plans: Example
- Displaying Result Cache Plans: Example
- Displaying Plans for Partitioned Objects: Example
- Comparing Execution Plans
- 7 PLAN_TABLE Reference
-
6
Explaining and Displaying Execution Plans
-
Part IV SQL Operators: Access Paths and Joins
-
8
Optimizer Access Paths
- Introduction to Access Paths
- Table Access Paths
- B-Tree Index Access Paths
- Bitmap Index Access Paths
- Table Cluster Access Paths
- 9 Joins
-
8
Optimizer Access Paths
-
Part V Optimizer Statistics
-
10
Optimizer Statistics Concepts
- Introduction to Optimizer Statistics
- About Optimizer Statistics Types
- How the Database Gathers Optimizer Statistics
- When the Database Gathers Optimizer Statistics
- 11 Histograms
-
12
Configuring Options for Optimizer Statistics Gathering
- About Optimizer Statistics Collection
- Setting Optimizer Statistics Preferences
- Configuring Options for Dynamic Statistics
- Dynamic Statistics for PL/SQL Functions
- Managing SQL Plan Directives
-
13
Gathering Optimizer Statistics
- Configuring Automatic Optimizer Statistics Collection
- Configuring High-Frequency Automatic Optimizer Statistics Collection
-
Gathering Optimizer Statistics Manually
- About Manual Statistics Collection with DBMS_STATS
- Guidelines for Gathering Optimizer Statistics Manually
- Determining When Optimizer Statistics Are Stale
- Gathering Schema and Table Statistics
- Gathering Statistics for Fixed Objects
- Gathering Statistics for Volatile Tables Using Dynamic Statistics
- Gathering Optimizer Statistics Concurrently
-
Gathering Incremental Statistics on Partitioned Objects
- Purpose of Incremental Statistics
- How DBMS_STATS Derives Global Statistics for Partitioned tables
- Gathering Statistics for a Partitioned Table: Basic Steps
- Maintaining Incremental Statistics for Partition Maintenance Operations
- Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics
- Gathering System Statistics Manually
- Running Statistics Gathering Functions in Reporting Mode
-
14
Managing Extended Statistics
- Managing Column Group Statistics
- Managing Expression Statistics
- 15 Controlling the Use of Optimizer Statistics
- 16 Managing Historical Optimizer Statistics
- 17 Importing and Exporting Optimizer Statistics
-
18
Analyzing Statistics Using Optimizer Statistics Advisor
- About Optimizer Statistics Advisor
-
Basic Tasks for Optimizer Statistics Advisor
- Creating an Optimizer Statistics Advisor Task
- Listing Optimizer Statistics Advisor Tasks
- Creating Filters for an Optimizer Advisor Task
- Executing an Optimizer Statistics Advisor Task
- Generating a Report for an Optimizer Statistics Advisor Task
- Implementing Optimizer Statistics Advisor Recommendations
-
10
Optimizer Statistics Concepts
-
Part VI Optimizer Controls
- 19 Influencing the Optimizer
- 20 Improving Real-World Performance Through Cursor Sharing
-
Part VII Monitoring and Tracing SQL
- 21 Gathering Diagnostic Data with SQL Test Case Builder
-
22
Monitoring Database Operations
- About Monitoring Database Operations
- Enabling and Disabling Real-Time Monitoring of Database Operations
- Defining a Composite Database Operation
- Generating and Accessing SQL Monitor Reports
- Monitoring Database Operations: Scenarios
- Real-Time SQL Monitoring Across Multiple PDBs
- SQL History Monitoring and Reporting
-
23
Performing Application Tracing
- Overview of End-to-End Application Tracing
- Enabling Statistics Gathering for End-to-End Tracing
- Enabling End-to-End Application Tracing
- Generating Output Files Using SQL Trace and TKPROF
- Guidelines for Interpreting TKPROF Output
- Application Tracing Utilities
- Views for Application Tracing
- Controls Over Application Tracing and Access to Trace Events
-
Part VIII Automatic SQL Tuning
- 24 Capturing Workloads in SQL Tuning Sets
-
25
Analyzing SQL with SQL Tuning Advisor
- About SQL Tuning Advisor
- Managing the Automatic SQL Tuning Task
- Running SQL Tuning Advisor On Demand
- The Automatic SQL Tuning Set
-
26
Optimizing Access Paths with SQL Access Advisor
- About SQL Access Advisor
- Using SQL Access Advisor: Basic Tasks
- Performing a SQL Access Advisor Quick Tune
-
Using SQL Access Advisor: Advanced Tasks
- Evaluating Existing Access Structures
- Updating SQL Access Advisor Task Attributes
- Creating and Using SQL Access Advisor Task Templates
- Terminating SQL Access Advisor Task Execution
- Deleting SQL Access Advisor Tasks
- Marking SQL Access Advisor Recommendations
- Modifying SQL Access Advisor Recommendations
- SQL Access Advisor Examples
- SQL Access Advisor Reference
-
Part IX SQL Management Objects
- 27 Managing SQL Profiles
- 28 Overview of SQL Plan Management
-
29
Managing SQL Plan Baselines
- About Managing SQL Plan Baselines
- Configuring the Capture and Use of SQL Plan Baselines
- Configuring SQL Plan Management
- Displaying Plans in a SQL Plan Baseline
- Loading SQL Plan Baselines
- Evolving SQL Plan Baselines Manually
- Adding SQL Plans to a Baseline
- Dropping SQL Plan Baselines
- Managing the SQL Management Base
- 30 Migrating Stored Outlines to SQL Plan Baselines
- Glossary
- Index