1/40
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database SQL Tuning Guide
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
Part I SQL Performance Fundamentals
1
Introduction to SQL Tuning
1.1
About SQL Tuning
1.2
Purpose of SQL Tuning
1.3
Prerequisites for SQL Tuning
1.4
Tasks and Tools for SQL Tuning
1.4.1
SQL Tuning Tasks
1.4.2
SQL Tuning Tools
1.4.3
User Interfaces to SQL Tuning Tools
2
SQL Performance Methodology
2.1
Designing Your Application
2.1.1
Data Modeling
2.1.2
Writing Efficient Applications
2.2
Deploying Your Application
2.2.1
Deploying in a Test Environment
2.2.2
Rollout Strategies
Part II Query Optimizer Fundamentals
3
SQL Processing
3.1
About SQL Processing
3.1.1
SQL Parsing
3.1.2
SQL Optimization
3.1.3
SQL Row Source Generation
3.1.4
SQL Execution
3.2
How Oracle Database Processes DML
3.2.1
Read Consistency
3.2.2
Data Changes
3.3
How Oracle Database Processes DDL
4
Query Optimizer Concepts
4.1
Introduction to the Query Optimizer
4.1.1
Purpose of the Query Optimizer
4.1.2
Cost-Based Optimization
4.1.3
Execution Plans
4.2
About Optimizer Components
4.2.1
Query Transformer
4.2.2
Estimator
4.2.3
Plan Generator
4.3
About Automatic Tuning Optimizer
4.4
About Adaptive Query Optimization
4.4.1
Adaptive Plans
4.4.2
Adaptive Statistics
4.5
About Optimizer Management of SQL Plan Baselines
5
Query Transformations
5.1
OR Expansion
5.2
View Merging
5.2.1
Query Blocks in View Merging
5.2.2
Simple View Merging
5.2.3
Complex View Merging
5.3
Predicate Pushing
5.4
Subquery Unnesting
5.5
Query Rewrite with Materialized Views
5.6
Star Transformation
5.6.1
About Star Schemas
5.6.2
Purpose of Star Transformations
5.6.3
How Star Transformation Works
5.6.4
Controls for Star Transformation
5.6.5
Star Transformation: Scenario
5.6.6
Temporary Table Transformation: Scenario
5.7
In-Memory Aggregation
5.7.1
Purpose of In-Memory Aggregation
5.7.2
How In-Memory Aggregation Works
5.7.3
Controls for In-Memory Aggregation
5.7.4
In-Memory Aggregation: Scenario
5.7.5
In-Memory Aggregation: Example
5.8
Table Expansion
5.8.1
Purpose of Table Expansion
5.8.2
How Table Expansion Works
5.8.3
Table Expansion: Scenario
5.8.4
Table Expansion and Star Transformation: Scenario
5.9
Join Factorization
5.9.1
Purpose of Join Factorization
5.9.2
How Join Factorization Works
5.9.3
Factorization and Join Orders: Scenario
5.9.4
Factorization of Outer Joins: Scenario
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
Minimizing Throw-Away
6.2.4
Looking Beyond Execution Plans
6.2.5
EXPLAIN PLAN Restrictions
6.2.6
The PLAN_TABLE Output Table
6.3
Generating Execution Plans
6.3.1
Identifying Statements for EXPLAIN PLAN
6.3.2
Specifying Different Tables for EXPLAIN PLAN
6.4
Displaying PLAN_TABLE Output
6.4.1
Displaying an Execution Plan: Example
6.4.2
Customizing PLAN_TABLE Output
7
Reading Execution Plans
7.1
Reading Execution Plans: Basic
7.2
Reading Execution Plans: Advanced
7.2.1
Reading Adaptive 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.6
PLAN_TABLE Columns
7.3
Execution Plan Reference
7.3.1
Execution Plan Views
7.3.2
PLAN_TABLE Columns
7.3.3
DBMS_XPLAN Program Units
Part IV SQL Operators
8
Optimizer Access Paths
8.1
Introduction to Access Paths
8.2
Table Access Paths
8.2.1
About Heap-Organized Table Access
8.2.2
Full Table Scans
8.2.3
Table Access by Rowid
8.2.4
Sample Table Scans
8.2.5
In-Memory Table Scans
8.3
B-Tree Index Access Paths
8.3.1
About B-Tree Index Access
8.3.2
Index Unique Scans
8.3.3
Index Range Scans
8.3.4
Index Full Scans
8.3.5
Index Fast Full Scans
8.3.6
Index Skip Scans
8.3.7
Index Join Scans
8.4
Bitmap Index Access Paths
8.4.1
About Bitmap Index Access
8.4.2
Bitmap Conversion to Rowid
8.4.3
Bitmap Index Single Value
8.4.4
Bitmap Index Range Scans
8.4.5
Bitmap Merge
8.5
Table Cluster Access Paths
8.5.1
Cluster Scans
8.5.2
Hash Scans
9
Joins
9.1
About Joins
9.1.1
Join Trees
9.1.2
How the Optimizer Executes Join Statements
9.1.3
How the Optimizer Chooses Execution Plans for Joins
9.2
Join Methods
9.2.1
Nested Loops Joins
9.2.2
Hash Joins
9.2.3
Sort Merge Joins
9.2.4
Cartesian Joins
9.3
Join Types
9.3.1
Inner Joins
9.3.2
Outer Joins
9.3.3
Semijoins
9.3.4
Antijoins
9.4
Join Optimizations
9.4.1
Bloom Filters
9.4.2
Partition-Wise Joins
Part V Optimizer Statistics
10
Optimizer Statistics Concepts
10.1
Introduction to Optimizer Statistics
10.2
About Optimizer Statistics Types
10.2.1
Table Statistics
10.2.2
Column Statistics
10.2.3
Index Statistics
10.2.4
Session-Specific Statistics for Global Temporary Tables
10.2.5
System Statistics
10.2.6
User-Defined Optimizer Statistics
10.3
How the Database Gathers Optimizer Statistics
10.3.1
DBMS_STATS Package
10.3.2
Dynamic Statistics
10.3.3
Online Statistics Gathering for Bulk Loads
10.4
When the Database Gathers Optimizer Statistics
10.4.1
SQL Plan Directives
10.4.2
When the Database Samples Data
10.4.3
How the Database Samples Data
11
Histograms
11.1
Purpose of Histograms
11.2
When Oracle Database Creates Histograms
11.3
Cardinality Algorithms When Using Histograms
11.3.1
Endpoint Numbers and Values
11.3.2
Popular and Nonpopular Values
11.3.3
Bucket Compression
11.4
Frequency Histograms
11.4.1
Criteria For Frequency Histograms
11.4.2
Generating a Frequency Histogram
11.4.3
Generating a Top Frequency Histogram
11.5
Height-Balanced Histograms (Legacy)
11.5.1
Criteria for Height-Balanced Histograms
11.5.2
Generating a Height-Balanced Histogram
11.6
Hybrid Histograms
11.6.1
How Endpoint Repeat Counts Work
11.6.2
Criteria for Hybrid Histograms
11.6.3
Generating a Hybrid Histogram
12
Managing Optimizer Statistics: Basic Topics
12.1
About Optimizer Statistics Collection
12.1.1
Purpose of Optimizer Statistics Collection
12.1.2
User Interfaces for Optimizer Statistics Management
12.2
Controlling Automatic Optimizer Statistics Collection
12.2.1
Controlling Automatic Optimizer Statistics Collection Using Cloud Control
12.2.2
Controlling Automatic Optimizer Statistics Collection from the Command Line
12.3
Setting Optimizer Statistics Preferences
12.3.1
About Optimizer Statistics Preferences
12.3.2
Setting Global Optimizer Statistics Preferences Using Cloud Control
12.3.3
Setting Object-Level Optimizer Statistics Preferences Using Cloud Control
12.3.4
Setting Optimizer Statistics Preferences from the Command Line
12.4
Gathering Optimizer Statistics Manually
12.4.1
About Manual Statistics Collection with DBMS_STATS
12.4.2
Guidelines for Gathering Optimizer Statistics Manually
12.4.3
Determining When Optimizer Statistics Are Stale
12.4.4
Gathering Schema and Table Statistics
12.4.5
Gathering Statistics for Fixed Objects
12.4.6
Gathering Statistics for Volatile Tables Using Dynamic Statistics
12.4.7
Gathering Optimizer Statistics Concurrently
12.4.8
Gathering Incremental Statistics on Partitioned Objects
12.5
Gathering System Statistics Manually
12.5.1
About Gathering System Statistics with DBMS_STATS
12.5.2
Guidelines for Gathering System Statistics
12.5.3
Gathering Workload Statistics
12.5.4
Gathering Noworkload Statistics
12.5.5
Deleting System Statistics
13
Managing Optimizer Statistics: Advanced Topics
13.1
Controlling Dynamic Statistics
13.1.1
About Dynamic Statistics Levels
13.1.2
Setting Dynamic Statistics Levels Manually
13.1.3
Disabling Dynamic Statistics
13.2
Publishing Pending Optimizer Statistics
13.2.1
User Interfaces for Publishing Optimizer Statistics
13.2.2
Managing Published and Pending Statistics
13.3
Managing Extended Statistics
13.3.1
Managing Column Group Statistics
13.3.2
Managing Expression Statistics
13.4
Locking and Unlocking Optimizer Statistics
13.4.1
Locking Statistics
13.4.2
Unlocking Statistics
13.5
Restoring Optimizer Statistics
13.5.1
Guidelines for Restoring Optimizer Statistics
13.5.2
Restrictions for Restoring Optimizer Statistics
13.5.3
Restoring Optimizer Statistics
13.6
Managing Optimizer Statistics Retention
13.6.1
Obtaining Optimizer Statistics History
13.6.2
Changing the Optimizer Statistics Retention Period
13.6.3
Purging Optimizer Statistics
13.7
Importing and Exporting Optimizer Statistics
13.7.1
About Transporting Optimizer Statistics
13.7.2
Transporting Optimizer Statistics to a Test Database
13.8
Running Statistics Gathering Functions in Reporting Mode
13.9
Reporting on Past Statistics Gathering Operations
13.10
Managing SQL Plan Directives
Part VI Optimizer Controls
14
Influencing the Optimizer
14.1
About Influencing the Optimizer
14.2
Influencing the Optimizer with Initialization Parameters
14.2.1
About Optimizer Initialization Parameters
14.2.2
Enabling Optimizer Features
14.2.3
Choosing an Optimizer Goal
14.2.4
Controlling Adaptive Optimization
14.3
Influencing the Optimizer with Hints
14.3.1
About Optimizer Hints
14.3.2
Guidelines for Join Order Hints
15
Controlling Cursor Sharing
15.1
About Bind Variables and Cursors
15.1.1
Bind Variable Peeking
15.1.2
SQL Sharing Criteria
15.1.3
Adaptive Cursor Sharing
15.1.4
Bind-Related Performance Views
15.2
Designing Applications for Cursor Sharing
15.3
Sharing Cursors for Existing Applications
15.3.1
How Similar Statements Can Share SQL Areas
15.3.2
When to Set CURSOR_SHARING to FORCE
Part VII Monitoring and Tracing SQL
16
Monitoring Database Operations
16.1
About Monitoring Database Operations
16.1.1
Purpose of Monitoring Database Operations
16.1.2
Database Operation Monitoring Concepts
16.1.3
User Interfaces for Database Operations Monitoring
16.1.4
Basic Tasks in Database Operations Monitoring
16.2
Enabling and Disabling Monitoring of Database Operations
16.2.1
Enabling Monitoring of Database Operations at the System Level
16.2.2
Enabling and Disabling Monitoring of Database Operations at the Statement Level
16.3
Creating a Database Operation
16.4
Reporting on Database Operations Using SQL Monitor
17
Gathering Diagnostic Data with SQL Test Case Builder
17.1
Purpose of SQL Test Case Builder
17.2
Concepts for SQL Test Case Builder
17.2.1
SQL Incidents
17.2.2
What SQL Test Case Builder Captures
17.2.3
Output of SQL Test Case Builder
17.3
User Interfaces for SQL Test Case Builder
17.3.1
Graphical Interface for SQL Test Case Builder
17.3.2
Command-Line Interface for SQL Test Case Builder
17.4
Running SQL Test Case Builder
18
Performing Application Tracing
18.1
Overview of End-to-End Application Tracing
18.1.1
Purpose of End-to-End Application Tracing
18.1.2
User Interfaces for End-to-End Application Tracing
18.2
Enabling Statistics Gathering for End-to-End Tracing
18.2.1
Enabling Statistics Gathering for a Client ID
18.2.2
Enabling Statistics Gathering for a Service, Module, and Action
18.3
Enabling End-to-End Application Tracing
18.3.1
Enabling Tracing for a Client Identifier
18.3.2
Enabling Tracing for a Service, Module, and Action
18.3.3
Enabling Tracing for a Session
18.3.4
Enabling Tracing for the Instance or Database
18.4
Generating Output Files Using SQL Trace and TKPROF
18.4.1
Step 1: Setting Initialization Parameters for Trace File Management
18.4.2
Step 2: Enabling the SQL Trace Facility
18.4.3
Step 3: Generating Output Files with TKPROF
18.4.4
Step 4: Storing SQL Trace Facility Statistics
18.5
Guidelines for Interpreting TKPROF Output
18.5.1
Guideline for Interpreting the Resolution of Statistics
18.5.2
Guideline for Recursive SQL Statements
18.5.3
Guideline for Deciding Which Statements to Tune
18.5.4
Guidelines for Avoiding Traps in TKPROF Interpretation
Application Tracing Utilities
TRCSESS
TKPROF
Views for Application Tracing
Views Relevant for Trace Statistics
Views Related to Enabling Tracing
Part VIII Automatic SQL Tuning
19
Managing SQL Tuning Sets
19.1
About SQL Tuning Sets
19.1.1
Purpose of SQL Tuning Sets
19.1.2
Concepts for SQL Tuning Sets
19.1.3
User Interfaces for SQL Tuning Sets
19.1.4
Basic Tasks for SQL Tuning Sets
19.2
Creating a SQL Tuning Set
19.3
Loading a SQL Tuning Set
19.4
Displaying the Contents of a SQL Tuning Set
19.5
Modifying a SQL Tuning Set
19.6
Transporting a SQL Tuning Set
19.6.1
About Transporting SQL Tuning Sets
19.6.2
Transporting SQL Tuning Sets with DBMS_SQLTUNE
19.7
Dropping a SQL Tuning Set
20
Analyzing SQL with SQL Tuning Advisor
20.1
About SQL Tuning Advisor
20.1.1
Purpose of SQL Tuning Advisor
20.1.2
SQL Tuning Advisor Architecture
20.1.3
Automatic Tuning Optimizer Concepts
20.2
Managing the Automatic SQL Tuning Task
20.2.1
About the Automatic SQL Tuning Task
20.2.2
Enabling and Disabling the Automatic SQL Tuning Task
20.2.3
Configuring the Automatic SQL Tuning Task
20.2.4
Viewing Automatic SQL Tuning Reports
20.3
Running SQL Tuning Advisor On Demand
20.3.1
About On-Demand SQL Tuning
20.3.2
Creating a SQL Tuning Task
20.3.3
Configuring a SQL Tuning Task
20.3.4
Executing a SQL Tuning Task
20.3.5
Monitoring a SQL Tuning Task
20.3.6
Displaying the Results of a SQL Tuning Task
21
Optimizing Access Paths with SQL Access Advisor
21.1
About SQL Access Advisor
21.1.1
Purpose of SQL Access Advisor
21.1.2
SQL Access Advisor Architecture
21.1.3
User Interfaces for SQL Access Advisor
21.2
Using SQL Access Advisor: Basic Tasks
21.2.1
Creating a SQL Tuning Set as Input for SQL Access Advisor
21.2.2
Populating a SQL Tuning Set with a User-Defined Workload
21.2.3
Creating and Configuring a SQL Access Advisor Task
21.2.4
Executing a SQL Access Advisor Task
21.2.5
Viewing SQL Access Advisor Task Results
21.2.6
Generating and Executing a Task Script
21.3
Performing a SQL Access Advisor Quick Tune
21.4
Using SQL Access Advisor: Advanced Tasks
21.4.1
Evaluating Existing Access Structures
21.4.2
Updating SQL Access Advisor Task Attributes
21.4.3
Creating and Using SQL Access Advisor Task Templates
21.4.4
Terminating SQL Access Advisor Task Execution
21.4.5
Deleting SQL Access Advisor Tasks
21.4.6
Marking SQL Access Advisor Recommendations
21.4.7
Modifying SQL Access Advisor Recommendations
21.5
SQL Access Advisor Examples
21.6
SQL Access Advisor Reference
21.6.1
Action Attributes in the DBA_ADVISOR_ACTIONS View
21.6.2
Categories for SQL Access Advisor Task Parameters
21.6.3
SQL Access Advisor Constants
Part IX SQL Controls
22
Managing SQL Profiles
22.1
About SQL Profiles
22.1.1
Purpose of SQL Profiles
22.1.2
Concepts for SQL Profiles
22.1.3
User Interfaces for SQL Profiles
22.1.4
Basic Tasks for SQL Profiles
22.2
Implementing a SQL Profile
22.2.1
About SQL Profile Implementation
22.2.2
Implementing a SQL Profile
22.3
Listing SQL Profiles
22.4
Altering a SQL Profile
22.5
Dropping a SQL Profile
22.6
Transporting a SQL Profile
23
Managing SQL Plan Baselines
23.1
About SQL Plan Management
23.1.1
Purpose of SQL Plan Management
23.1.2
Plan Capture
23.1.3
Plan Selection
23.1.4
Plan Evolution
23.1.5
Storage Architecture for SQL Plan Management
23.1.6
User Interfaces for SQL Plan Management
23.1.7
Basic Tasks in SQL Plan Management
23.2
Configuring SQL Plan Management
23.2.1
Configuring the Capture and Use of SQL Plan Baselines
23.2.2
Managing the SPM Evolve Advisor Task
23.3
Displaying Plans in a SQL Plan Baseline
23.4
Loading SQL Plan Baselines
23.4.1
Loading Plans from a SQL Tuning Set
23.4.2
Loading Plans from the Shared SQL Area
23.4.3
Loading Plans from a Staging Table
23.5
Evolving SQL Plan Baselines Manually
23.5.1
About the DBMS_SPM Evolve Functions
23.5.2
Managing an Evolve Task
23.6
Dropping SQL Plan Baselines
23.7
Managing the SQL Management Base
23.7.1
Changing the Disk Space Limit for the SMB
23.7.2
Changing the Plan Retention Policy in the SMB
24
Migrating Stored Outlines to SQL Plan Baselines
24.1
About Stored Outline Migration
24.1.1
Purpose of Stored Outline Migration
24.1.2
How Stored Outline Migration Works
24.1.3
User Interface for Stored Outline Migration
24.1.4
Basic Steps in Stored Outline Migration
24.2
Preparing for Stored Outline Migration
24.3
Migrating Outlines to Utilize SQL Plan Management Features
24.4
Migrating Outlines to Preserve Stored Outline Behavior
24.5
Performing Follow-Up Tasks After Stored Outline Migration
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 Using SQL Access Advisor
A.1.3
Guidelines for Choosing Columns and Expressions to Index
A.1.4
Guidelines for Choosing Composite Indexes
A.1.5
Guidelines for Writing SQL Statements That Use Indexes
A.1.6
Guidelines for Writing SQL Statements That Avoid Using Indexes
A.1.7
Guidelines for Re-Creating Indexes
A.1.8
Guidelines for Compacting Indexes
A.1.9
Guidelines for Using Nonunique Indexes to Enforce Uniqueness
A.1.10
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
Glossary
Index
Scripting on this page enhances content navigation, but does not change the content in any way.