Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

SQL Tuning Guide

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • 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 Release 19c, Version 19.1
      • New Features
      • Other Changes
    • Changes in Oracle Database Release 18c, Version 18.1
      • New Features
  • 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.2.1 Automated SQL Tuning Tools
            • 1.4.2.1.1 Automatic Database Diagnostic Monitor (ADDM)
            • 1.4.2.1.2 SQL Tuning Advisor
            • 1.4.2.1.3 SQL Access Advisor
            • 1.4.2.1.4 Automatic Indexing
            • 1.4.2.1.5 SQL Plan Management
            • 1.4.2.1.6 SQL Performance Analyzer
          • 1.4.2.2 Manual SQL Tuning Tools
            • 1.4.2.2.1 Execution Plans
            • 1.4.2.2.2 Real-Time SQL Monitoring and Real-Time Database Operations
            • 1.4.2.2.3 Application Tracing
            • 1.4.2.2.4 Optimizer Hints
        • 1.4.3 User Interfaces to SQL Tuning Tools
    • 2 SQL Performance Methodology
      • 2.1 Guidelines for Designing Your Application
        • 2.1.1 Guideline for Data Modeling
        • 2.1.2 Guideline for Writing Efficient Applications
      • 2.2 Guidelines for Deploying Your Application
        • 2.2.1 Guideline for Deploying in a Test Environment
        • 2.2.2 Guidelines for Application Rollout
  • Part II Query Optimizer Fundamentals
    • 3 SQL Processing
      • 3.1 About SQL Processing
        • 3.1.1 SQL Parsing
          • 3.1.1.1 Syntax Check
          • 3.1.1.2 Semantic Check
          • 3.1.1.3 Shared Pool Check
        • 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 How Row Sets Are Fetched
        • 3.2.2 Read Consistency
        • 3.2.3 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.1.3.1 Query Blocks
          • 4.1.3.2 Query Subplans
          • 4.1.3.3 Analogy for the Optimizer
      • 4.2 About Optimizer Components
        • 4.2.1 Query Transformer
        • 4.2.2 Estimator
          • 4.2.2.1 Selectivity
          • 4.2.2.2 Cardinality
          • 4.2.2.3 Cost
        • 4.2.3 Plan Generator
      • 4.3 About Automatic Tuning Optimizer
      • 4.4 About Adaptive Query Optimization
        • 4.4.1 Adaptive Query Plans
          • 4.4.1.1 About Adaptive Query Plans
          • 4.4.1.2 Purpose of Adaptive Query Plans
          • 4.4.1.3 How Adaptive Query Plans Work
            • 4.4.1.3.1 Adaptive Query Plans: Join Method Example
            • 4.4.1.3.2 Adaptive Query Plans: Parallel Distribution Methods
            • 4.4.1.3.3 Adaptive Query Plans: Bitmap Index Pruning
          • 4.4.1.4 When Adaptive Query Plans Are Enabled
        • 4.4.2 Adaptive Statistics
          • 4.4.2.1 Dynamic Statistics
          • 4.4.2.2 Automatic Reoptimization
            • 4.4.2.2.1 Reoptimization: Statistics Feedback
            • 4.4.2.2.2 Reoptimization: Performance Feedback
          • 4.4.2.3 SQL Plan Directives
          • 4.4.2.4 When Adaptive Statistics Are Enabled
      • 4.5 About Approximate Query Processing
        • 4.5.1 Approximate Query Initialization Parameters
        • 4.5.2 Approximate Query SQL Functions
      • 4.6 About SQL Plan Management
      • 4.7 About Quarantined SQL Plans
      • 4.8 About the Expression Statistics Store (ESS)
    • 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.5.1 About Query Rewrite and the Optimizer
        • 5.5.2 About Initialization Parameters for Query Rewrite
        • 5.5.3 About the Accuracy of Query Rewrite
        • 5.5.4 Example of Query Rewrite
      • 5.6 Statistics-Based Query Transformation
      • 5.7 Star Transformation
        • 5.7.1 About Star Schemas
        • 5.7.2 Purpose of Star Transformations
        • 5.7.3 How Star Transformation Works
        • 5.7.4 Controls for Star Transformation
        • 5.7.5 Star Transformation: Scenario
        • 5.7.6 Temporary Table Transformation: Scenario
      • 5.8 In-Memory Aggregation (VECTOR GROUP BY)
      • 5.9 Cursor-Duration Temporary Tables
        • 5.9.1 Purpose of Cursor-Duration Temporary Tables
        • 5.9.2 How Cursor-Duration Temporary Tables Work
        • 5.9.3 Cursor-Duration Temporary Tables: Example
      • 5.10 Table Expansion
        • 5.10.1 Purpose of Table Expansion
        • 5.10.2 How Table Expansion Works
        • 5.10.3 Table Expansion: Scenario
        • 5.10.4 Table Expansion and Star Transformation: Scenario
      • 5.11 Join Factorization
        • 5.11.1 Purpose of Join Factorization
        • 5.11.2 How Join Factorization Works
        • 5.11.3 Factorization and Join Orders: Scenario
        • 5.11.4 Factorization of Outer Joins: Scenario
  • Part III Query Execution Plans
    • 6 Explaining and Displaying Execution Plans
      • 6.1 Introduction to Execution Plans
        • 6.1.1 Contents of an Execution Plan
        • 6.1.2 Why Execution Plans Change
          • 6.1.2.1 Different Schemas
          • 6.1.2.2 Different Costs
      • 6.2 Generating Plan Output Using the EXPLAIN PLAN Statement
        • 6.2.1 About the EXPLAIN PLAN Statement
          • 6.2.1.1 About PLAN_TABLE
          • 6.2.1.2 EXPLAIN PLAN Restrictions
        • 6.2.2 Explaining a SQL Statement: Basic Steps
        • 6.2.3 Specifying a Statement ID in EXPLAIN PLAN: Example
        • 6.2.4 Specifying a Different Location for EXPLAIN PLAN Output: Example
      • 6.3 Displaying Execution Plans
        • 6.3.1 About the Display of PLAN_TABLE Output
          • 6.3.1.1 DBMS_XPLAN Display Functions
          • 6.3.1.2 Plan-Related Views
        • 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.2.1 About EXPLAIN PLAN and Parallel Queries
            • 6.3.4.2.2 Viewing Parallel Queries with EXPLAIN PLAN: 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.5.1 When the IN-List Column is an Index Column: Example
              • 6.3.4.5.5.2 When the IN-List Column is an Index and a Partition Column: Example
              • 6.3.4.5.5.3 When the IN-List Column is a Partition Column: Example
            • 6.3.4.5.6 Example of Domain Indexes and EXPLAIN PLAN
      • 6.4 Comparing Execution Plans
        • 6.4.1 Purpose of Plan Comparison
        • 6.4.2 User Interface for Plan Comparison
        • 6.4.3 Comparing Execution Plans: Tutorial
        • 6.4.4 Comparing Execution Plans: Examples
    • 7 PLAN_TABLE Reference
      • 7.1 PLAN_TABLE Columns
      • 7.2 OPERATION and OPTION Columns of PLAN_TABLE
      • 7.3 DISTRIBUTION Column of PLAN_TABLE
  • Part IV SQL Operators: Access Paths and Joins
    • 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.1.1 Row Storage in Data Blocks and Segments: A Primer
          • 8.2.1.2 Importance of Rowids for Row Access
          • 8.2.1.3 Direct Path Reads
        • 8.2.2 Full Table Scans
          • 8.2.2.1 When the Optimizer Considers a Full Table Scan
          • 8.2.2.2 How a Full Table Scan Works
          • 8.2.2.3 Full Table Scan: Example
        • 8.2.3 Table Access by Rowid
          • 8.2.3.1 When the Optimizer Chooses Table Access by Rowid
          • 8.2.3.2 How Table Access by Rowid Works
          • 8.2.3.3 Table Access by Rowid: Example
        • 8.2.4 Sample Table Scans
          • 8.2.4.1 When the Optimizer Chooses a Sample Table Scan
          • 8.2.4.2 Sample Table Scans: Example
        • 8.2.5 In-Memory Table Scans
          • 8.2.5.1 When the Optimizer Chooses an In-Memory Table Scan
          • 8.2.5.2 In-Memory Query Controls
          • 8.2.5.3 In-Memory Table Scans: Example
      • 8.3 B-Tree Index Access Paths
        • 8.3.1 About B-Tree Index Access
          • 8.3.1.1 B-Tree Index Structure
          • 8.3.1.2 How Index Storage Affects Index Scans
          • 8.3.1.3 Unique and Nonunique Indexes
          • 8.3.1.4 B-Tree Indexes and Nulls
        • 8.3.2 Index Unique Scans
          • 8.3.2.1 When the Optimizer Considers Index Unique Scans
          • 8.3.2.2 How Index Unique Scans Work
          • 8.3.2.3 Index Unique Scans: Example
        • 8.3.3 Index Range Scans
          • 8.3.3.1 When the Optimizer Considers Index Range Scans
          • 8.3.3.2 How Index Range Scans Work
          • 8.3.3.3 Index Range Scan: Example
          • 8.3.3.4 Index Range Scan Descending: Example
        • 8.3.4 Index Full Scans
          • 8.3.4.1 When the Optimizer Considers Index Full Scans
          • 8.3.4.2 How Index Full Scans Work
          • 8.3.4.3 Index Full Scans: Example
        • 8.3.5 Index Fast Full Scans
          • 8.3.5.1 When the Optimizer Considers Index Fast Full Scans
          • 8.3.5.2 How Index Fast Full Scans Work
          • 8.3.5.3 Index Fast Full Scans: Example
        • 8.3.6 Index Skip Scans
          • 8.3.6.1 When the Optimizer Considers Index Skips Scans
          • 8.3.6.2 How Index Skip Scans Work
          • 8.3.6.3 Index Skip Scans: Example
        • 8.3.7 Index Join Scans
          • 8.3.7.1 When the Optimizer Considers Index Join Scans
          • 8.3.7.2 How Index Join Scans Work
          • 8.3.7.3 Index Join Scans: Example
      • 8.4 Bitmap Index Access Paths
        • 8.4.1 About Bitmap Index Access
          • 8.4.1.1 Differences Between Bitmap and B-Tree Indexes
          • 8.4.1.2 Purpose of Bitmap Indexes
          • 8.4.1.3 Bitmaps and Rowids
          • 8.4.1.4 Bitmap Join Indexes
          • 8.4.1.5 Bitmap Storage
        • 8.4.2 Bitmap Conversion to Rowid
          • 8.4.2.1 When the Optimizer Chooses Bitmap Conversion to Rowid
          • 8.4.2.2 How Bitmap Conversion to Rowid Works
          • 8.4.2.3 Bitmap Conversion to Rowid: Example
        • 8.4.3 Bitmap Index Single Value
          • 8.4.3.1 When the Optimizer Considers Bitmap Index Single Value
          • 8.4.3.2 How Bitmap Index Single Value Works
          • 8.4.3.3 Bitmap Index Single Value: Example
        • 8.4.4 Bitmap Index Range Scans
          • 8.4.4.1 When the Optimizer Considers Bitmap Index Range Scans
          • 8.4.4.2 How Bitmap Index Range Scans Work
          • 8.4.4.3 Bitmap Index Range Scans: Example
        • 8.4.5 Bitmap Merge
          • 8.4.5.1 When the Optimizer Considers Bitmap Merge
          • 8.4.5.2 How Bitmap Merge Works
          • 8.4.5.3 Bitmap Merge: Example
      • 8.5 Table Cluster Access Paths
        • 8.5.1 Cluster Scans
          • 8.5.1.1 When the Optimizer Considers Cluster Scans
          • 8.5.1.2 How a Cluster Scan Works
          • 8.5.1.3 Cluster Scans: Example
        • 8.5.2 Hash Scans
          • 8.5.2.1 When the Optimizer Considers a Hash Scan
          • 8.5.2.2 How a Hash Scan Works
          • 8.5.2.3 Hash Scans: Example
    • 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.1.1 When the Optimizer Considers Nested Loops Joins
          • 9.2.1.2 How Nested Loops Joins Work
          • 9.2.1.3 Nested Nested Loops
          • 9.2.1.4 Current Implementation for Nested Loops Joins
          • 9.2.1.5 Original Implementation for Nested Loops Joins
          • 9.2.1.6 Nested Loops Controls
        • 9.2.2 Hash Joins
          • 9.2.2.1 When the Optimizer Considers Hash Joins
          • 9.2.2.2 How Hash Joins Work
            • 9.2.2.2.1 Hash Tables
            • 9.2.2.2.2 Hash Join: Basic Steps
          • 9.2.2.3 How Hash Joins Work When the Hash Table Does Not Fit in the PGA
          • 9.2.2.4 Hash Join Controls
        • 9.2.3 Sort Merge Joins
          • 9.2.3.1 When the Optimizer Considers Sort Merge Joins
          • 9.2.3.2 How Sort Merge Joins Work
          • 9.2.3.3 Sort Merge Join Controls
      • 9.3 Join Types
        • 9.3.1 Inner Joins
          • 9.3.1.1 Equijoins
          • 9.3.1.2 Nonequijoins
          • 9.3.1.3 Band Joins
        • 9.3.2 Outer Joins
          • 9.3.2.1 Nested Loops Outer Joins
          • 9.3.2.2 Hash Join Outer Joins
          • 9.3.2.3 Sort Merge Outer Joins
          • 9.3.2.4 Full Outer Joins
          • 9.3.2.5 Multiple Tables on the Left of an Outer Join
        • 9.3.3 Semijoins
          • 9.3.3.1 When the Optimizer Considers Semijoins
          • 9.3.3.2 How Semijoins Work
        • 9.3.4 Antijoins
          • 9.3.4.1 When the Optimizer Considers Antijoins
          • 9.3.4.2 How Antijoins Work
          • 9.3.4.3 How Antijoins Handle Nulls
        • 9.3.5 Cartesian Joins
          • 9.3.5.1 When the Optimizer Considers Cartesian Joins
          • 9.3.5.2 How Cartesian Joins Work
          • 9.3.5.3 Cartesian Join Controls
      • 9.4 Join Optimizations
        • 9.4.1 Bloom Filters
          • 9.4.1.1 Purpose of Bloom Filters
          • 9.4.1.2 How Bloom Filters Work
          • 9.4.1.3 Bloom Filter Controls
          • 9.4.1.4 Bloom Filter Metadata
          • 9.4.1.5 Bloom Filters: Scenario
        • 9.4.2 Partition-Wise Joins
          • 9.4.2.1 Purpose of Partition-Wise Joins
          • 9.4.2.2 How Partition-Wise Joins Work
            • 9.4.2.2.1 How a Full Partition-Wise Join Works
            • 9.4.2.2.2 How a Partial Partition-Wise Join Works
        • 9.4.3 In-Memory Join Groups
  • 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.1.1 Permanent Table Statistics
          • 10.2.1.2 Temporary Table Statistics
            • 10.2.1.2.1 Types of Temporary Tables
            • 10.2.1.2.2 Statistics for Global Temporary Tables
            • 10.2.1.2.3 Shared and Session-Specific Statistics for Global Temporary Tables
        • 10.2.2 Column Statistics
        • 10.2.3 Index Statistics
          • 10.2.3.1 Types of Index Statistics
          • 10.2.3.2 Index Clustering Factor
          • 10.2.3.3 Effect of Index Clustering Factor on Cost: Example
        • 10.2.4 System Statistics
        • 10.2.5 User-Defined Optimizer Statistics
      • 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.3.1 Purpose of Real-Time Statistics
            • 10.3.3.3.2 How Real-Time Statistics Work
            • 10.3.3.3.3 User Interface for Real-Time Statistics
            • 10.3.3.3.4 Real-Time Statistics: Example
      • 10.4 When the Database Gathers Optimizer Statistics
        • 10.4.1 Sources for Optimizer Statistics
        • 10.4.2 SQL Plan Directives
          • 10.4.2.1 When the Database Creates SQL Plan Directives
          • 10.4.2.2 How the Database Uses SQL Plan Directives
          • 10.4.2.3 SQL Plan Directive Maintenance
          • 10.4.2.4 How the Optimizer Uses SQL Plan Directives: Example
          • 10.4.2.5 How the Optimizer Uses Extensions and SQL Plan Directives: Example
        • 10.4.3 When the Database Samples Data
        • 10.4.4 How the Database Samples Data
    • 11 Histograms
      • 11.1 Purpose of Histograms
      • 11.2 When Oracle Database Creates Histograms
      • 11.3 How Oracle Database Chooses the Histogram Type
      • 11.4 Cardinality Algorithms When Using Histograms
        • 11.4.1 Endpoint Numbers and Values
        • 11.4.2 Popular and Nonpopular Values
        • 11.4.3 Bucket Compression
      • 11.5 Frequency Histograms
        • 11.5.1 Criteria For Frequency Histograms
        • 11.5.2 Generating a Frequency Histogram
      • 11.6 Top Frequency Histograms
        • 11.6.1 Criteria For Top Frequency Histograms
        • 11.6.2 Generating a Top Frequency Histogram
      • 11.7 Height-Balanced Histograms (Legacy)
        • 11.7.1 Criteria for Height-Balanced Histograms
        • 11.7.2 Generating a Height-Balanced Histogram
      • 11.8 Hybrid Histograms
        • 11.8.1 How Endpoint Repeat Counts Work
        • 11.8.2 Criteria for Hybrid Histograms
        • 11.8.3 Generating a Hybrid Histogram
    • 12 Configuring Options for Optimizer Statistics Gathering
      • 12.1 About Optimizer Statistics Collection
        • 12.1.1 Purpose of Optimizer Statistics Collection
        • 12.1.2 User Interfaces for Optimizer Statistics Management
          • 12.1.2.1 Graphical Interface for Optimizer Statistics Management
            • 12.1.2.1.1 Accessing the Database Home Page in Cloud Control
            • 12.1.2.1.2 Accessing the Optimizer Statistics Console
          • 12.1.2.2 Command-Line Interface for Optimizer Statistics Management
      • 12.2 Setting Optimizer Statistics Preferences
        • 12.2.1 About Optimizer Statistics Preferences
          • 12.2.1.1 Purpose of Optimizer Statistics Preferences
          • 12.2.1.2 Examples of Statistics Preferences
          • 12.2.1.3 DBMS_STATS Procedures for Setting Statistics Preferences
          • 12.2.1.4 Statistics Preference Overrides
          • 12.2.1.5 Setting Statistics Preferences: Example
        • 12.2.2 Setting Global Optimizer Statistics Preferences Using Cloud Control
        • 12.2.3 Setting Object-Level Optimizer Statistics Preferences Using Cloud Control
        • 12.2.4 Setting Optimizer Statistics Preferences from the Command Line
      • 12.3 Configuring Options for Dynamic Statistics
        • 12.3.1 About Dynamic Statistics Levels
        • 12.3.2 Setting Dynamic Statistics Levels Manually
        • 12.3.3 Disabling Dynamic Statistics
      • 12.4 Managing SQL Plan Directives
    • 13 Gathering Optimizer Statistics
      • 13.1 Configuring Automatic Optimizer Statistics Collection
        • 13.1.1 About Automatic Optimizer Statistics Collection
        • 13.1.2 Configuring Automatic Optimizer Statistics Collection Using Cloud Control
        • 13.1.3 Configuring Automatic Optimizer Statistics Collection from the Command Line
      • 13.2 Configuring High-Frequency Automatic Optimizer Statistics Collection
        • 13.2.1 About High-Frequency Automatic Optimizer Statistics Collection
        • 13.2.2 Setting Preferences for High-Frequency Automatic Optimizer Statistics Collection
        • 13.2.3 High-Frequency Automatic Optimizer Statistics Collection: Example
      • 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.2.1 Guideline for Setting the Sample Size
          • 13.3.2.2 Guideline for Gathering Statistics in Parallel
          • 13.3.2.3 Guideline for Partitioned Objects
          • 13.3.2.4 Guideline for Frequently Changing Objects
          • 13.3.2.5 Guideline for External Tables
        • 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.7.1 About Concurrent Statistics Gathering
            • 13.3.7.1.1 How DBMS_STATS Gathers Statistics Concurrently
            • 13.3.7.1.2 Concurrent Statistics Gathering and Resource Management
          • 13.3.7.2 Enabling Concurrent Statistics Gathering
          • 13.3.7.3 Monitoring Statistics Gathering Operations
        • 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.2.1 Partition-Level Synopses
            • 13.3.8.2.2 NDV Algorithms: Adaptive Sampling and HyperLogLog
            • 13.3.8.2.3 Aggregation of Global Statistics Using Synopses: Example
          • 13.3.8.3 Gathering Statistics for a Partitioned Table: Basic Steps
            • 13.3.8.3.1 Considerations for Incremental Statistics Maintenance
            • 13.3.8.3.2 Enabling Incremental Statistics Using SET_TABLE_PREFS
            • 13.3.8.3.3 About the APPROXIMATE_NDV_ALGORITHM Settings
            • 13.3.8.3.4 Configuring Synopsis Generation: Examples
          • 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.4.1 About System Statistics
        • 13.4.2 Guidelines for Gathering System Statistics
        • 13.4.3 Gathering System Statistics with DBMS_STATS
          • 13.4.3.1 About the GATHER_SYSTEM_STATS Procedure
          • 13.4.3.2 Gathering Workload Statistics
            • 13.4.3.2.1 About Workload Statistics
            • 13.4.3.2.2 Starting and Stopping System Statistics Gathering
            • 13.4.3.2.3 Gathering System Statistics During a Specified Interval
          • 13.4.3.3 Gathering Noworkload Statistics
        • 13.4.4 Deleting System Statistics
      • 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.1.1 Why Column Group Statistics Are Needed: Example
          • 14.1.1.2 Automatic and Manual Column Group Statistics
          • 14.1.1.3 User Interface for Column Group Statistics
        • 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.2.1 About Expression Statistics
          • 14.2.1.1 When Expression Statistics Are Useful: Example
        • 14.2.2 Creating Expression Statistics
        • 14.2.3 Displaying Expression Statistics
        • 14.2.4 Dropping Expression Statistics
    • 15 Controlling the Use of Optimizer Statistics
      • 15.1 Locking and Unlocking Optimizer Statistics
        • 15.1.1 Locking Statistics
        • 15.1.2 Unlocking Statistics
      • 15.2 Publishing Pending Optimizer Statistics
        • 15.2.1 About Pending Optimizer Statistics
        • 15.2.2 User Interfaces for Publishing Optimizer Statistics
        • 15.2.3 Managing Published and Pending Statistics
      • 15.3 Creating Artificial Optimizer Statistics for Testing
        • 15.3.1 About Artificial Optimizer Statistics
        • 15.3.2 Setting Artificial Optimizer Statistics for a Table
        • 15.3.3 Setting Optimizer Statistics: Example
    • 16 Managing Historical Optimizer Statistics
      • 16.1 Restoring Optimizer Statistics
        • 16.1.1 About Restore Operations for Optimizer Statistics
        • 16.1.2 Guidelines for Restoring Optimizer Statistics
        • 16.1.3 Restrictions for Restoring Optimizer Statistics
        • 16.1.4 Restoring Optimizer Statistics Using DBMS_STATS
      • 16.2 Managing Optimizer Statistics Retention
        • 16.2.1 Obtaining Optimizer Statistics History
        • 16.2.2 Changing the Optimizer Statistics Retention Period
        • 16.2.3 Purging Optimizer Statistics
      • 16.3 Reporting on Past Statistics Gathering Operations
    • 17 Importing and Exporting Optimizer Statistics
      • 17.1 About Transporting Optimizer Statistics
        • 17.1.1 Purpose of Transporting Optimizer Statistics
        • 17.1.2 How Transporting Optimizer Statistics Works
        • 17.1.3 User Interface for Importing and Exporting Optimizer Statistics
      • 17.2 Transporting Optimizer Statistics to a Test Database: Tutorial
    • 18 Analyzing Statistics Using Optimizer Statistics Advisor
      • 18.1 About Optimizer Statistics Advisor
        • 18.1.1 Purpose of Optimizer Statistics Advisor
          • 18.1.1.1 Problems with a Traditional Script-Based Approach
          • 18.1.1.2 Advantages of Optimizer Statistics Advisor
        • 18.1.2 Optimizer Statistics Advisor Concepts
          • 18.1.2.1 Components of Optimizer Statistics Advisor
            • 18.1.2.1.1 Rules for Optimizer Statistics Advisor
            • 18.1.2.1.2 Findings for Optimizer Statistics Advisor
            • 18.1.2.1.3 Recommendations for Optimizer Statistics Advisor
            • 18.1.2.1.4 Actions for Optimizer Statistics Advisor
          • 18.1.2.2 Operational Modes for Optimizer Statistics Advisor
        • 18.1.3 Command-Line Interface to 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.3.1 About Filters for Optimizer Statistics Advisor
          • 18.2.3.2 Creating an Object Filter for an Optimizer Advisor Task
          • 18.2.3.3 Creating a Rule Filter for an Optimizer Advisor Task
          • 18.2.3.4 Creating an Operation Filter 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
          • 18.2.6.1 Implementing Actions Recommended by Optimizer Statistics Advisor
          • 18.2.6.2 Generating a Script Using Optimizer Statistics Advisor
  • Part VI Optimizer Controls
    • 19 Influencing the Optimizer
      • 19.1 Techniques for Influencing the Optimizer
      • 19.2 Influencing the Optimizer with Initialization Parameters
        • 19.2.1 About Optimizer Initialization Parameters
        • 19.2.2 Enabling Optimizer Features
        • 19.2.3 Choosing an Optimizer Goal
        • 19.2.4 Controlling Adaptive Optimization
      • 19.3 Influencing the Optimizer with Hints
        • 19.3.1 About Optimizer Hints
          • 19.3.1.1 Purpose of Hints
          • 19.3.1.2 Types of Hints
          • 19.3.1.3 Scope of Hints
        • 19.3.2 Guidelines for Join Order Hints
        • 19.3.3 Reporting on Hints
          • 19.3.3.1 Purpose of Hint Usage Reports
          • 19.3.3.2 User Interface for Hint Usage Reports
          • 19.3.3.3 Reporting on Hint Usage: Tutorial
          • 19.3.3.4 Hint Usage Reports: Examples
    • 20 Improving Real-World Performance Through Cursor Sharing
      • 20.1 Overview of Cursor Sharing
        • 20.1.1 About Cursors
          • 20.1.1.1 Private and Shared SQL Areas
          • 20.1.1.2 Parent and Child Cursors
            • 20.1.1.2.1 Parent Cursors and V$SQLAREA
            • 20.1.1.2.2 Child Cursors and V$SQL
            • 20.1.1.2.3 Cursor Mismatches and V$SQL_SHARED_CURSOR
        • 20.1.2 About Cursors and Parsing
        • 20.1.3 About Literals and Bind Variables
          • 20.1.3.1 Literals and Cursors
          • 20.1.3.2 Bind Variables and Cursors
          • 20.1.3.3 Bind Variable Peeking
        • 20.1.4 About the Life Cycle of Shared Cursors
          • 20.1.4.1 Cursor Marked Invalid
          • 20.1.4.2 Cursors Marked Rolling Invalid
      • 20.2 CURSOR_SHARING and Bind Variable Substitution
        • 20.2.1 CURSOR_SHARING Initialization Parameter
        • 20.2.2 Parsing Behavior When CURSOR_SHARING = FORCE
      • 20.3 Adaptive Cursor Sharing
        • 20.3.1 Purpose of Adaptive Cursor Sharing
        • 20.3.2 How Adaptive Cursor Sharing Works: Example
        • 20.3.3 Bind-Sensitive Cursors
        • 20.3.4 Bind-Aware Cursors
        • 20.3.5 Cursor Merging
        • 20.3.6 Adaptive Cursor Sharing Views
      • 20.4 Real-World Performance Guidelines for Cursor Sharing
        • 20.4.1 Develop Applications with Bind Variables for Security and Performance
        • 20.4.2 Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix
        • 20.4.3 Establish Coding Conventions to Increase Cursor Reuse
        • 20.4.4 Minimize Session-Level Changes to the Optimizer Environment
  • 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.4.1 Monitored SQL Executions Page in Cloud Control
            • 21.1.4.1.1 Accessing the Monitored SQL Executions Page
          • 21.1.4.2 DBMS_SQL_MONITOR Package
          • 21.1.4.3 Attributes of composite Database Operations
          • 21.1.4.4 MONITOR and NO_MONITOR Hints
          • 21.1.4.5 Views for Monitoring and Reporting on Database Operations
        • 21.1.5 Basic Tasks in Database Operations Monitoring
      • 21.2 Enabling and Disabling Monitoring of Database Operations
        • 21.2.1 Enabling Monitoring of Database Operations at the System Level
        • 21.2.2 Enabling and Disabling Monitoring of Database Operations at the Statement Level
      • 21.3 Defining a Composite Database Operation
      • 21.4 Generating and Accessing SQL Monitor Reports
      • 21.5 Monitoring Database Operations: Scenarios
        • 21.5.1 Reporting on a Simple Database Operation: Scenario
        • 21.5.2 Reporting on Composite Database Operation: Scenario
    • 22 Performing Application Tracing
      • 22.1 Overview of End-to-End Application Tracing
        • 22.1.1 Purpose of End-to-End Application Tracing
        • 22.1.2 End-to-End Application Tracing in a Multitenant Environment
        • 22.1.3 Tools for End-to-End Application Tracing
          • 22.1.3.1 Overview of the SQL Trace Facility
          • 22.1.3.2 Overview of TKPROF
      • 22.2 Enabling Statistics Gathering for End-to-End Tracing
        • 22.2.1 Enabling Statistics Gathering for a Client ID
        • 22.2.2 Enabling Statistics Gathering for Services, Modules, and Actions
      • 22.3 Enabling End-to-End Application Tracing
        • 22.3.1 Enabling Tracing for a Client Identifier
        • 22.3.2 Enabling Tracing for a Service, Module, and Action
        • 22.3.3 Enabling Tracing for a Session
        • 22.3.4 Enabling Tracing for the Instance or Database
      • 22.4 Generating Output Files Using SQL Trace and TKPROF
        • 22.4.1 Step 1: Setting Initialization Parameters for Trace File Management
        • 22.4.2 Step 2: Enabling the SQL Trace Facility
        • 22.4.3 Step 3: Generating Output Files with TKPROF
        • 22.4.4 Step 4: Storing SQL Trace Facility Statistics
          • 22.4.4.1 Generating the TKPROF Output SQL Script
          • 22.4.4.2 Editing the TKPROF Output SQL Script
          • 22.4.4.3 Querying the Output Table
      • 22.5 Guidelines for Interpreting TKPROF Output
        • 22.5.1 Guideline for Interpreting the Resolution of Statistics
        • 22.5.2 Guideline for Recursive SQL Statements
        • 22.5.3 Guideline for Deciding Which Statements to Tune
        • 22.5.4 Guidelines for Avoiding Traps in TKPROF Interpretation
          • 22.5.4.1 Guideline for Avoiding the Argument Trap
          • 22.5.4.2 Guideline for Avoiding the Read Consistency Trap
          • 22.5.4.3 Guideline for Avoiding the Schema Trap
          • 22.5.4.4 Guideline for Avoiding the Time Trap
      • 22.1 Application Tracing Utilities
        • 22.1.1 TRCSESS
          • 22.1.1.1 Purpose
          • 22.1.1.2 Guidelines
          • 22.1.1.3 Syntax
          • 22.1.1.4 Options
          • 22.1.1.5 Examples
        • 22.1.2 TKPROF
          • 22.1.2.1 Purpose
          • 22.1.2.2 Guidelines
          • 22.1.2.3 Syntax
          • 22.1.2.4 Options
          • 22.1.2.5 Output
            • 22.1.2.5.1 Identification of User Issuing the SQL Statement in TKPROF
            • 22.1.2.5.2 Tabular Statistics in TKPROF
            • 22.1.2.5.3 Library Cache Misses in TKPROF
            • 22.1.2.5.4 Row Source Operations in TKPROF
            • 22.1.2.5.5 Wait Event Information in TKPROF
          • 22.1.2.6 Examples
      • 22.1 Views for Application Tracing
        • 22.1.1 Views Relevant for Trace Statistics
        • 22.1.2 Views Related to Enabling Tracing
  • Part VIII Automatic SQL Tuning
    • 23 Managing SQL Tuning Sets
      • 23.1 About SQL Tuning Sets
        • 23.1.1 Purpose of SQL Tuning Sets
        • 23.1.2 Concepts for SQL Tuning Sets
        • 23.1.3 User Interfaces for SQL Tuning Sets
          • 23.1.3.1 Accessing the SQL Tuning Sets Page in Cloud Control
          • 23.1.3.2 Command-Line Interface to SQL Tuning Sets
        • 23.1.4 Basic Tasks for SQL Tuning Sets
      • 23.2 Creating a SQL Tuning Set
      • 23.3 Loading a SQL Tuning Set
      • 23.4 Displaying the Contents of a SQL Tuning Set
      • 23.5 Modifying a SQL Tuning Set
      • 23.6 Transporting a SQL Tuning Set
        • 23.6.1 About Transporting SQL Tuning Sets
          • 23.6.1.1 Basic Steps for Transporting SQL Tuning Sets
          • 23.6.1.2 Basic Steps for Transporting SQL Tuning Sets When the CON_DBID Values Differ
        • 23.6.2 Transporting SQL Tuning Sets with DBMS_SQLTUNE
      • 23.7 Dropping a SQL Tuning Set
    • 24 Analyzing SQL with SQL Tuning Advisor
      • 24.1 About SQL Tuning Advisor
        • 24.1.1 Purpose of SQL Tuning Advisor
        • 24.1.2 SQL Tuning Advisor Architecture
          • 24.1.2.1 Input to SQL Tuning Advisor
          • 24.1.2.2 Output of SQL Tuning Advisor
          • 24.1.2.3 Automatic Tuning Optimizer Analyses
            • 24.1.2.3.1 Statistical Analysis
            • 24.1.2.3.2 SQL Profiling
              • 24.1.2.3.2.1 How SQL Profiling Works
              • 24.1.2.3.2.2 SQL Profile Implementation
            • 24.1.2.3.3 Access Path Analysis
            • 24.1.2.3.4 SQL Structural Analysis
            • 24.1.2.3.5 Alternative Plan Analysis
        • 24.1.3 SQL Tuning Advisor Operation
          • 24.1.3.1 Automatic and On-Demand SQL Tuning
          • 24.1.3.2 Local and Remote SQL Tuning
      • 24.2 Managing the Automatic SQL Tuning Task
        • 24.2.1 About the Automatic SQL Tuning Task
          • 24.2.1.1 Purpose of Automatic SQL Tuning
          • 24.2.1.2 Automatic SQL Tuning Concepts
          • 24.2.1.3 Command-Line Interface to SQL Tuning Advisor
          • 24.2.1.4 Basic Tasks for Automatic SQL Tuning
        • 24.2.2 Enabling and Disabling the Automatic SQL Tuning Task
          • 24.2.2.1 Enabling and Disabling the Automatic SQL Tuning Task Using Cloud Control
          • 24.2.2.2 Enabling and Disabling the Automatic SQL Tuning Task from the Command Line
        • 24.2.3 Configuring the Automatic SQL Tuning Task
          • 24.2.3.1 Configuring the Automatic SQL Tuning Task Using Cloud Control
          • 24.2.3.2 Configuring the Automatic SQL Tuning Task Using the Command Line
        • 24.2.4 Viewing Automatic SQL Tuning Reports
          • 24.2.4.1 Viewing Automatic SQL Tuning Reports Using the Command Line
      • 24.3 Running SQL Tuning Advisor On Demand
        • 24.3.1 About On-Demand SQL Tuning
          • 24.3.1.1 Purpose of On-Demand SQL Tuning
          • 24.3.1.2 User Interfaces for On-Demand SQL Tuning
            • 24.3.1.2.1 Accessing the SQL Tuning Advisor Using Cloud Control
            • 24.3.1.2.2 Command-Line Interface to On-Demand SQL Tuning
          • 24.3.1.3 Basic Tasks in On-Demand SQL Tuning
        • 24.3.2 Creating a SQL Tuning Task
        • 24.3.3 Configuring a SQL Tuning Task
        • 24.3.4 Executing a SQL Tuning Task
        • 24.3.5 Monitoring a SQL Tuning Task
        • 24.3.6 Displaying the Results of a SQL Tuning Task
    • 25 Optimizing Access Paths with SQL Access Advisor
      • 25.1 About SQL Access Advisor
        • 25.1.1 Purpose of SQL Access Advisor
        • 25.1.2 SQL Access Advisor Architecture
          • 25.1.2.1 Input to SQL Access Advisor
          • 25.1.2.2 Filter Options for SQL Access Advisor
          • 25.1.2.3 SQL Access Advisor Recommendations
          • 25.1.2.4 SQL Access Advisor Actions
            • 25.1.2.4.1 Types of Actions
            • 25.1.2.4.2 Guidelines for Interpreting Partitioning Recommendations
          • 25.1.2.5 SQL Access Advisor Repository
        • 25.1.3 User Interfaces for SQL Access Advisor
          • 25.1.3.1 Accessing the SQL Access Advisor: Initial Options Page Using Cloud Control
          • 25.1.3.2 Command-Line Interface to SQL Tuning Sets
      • 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.4.1 Interrupting SQL Access Advisor Tasks
          • 25.4.4.2 Canceling SQL Access Advisor Tasks
        • 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
        • 25.6.1 Action Attributes in the DBA_ADVISOR_ACTIONS View
        • 25.6.2 Categories for SQL Access Advisor Task Parameters
        • 25.6.3 SQL Access Advisor Constants
  • Part IX SQL Management Objects
    • 26 Managing SQL Profiles
      • 26.1 About SQL Profiles
        • 26.1.1 Purpose of SQL Profiles
        • 26.1.2 Concepts for SQL Profiles
          • 26.1.2.1 Statistics in SQL Profiles
          • 26.1.2.2 SQL Profiles and Execution Plans
          • 26.1.2.3 SQL Profile Recommendations
          • 26.1.2.4 SQL Profiles and SQL Plan Baselines
        • 26.1.3 User Interfaces for SQL Profiles
        • 26.1.4 Basic Tasks for SQL Profiles
      • 26.2 Implementing a SQL Profile
        • 26.2.1 About SQL Profile Implementation
        • 26.2.2 Implementing a SQL Profile
      • 26.3 Listing SQL Profiles
      • 26.4 Altering a SQL Profile
      • 26.5 Dropping a SQL Profile
      • 26.6 Transporting a SQL Profile
    • 27 Overview of SQL Plan Management
      • 27.1 About SQL Plan Baselines
      • 27.2 Purpose of SQL Plan Management
        • 27.2.1 Benefits of SQL Plan Management
        • 27.2.2 Differences Between SQL Plan Baselines and SQL Profiles
      • 27.3 Plan Capture
        • 27.3.1 Automatic Initial Plan Capture
          • 27.3.1.1 Eligibility for Automatic Initial Plan Capture
          • 27.3.1.2 Plan Matching for Automatic Initial Plan Capture
        • 27.3.2 Manual Plan Capture
      • 27.4 Plan Selection
      • 27.5 Plan Evolution
        • 27.5.1 Purpose of Plan Evolution
        • 27.5.2 How Plan Evolution Works
        • 27.5.3 PL/SQL Subprograms for Plan Evolution
      • 27.6 Storage Architecture for SQL Plan Management
        • 27.6.1 SQL Management Base
        • 27.6.2 SQL Statement Log
        • 27.6.3 SQL Plan History
          • 27.6.3.1 Enabled Plans
          • 27.6.3.2 Accepted Plans
          • 27.6.3.3 Fixed Plans
    • 28 Managing SQL Plan Baselines
      • 28.1 About Managing SQL Plan Baselines
        • 28.1.1 User Interfaces for SQL Plan Management
          • 28.1.1.1 Accessing the SQL Plan Baseline Page in Cloud Control
          • 28.1.1.2 DBMS_SPM Package
        • 28.1.2 Basic Tasks in SQL Plan Management
      • 28.2 Configuring SQL Plan Management
        • 28.2.1 Configuring the Capture and Use of SQL Plan Baselines
          • 28.2.1.1 Enabling Automatic Initial Plan Capture for SQL Plan Management
          • 28.2.1.2 Configuring Filters for Automatic Plan Capture
          • 28.2.1.3 Disabling All SQL Plan Baselines
        • 28.2.2 Managing the SPM Evolve Advisor Task
          • 28.2.2.1 About the SPM Evolve Advisor Task
          • 28.2.2.2 Enabling and Disabling the SPM Evolve Advisor Task
          • 28.2.2.3 Configuring the Automatic SPM Evolve Advisor Task
      • 28.3 Displaying Plans in a SQL Plan Baseline
      • 28.4 Loading SQL Plan Baselines
        • 28.4.1 About Loading SQL Plan Baselines
        • 28.4.2 Loading Plans from AWR
        • 28.4.3 Loading Plans from the Shared SQL Area
        • 28.4.4 Loading Plans from a SQL Tuning Set
        • 28.4.5 Loading Plans from a Staging Table
      • 28.5 Evolving SQL Plan Baselines Manually
        • 28.5.1 About the DBMS_SPM Evolve Functions
        • 28.5.2 Managing an Evolve Task
      • 28.6 Dropping SQL Plan Baselines
      • 28.7 Managing the SQL Management Base
        • 28.7.1 About Managing the SMB
        • 28.7.2 Changing the Disk Space Limit for the SMB
        • 28.7.3 Changing the Plan Retention Policy in the SMB
    • 29 Migrating Stored Outlines to SQL Plan Baselines
      • 29.1 About Stored Outline Migration
        • 29.1.1 Purpose of Stored Outline Migration
        • 29.1.2 How Stored Outline Migration Works
          • 29.1.2.1 Stages of Stored Outline Migration
          • 29.1.2.2 Outline Categories and Baseline Modules
        • 29.1.3 User Interface for Stored Outline Migration
        • 29.1.4 Basic Steps in Stored Outline Migration
      • 29.2 Preparing for Stored Outline Migration
      • 29.3 Migrating Outlines to Utilize SQL Plan Management Features
      • 29.4 Migrating Outlines to Preserve Stored Outline Behavior
      • 29.5 Performing Follow-Up Tasks After Stored Outline Migration
  • Glossary
  • Index

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. SQL Tuning Guide
  2. Query Execution Plans

Part III Query Execution Plans

If a query has suboptimal performance, the execution plan is the key tool for understanding the problem and supplying a solution.

This part contains the following chapters:

  • Explaining and Displaying Execution Plans
    Knowledge of how to explain a statement and display its plan is essential to SQL tuning.
  • PLAN_TABLE Reference
    This chapter describes PLAN_TABLE columns.
  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights
  • Cookie Preferences
Copyright © 2013, 2019, Oracle and/or its affiliates. All rights reserved.
  • Previous
  • Next
Video