SQL Tuning Guide
User Manual:
Open the PDF directly: View PDF 
Page Count: 837 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Contents
- Preface
- Changes in This Release for Oracle Database SQL Tuning Guide
- Part I SQL Performance Fundamentals
- 1 Introduction to SQL Tuning
- 2 SQL Performance Methodology
- Part II Query Optimizer Fundamentals
- 3 SQL Processing
- 4 Query Optimizer Concepts
- 4.1 Introduction to the Query Optimizer
- 4.2 About Optimizer Components
- 4.3 About Automatic Tuning Optimizer
- 4.4 About Adaptive Query Optimization
- 4.5 About Approximate Query Processing
- 4.6 About SQL Plan Management
- 4.7 About the Expression Statistics Store (ESS)
- 5 Query Transformations
- Part III Query Execution Plans
- 6 Generating and Displaying Execution Plans
- 6.1 Introduction to Execution Plans
- 6.2 About Plan Generation and Display
- 6.2.1 About the Plan Explanation
- 6.2.2 Why Execution Plans Change
- 6.2.3 Guideline for Minimizing Throw-Away
- 6.2.4 Guidelines for Evaluating Execution Plans Using EXPLAIN PLAN
- 6.2.5 Guidelines for Evaluating Plans Using the V$SQL_PLAN Views
- 6.2.6 EXPLAIN PLAN Restrictions
- 6.2.7 Guidelines for Creating PLAN_TABLE
- 6.3 Generating Execution Plans
- 6.4 Displaying 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.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.6 Example of Domain Indexes and EXPLAIN PLAN
- 7.2.6 PLAN_TABLE Columns
- 7.3 Execution Plan Reference
- 6 Generating and Displaying Execution Plans
- Part IV SQL Operators: Access Paths and Joins
- 8 Optimizer Access Paths
- 8.1 Introduction to Access Paths
- 8.2 Table Access Paths
- 8.3 B-Tree Index Access Paths
- 8.4 Bitmap Index Access Paths
- 8.5 Table Cluster Access Paths
- 9 Joins
- 8 Optimizer Access Paths
- Part V Optimizer Statistics
- 10 Optimizer Statistics Concepts
- 10.1 Introduction to Optimizer Statistics
- 10.2 About Optimizer Statistics Types
- 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
- 11 Histograms
- 12 Configuring Options for Optimizer Statistics Gathering
- 12.1 About Optimizer Statistics Collection
- 12.2 Setting Optimizer Statistics Preferences
- 12.3 Configuring Options for Dynamic Statistics
- 12.4 Managing SQL Plan Directives
- 13 Gathering Optimizer Statistics
- 13.1 Configuring Automatic Optimizer Statistics Collection
- 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.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.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.3 Gathering Statistics for a Partitioned Table: Basic Steps
- 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.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.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.1 Managing Column Group Statistics
- 15 Controlling the Use of Optimizer Statistics
- 16 Managing Historical Optimizer Statistics
- 17 Transporting Optimizer Statistics
- 18 Analyzing Statistics Using Optimizer Statistics Advisor
- 18.1 About 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.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
- 10 Optimizer Statistics Concepts
- Part VI Optimizer Controls
- 19 Influencing the Optimizer
- 20 Improving Real-World Performance Through Cursor Sharing
- 20.1 Overview of Cursor Sharing
- 20.2 CURSOR_SHARING and Bind Variable Substitution
- 20.3 Adaptive Cursor Sharing
- 20.4 Real-World Performance Guidelines for Cursor Sharing
- Part VII Monitoring and Tracing SQL
- 21 Monitoring Database Operations
- 21.1 About Monitoring Database Operations
- 21.2 Enabling and Disabling Monitoring of Database Operations
- 21.3 Creating a Database Operation
- 21.4 Monitoring SQL Executions Using Cloud Control
- 22 Gathering Diagnostic Data with SQL Test Case Builder
- 23 Performing Application Tracing
- 23.1 Overview of End-to-End Application Tracing
- 23.2 Enabling Statistics Gathering for End-to-End Tracing
- 23.3 Enabling End-to-End Application Tracing
- 23.4 Generating Output Files Using SQL Trace and TKPROF
- 23.5 Guidelines for Interpreting TKPROF Output
- 23.6.1 Application Tracing Utilities
- 23.7.1 Views for Application Tracing
- 21 Monitoring Database Operations
- Part VIII Automatic SQL Tuning
- 24 Managing SQL Tuning Sets
- 25 Analyzing SQL with SQL Tuning Advisor
- 25.1 About SQL Tuning Advisor
- 25.2 Managing the Automatic SQL Tuning Task
- 25.3 Running SQL Tuning Advisor On Demand
- 26 Optimizing Access Paths with SQL Access Advisor
- 26.1 About SQL Access Advisor
- 26.2 Optimizing Access Paths with 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.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
- Part IX SQL Controls: Profiles and Plan Baselines
- 27 Managing SQL Profiles
- 28 Overview of SQL Plan Management
- 29 Managing SQL Plan Baselines
- 29.1 About Managing SQL Plan Baselines
- 29.2 Configuring SQL Plan Management
- 29.3 Displaying Plans in a SQL Plan Baseline
- 29.4 Loading SQL Plan Baselines
- 29.5 Evolving SQL Plan Baselines Manually
- 29.6 Dropping SQL Plan Baselines
- 29.7 Managing the SQL Management Base
- 30 Migrating Stored Outlines to SQL Plan Baselines
- 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 Choosing Columns and Expressions to Index
- A.1.3 Guidelines for Choosing Composite Indexes
- A.1.4 Guidelines for Writing SQL Statements That Use Indexes
- A.1.5 Guidelines for Writing SQL Statements That Avoid Using Indexes
- A.1.6 Guidelines for Re-Creating Indexes
- A.1.7 Guidelines for Compacting Indexes
- A.1.8 Guidelines for Using Nonunique Indexes to Enforce Uniqueness
- A.1.9 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
- A.1 Guidelines for Tuning Index Performance
- Glossary
- accepted plan
- access path
- adaptive cursor sharing
- adaptive dynamic sampling
- adaptive optimizer
- adaptive query plan
- adaptive query optimization
- ADDM
- antijoin
- approximate query processing
- automatic capture filter
- Automatic Database Diagnostic Monitor (ADDM)
- automatic optimizer statistics collection
- automatic initial plan capture
- automatic reoptimization
- automatic SQL tuning
- Automatic SQL Tuning Advisor
- Automatic Tuning Optimizer
- Automatic Workload Repository (AWR)
- AWR
- AWR snapshot
- band join
- base cardinality
- baseline
- bind-aware cursor
- bind-sensitive cursor
- bind variable
- bind variable peeking
- bitmap join index
- bitmap piece
- B-tree index
- bulk load
- bushy join tree
- cardinality
- Cartesian join
- child cursor
- cluster scan
- column group
- column group statistics
- column statistics
- complex view merging
- composite database operation
- concurrency
- concurrent statistics gathering mode
- condition
- cost
- cost-based optimizer (CBO)
- cost model
- cumulative statistics
- cursor
- cursor cache
- cursor merging
- cursor-duration temporary table
- data flow operator (DFO)
- data skew
- database operation
- default plan
- disabled plan
- degree of parallelism (DOP)
- dense key
- dense grouping key
- dense join key
- density
- direct path read
- driving table
- dynamic performance view
- dynamic plan
- dynamic statistics
- dynamic statistics level
- enabled plan
- endpoint number
- endpoint repeat count
- endpoint value
- equijoin
- estimator
- execution plan
- execution tree
- expected cardinality
- expression
- expression statistics
- extended statistics
- extensible optimizer
- extension
- external table
- filter condition
- final plan
- fixed object
- fixed plan
- frequency histogram
- full outer join
- full table scan
- global temporary table
- hard parse
- hash cluster
- hash collision
- hash function
- hash join
- hash scan
- hash table
- hash value
- hashing
- heap-organized table
- height-balanced histogram
- hint
- histogram
- hybrid hash distribution technique
- hybrid histogram
- implicit query
- In-Memory scan
- incremental statistics maintenance
- index
- index cluster
- index clustering factor
- index fast full scan
- index full scan
- index-organized table
- index range scan
- index range scan descending
- index skip scan
- index statistics
- index unique scan
- indextype
- inner join
- inner table
- join
- join condition
- join elimination
- join factorization
- join group
- join method
- join order
- join predicate
- key vector
- latch
- left deep join tree
- left table
- library cache
- library cache hit
- library cache miss
- maintenance window
- manual plan capture
- materialized view
- multiblock read
- NDV
- nested loops join
- nonequijoin
- nonjoin column
- nonpopular value
- noworkload statistics
- on-demand SQL tuning
- optimization
- optimizer
- optimizer cost model
- optimizer environment
- optimizer goal
- optimizer statistics
- Optimizer Statistics Advisor
- Optimizer Statistics Advisor rules
- optimizer statistics collection
- optimizer statistics collector
- optimizer statistics preferences
- outer join
- outer table
- parallel execution
- parallel query
- parent cursor
- parse call
- parsing
- partition maintenance operation
- partition-wise join
- pending statistics
- performance feedback
- pipelined table function
- plan evolution
- plan generator
- plan selection
- plan verification
- popular value
- predicate pushing
- private SQL area
- proactive SQL tuning
- projection view
- query
- query block
- query optimizer
- reactive SQL tuning
- recursive SQL
- reoptimization
- repeatable SQL statement
- response time
- result set
- right deep join tree
- right table
- rowid
- row set
- row source
- row source generator
- row source tree
- rule filter
- sample table scan
- sampling
- selectivity
- semijoin
- shared cursor
- shared pool
- shared SQL area
- simple database operation
- simple view merging
- SMB
- snowflake schema
- snowstorm schema
- soft parse
- sort merge join
- SQL Access Advisor
- SQL compilation
- SQL handle
- SQL ID
- SQL incident
- SQL management base (SMB)
- SQL plan baseline
- SQL plan capture
- SQL plan directive
- SQL plan history
- SQL plan management
- SQL processing
- SQL profile
- SQL profiling
- SQL signature
- SQL statement log
- SQL test case
- SQL test case builder
- SQL trace file
- SQL tuning
- SQL Tuning Advisor
- SQL tuning set (STS)
- star schema
- statistics feedback
- stored outline
- subplan
- subplan group
- subquery
- subquery unnesting
- synopsis
- system statistics
- table cluster
- table expansion
- table statistics
- throughput
- top frequency histogram
- tuning mode
- unaccepted plan
- unselective
- user response time
- V$ view
- vector I/O
- view merging
- workload statistics
- Index