Day 1: Introduction to SQL Optimization

1-1 – Introduction to SQL Tuning

Intro to the class
History of SQL
Evolution of SQL
Exercise - declarative SQL

1-2 – Internal processing of SQL statements

Parsing SQL Syntax
SQL Semantic Analysis
Generating the execution plan
Using optimizer plan stability
Using the v$sql view
Using the v$sql_plan view
Exercise – Query the library cache

1-3 – Optimizer Statistics

Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Statistics management
Exercise – gather system stats

1-4 – Optimizer modes and goals

Management issues with system-wide optimization
Different modes of SQL optimization
Bi-modal databases
Rule-based optimization
Cost-based optimization
All rows optimization
First_rows optimization
Exercise – display and change optimizer_mode
1-5 - Table joining internals
Sort-merge joins
Nested Loop joins
Hash joins
STAR joins
Bitmap joins
Exercise – Change table join techniques & evaluate performance
1-6 – SQL Tuning and full-table scans
Basics of file I/O
Sequential reads vs. scattered reads
When full scans are best
RAM caching in the SGA
Automating table caching
Solid State Disks
Tracking full-scans over time with AWR
Exercise – Query v$sql

DAY 2 – Execution plan internals

2-1 –Oracle parallel query and parallel DML

Parallel and SMP processing
Parallel query optimal degree
Parallel query management (system, session, statement)
Parallel DML
Parallel parallelism
Exercise: Run a parallel query

2-2 – Exposing execution plans

Evaluating Large-table, full-table scans
Index Usage Analysis
Reports on system-wide SQL execution
Exercise – run autotrace options
2-3 - Altering SQL execution plans
Using hints
Changing the system-wide optimizer mode
Changing optimizer mode for specific statements
Re-writing SQL queries
Table join order evaluation
Using the ordered hint
Exercise – Optimizer costing models

2_4 – Tuning SQL with hints

Optimizer directives
Scope of hints (session-level, statement level)
Broad hints (optimizer mode) vs. narrow hints
Good Hints vs. bad hints
Using the ORDERED and LEADING HINTS
Forcing index usage
Using hints in subqueries
Exercise – change an execution  plan with a  hint

2_5 – Oracle Index Optimization

B-Tree indexes
Bitmap Indexes
Function-based Indexes
Clustered indexes
Index-only tables
Exercise – Create a function-based index

2_6 – Tuning Oracle sorting

When a sort is invoked (order by, group by, etc.)
Sorting with indexes vs internal sorting
Detecting disk sorts
Sorted Hash Clusters
Exercise: Force two sort methods

DAY 3 – Advanced SQL Tuning Topics

3_1 – Monitoring SQL Performance

Measuring end-to-end response time
Measuring SQL throughput
Using v$session_longops
Optional Exercise – Run plan9i.sql

3_2 – Oracle DML Tuning

DML Tuning is not for neophytes
Oracle DML tuning
Optimizing Oracle SQL insert performance
High Impact insert Tuning Techniques
Tips for batch inserts
Low-impact insert techniques (% and % faster)
Tuning insert speed with the nologging option
Reverse key indexes and insert performance
Blocksize and insert performance
Oracle Delete & Update Tuning
High impact update techniques (over % faster):
Low-impact techniques (between % and % faster)
Using bulk binds for PL/SQL updates
Oracle subquery factoring (with clause) for DML

3_3 – Tuning with materialized views and temporary objects

Materialized views
Global temporary tables
Using scalar subqueries (WITH clause)
Simplifying complex SQL with temporary objects
Exercise – Re-write complex query using temporary tables and WITH clause

3_4 – Tuning subqueries

Subquery Tuning and SQL
Types of SQL Subqueries
Tuning Guidelines for Subqueries
Avoiding SQL Subqueries
Subqueries in the where Clause
In vs. exists Subqueries
Same Results, Different Syntax and Plans
Non-correlated subquery:
Outer Join:
Correlated Subquery:
Tuning Scalar Subqueries
Scalar Subquery Performance
Removing Subqueries for Fast SQL Performance
Internals of Temporary Tables
Correlated vs. Non-correlated Subqueries
Tuning Correlated Subqueries
Automatic Rewriting not exists Subqueries
Automatic Rewriting exists Subqueries
Rewriting Non-equality Correlated Subqueries
Rewriting exists Subqueries with the rank Function
Subquery Hint Tuning
Subquery Tuning with Index Hints
Tuning Subqueries With the push_subq Hint
Table Anti-Join Hints
The merge_aj Hint
SQL Tuning With the hash_aj Hint
Exercise: Tune an anti-join

3_5 – Troubleshooting bad SQL

Troubleshooting Problem SQL
The Holistic Approach to SQL Tuning
Troubleshooting Oracle SQL Bugs
What is Bad SQL?
Identifying Problem SQL
Troubleshooting with v$sql_plan
SQL Troubleshooting with v$sql_plan_statistics
Finding indexing opportunities
Exercise: Find top SQL hogs

3_6 – Advanced Optimizer Statistics

Histograms
Exporting/importing statistics
Statistics management
Exercise – Analyze schema and tables