Table of Contents
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle Database SQL Tuning Guide
-
Part I SQL Performance Fundamentals
- 1 Introduction to SQL Tuning
- 2 SQL Performance Methodology
-
Part II Query Optimizer Fundamentals
- 3 SQL Processing
-
4
Query Optimizer Concepts
- 4.1 Introduction to the Query Optimizer
- 4.2 About Optimizer Components
- 4.3 About Automatic Tuning Optimizer
- 4.4 About Adaptive Query Optimization
- 4.5 About Approximate Query Processing
- 4.6 About SQL Plan Management
- 4.7 About Quarantined SQL Plans
- 4.8 About the Expression Statistics Store (ESS)
- 5 Query Transformations
-
Part III Query Execution Plans
-
6
Explaining and Displaying Execution Plans
- 6.1 Introduction to Execution Plans
- 6.2 Generating Plan Output Using the EXPLAIN PLAN Statement
-
6.3
Displaying Execution Plans
- 6.3.1 About the Display of PLAN_TABLE Output
- 6.3.2 Displaying Execution Plans: Basic Steps
- 6.3.3 Displaying Adaptive Query Plans: Tutorial
-
6.3.4
Display Execution Plans: Examples
- 6.3.4.1 Customizing PLAN_TABLE Output
- 6.3.4.2 Displaying Parallel Execution Plans: Example
- 6.3.4.3 Displaying Bitmap Index Plans: Example
- 6.3.4.4 Displaying Result Cache Plans: Example
-
6.3.4.5
Displaying Plans for Partitioned Objects: Example
- 6.3.4.5.1 Displaying Range and Hash Partitioning with EXPLAIN PLAN: Examples
- 6.3.4.5.2 Pruning Information with Composite Partitioned Objects: Examples
- 6.3.4.5.3 Examples of Partial Partition-Wise Joins
- 6.3.4.5.4 Example of Full Partition-Wise Join
- 6.3.4.5.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
- 6.3.4.5.6 Example of Domain Indexes and EXPLAIN PLAN
- 6.4 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
- 8.1 Introduction to Access Paths
- 8.2 Table Access Paths
- 8.3 B-Tree Index Access Paths
- 8.4 Bitmap Index Access Paths
- 8.5 Table Cluster Access Paths
-
9
Joins
- 9.1 About Joins
- 9.2 Join Methods
- 9.3 Join Types
- 9.4 Join Optimizations
-
8
Optimizer Access Paths
-
Part V Optimizer Statistics
-
10
Optimizer Statistics Concepts
- 10.1 Introduction to Optimizer Statistics
- 10.2 About Optimizer Statistics Types
-
10.3
How the Database Gathers Optimizer Statistics
- 10.3.1 DBMS_STATS Package
- 10.3.2 Supplemental Dynamic Statistics
-
10.3.3
Online Statistics Gathering
-
10.3.3.1
Online Statistics Gathering for Bulk Loads
- 10.3.3.1.1 Purpose of Online Statistics Gathering for Bulk Loads
- 10.3.3.1.2 Global Statistics During Inserts into Partitioned Tables
- 10.3.3.1.3 Histogram Creation After Bulk Loads
- 10.3.3.1.4 Restrictions for Online Statistics Gathering for Bulk Loads
- 10.3.3.1.5 User Interface for Online Statistics Gathering for Bulk Loads
- 10.3.3.2 Online Statistics Gathering for Partition Maintenance Operations
- 10.3.3.3 Real-Time Statistics
-
10.3.3.1
Online Statistics Gathering for Bulk Loads
- 10.4 When the Database Gathers Optimizer Statistics
- 11 Histograms
-
12
Configuring Options for Optimizer Statistics Gathering
- 12.1 About Optimizer Statistics Collection
- 12.2 Setting Optimizer Statistics Preferences
- 12.3 Configuring Options for Dynamic Statistics
- 12.4 Managing SQL Plan Directives
-
13
Gathering Optimizer Statistics
- 13.1 Configuring Automatic Optimizer Statistics Collection
- 13.2 Configuring High-Frequency Automatic Optimizer Statistics Collection
-
13.3
Gathering Optimizer Statistics Manually
- 13.3.1 About Manual Statistics Collection with DBMS_STATS
- 13.3.2 Guidelines for Gathering Optimizer Statistics Manually
- 13.3.3 Determining When Optimizer Statistics Are Stale
- 13.3.4 Gathering Schema and Table Statistics
- 13.3.5 Gathering Statistics for Fixed Objects
- 13.3.6 Gathering Statistics for Volatile Tables Using Dynamic Statistics
- 13.3.7 Gathering Optimizer Statistics Concurrently
-
13.3.8
Gathering Incremental Statistics on Partitioned Objects
- 13.3.8.1 Purpose of Incremental Statistics
- 13.3.8.2 How DBMS_STATS Derives Global Statistics for Partitioned tables
- 13.3.8.3 Gathering Statistics for a Partitioned Table: Basic Steps
- 13.3.8.4 Maintaining Incremental Statistics for Partition Maintenance Operations
- 13.3.8.5 Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics
- 13.4 Gathering System Statistics Manually
- 13.5 Running Statistics Gathering Functions in Reporting Mode
-
14
Managing Extended Statistics
-
14.1
Managing Column Group Statistics
- 14.1.1 About Statistics on Column Groups
- 14.1.2 Detecting Useful Column Groups for a Specific Workload
- 14.1.3 Creating Column Groups Detected During Workload Monitoring
- 14.1.4 Creating and Gathering Statistics on Column Groups Manually
- 14.1.5 Displaying Column Group Information
- 14.1.6 Dropping a Column Group
- 14.2 Managing Expression Statistics
-
14.1
Managing Column Group 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
- 18.1 About Optimizer Statistics Advisor
-
18.2
Basic Tasks for Optimizer Statistics Advisor
- 18.2.1 Creating an Optimizer Statistics Advisor Task
- 18.2.2 Listing Optimizer Statistics Advisor Tasks
- 18.2.3 Creating Filters for an Optimizer Advisor Task
- 18.2.4 Executing an Optimizer Statistics Advisor Task
- 18.2.5 Generating a Report for an Optimizer Statistics Advisor Task
- 18.2.6 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
- 20.1 Overview of Cursor Sharing
- 20.2 CURSOR_SHARING and Bind Variable Substitution
- 20.3 Adaptive Cursor Sharing
- 20.4 Real-World Performance Guidelines for Cursor Sharing
-
Part VII Monitoring and Tracing SQL
-
21
Monitoring Database Operations
-
21.1
About Monitoring Database Operations
- 21.1.1 About Database Operations
- 21.1.2 Purpose of Monitoring Database Operations
- 21.1.3 How Database Monitoring Works
- 21.1.4 User Interfaces for Database Operations Monitoring
- 21.1.5 Basic Tasks in Database Operations Monitoring
- 21.2 Enabling and Disabling Monitoring of Database Operations
- 21.3 Defining a Composite Database Operation
- 21.4 Generating and Accessing SQL Monitor Reports
- 21.5 Monitoring Database Operations: Scenarios
-
21.1
About Monitoring Database Operations
-
22
Performing Application Tracing
- 22.1 Overview of End-to-End Application Tracing
- 22.2 Enabling Statistics Gathering for End-to-End Tracing
- 22.3 Enabling End-to-End Application Tracing
- 22.4 Generating Output Files Using SQL Trace and TKPROF
- 22.5 Guidelines for Interpreting TKPROF Output
- 22.1 Application Tracing Utilities
- 22.1 Views for Application Tracing
-
21
Monitoring Database Operations
-
Part VIII Automatic SQL Tuning
- 23 Managing SQL Tuning Sets
-
24
Analyzing SQL with SQL Tuning Advisor
- 24.1 About SQL Tuning Advisor
- 24.2 Managing the Automatic SQL Tuning Task
- 24.3 Running SQL Tuning Advisor On Demand
-
25
Optimizing Access Paths with SQL Access Advisor
- 25.1 About SQL Access Advisor
-
25.2
Using SQL Access Advisor: Basic Tasks
- 25.2.1 Creating a SQL Tuning Set as Input for SQL Access Advisor
- 25.2.2 Populating a SQL Tuning Set with a User-Defined Workload
- 25.2.3 Creating and Configuring a SQL Access Advisor Task
- 25.2.4 Executing a SQL Access Advisor Task
- 25.2.5 Viewing SQL Access Advisor Task Results
- 25.2.6 Generating and Executing a Task Script
- 25.3 Performing a SQL Access Advisor Quick Tune
-
25.4
Using SQL Access Advisor: Advanced Tasks
- 25.4.1 Evaluating Existing Access Structures
- 25.4.2 Updating SQL Access Advisor Task Attributes
- 25.4.3 Creating and Using SQL Access Advisor Task Templates
- 25.4.4 Terminating SQL Access Advisor Task Execution
- 25.4.5 Deleting SQL Access Advisor Tasks
- 25.4.6 Marking SQL Access Advisor Recommendations
- 25.4.7 Modifying SQL Access Advisor Recommendations
- 25.5 SQL Access Advisor Examples
- 25.6 SQL Access Advisor Reference
-
Part IX SQL Management Objects
- 26 Managing SQL Profiles
- 27 Overview of SQL Plan Management
-
28
Managing SQL Plan Baselines
- 28.1 About Managing SQL Plan Baselines
-
28.2
Configuring SQL Plan Management
- 28.2.1 Configuring the Capture and Use of SQL Plan Baselines
- 28.2.2 Managing the SPM Evolve Advisor Task
- 28.3 Displaying Plans in a SQL Plan Baseline
- 28.4 Loading SQL Plan Baselines
- 28.5 Evolving SQL Plan Baselines Manually
- 28.6 Dropping SQL Plan Baselines
- 28.7 Managing the SQL Management Base
- 29 Migrating Stored Outlines to SQL Plan Baselines
- Glossary
- Index