1/40
Contents
Title and Copyright Information
Preface
Audience
Related Documents
Conventions
Changes in This Release for Oracle Database SQL Tuning Guide
Changes in Oracle Database 12c Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12c Release 1 (12.1.0.1)
New Features
Deprecated Features
Desupported Features
Other Changes
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
SQL Tuning Tasks
SQL Tuning Tools
User Interfaces to SQL Tuning Tools
2
SQL Performance Methodology
Guidelines for Designing Your Application
Guideline for Data Modeling
Guideline for Writing Efficient Applications
Guidelines for Deploying Your Application
Guideline for Deploying in a Test Environment
Guidelines for Application Rollout
Part II Query Optimizer Fundamentals
3
SQL Processing
About SQL Processing
SQL Parsing
SQL Optimization
SQL Row Source Generation
SQL Execution
How Oracle Database Processes DML
How Row Sets Are Fetched
Read Consistency
Data Changes
How Oracle Database Processes DDL
4
Query Optimizer Concepts
Introduction to the Query Optimizer
Purpose of the Query Optimizer
Cost-Based Optimization
Execution Plans
About Optimizer Components
Query Transformer
Estimator
Plan Generator
About Automatic Tuning Optimizer
About Adaptive Query Optimization
Adaptive Plans
Adaptive Statistics
About Optimizer Management of SQL Plan Baselines
5
Query Transformations
OR Expansion
View Merging
Query Blocks in View Merging
Simple View Merging
Complex View Merging
Predicate Pushing
Subquery Unnesting
Query Rewrite with Materialized Views
Star Transformation
About Star Schemas
Purpose of Star Transformations
How Star Transformation Works
Controls for Star Transformation
Star Transformation: Scenario
Temporary Table Transformation: Scenario
In-Memory Aggregation
Purpose of In-Memory Aggregation
How In-Memory Aggregation Works
Controls for In-Memory Aggregation
In-Memory Aggregation: Scenario
In-Memory Aggregation: Example
Table Expansion
Purpose of Table Expansion
How Table Expansion Works
Table Expansion: Scenario
Table Expansion and Star Transformation: Scenario
Join Factorization
Purpose of Join Factorization
How Join Factorization Works
Factorization and Join Orders: Scenario
Factorization of Outer Joins: Scenario
Part III Query Execution Plans
6
Generating and Displaying Execution Plans
Introduction to Execution Plans
About Plan Generation and Display
About the Plan Explanation
Why Execution Plans Change
Guideline for Minimizing Throw-Away
Guidelines for Evaluating Execution Plans
EXPLAIN PLAN Restrictions
Guidelines for Creating PLAN_TABLE
Generating Execution Plans
Executing EXPLAIN PLAN for a Single Statement
Executing EXPLAIN PLAN Using a Statement ID
Directing EXPLAIN PLAN Output to a Nondefault Table
Displaying PLAN_TABLE Output
Displaying an Execution Plan: Example
Customizing PLAN_TABLE Output
7
Reading Execution Plans
Reading Execution Plans: Basic
Reading Execution Plans: Advanced
Reading Adaptive Plans
Viewing Parallel Execution with EXPLAIN PLAN
Viewing Bitmap Indexes with EXPLAIN PLAN
Viewing Result Cache with EXPLAIN PLAN
Viewing Partitioned Objects with EXPLAIN PLAN
PLAN_TABLE Columns
Execution Plan Reference
Execution Plan Views
PLAN_TABLE Columns
DBMS_XPLAN Program Units
Part IV SQL Operators
8
Optimizer Access Paths
Introduction to Access Paths
Table Access Paths
About Heap-Organized Table Access
Full Table Scans
Table Access by Rowid
Sample Table Scans
In-Memory Table Scans
B-Tree Index Access Paths
About B-Tree Index Access
Index Unique Scans
Index Range Scans
Index Full Scans
Index Fast Full Scans
Index Skip Scans
Index Join Scans
Bitmap Index Access Paths
About Bitmap Index Access
Bitmap Conversion to Rowid
Bitmap Index Single Value
Bitmap Index Range Scans
Bitmap Merge
Table Cluster Access Paths
Cluster Scans
Hash Scans
9
Joins
About Joins
Join Trees
How the Optimizer Executes Join Statements
How the Optimizer Chooses Execution Plans for Joins
Join Methods
Nested Loops Joins
Hash Joins
Sort Merge Joins
Cartesian Joins
Join Types
Inner Joins
Outer Joins
Semijoins
Antijoins
Join Optimizations
Bloom Filters
Partition-Wise Joins
Part V Optimizer Statistics
10
Optimizer Statistics Concepts
Introduction to Optimizer Statistics
About Optimizer Statistics Types
Table Statistics
Column Statistics
Index Statistics
Session-Specific Statistics for Global Temporary Tables
System Statistics
User-Defined Optimizer Statistics
How the Database Gathers Optimizer Statistics
DBMS_STATS Package
Dynamic Statistics
Online Statistics Gathering for Bulk Loads
When the Database Gathers Optimizer Statistics
Sources for Optimizer Statistics
SQL Plan Directives
When the Database Samples Data
How the Database Samples Data
11
Histograms
Purpose of Histograms
When Oracle Database Creates Histograms
How Oracle Database Chooses the Histogram Type
Cardinality Algorithms When Using Histograms
Endpoint Numbers and Values
Popular and Nonpopular Values
Bucket Compression
Frequency Histograms
Criteria For Frequency Histograms
Generating a Frequency Histogram
Top Frequency Histograms
Criteria For Top Frequency Histograms
Generating a Top Frequency Histogram
Height-Balanced Histograms (Legacy)
Criteria for Height-Balanced Histograms
Generating a Height-Balanced Histogram
Hybrid Histograms
How Endpoint Repeat Counts Work
Criteria for Hybrid Histograms
Generating a Hybrid Histogram
12
Managing Optimizer Statistics: Basic Topics
About Optimizer Statistics Collection
Purpose of Optimizer Statistics Collection
User Interfaces for Optimizer Statistics Management
Configuring Automatic Optimizer Statistics Collection
About Automatic Optimizer Statistics Collection
Configuring Automatic Optimizer Statistics Collection Using Cloud Control
Configuring Automatic Optimizer Statistics Collection from the Command Line
Setting Optimizer Statistics Preferences
About Optimizer Statistics Preferences
Setting Global Optimizer Statistics Preferences Using Cloud Control
Setting Object-Level Optimizer Statistics Preferences Using Cloud Control
Setting Optimizer Statistics Preferences from the Command Line
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
Gathering System Statistics Manually
About Gathering System Statistics with DBMS_STATS
Guidelines for Gathering System Statistics
Gathering Workload Statistics
Gathering Noworkload Statistics
Deleting System Statistics
13
Managing Optimizer Statistics: Advanced Topics
Controlling Dynamic Statistics
About Dynamic Statistics Levels
Setting Dynamic Statistics Levels Manually
Disabling Dynamic Statistics
Publishing Pending Optimizer Statistics
About Pending Optimizer Statistics
User Interfaces for Publishing Optimizer Statistics
Managing Published and Pending Statistics
Managing Extended Statistics
Managing Column Group Statistics
Managing Expression Statistics
Locking and Unlocking Optimizer Statistics
Locking Statistics
Unlocking Statistics
Restoring Optimizer Statistics
About Restore Operations for Optimizer Statistics
Guidelines for Restoring Optimizer Statistics
Restrictions for Restoring Optimizer Statistics
Restoring Optimizer Statistics Using DBMS_STATS
Managing Optimizer Statistics Retention
Obtaining Optimizer Statistics History
Changing the Optimizer Statistics Retention Period
Purging Optimizer Statistics
Importing and Exporting Optimizer Statistics
About Transporting Optimizer Statistics
Transporting Optimizer Statistics to a Test Database
Running Statistics Gathering Functions in Reporting Mode
Reporting on Past Statistics Gathering Operations
Managing SQL Plan Directives
Part VI Optimizer Controls
14
Influencing the Optimizer
Techniques for Influencing the Optimizer
Influencing the Optimizer with Initialization Parameters
About Optimizer Initialization Parameters
Enabling Optimizer Features
Choosing an Optimizer Goal
Controlling Adaptive Optimization
Influencing the Optimizer with Hints
About Optimizer Hints
Guidelines for Join Order Hints
15
Improving Real-World Performance Through Cursor Sharing
Overview of Cursor Sharing
About Cursors
About Cursors and Parsing
About Literals and Bind Variables
CURSOR_SHARING and Bind Variable Substitution
CURSOR_SHARING Initialization Parameter
Parsing Behavior When CURSOR_SHARING = FORCE
Adaptive Cursor Sharing
Purpose of Adaptive Cursor Sharing
How Adaptive Cursor Sharing Works: Example
Bind-Sensitive Cursors
Bind-Aware Cursors
Cursor Merging
Adaptive Cursor Sharing Views
Real-World Performance Guidelines for Cursor Sharing
Develop Applications with Bind Variables for Security and Performance
Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix
Establish Coding Conventions to Increase Cursor Reuse
Minimize Session-Level Changes to the Optimizer Environment
Part VII Monitoring and Tracing SQL
16
Monitoring Database Operations
About Monitoring Database Operations
Purpose of Monitoring Database Operations
Database Operation Monitoring Concepts
User Interfaces for Database Operations Monitoring
Basic Tasks in Database Operations Monitoring
Enabling and Disabling Monitoring of Database Operations
Enabling Monitoring of Database Operations at the System Level
Enabling and Disabling Monitoring of Database Operations at the Statement Level
Creating a Database Operation
Reporting on Database Operations Using SQL Monitor
17
Gathering Diagnostic Data with SQL Test Case Builder
Purpose of SQL Test Case Builder
Concepts for SQL Test Case Builder
SQL Incidents
What SQL Test Case Builder Captures
Output of SQL Test Case Builder
User Interfaces for SQL Test Case Builder
Graphical Interface for SQL Test Case Builder
Command-Line Interface for SQL Test Case Builder
Running SQL Test Case Builder
18
Performing Application Tracing
Overview of End-to-End Application Tracing
Purpose of End-to-End Application Tracing
Tools for End-to-End Application Tracing
Enabling Statistics Gathering for End-to-End Tracing
Enabling Statistics Gathering for a Client ID
Enabling Statistics Gathering for a Service, Module, and Action
Enabling End-to-End Application Tracing
Enabling Tracing for a Client Identifier
Enabling Tracing for a Service, Module, and Action
Enabling Tracing for a Session
Enabling Tracing for the Instance or Database
Generating Output Files Using SQL Trace and TKPROF
Step 1: Setting Initialization Parameters for Trace File Management
Step 2: Enabling the SQL Trace Facility
Step 3: Generating Output Files with TKPROF
Step 4: Storing SQL Trace Facility Statistics
Guidelines for Interpreting TKPROF Output
Guideline for Interpreting the Resolution of Statistics
Guideline for Recursive SQL Statements
Guideline for Deciding Which Statements to Tune
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
About SQL Tuning Sets
Purpose of SQL Tuning Sets
Concepts for SQL Tuning Sets
User Interfaces for SQL Tuning Sets
Basic Tasks for SQL Tuning Sets
Creating a SQL Tuning Set
Loading a SQL Tuning Set
Displaying the Contents of a SQL Tuning Set
Modifying a SQL Tuning Set
Transporting a SQL Tuning Set
About Transporting SQL Tuning Sets
Transporting SQL Tuning Sets with DBMS_SQLTUNE
Dropping a SQL Tuning Set
20
Analyzing SQL with SQL Tuning Advisor
About SQL Tuning Advisor
Purpose of SQL Tuning Advisor
SQL Tuning Advisor Architecture
Automatic Tuning Optimizer Concepts
Managing the Automatic SQL Tuning Task
About the Automatic SQL Tuning Task
Enabling and Disabling the Automatic SQL Tuning Task
Configuring the Automatic SQL Tuning Task
Viewing Automatic SQL Tuning Reports
Running SQL Tuning Advisor On Demand
About On-Demand SQL Tuning
Creating a SQL Tuning Task
Configuring a SQL Tuning Task
Executing a SQL Tuning Task
Monitoring a SQL Tuning Task
Displaying the Results of a SQL Tuning Task
21
Optimizing Access Paths with SQL Access Advisor
About SQL Access Advisor
Purpose of SQL Access Advisor
SQL Access Advisor Architecture
User Interfaces for SQL Access Advisor
Using SQL Access Advisor: Basic Tasks
Creating a SQL Tuning Set as Input for SQL Access Advisor
Populating a SQL Tuning Set with a User-Defined Workload
Creating and Configuring a SQL Access Advisor Task
Executing a SQL Access Advisor Task
Viewing SQL Access Advisor Task Results
Generating and Executing a Task Script
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
Action Attributes in the DBA_ADVISOR_ACTIONS View
Categories for SQL Access Advisor Task Parameters
SQL Access Advisor Constants
Part IX SQL Controls
22
Managing SQL Profiles
About SQL Profiles
Purpose of SQL Profiles
Concepts for SQL Profiles
User Interfaces for SQL Profiles
Basic Tasks for SQL Profiles
Implementing a SQL Profile
About SQL Profile Implementation
Implementing a SQL Profile
Listing SQL Profiles
Altering a SQL Profile
Dropping a SQL Profile
Transporting a SQL Profile
23
Managing SQL Plan Baselines
About SQL Plan Management
Purpose of SQL Plan Management
Plan Capture
Plan Selection
Plan Evolution
Storage Architecture for SQL Plan Management
User Interfaces for SQL Plan Management
Basic Tasks in SQL Plan Management
Configuring SQL Plan Management
Configuring the Capture and Use of SQL Plan Baselines
Managing the SPM Evolve Advisor Task
Displaying Plans in a SQL Plan Baseline
Loading SQL Plan Baselines
Loading Plans from a SQL Tuning Set
Loading Plans from the Shared SQL Area
Loading Plans from a Staging Table
Evolving SQL Plan Baselines Manually
About the DBMS_SPM Evolve Functions
Managing an Evolve Task
Dropping SQL Plan Baselines
Managing the SQL Management Base
Changing the Disk Space Limit for the SMB
Changing the Plan Retention Policy in the SMB
24
Migrating Stored Outlines to SQL Plan Baselines
About Stored Outline Migration
Purpose of Stored Outline Migration
How Stored Outline Migration Works
User Interface for Stored Outline Migration
Basic Steps in Stored Outline Migration
Preparing for Stored Outline Migration
Migrating Outlines to Utilize SQL Plan Management Features
Migrating Outlines to Preserve Stored Outline Behavior
Performing Follow-Up Tasks After Stored Outline Migration
A
Guidelines for Indexes and Table Clusters
Guidelines for Tuning Index Performance
Guidelines for Tuning the Logical Structure
Guidelines for Using SQL Access Advisor
Guidelines for Choosing Columns and Expressions to Index
Guidelines for Choosing Composite Indexes
Guidelines for Writing SQL Statements That Use Indexes
Guidelines for Writing SQL Statements That Avoid Using Indexes
Guidelines for Re-Creating Indexes
Guidelines for Compacting Indexes
Guidelines for Using Nonunique Indexes to Enforce Uniqueness
Guidelines for Using Enabled Novalidated Constraints
Guidelines for Using Function-Based Indexes for Performance
Guidelines for Using Partitioned Indexes for Performance
Guidelines for Using Index-Organized Tables for Performance
Guidelines for Using Bitmap Indexes for Performance
Guidelines for Using Bitmap Join Indexes for Performance
Guidelines for Using Domain Indexes for Performance
Guidelines for Using Table Clusters
Guidelines for Using Hash Clusters for Performance
Glossary
shared cursor
Index
Scripting on this page enhances content navigation, but does not change the content in any way.