Table of 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 Release 18c, Version 18.1
    • New Features
  • Changes in Oracle Database 12c Release 2 (12.2.0.1)
    • New Features
    • Desupported Features
    • Other Changes
  • 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
    • 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 SQL Plan Management
          • 1.4.2.1.5 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 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 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 (VECTOR GROUP BY)
    • 5.8 Cursor-Duration Temporary Tables
      • 5.8.1 Purpose of Cursor-Duration Temporary Tables
      • 5.8.2 How Cursor-Duration Temporary Tables Work
      • 5.8.3 Cursor-Duration Temporary Tables: Example
    • 5.9 Table Expansion
      • 5.9.1 Purpose of Table Expansion
      • 5.9.2 How Table Expansion Works
      • 5.9.3 Table Expansion: Scenario
      • 5.9.4 Table Expansion and Star Transformation: Scenario
    • 5.10 Join Factorization
      • 5.10.1 Purpose of Join Factorization
      • 5.10.2 How Join Factorization Works
      • 5.10.3 Factorization and Join Orders: Scenario
      • 5.10.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.2.1 Different Schemas
        • 6.2.2.2 Different Costs
      • 6.2.3 Guideline for Minimizing Throw-Away
      • 6.2.4 Guidelines for Evaluating Execution Plans Using EXPLAIN PLAN
        • 6.2.4.1 Guidelines for Evaluating Plans Using the V$SQL_PLAN Views
      • 6.2.5 EXPLAIN PLAN Restrictions
      • 6.2.6 Guidelines for Creating PLAN_TABLE
    • 6.3 Generating Plan Output Using the EXPLAIN PLAN Statement
      • 6.3.1 Executing EXPLAIN PLAN for a Single Statement
      • 6.3.2 Executing EXPLAIN PLAN Using a Statement ID
      • 6.3.3 Directing EXPLAIN PLAN Output to a Nondefault Table
    • 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 Query Plans
      • 7.2.2 Viewing Parallel Execution with EXPLAIN PLAN
        • 7.2.2.1 About EXPLAIN PLAN and Parallel Queries
        • 7.2.2.2 Viewing Parallel Queries with EXPLAIN PLAN: Example
      • 7.2.3 Viewing Bitmap Indexes with EXPLAIN PLAN
      • 7.2.4 Viewing Result Cache with EXPLAIN PLAN
      • 7.2.5 Viewing Partitioned Objects with EXPLAIN PLAN
        • 7.2.5.1 Displaying Range and Hash Partitioning with EXPLAIN PLAN: Examples
        • 7.2.5.2 Pruning Information with Composite Partitioned Objects: Examples
        • 7.2.5.3 Examples of Partial Partition-Wise Joins
        • 7.2.5.4 Example of Full Partition-Wise Join
        • 7.2.5.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
          • 7.2.5.5.1 When the IN-List Column is an Index Column: Example
          • 7.2.5.5.2 When the IN-List Column is an Index and a Partition Column: Example
          • 7.2.5.5.3 When the IN-List Column is a Partition Column: Example
        • 7.2.5.6 Example of Domain Indexes and 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 Display Functions
• 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 for Bulk Loads
        • 10.3.3.1 Purpose of Online Statistics Gathering for Bulk Loads
        • 10.3.3.2 Global Statistics During Inserts into Empty Partitioned Tables
        • 10.3.3.3 Index Statistics and Histograms During Bulk Loads
        • 10.3.3.4 Restrictions for Online Statistics Gathering for Bulk Loads
        • 10.3.3.5 Hints for Online Statistics Gathering for Bulk Loads
    • 10.4 When the Database Gathers Optimizer Statistics
      • 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 Gathering Optimizer Statistics Manually
      • 13.2.1 About Manual Statistics Collection with DBMS_STATS
      • 13.2.2 Guidelines for Gathering Optimizer Statistics Manually
        • 13.2.2.1 Guideline for Setting the Sample Size
        • 13.2.2.2 Guideline for Gathering Statistics in Parallel
        • 13.2.2.3 Guideline for Partitioned Objects
        • 13.2.2.4 Guideline for Frequently Changing Objects
        • 13.2.2.5 Guideline for External Tables
      • 13.2.3 Determining When Optimizer Statistics Are Stale
      • 13.2.4 Gathering Schema and Table Statistics
      • 13.2.5 Gathering Statistics for Fixed Objects
      • 13.2.6 Gathering Statistics for Volatile Tables Using Dynamic Statistics
      • 13.2.7 Gathering Optimizer Statistics Concurrently
        • 13.2.7.1 About Concurrent Statistics Gathering
          • 13.2.7.1.1 How DBMS_STATS Gathers Statistics Concurrently
          • 13.2.7.1.2 Concurrent Statistics Gathering and Resource Management
        • 13.2.7.2 Enabling Concurrent Statistics Gathering
        • 13.2.7.3 Monitoring Statistics Gathering Operations
      • 13.2.8 Gathering Incremental Statistics on Partitioned Objects
        • 13.2.8.1 Purpose of Incremental Statistics
        • 13.2.8.2 How DBMS_STATS Derives Global Statistics for Partitioned tables
          • 13.2.8.2.1 Partition-Level Synopses
          • 13.2.8.2.2 NDV Algorithms: Adaptive Sampling and HyperLogLog
          • 13.2.8.2.3 Aggregation of Global Statistics Using Synopses: Example
        • 13.2.8.3 Gathering Statistics for a Partitioned Table: Basic Steps
          • 13.2.8.3.1 Considerations for Incremental Statistics Maintenance
          • 13.2.8.3.2 Enabling Incremental Statistics Using SET_TABLE_PREFS
          • 13.2.8.3.3 About the APPROXIMATE_NDV_ALGORITHM Settings
          • 13.2.8.3.4 Configuring Synopsis Generation: Examples
        • 13.2.8.4 Maintaining Incremental Statistics for Partition Maintenance Operations
        • 13.2.8.5 Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics
    • 13.3 Gathering System Statistics Manually
      • 13.3.1 About System Statistics
      • 13.3.2 Guidelines for Gathering System Statistics
      • 13.3.3 Gathering System Statistics with DBMS_STATS
        • 13.3.3.1 About the GATHER_SYSTEM_STATS Procedure
        • 13.3.3.2 Gathering Workload Statistics
          • 13.3.3.2.1 About Workload Statistics
          • 13.3.3.2.2 Starting and Stopping System Statistics Gathering
          • 13.3.3.2.3 Gathering System Statistics During a Specified Interval
        • 13.3.3.3 Gathering Noworkload Statistics
      • 13.3.4 Deleting System Statistics
    • 13.4 Running Statistics Gathering Functions in Reporting Mode
  • 14 Managing Extended Statistics
    • 14.1 Managing Column Group Statistics
      • 14.1.1 About Statistics on Column Groups
        • 14.1.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.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 Types of Hints
        • 19.3.1.2 Scope of Hints
        • 19.3.1.3 Guidelines for Hints
      • 19.3.2 Guidelines for Join Order Hints
  • 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 Purpose of Monitoring Database Operations
      • 21.1.2 Database Operation Monitoring Concepts
        • 21.1.2.1 About the Architecture of Real-Time SQL Monitoring
        • 21.1.2.2 When the Database Monitors Operations
          • 21.1.2.2.1 Simple Database Operations
          • 21.1.2.2.2 Composite Database Operations
        • 21.1.2.3 Attributes of composite Database Operations
      • 21.1.3 User Interfaces for Database Operations Monitoring
        • 21.1.3.1 Monitored SQL Executions Page in Cloud Control
          • 21.1.3.1.1 Accessing the Monitored SQL Executions Page
        • 21.1.3.2 DBMS_SQL_MONITOR Package
        • 21.1.3.3 Views for Monitoring and Reporting on Database Operations
      • 21.1.4 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 Monitoring SQL Executions Using Cloud Control
    • 21.5 Monitoring Database Operations: Scenarios
      • 21.5.1 Reporting on a Simple Operation: Scenario
      • 21.5.2 Reporting on a Composite Operation: Scenario
  • 22 Gathering Diagnostic Data with SQL Test Case Builder
    • 22.1 Purpose of SQL Test Case Builder
    • 22.2 Concepts for SQL Test Case Builder
      • 22.2.1 SQL Incidents
      • 22.2.2 What SQL Test Case Builder Captures
      • 22.2.3 Output of SQL Test Case Builder
    • 22.3 User Interfaces for SQL Test Case Builder
      • 22.3.1 Graphical Interface for SQL Test Case Builder
        • 22.3.1.1 Accessing the Incident Manager
        • 22.3.1.2 Accessing the Support Workbench
      • 22.3.2 Command-Line Interface for SQL Test Case Builder
    • 22.4 Running SQL Test Case Builder
  • 23 Performing Application Tracing
    • 23.1 Overview of End-to-End Application Tracing
      • 23.1.1 Purpose of End-to-End Application Tracing
      • 23.1.2 End-to-End Application Tracing in a Multitenant Environment
      • 23.1.3 Tools for End-to-End Application Tracing
        • 23.1.3.1 Overview of the SQL Trace Facility
        • 23.1.3.2 Overview of TKPROF
    • 23.2 Enabling Statistics Gathering for End-to-End Tracing
      • 23.2.1 Enabling Statistics Gathering for a Client ID
      • 23.2.2 Enabling Statistics Gathering for Services, Modules, and Actions
    • 23.3 Enabling End-to-End Application Tracing
      • 23.3.1 Enabling Tracing for a Client Identifier
      • 23.3.2 Enabling Tracing for a Service, Module, and Action
      • 23.3.3 Enabling Tracing for a Session
      • 23.3.4 Enabling Tracing for an Instance or Database
    • 23.4 Generating Output Files Using SQL Trace and TKPROF
      • 23.4.1 Step 1: Setting Initialization Parameters for Trace File Management
      • 23.4.2 Step 2: Enabling the SQL Trace Facility
      • 23.4.3 Step 3: Generating Output Files with TKPROF
      • 23.4.4 Step 4: Storing SQL Trace Facility Statistics
        • 23.4.4.1 Generating the TKPROF Output SQL Script
        • 23.4.4.2 Editing the TKPROF Output SQL Script
        • 23.4.4.3 Querying the Output Table
    • 23.5 Guidelines for Interpreting TKPROF Output
      • 23.5.1 Guideline for Interpreting the Resolution of Statistics
      • 23.5.2 Guideline for Recursive SQL Statements
      • 23.5.3 Guideline for Deciding Which Statements to Tune
      • 23.5.4 Guidelines for Avoiding Traps in TKPROF Interpretation
        • 23.5.4.1 Guideline for Avoiding the Argument Trap
        • 23.5.4.2 Guideline for Avoiding the Read Consistency Trap
        • 23.5.4.3 Guideline for Avoiding the Schema Trap
        • 23.5.4.4 Guideline for Avoiding the Time Trap
    • 23.1 Application Tracing Utilities
      • 23.1.1 TRCSESS
        • 23.1.1.1 Purpose
        • 23.1.1.2 Guidelines
        • 23.1.1.3 Syntax
        • 23.1.1.4 Options
        • 23.1.1.5 Examples
      • 23.1.2 TKPROF
        • 23.1.2.1 Purpose
        • 23.1.2.2 Guidelines
        • 23.1.2.3 Syntax
        • 23.1.2.4 Options
        • 23.1.2.5 Output
          • 23.1.2.5.1 Identification of User Issuing the SQL Statement in TKPROF
          • 23.1.2.5.2 Tabular Statistics in TKPROF
          • 23.1.2.5.3 Library Cache Misses in TKPROF
          • 23.1.2.5.4 Row Source Operations in TKPROF
          • 23.1.2.5.5 Wait Event Information in TKPROF
        • 23.1.2.6 Examples
    • 23.1 Views for Application Tracing
      • 23.1.1 Views Relevant for Trace Statistics
      • 23.1.2 Views Related to Enabling Tracing
• Part VIII Automatic SQL Tuning
  • 24 Managing SQL Tuning Sets
    • 24.1 About SQL Tuning Sets
      • 24.1.1 Purpose of SQL Tuning Sets
      • 24.1.2 Concepts for SQL Tuning Sets
      • 24.1.3 User Interfaces for SQL Tuning Sets
        • 24.1.3.1 Accessing the SQL Tuning Sets Page in Cloud Control
        • 24.1.3.2 Command-Line Interface to SQL Tuning Sets
      • 24.1.4 Basic Tasks for Managing SQL Tuning Sets
    • 24.2 Creating a SQL Tuning Set Using CREATE_SQLSET
    • 24.3 Loading a SQL Tuning Set Using LOAD_SQLSET
    • 24.4 Querying a SQL Tuning Set
    • 24.5 Modifying a SQL Tuning Set Using UPDATE_SQLSET
    • 24.6 Transporting a SQL Tuning Set
      • 24.6.1 About Transporting SQL Tuning Sets
        • 24.6.1.1 Basic Steps for Transporting SQL Tuning Sets
        • 24.6.1.2 Basic Steps for Transporting SQL Tuning Sets When the CON_DBID Values Differ
      • 24.6.2 Transporting SQL Tuning Sets with DBMS_SQLTUNE
    • 24.7 Dropping a SQL Tuning Set Using DROP_SQLSET
  • 25 Analyzing SQL with SQL Tuning Advisor
    • 25.1 About SQL Tuning Advisor
      • 25.1.1 Purpose of SQL Tuning Advisor
      • 25.1.2 SQL Tuning Advisor Architecture
        • 25.1.2.1 Input to SQL Tuning Advisor
        • 25.1.2.2 Output of SQL Tuning Advisor
        • 25.1.2.3 Automatic Tuning Optimizer Analyses
          • 25.1.2.3.1 Statistical Analysis
          • 25.1.2.3.2 SQL Profiling
            • 25.1.2.3.2.1 How SQL Profiling Works
            • 25.1.2.3.2.2 SQL Profile Implementation
          • 25.1.2.3.3 Access Path Analysis
          • 25.1.2.3.4 SQL Structural Analysis
          • 25.1.2.3.5 Alternative Plan Analysis
      • 25.1.3 SQL Tuning Advisor Operation
        • 25.1.3.1 Automatic and On-Demand SQL Tuning
        • 25.1.3.2 Local and Remote SQL Tuning
    • 25.2 Managing the Automatic SQL Tuning Task
      • 25.2.1 About the Automatic SQL Tuning Task
        • 25.2.1.1 Purpose of Automatic SQL Tuning
        • 25.2.1.2 Automatic SQL Tuning Concepts
        • 25.2.1.3 Command-Line Interface to SQL Tuning Advisor
        • 25.2.1.4 Basic Tasks for Automatic SQL Tuning
      • 25.2.2 Enabling and Disabling the Automatic SQL Tuning Task
        • 25.2.2.1 Enabling and Disabling the Automatic SQL Tuning Task Using Cloud Control
        • 25.2.2.2 Enabling and Disabling the Automatic SQL Tuning Task from the Command Line
      • 25.2.3 Configuring the Automatic SQL Tuning Task
        • 25.2.3.1 Configuring the Automatic SQL Tuning Task Using Cloud Control
        • 25.2.3.2 Configuring the Automatic SQL Tuning Task Using the Command Line
      • 25.2.4 Viewing Automatic SQL Tuning Reports
        • 25.2.4.1 Viewing Automatic SQL Tuning Reports Using the Command Line
    • 25.3 Running SQL Tuning Advisor On Demand
      • 25.3.1 About On-Demand SQL Tuning
        • 25.3.1.1 Purpose of On-Demand SQL Tuning
        • 25.3.1.2 User Interfaces for On-Demand SQL Tuning
          • 25.3.1.2.1 Accessing the SQL Tuning Advisor Using Cloud Control
          • 25.3.1.2.2 Command-Line Interface to On-Demand SQL Tuning
        • 25.3.1.3 Basic Tasks in On-Demand SQL Tuning
      • 25.3.2 Creating a SQL Tuning Task
      • 25.3.3 Configuring a SQL Tuning Task
      • 25.3.4 Executing a SQL Tuning Task
      • 25.3.5 Monitoring a SQL Tuning Task
      • 25.3.6 Displaying the Results of a SQL Tuning Task
  • 26 Optimizing Access Paths with SQL Access Advisor
    • 26.1 About SQL Access Advisor
      • 26.1.1 Purpose of SQL Access Advisor
      • 26.1.2 SQL Access Advisor Architecture
        • 26.1.2.1 Input to SQL Access Advisor
        • 26.1.2.2 Filter Options for SQL Access Advisor
        • 26.1.2.3 SQL Access Advisor Recommendations
        • 26.1.2.4 SQL Access Advisor Actions
          • 26.1.2.4.1 Types of Actions
          • 26.1.2.4.2 Guidelines for Interpreting Partitioning Recommendations
        • 26.1.2.5 SQL Access Advisor Repository
      • 26.1.3 User Interfaces for SQL Access Advisor
        • 26.1.3.1 Accessing the SQL Access Advisor: Initial Options Page Using Cloud Control
        • 26.1.3.2 Command-Line Interface to SQL Tuning Sets
    • 26.2 Using SQL Access Advisor: Basic Tasks
      • 26.2.1 Creating a SQL Tuning Set as Input for SQL Access Advisor
      • 26.2.2 Populating a SQL Tuning Set with a User-Defined Workload
      • 26.2.3 Creating and Configuring a SQL Access Advisor Task
      • 26.2.4 Executing a SQL Access Advisor Task
      • 26.2.5 Viewing SQL Access Advisor Task Results
      • 26.2.6 Generating and Executing a Task Script
    • 26.3 Performing a SQL Access Advisor Quick Tune
    • 26.4 Using SQL Access Advisor: Advanced Tasks
      • 26.4.1 Evaluating Existing Access Structures
      • 26.4.2 Updating SQL Access Advisor Task Attributes
      • 26.4.3 Creating and Using SQL Access Advisor Task Templates
      • 26.4.4 Terminating SQL Access Advisor Task Execution
        • 26.4.4.1 Interrupting SQL Access Advisor Tasks
        • 26.4.4.2 Canceling SQL Access Advisor Tasks
      • 26.4.5 Deleting SQL Access Advisor Tasks
      • 26.4.6 Marking SQL Access Advisor Recommendations
      • 26.4.7 Modifying SQL Access Advisor Recommendations
    • 26.5 SQL Access Advisor Examples
    • 26.6 SQL Access Advisor Reference
      • 26.6.1 Action Attributes in the DBA_ADVISOR_ACTIONS View
      • 26.6.2 Categories for SQL Access Advisor Task Parameters
      • 26.6.3 SQL Access Advisor Constants
• Part IX SQL Management Objects
  • 27 Managing SQL Profiles
    • 27.1 About SQL Profiles
      • 27.1.1 Purpose of SQL Profiles
      • 27.1.2 Concepts for SQL Profiles
        • 27.1.2.1 Statistics in SQL Profiles
        • 27.1.2.2 SQL Profiles and Execution Plans
        • 27.1.2.3 SQL Profile Recommendations
        • 27.1.2.4 SQL Profiles and SQL Plan Baselines
      • 27.1.3 User Interfaces for SQL Profiles
      • 27.1.4 Basic Tasks for SQL Profiles
    • 27.2 Implementing a SQL Profile
      • 27.2.1 About SQL Profile Implementation
      • 27.2.2 Implementing a SQL Profile
    • 27.3 Listing SQL Profiles
    • 27.4 Altering a SQL Profile
    • 27.5 Dropping a SQL Profile
    • 27.6 Transporting a SQL Profile
  • 28 Overview of SQL Plan Management
    • 28.1 Purpose of SQL Plan Management
      • 28.1.1 Benefits of SQL Plan Management
      • 28.1.2 Differences Between SQL Plan Baselines and SQL Profiles
    • 28.2 Plan Capture
      • 28.2.1 Automatic Initial Plan Capture
        • 28.2.1.1 Eligibility for Automatic Initial Plan Capture
        • 28.2.1.2 Plan Matching for Automatic Initial Plan Capture
      • 28.2.2 Manual Plan Capture
    • 28.3 Plan Selection
    • 28.4 Plan Evolution
      • 28.4.1 Purpose of Plan Evolution
      • 28.4.2 PL/SQL Subprograms for Plan Evolution
    • 28.5 Storage Architecture for SQL Plan Management
      • 28.5.1 SQL Management Base
      • 28.5.2 SQL Statement Log
      • 28.5.3 SQL Plan History
        • 28.5.3.1 Enabled Plans
        • 28.5.3.2 Accepted Plans
        • 28.5.3.3 Fixed Plans
  • 29 Managing SQL Plan Baselines
    • 29.1 About Managing SQL Plan Baselines
      • 29.1.1 User Interfaces for SQL Plan Management
        • 29.1.1.1 Accessing the SQL Plan Baseline Page in Cloud Control
        • 29.1.1.2 DBMS_SPM Package
      • 29.1.2 Basic Tasks in SQL Plan Management
    • 29.2 Configuring SQL Plan Management
      • 29.2.1 Configuring the Capture and Use of SQL Plan Baselines
        • 29.2.1.1 Enabling Automatic Initial Plan Capture for SQL Plan Management
        • 29.2.1.2 Configuring Filters for Automatic Plan Capture
        • 29.2.1.3 Disabling All SQL Plan Baselines
      • 29.2.2 Managing the SPM Evolve Advisor Task
        • 29.2.2.1 About the SPM Evolve Advisor Task
        • 29.2.2.2 Enabling and Disabling the Automatic SPM Evolve Advisor Task
        • 29.2.2.3 Configuring the Automatic SPM Evolve Advisor Task
    • 29.3 Displaying Plans in a SQL Plan Baseline
    • 29.4 Loading SQL Plan Baselines
      • 29.4.1 About Loading SQL Plan Baselines
      • 29.4.2 Loading Plans from AWR
      • 29.4.3 Loading Plans from the Shared SQL Area
      • 29.4.4 Loading Plans from a SQL Tuning Set
      • 29.4.5 Loading Plans from a Staging Table
    • 29.5 Evolving SQL Plan Baselines Manually
      • 29.5.1 About the DBMS_SPM Evolve Functions
      • 29.5.2 Managing an Evolve Task
    • 29.6 Dropping SQL Plan Baselines
    • 29.7 Managing the SQL Management Base
      • 29.7.1 About Managing the SMB
      • 29.7.2 Changing the Disk Space Limit for the SMB
      • 29.7.3 Changing the Plan Retention Policy in the SMB
  • 30 Migrating Stored Outlines to SQL Plan Baselines
    • 30.1 About Stored Outline Migration
      • 30.1.1 Purpose of Stored Outline Migration
      • 30.1.2 How Stored Outline Migration Works
        • 30.1.2.1 Stages of Stored Outline Migration
        • 30.1.2.2 Outline Categories and Baseline Modules
      • 30.1.3 User Interface for Stored Outline Migration
      • 30.1.4 Basic Steps in Stored Outline Migration
    • 30.2 Preparing for Stored Outline Migration
    • 30.3 Migrating Outlines to Utilize SQL Plan Management Features
    • 30.4 Migrating Outlines to Preserve Stored Outline Behavior
    • 30.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.4.1 Guidelines for Choosing Keys for Composite Indexes
      • A.1.4.2 Guidelines for Ordering Keys for 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 Avoiding Index Serialization on a Sequence-Generated Key
    • A.1.8 Guidelines for Re-Creating Indexes
    • A.1.9 Guidelines for Compacting Indexes
    • A.1.10 Guidelines for Using Nonunique Indexes to Enforce Uniqueness
    • A.1.11 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