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 the Expression Statistics Store (ESS)
- 5 Query Transformations
-
Part III Query Execution Plans
-
6
Generating and Displaying Execution Plans
- 6.1 Introduction to Execution Plans
-
6.2
About Plan Generation and Display
- 6.2.1 About the Plan Explanation
- 6.2.2 Why Execution Plans Change
- 6.2.3 Guideline for Minimizing Throw-Away
- 6.2.4 Guidelines for Evaluating Execution Plans Using EXPLAIN PLAN
- 6.2.5 Guidelines for Evaluating Plans Using the V$SQL_PLAN Views
- 6.2.6 EXPLAIN PLAN Restrictions
- 6.2.7 Guidelines for Creating PLAN_TABLE
- 6.3 Generating Execution Plans
- 6.4 Displaying PLAN_TABLE Output
-
7
Reading Execution Plans
- 7.1 Reading Execution Plans: Basic
-
7.2
Reading Execution Plans: Advanced
- 7.2.1 Reading Adaptive Query Plans
- 7.2.2 Viewing Parallel Execution with EXPLAIN PLAN
- 7.2.3 Viewing Bitmap Indexes with EXPLAIN PLAN
- 7.2.4 Viewing Result Cache with EXPLAIN PLAN
-
7.2.5
Viewing Partitioned Objects with EXPLAIN PLAN
- 7.2.5.1 Displaying Range and Hash Partitioning with EXPLAIN PLAN: Examples
- 7.2.5.2 Pruning Information with Composite Partitioned Objects: Examples
- 7.2.5.3 Examples of Partial Partition-Wise Joins
- 7.2.5.4 Example of Full Partition-Wise Join
- 7.2.5.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
- 7.2.5.6 Example of Domain Indexes and EXPLAIN PLAN
- 7.2.6 PLAN_TABLE Columns
- 7.3 Execution Plan Reference
-
6
Generating 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 for Bulk Loads
- 10.3.3.1 Purpose of Online Statistics Gathering for Bulk Loads
- 10.3.3.2 Global Statistics During Inserts into Empty Partitioned Tables
- 10.3.3.3 Index Statistics and Histograms During Bulk Loads
- 10.3.3.4 Restrictions for Online Statistics Gathering for Bulk Loads
- 10.3.3.5 Hints for 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
Gathering Optimizer Statistics Manually
- 13.2.1 About Manual Statistics Collection with DBMS_STATS
- 13.2.2 Guidelines for Gathering Optimizer Statistics Manually
- 13.2.3 Determining When Optimizer Statistics Are Stale
- 13.2.4 Gathering Schema and Table Statistics
- 13.2.5 Gathering Statistics for Fixed Objects
- 13.2.6 Gathering Statistics for Volatile Tables Using Dynamic Statistics
- 13.2.7 Gathering Optimizer Statistics Concurrently
-
13.2.8
Gathering Incremental Statistics on Partitioned Objects
- 13.2.8.1 Purpose of Incremental Statistics
- 13.2.8.2 How DBMS_STATS Derives Global Statistics for Partitioned tables
- 13.2.8.3 Gathering Statistics for a Partitioned Table: Basic Steps
- 13.2.8.4 Maintaining Incremental Statistics for Partition Maintenance Operations
- 13.2.8.5 Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics
- 13.3 Gathering System Statistics Manually
- 13.4 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 Transporting 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.2 Enabling and Disabling Monitoring of Database Operations
- 21.3 Creating a Database Operation
- 21.4 Monitoring SQL Executions Using Cloud Control
- 22 Gathering Diagnostic Data with SQL Test Case Builder
-
23
Performing Application Tracing
- 23.1 Overview of End-to-End Application Tracing
- 23.2 Enabling Statistics Gathering for End-to-End Tracing
- 23.3 Enabling End-to-End Application Tracing
- 23.4 Generating Output Files Using SQL Trace and TKPROF
- 23.5 Guidelines for Interpreting TKPROF Output
- 23.1 Application Tracing Utilities
- 23.1 Views for Application Tracing
-
21
Monitoring Database Operations
-
Part VIII Automatic SQL Tuning
- 24 Managing SQL Tuning Sets
-
25
Analyzing SQL with SQL Tuning Advisor
- 25.1 About SQL Tuning Advisor
- 25.2 Managing the Automatic SQL Tuning Task
- 25.3 Running SQL Tuning Advisor On Demand
-
26
Optimizing Access Paths with SQL Access Advisor
- 26.1 About SQL Access Advisor
-
26.2
Optimizing Access Paths with SQL Access Advisor: Basic Tasks
- 26.2.1 Creating a SQL Tuning Set as Input for SQL Access Advisor
- 26.2.2 Populating a SQL Tuning Set with a User-Defined Workload
- 26.2.3 Creating and Configuring a SQL Access Advisor Task
- 26.2.4 Executing a SQL Access Advisor Task
- 26.2.5 Viewing SQL Access Advisor Task Results
- 26.2.6 Generating and Executing a Task Script
- 26.3 Performing a SQL Access Advisor Quick Tune
-
26.4
Using SQL Access Advisor: Advanced Tasks
- 26.4.1 Evaluating Existing Access Structures
- 26.4.2 Updating SQL Access Advisor Task Attributes
- 26.4.3 Creating and Using SQL Access Advisor Task Templates
- 26.4.4 Terminating SQL Access Advisor Task Execution
- 26.4.5 Deleting SQL Access Advisor Tasks
- 26.4.6 Marking SQL Access Advisor Recommendations
- 26.4.7 Modifying SQL Access Advisor Recommendations
- 26.5 SQL Access Advisor Examples
- 26.6 SQL Access Advisor Reference
-
Part IX SQL Controls: Profiles and Plan Baselines
- 27 Managing SQL Profiles
- 28 Overview of SQL Plan Management
-
29
Managing SQL Plan Baselines
- 29.1 About Managing SQL Plan Baselines
- 29.2 Configuring SQL Plan Management
- 29.3 Displaying Plans in a SQL Plan Baseline
- 29.4 Loading SQL Plan Baselines
- 29.5 Evolving SQL Plan Baselines Manually
- 29.6 Dropping SQL Plan Baselines
- 29.7 Managing the SQL Management Base
- 30 Migrating Stored Outlines to SQL Plan Baselines
-
A
Guidelines for Indexes and Table Clusters
-
A.1
Guidelines for Tuning Index Performance
- A.1.1 Guidelines for Tuning the Logical Structure
- A.1.2 Guidelines for Choosing Columns and Expressions to Index
- A.1.3 Guidelines for Choosing Composite Indexes
- A.1.4 Guidelines for Writing SQL Statements That Use Indexes
- A.1.5 Guidelines for Writing SQL Statements That Avoid Using Indexes
- A.1.6 Guidelines for Re-Creating Indexes
- A.1.7 Guidelines for Compacting Indexes
- A.1.8 Guidelines for Using Nonunique Indexes to Enforce Uniqueness
- A.1.9 Guidelines for Using Enabled Novalidated Constraints
- A.2 Guidelines for Using Function-Based Indexes for Performance
- A.3 Guidelines for Using Partitioned Indexes for Performance
- A.4 Guidelines for Using Index-Organized Tables for Performance
- A.5 Guidelines for Using Bitmap Indexes for Performance
- A.6 Guidelines for Using Bitmap Join Indexes for Performance
- A.7 Guidelines for Using Domain Indexes for Performance
- A.8 Guidelines for Using Table Clusters
- A.9 Guidelines for Using Hash Clusters for Performance
-
A.1
Guidelines for Tuning Index Performance
- Glossary
- Index