Contents
SQL and Declarative Programming Languages
Cross-Referencing Statement and SQL_ID
The Concept of Subquery Factoring
Inner Joins and Traditional Join Syntax
Outer Joins and ANSI Join Syntax
Chapter 2: The Cost-Based Optimizer
The CBO’s Cost-Estimating Algorithm
The Quality of the CBO’s Plan Selection
Final State Query Optimization
Chapter 3: Basic Execution Plan Concepts
Displaying the Results of EXPLAIN PLAN
EXPLAIN PLAN May Be Misleading
Displaying Output from the Cursor Cache
Displaying Execution Plans from the AWR
Collecting Operation Level Runtime Data
The GATHER_PLAN_STATISTICS Hint
Displaying Operational Level Data
Displaying Runtime Engine Statistics with DBMS_XPLAN.DISPLAY_CURSOR
Displaying Runtime Engine Statistics with V$SQL_PLAN_STATISTICS_ALL
Displaying Session Level Statistics with Snapper
Allocating Memory to a Workarea
Optimal, One-Pass, and Multipass Operations
Chapter 5: Introduction to Tuning
Understanding the Business Problem
Understanding the Technical Problem
Understanding the SQL Statement
Understanding the Problem Wrap Up
Running the Statement to Completion
When the Elapsed Times Doesn’t Add Up
Making Physical Changes to the Database
Making Changes to the Environment
Running the SQL Tuning Advisor
Chapter 6: Object Statistics and Deployment
The Principle of Performance Management
Service Level Agreements in IT
Non-database Deployment Strategies
The Strategic Direction for the CBO
The History of Strategic Features
Implications of the CBO Strategy
Why We Need to Gather Statistics
How Often Do We Need to Change Execution Plans?
Wolfgang Breitling’s Tuning by Cardinality Feedback
Concurrent Execution Plans Wrap Up
Oracle’s Plan Stability Features
Deployment Options for Tuned SQL
When Just One SQL Statement Needs to Change
When Multiple SQL Statements Need to Change
Chapter 7: Advanced SQL Concepts
How Query Blocks are Processed
Combining Aggregate and Analytic Functions
A Moving Median with the MODEL Clause
Chapter 8: Advanced Execution Plan Concepts
Displaying Additional Execution Plan Sections
Running EXPLAIN PLAN for Analysis
Understanding Parallel Execution Plans
Operations That Can Be Run in Parallel
Controlling Parallel Execution
Parallel Query Server Sets and DFO Trees
Parallel Query Distribution Mechanisms
Why Forcing Parallel Query Doesn’t Force Parallel Query
Applying Hints to Transformed Queries
The Purpose of Object Statistics
Exporting and Importing Statistics
Creating or Rebuilding Indexes and Tables
Creating Object Statistics Wrap Up
Examining Object Statistics in the Data Dictionary
Examining Exported Object Statistics
Statistics Descriptions Wrap-up
Gathering Statistics on Partitioned Tables
How the CBO Uses Partition-level Statistics
Why We Need Partition-level Statistics
Statistics and Partitions Wrap-up
A Footnote on Other Inputs to the CBO
Other Data Dictionary Information
Part 3: The Cost-Based Optimizer
Join orders without hash join input swapping
Join orders with hash join input swapping
Distribution Mechanisms for Parallel Joins
The PQ_DISTRIBUTE hint and parallel joins
Chapter 12: Final State Optimization
Chapter 13: Optimizer Transformations
Factored Subquery Materialization
Grouping Sets to Union Expansion
The Distributed Join Filter Problem
Solving the Distributed Join Filter Problem
Chapter 14: Why Do Things Go Wrong?
Statistics Feedback and DBMS_STATS.SEED_COL_USAGE Features
Chapter 15: Physical Database Design
Full Table Scans on Partitions or Subpartitions
Parallelization and Partitioning
Manual Aggregation and Join Tables
Use of Expressions in Predicates
Equality Versus Inequality Predicates
Implicit Data-Type Conversions
Issues with General Purpose Views
Avoiding Multiple Similar Subqueries
Non-sorting Aggregate Functions
Index Range Scans and Index Full Scans
Solving the Pagination Problem
Additional Predicates with Analytic Functions
Documented versus undocumented hints
Edition -based redefinition hints
Optimizer hints that are hints
Production-hinting case studies
Materialization of factored subqueries
Suppressing order by elimination and subquery unnesting
The v$database_block_corruption view
Chapter 19: Advanced Tuning Techniques
Leveraging an INDEX FAST FULL SCAN
Simulating a Star Transformation
Using ROWID Ranges for Application-Coded Parallel Execution
Converting an Inner Join to an Outer Join
Part 5: Managing Statistics with TSTATS
Chapter 20: Managing Statistics with TSTATS
Skewed column values and range predicates
Correlated columns and expressions
Use of sample data for complex statistical issues
Managing column statistics wrap up
The DBMS_STATS.COPY_TABLE_STATS myth
Cardinality estimates with global statistics
Costing full table scans of table partitions
The pros and cons of dynamic sampling