Go to main content
1/46
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 2 (12.2.0.1)
New Features
Desupported Features
Other Changes
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12
c
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
Automated SQL Tuning Tools
Automatic Database Diagnostic Monitor (ADDM)
SQL Tuning Advisor
SQL Access Advisor
SQL Plan Management
SQL Performance Analyzer
Manual SQL Tuning Tools
Execution Plans
Real-Time SQL Monitoring and Real-Time Database Operations
Application Tracing
Optimizer Hints
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
Syntax Check
Semantic Check
Shared Pool Check
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
Query Blocks
Query Subplans
Analogy for the Optimizer
About Optimizer Components
Query Transformer
Estimator
Selectivity
Cardinality
Cost
Plan Generator
About Automatic Tuning Optimizer
About Adaptive Query Optimization
Adaptive Query Plans
About Adaptive Query Plans
Purpose of Adaptive Query Plans
How Adaptive Query Plans Work
Adaptive Query Plans: Join Method Example
Adaptive Query Plans: Parallel Distribution Methods
Adaptive Query Plans: Bitmap Index Pruning
When Adaptive Query Plans Are Enabled
Adaptive Statistics
Dynamic Statistics
Automatic Reoptimization
Reoptimization: Statistics Feedback
Reoptimization: Performance Feedback
SQL Plan Directives
When Adaptive Statistics Are Enabled
About Approximate Query Processing
About SQL Plan Management
About the Expression Statistics Store (ESS)
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 (VECTOR GROUP BY)
Cursor-Duration Temporary Tables
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
Different Schemas
Different Costs
Guideline for Minimizing Throw-Away
Guidelines for Evaluating Execution Plans
Guidelines for Evaluating Plans Using the V$SQL_PLAN Views
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 Query Plans
Viewing Parallel Execution with EXPLAIN PLAN
About EXPLAIN PLAN and Parallel Queries
Viewing Parallel Queries with EXPLAIN PLAN: Example
Viewing Bitmap Indexes with EXPLAIN PLAN
Viewing Result Cache with EXPLAIN PLAN
Viewing Partitioned Objects with EXPLAIN PLAN
Displaying Range and Hash Partitioning with EXPLAIN PLAN: Examples
Pruning Information with Composite Partitioned Objects: Examples
Examples of Partial Partition-Wise Joins
Example of Full Partition-Wise Join
Examples of INLIST ITERATOR and EXPLAIN PLAN
When the IN-List Column is an Index Column: Example
When the IN-List Column is an Index and a Partition Column: Example
When the IN-List Column is a Partition Column: Example
Example of Domain Indexes and EXPLAIN PLAN
PLAN_TABLE Columns
Execution Plan Reference
Execution Plan Views
PLAN_TABLE Columns
DBMS_XPLAN Program Units
Part IV SQL Operators: Access Paths and Joins
8
Optimizer Access Paths
Introduction to Access Paths
Table Access Paths
About Heap-Organized Table Access
Row Storage in Data Blocks and Segments: A Primer
Importance of Rowids for Row Access
Direct Path Reads
Full Table Scans
When the Optimizer Considers a Full Table Scan
How a Full Table Scan Works
Full Table Scan: Example
Table Access by Rowid
When the Optimizer Chooses Table Access by Rowid
How Table Access by Rowid Works
Table Access by Rowid: Example
Sample Table Scans
When the Optimizer Chooses a Sample Table Scan
Sample Table Scans: Example
In-Memory Table Scans
When the Optimizer Chooses an In-Memory Table Scan
In-Memory Query Controls
In-Memory Table Scans: Example
B-Tree Index Access Paths
About B-Tree Index Access
B-Tree Index Structure
How Index Storage Affects Index Scans
Unique and Nonunique Indexes
B-Tree Indexes and Nulls
Index Unique Scans
When the Optimizer Considers Index Unique Scans
How Index Unique Scans Work
Index Unique Scans: Example
Index Range Scans
When the Optimizer Considers Index Range Scans
How Index Range Scans Work
Index Range Scan: Example
Index Range Scan Descending: Example
Index Full Scans
When the Optimizer Considers Index Full Scans
How Index Full Scans Work
Index Full Scans: Example
Index Fast Full Scans
When the Optimizer Considers Index Fast Full Scans
How Index Fast Full Scans Work
Index Fast Full Scans: Example
Index Skip Scans
When the Optimizer Considers Index Skips Scans
How Index Skip Scans Work
Index Skip Scans: Example
Index Join Scans
When the Optimizer Considers Index Join Scans
How Index Join Scans Work
Index Join Scans: Example
Bitmap Index Access Paths
About Bitmap Index Access
Differences Between Bitmap and B-Tree Indexes
Purpose of Bitmap Indexes
Bitmaps and Rowids
Bitmap Join Indexes
Bitmap Storage
Bitmap Conversion to Rowid
When the Optimizer Chooses Bitmap Conversion to Rowid
How Bitmap Conversion to Rowid Works
Bitmap Conversion to Rowid: Example
Bitmap Index Single Value
When the Optimizer Considers Bitmap Index Single Value
How Bitmap Index Single Value Works
Bitmap Index Single Value: Example
Bitmap Index Range Scans
When the Optimizer Considers Bitmap Index Range Scans
How Bitmap Index Range Scans Work
Bitmap Index Range Scans: Example
Bitmap Merge
When the Optimizer Considers Bitmap Merge
How Bitmap Merge Works
Bitmap Merge: Example
Table Cluster Access Paths
Cluster Scans
When the Optimizer Considers Cluster Scans
How a Cluster Scan Works
Cluster Scans: Example
Hash Scans
When the Optimizer Considers a Hash Scan
How a Hash Scan Works
Hash Scans: Example
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
When the Optimizer Considers Nested Loops Joins
How Nested Loop Joins Work
Nested Nested Loops
Current Implementation for Nested Loops Joins
Original Implementation for Nested Loops Joins
Nested Loops Controls
Hash Joins
When the Optimizer Considers Hash Joins
How Hash Joins Work
Hash Tables
Hash Join: Basic Steps
How Hash Joins Work When the Hash Table Does Not Fit in the PGA
Hash Join Controls
Sort Merge Joins
When the Optimizer Considers Sort Merge Joins
How Sort Merge Joins Work
Sort Merge Join Controls
Cartesian Joins
When the Optimizer Considers Cartesian Joins
How Cartesian Joins Work
Cartesian Join Controls
Join Types
Inner Joins
Equijoins
Nonequijoins
Band Joins
Outer Joins
Nested Loop Outer Joins
Hash Join Outer Joins
Sort Merge Outer Joins
Full Outer Joins
Multiple Tables on the Left of an Outer Join
Semijoins
When the Optimizer Considers Semijoins
How Semijoins Work
Antijoins
When the Optimizer Considers Antijoins
How Antijoins Work
How Antijoins Handle Nulls
Join Optimizations
Bloom Filters
Purpose of Bloom Filters
How Bloom Filters Work
Bloom Filter Controls
Bloom Filter Metadata
Bloom Filters: Scenario
Partition-Wise Joins
Purpose of Partition-Wise Joins
How Partition-Wise Joins Work
How a Full Partition-Wise Join Works
How a Partial Partition-Wise Join Works
In-Memory Join Groups
Part V Optimizer Statistics
10
Optimizer Statistics Concepts
Introduction to Optimizer Statistics
About Optimizer Statistics Types
Table Statistics
Column Statistics
Index Statistics
Types of Index Statistics
Index Clustering Factor
Effect of Index Clustering Factor on Cost: Example
Session-Specific Statistics for Global Temporary Tables
Shared and Session-Specific Statistics for Global Temporary Tables
Effect of DBMS_STATS on Transaction-Specific Temporary Tables
System Statistics
User-Defined Optimizer Statistics
How the Database Gathers Optimizer Statistics
DBMS_STATS Package
Supplemental Dynamic Statistics
Online Statistics Gathering for Bulk Loads
Purpose of Online Statistics Gathering for Bulk Loads
Global Statistics During Inserts into Empty Partitioned Tables
Index Statistics and Histograms During Bulk Loads
Restrictions for Online Statistics Gathering for Bulk Loads
Hints for Online Statistics Gathering for Bulk Loads
When the Database Gathers Optimizer Statistics
Sources for Optimizer Statistics
SQL Plan Directives
When the Database Creates SQL Plan Directives
How the Database Uses SQL Plan Directives
SQL Plan Directive Maintenance
How the Optimizer Uses SQL Plan Directives: Example
How the Optimizer Uses Extensions and SQL Plan Directives: Example
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
Configuring Options for Optimizer Statistics Gathering
About Optimizer Statistics Collection
Purpose of Optimizer Statistics Collection
User Interfaces for Optimizer Statistics Management
Graphical Interface for Optimizer Statistics Management
Accessing the Database Home Page in Cloud Control
Accessing the Optimizer Statistics Console
Command-Line Interface for Optimizer Statistics Management
Setting Optimizer Statistics Preferences
About Optimizer Statistics Preferences
Purpose of Optimizer Statistics Preferences
DBMS_STATS Procedures for Setting Statistics Preferences
Statistics Preference Overrides
Setting Statistics Preferences: Example
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
Configuring Options for Dynamic Statistics
About Dynamic Statistics Levels
Setting Dynamic Statistics Levels Manually
Disabling Dynamic Statistics
Managing SQL Plan Directives
13
Gathering Optimizer Statistics
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
Gathering Optimizer Statistics Manually
About Manual Statistics Collection with DBMS_STATS
Guidelines for Gathering Optimizer Statistics Manually
Guideline for Setting the Sample Size
Guideline for Gathering Statistics in Parallel
Guideline for Partitioned Objects
Guideline for Frequently Changing Objects
Guideline for External Tables
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
About Concurrent Statistics Gathering
How DBMS_STATS Gathers Statistics Concurrently
Concurrent Statistics Gathering and Resource Management
Enabling Concurrent Statistics Gathering
Monitoring Statistics Gathering Operations
Gathering Incremental Statistics on Partitioned Objects
Purpose of Incremental Statistics
How DBMS_STATS Derives Global Statistics for Partitioned tables
Partition-Level Synopses
NDV Algorithms: Adaptive Sampling and HyperLogLog
Aggregation of Global Statistics Using Synopses: Example
Gathering Statistics for a Partitioned Table: Basic Steps
Considerations for Incremental Statistics Maintenance
Enabling Incremental Statistics Using SET_TABLE_PREFS
About the APPROXIMATE_NDV_ALGORITHM Settings
Configuring Synopsis Generation: Examples
Maintaining Incremental Statistics for Partition Maintenance Operations
Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics
Gathering System Statistics Manually
About Gathering System Statistics with DBMS_STATS
Guidelines for Gathering System Statistics
Gathering Workload Statistics
About Workload Statistics
Starting and Stopping System Statistics Gathering
Gathering System Statistics During a Specified Interval
Gathering Noworkload Statistics
Deleting System Statistics
Running Statistics Gathering Functions in Reporting Mode
14
Managing Extended Statistics
Managing Column Group Statistics
About Statistics on Column Groups
Why Column Group Statistics Are Needed: Example
Automatic and Manual Column Group Statistics
User Interface for Column Group Statistics
Detecting Useful Column Groups for a Specific Workload
Creating Column Groups Detected During Workload Monitoring
Creating and Gathering Statistics on Column Groups Manually
Displaying Column Group Information
Dropping a Column Group
Managing Expression Statistics
About Expression Statistics
When Expression Statistics Are Useful: Example
Creating Expression Statistics
Displaying Expression Statistics
Dropping Expression Statistics
15
Controlling the Use of Optimizer Statistics
Locking and Unlocking Optimizer Statistics
Locking Statistics
Unlocking Statistics
Publishing Pending Optimizer Statistics
About Pending Optimizer Statistics
User Interfaces for Publishing Optimizer Statistics
Managing Published and Pending Statistics
Creating Artificial Optimizer Statistics for Testing
About Artificial Optimizer Statistics
Setting Artificial Optimizer Statistics for a Table
Setting Optimizer Statistics: Example
16
Managing Historical Optimizer 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
Reporting on Past Statistics Gathering Operations
17
Transporting Optimizer Statistics
About Transporting Optimizer Statistics
Transporting Optimizer Statistics to a Test Database: Tutorial
18
Analyzing Statistics Using Optimizer Statistics Advisor
About Optimizer Statistics Advisor
Purpose of Optimizer Statistics Advisor
Problems with a Traditional Script-Based Approach
Advantages of Optimizer Statistics Advisor
Optimizer Statistics Advisor Concepts
Components of Optimizer Statistics Advisor
Rules for Optimizer Statistics Advisor
Findings for Optimizer Statistics Advisor
Recommendations for Optimizer Statistics Advisor
Actions for Optimizer Statistics Advisor
Operational Modes for Optimizer Statistics Advisor
Command-Line Interface to 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
About Filters for Optimizer Statistics Advisor
Creating an Object Filter for an Optimizer Advisor Task
Creating a Rule Filter for an Optimizer Advisor Task
Creating an Operation Filter 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
Implementing Actions Recommended by Optimizer Statistics Advisor
Generating a Script Using Optimizer Statistics Advisor
Part VI Optimizer Controls
19
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
Types of Hints
Scope of Hints
Considerations for Hints
Guidelines for Join Order Hints
20
Improving Real-World Performance Through Cursor Sharing
Overview of Cursor Sharing
About Cursors
Private and Shared SQL Areas
Parent and Child Cursors
Parent Cursors and V$SQLAREA
Child Cursors and V$SQL
Cursor Mismatches and V$SQL_SHARED_CURSOR
About Cursors and Parsing
About Literals and Bind Variables
Literals and Cursors
Bind Variables and Cursors
Bind Variable Peeking
About the Life Cycle of Shared Cursors
Cursor Marked Invalid
Cursors Marked Rolling Invalid
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
21
Monitoring Database Operations
About Monitoring Database Operations
Purpose of Monitoring Database Operations
Simple Database Operation Use Cases
Composite Database Operation Use Cases
Database Operation Monitoring Concepts
About the Architecture of Database Operations
Composite Database Operations
Attributes of Database Operations
User Interfaces for Database Operations Monitoring
Monitored SQL Executions Page in Cloud Control
Accessing the Monitored SQL Executions Page
DBMS_SQL_MONITOR Package
Views 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
Monitoring SQL Executions Using Cloud Control
22
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
Accessing the Incident Manager
Accessing the Support Workbench
Command-Line Interface for SQL Test Case Builder
Running SQL Test Case Builder
23
Performing Application Tracing
Overview of End-to-End Application Tracing
Purpose of End-to-End Application Tracing
End-to-End Application Tracing in a Multitenant Environment
Tools for End-to-End Application Tracing
Overview of the SQL Trace Facility
Overview of TKPROF
Enabling Statistics Gathering for End-to-End Tracing
Enabling Statistics Gathering for a Client ID
Enabling Statistics Gathering for Services, Modules, and Actions
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
Generating the TKPROF Output SQL Script
Editing the TKPROF Output SQL Script
Querying the Output Table
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
Guideline for Avoiding the Argument Trap
Guideline for Avoiding the Read Consistency Trap
Guideline for Avoiding the Schema Trap
Guideline for Avoiding the Time Trap
Application Tracing Utilities
TRCSESS
Purpose
Guidelines
Syntax
Options
Examples
TKPROF
Purpose
Guidelines
Syntax
Options
Output
Identification of User Issuing the SQL Statement in TKPROF
Tabular Statistics in TKPROF
Library Cache Misses in TKPROF
Row Source Operations in TKPROF
Wait Event Information in TKPROF
Examples
Views for Application Tracing
Views Relevant for Trace Statistics
Views Related to Enabling Tracing
Part VIII Automatic SQL Tuning
24
Managing SQL Tuning Sets
About SQL Tuning Sets
Purpose of SQL Tuning Sets
Concepts for SQL Tuning Sets
User Interfaces for SQL Tuning Sets
Accessing the SQL Tuning Sets Page in Cloud Control
Command-Line Interface to 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
Basic Steps for Transporting SQL Tuning Sets
Basic Steps for Transporting SQL Tuning Sets When the CON_DBID Values Differ
Transporting SQL Tuning Sets with DBMS_SQLTUNE
Dropping a SQL Tuning Set
25
Analyzing SQL with SQL Tuning Advisor
About SQL Tuning Advisor
Purpose of SQL Tuning Advisor
SQL Tuning Advisor Architecture
Input to SQL Tuning Advisor
Output of SQL Tuning Advisor
Automatic Tuning Optimizer Analyses
Statistical Analysis
SQL Profiling
Access Path Analysis
SQL Structural Analysis
Alternative Plan Analysis
SQL Tuning Advisor Operation
Automatic and On-Demand SQL Tuning
Local and Remote SQL Tuning
Managing the Automatic SQL Tuning Task
About the Automatic SQL Tuning Task
Purpose of Automatic SQL Tuning
Automatic SQL Tuning Concepts
Command-Line Interface to SQL Tuning Advisor
Basic Tasks for Automatic SQL Tuning
Enabling and Disabling the Automatic SQL Tuning Task
Enabling and Disabling the Automatic SQL Tuning Task Using Cloud Control
Enabling and Disabling the Automatic SQL Tuning Task from the Command Line
Configuring the Automatic SQL Tuning Task
Configuring the Automatic SQL Tuning Task Using Cloud Control
Configuring the Automatic SQL Tuning Task Using the Command Line
Viewing Automatic SQL Tuning Reports
Viewing Automatic SQL Tuning Reports Using the Command Line
Running SQL Tuning Advisor On Demand
About On-Demand SQL Tuning
Purpose of On-Demand SQL Tuning
User Interfaces for On-Demand SQL Tuning
Accessing the SQL Tuning Advisor Using Cloud Control
Command-Line Interface to On-Demand SQL Tuning
Basic Tasks in 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
26
Optimizing Access Paths with SQL Access Advisor
About SQL Access Advisor
Purpose of SQL Access Advisor
SQL Access Advisor Architecture
Input to SQL Access Advisor
Filter Options for SQL Access Advisor
SQL Access Advisor Recommendations
SQL Access Advisor Actions
Types of Actions
Guidelines for Interpreting Partitioning Recommendations
SQL Access Advisor Repository
User Interfaces for SQL Access Advisor
Accessing the SQL Access Advisor: Initial Options Page Using Cloud Control
Command-Line Interface to SQL Tuning Sets
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
Interrupting SQL Access Advisor Tasks
Canceling SQL Access Advisor Tasks
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: Profiles and Plan Baselines
27
Managing SQL Profiles
About SQL Profiles
Purpose of SQL Profiles
Concepts for SQL Profiles
SQL Profile Recommendations
SQL Profiles and SQL Plan Baselines
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
28
Overview of SQL Plan Management
Purpose of SQL Plan Management
Benefits of SQL Plan Management
Differences Between SQL Plan Baselines and SQL Profiles
Plan Capture
Automatic Initial Plan Capture
Eligibility for Automatic Initial Plan Capture
Plan Matching for Automatic Initial Plan Capture
Manual Plan Capture
Plan Selection
Plan Evolution
Purpose of Plan Evolution
PL/SQL Subprograms for Plan Evolution
Storage Architecture for SQL Plan Management
SQL Management Base
SQL Statement Log
SQL Plan History
Enabled Plans
Accepted Plans
Fixed Plans
29
Managing SQL Plan Baselines
About Managing SQL Plan Baselines
User Interfaces for SQL Plan Management
Accessing the SQL Plan Baseline Page in Cloud Control
DBMS_SPM Package
Basic Tasks in SQL Plan Management
Configuring SQL Plan Management
Configuring the Capture and Use of SQL Plan Baselines
Enabling Automatic Initial Plan Capture for SQL Plan Management
Configuring Filters for Automatic Plan Capture
Disabling All SQL Plan Baselines
Managing the SPM Evolve Advisor Task
About the SPM Evolve Advisor Task
Enabling and Disabling the SPM Evolve Advisor Task
Configuring the Automatic SPM Evolve Advisor Task
Displaying Plans in a SQL Plan Baseline
Loading SQL Plan Baselines
About Loading SQL Plan Baselines
Loading Plans from AWR
Loading Plans from the Shared SQL Area
Loading Plans from a SQL Tuning Set
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
About Managing the SMB
Changing the Disk Space Limit for the SMB
Changing the Plan Retention Policy in the SMB
30
Migrating Stored Outlines to SQL Plan Baselines
About Stored Outline Migration
Purpose of Stored Outline Migration
How Stored Outline Migration Works
Stages of Stored Outline Migration
Outline Categories and Baseline Modules
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 Choosing Keys for Composite Indexes
Guidelines for Ordering Keys for 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
Index
Scripting on this page enhances content navigation, but does not change the content in any way.