SQL Tuning

This course aims to cover the following topics:

• Introduction to SQL Tuning
• Optimizer Fundamentals
• Bind Variables
• Displaying Execution Plans
• Reading and interpreting Execution Plans
• Optimizer Access Paths
• Optimizer Joins
• Other Optimizer Operators
• SQL Tuning Techniques

Introduction to SQL Tuning

• Brief Introduction to Statement Processing
• SQL Tuning Session
• What Is Bad SQL?
• SQL Tuning Strategies

Optimizer Fundamentals

• Components of the Optimizer
• Query Transformer
• Estimator: Selectivity and Cardinality
• Estimator: Cost and Cost Components
• Plan Generator
• How Statistics affect plans
• Best practice for gathering statistics
• Controlling the Behaviour of the Optimizer

Bind Variables

• Cursor Sharing and Different Literal Values
• Cursor Sharing and Bind Variables
• Bind Variable Peeking
• The CURSOR_SHARING Parameter

• Forcing Cursor Sharing
• Adaptive Cursor Sharing (ACS)
• Interacting with Adaptive Cursor Sharing
• Adaptive Cursor Sharing: Views

Displaying Execution Plans

• What Is an Execution Plan?
• Viewing Execution Plans
• The EXPLAIN PLAN Command
• Displaying from PLAN_TABLE
• Using the V$SQL_PLAN View
• DBMS_XPLAN.DISPLAY_CURSOR
• Links between important Dynamic Performance Views
• AUTOTRACE
• Automatic Workload Repository (AWR)
• SQL Monitoring
• DBMS_SQLTUNE output to HTML/Text

Reading and Interpreting Execution Plans

• Explain the execution plan
• Interpreting a Serial Execution Plan
• Interpreting a Parallel Execution Plan
• Evaluating Estimates and Actuals
• Adaptive Plans/ Optimizations
• Reading Adaptive Query plans
• Understanding the key data in dynamic performance views
• Understanding Statistics
• SQL Hints and how hints can affect plans

Optimizer Access Paths

• Row Source Operations
• Main Structures and Access Paths
• Full Table Scan
• ROWID Scan
• Sample Table Scans
• Indexes: Overview
• Normal B*‐tree Indexes
• Index Scans
• Index Unique Scan
• Index Range Scan
• Index Range Scan: Descending
• Index Range Scan: Function-Based Index
• Full Index Scan
• Fast Full Index Scan
• Skip Scan
• Index Join Scan
• Composite Indexes 
• B*-tree Indexes and Nulls
• Using Indexes: Index-Organized Table Scans
• Bitmap Indexes
• Bitmap Operations
• Bitmap Join Index
• Invisible Indexes
• Guidelines for Managing Indexes

Optimizer Joins

• Join Methods
• Nested Loops Join
• Sort-Merge Join
• Hash Join
• Cartesian Join
• Join Methods in Summary 
• Join Types: Equijoins and Nonequi Joins
• Join Types: Outer Joins
• Join Types: Semijoins
• Join Types: Antijoins

Other Optimizer Operations

• Sorting Operators
• Buffer Sort Operator
• Inlist Iterator
• View Operator
• Min/Max and First Row Operators
• FILTER Operations
• SET Operations
• Result Cache

SQL Tuning Techniques

• Overview of writing efficient SQL
• Table Design?
• Index Usage
• Transformed Index
• Data Type Mismatch
• NULL usage
• Tune the ORDER BY Clause
• Retrieve a MIN/MAX value
• Correlated Subquery verses Inline Views
• UNION and UNION ALL
• Avoid Using HAVING Inappropriately
• Tune the BETWEEN Operator
• Tune a Star Query
• Tune the Join Order
• Test for Existence of Rows
• LIKE '%STRING'
• Use Caution When Managing Views
• Join Column and Index
• Ordering Keys for Composite Index 
• Bitmap Join Index
• Tune Complex Logic
• Writing Combined SQL Statement 
• Write a Multitable INSERT Statement 
• Using Temporary Table 
• Using the WITH Clause
• Using Materialized Views
• Partition Pruning
• Using a Bind Variable
• Functions in the WHERE clause
• Using Scalar Subqueries