Introduction
Explain the purpose of the book: to uncover hidden
gems and advanced techniques in Oracle SQL tuning.
Highlight the importance of SQL tuning in modern database
environments.
Briefly outline what readers will learn and how it will help
them in their day-to-day work.
Chapter 1: The Oracle Optimizer –
Beyond the Basics
1.1 Deep Dive into the Optimizer
Explain the Cost-Based Optimizer (CBO) and Rule-Based
Optimizer (RBO).
How the optimizer evaluates execution plans based on
statistics.
The role of cardinality, selectivity, and cost in plan
selection.
1.2 Optimizer Hints
Common hints: /*+ FULL */, /*+ INDEX */, /*+ PARALLEL */, /*+
USE_HASH */.
When to use hints and when to avoid them.
Example: Forcing a specific join method or access path.
1.3 Adaptive Query Optimization
How Oracle adapts execution plans at runtime.
Adaptive join methods and adaptive parallel
distribution.
Example: A query that switches from a nested loop to a hash
join during execution.
1.4 Optimizer Statistics
Advanced techniques for gathering
statistics: DBMS_STATS, ESTIMATE_PERCENT, and CASCADE.
Handling skewed data with histograms.
Example: Fixing a query that performs poorly due to outdated
statistics.
Chapter 2: Execution Plans – Reading
Between the Lines
2.1 Understanding Execution Plans
Key components: predicate information, access paths,
and join methods.
How to read and interpret execution plans.
2.2 Real vs. Predicted Execution Plans
Differences between EXPLAIN PLAN and real execution plans.
Using DBMS_XPLAN.DISPLAY_CURSOR for real execution plans.
2.3 SQL Monitor
How to use SQL Monitor for real-time performance analysis.
Example: Diagnosing a long-running query with SQL Monitor.
2.4 Identifying Bottlenecks
Common bottlenecks: full table scans, inefficient joins,
and high CPU usage.
Example: Fixing a query with a high-cost nested loop join.
Chapter 3: Partitioning – The Secret to
Massive Performance Gains
3.1 Partitioning Strategies
Range partitioning: For time-series data.
Hash partitioning: For even data distribution.
List partitioning: For discrete values.
Composite partitioning: Combining multiple strategies.
3.2 Partition Pruning
How Oracle eliminates unnecessary partitions.
Example: A query that scans only relevant partitions.
3.3 Partition-Wise Joins
Improving performance for large datasets.
Example: Joining two partitioned tables.
3.4 Global vs. Local Indexes
When to use global and local indexes.
Example: Optimizing a query with a global index.
Chapter 4: Indexing – Beyond B-Trees
4.1 Function-Based Indexes
Solving problems with expressions and functions.
Example: Indexing a UPPER(last_name) column.
4.2 Invisible Indexes
Testing indexes without affecting production.
Example: Making an index invisible to test its impact.
4.3 Reverse Key Indexes
Improving performance for high-concurrency systems.
Example: Using a reverse key index for a sequence-based
primary key.
4.4 Bitmap Indexes
When and how to use bitmap indexes.
Example: Optimizing a data warehouse query with bitmap
indexes.
Chapter 5: Parallel Execution –
Unleashing the Power of Multi-Core
Systems
5.1 How Parallel Execution Works
Parallel query, parallel DML, and parallel DDL.
Example: Running a query with parallel execution.
5.2 Tuning Parallel Execution
Setting the degree of parallelism (DOP).
Example: Adjusting DOP for a large table scan.
5.3 Adaptive Parallelism
How Oracle adjusts parallelism at runtime.
Example: A query that dynamically adjusts its DOP.
Chapter 6: Query Transformations – The
Optimizer’s Hidden Magic
6.1 Common Query Transformations
Subquery unnesting: Converting subqueries into joins.
View merging: Combining views with the main query.
Star transformation: Optimizing star schema queries.
Join factorization: Reducing redundant joins.
6.2 Influencing Query Transformations
Using hints to control transformations.
Example: Forcing subquery unnesting with /*+ UNNEST */.
Chapter 7: Materialized Views –
Precomputing Results for Speed
7.1 How Materialized Views Work
Precomputing and storing query results.
Example: Creating a materialized view for a complex
aggregation.
7.2 Query Rewrite
Automatically using materialized views.
Example: A query that rewrites to use a materialized view.
7.3 Refresh Strategies
Fast refresh: Incremental updates.
Complete refresh: Rebuilding the materialized view.
Example: Refreshing a materialized view on demand.
Chapter 8: Advanced Join Techniques –
Beyond Nested Loops
8.1 Hash Joins
Optimizing large dataset joins.
Example: Joining two large tables with a hash join.
8.2 Merge Joins
When to use merge joins.
Example: Joining sorted datasets.
8.3 Star Joins
Optimizing data warehouse queries.
Example: A star join in a fact table and dimension tables.
Chapter 9: SQL Tuning Tools – Oracle’s
Hidden Gems
9.1 SQL Tuning Advisor
Automating SQL tuning.
Example: Running the SQL Tuning Advisor on a problematic
query.
9.2 SQL Access Advisor
Optimizing indexes and materialized views.
Example: Using the SQL Access Advisor to recommend
indexes.
9.3 AWR and ADDM
Analyzing historical performance.
Example: Diagnosing a performance issue with AWR.
Chapter 10: Tuning for the Cloud –
Oracle Autonomous Database
10.1 Automatic Indexing
How Oracle Autonomous Database creates and manages
indexes.
Example: A query that benefits from automatic indexing.
10.2 Automatic SQL Tuning
How Oracle Autonomous Database tunes SQL automatically.
Example: A query that improves with automatic SQL tuning.
Chapter 11: Case Studies – Real-World
SQL Tuning Challenges
11.1 Case Study 1: Tuning a Complex Execution Plan
Problem: A query with multiple joins and subqueries.
Solution: Rewriting the query and using hints.
11.2 Case Study 2: Optimizing a Data Warehouse
Query
Problem: A slow-running aggregation query.
Solution: Using partitioning and materialized views.
Chapter 12: The Future of SQL Tuning –
AI and Machine Learning
12.1 AI and Machine Learning in SQL Tuning
How AI is changing SQL tuning.
Example: Oracle’s Automatic SQL Tuning.
This expanded content provides a solid foundation for each chapter.
Let me know if you’d like further details or additional examples for
any specific chapter! 😊
