Contents Listing

Introduction



The SQL Optimizers

Understanding the Rule-Based Optimizer

Understanding the Cost-Based Optimizer

Some Common Optimizer Misconceptions

Which Optimizer to Use?



Rule-Based Optimizer Problems and Solutions

Problem 1: Incorrect Driving Table

Problem 2: Incorrect Index

Problem 3: Incorrect Driving Index

Problem 4: Using the ORDER BY Index and notthe WHERE Index



Cost-Based Optimizer Problems and Solutions

Problem 1: The Skewness Problem

Problem 2: Analyzing with Wrong Data

Problem 3: Mixing the Optimizers in Joins

Problem 4: Choosing an Inferior Index

Problem 5: Joining Too Many Tables

Problem 6: Incorrect INIT.ORA Parameter Settings



Problems Common to Rule and Cost with Solutions

Problem 1: Statement Not Written for Indexes

Problem 2: Indexes Are Missing or Inappropriate

Problem 3: Use of Single-Column Index Merge

Problem 4: Misuse of Nested Loop, Sort Merge,or Hash Join

Problem 5: Misuse of IN, EXISTS, NOT IN, NOT EXISTS,or Table Joins

Problem 6: Unnecessary Sorts

Problem 7: Too Many Indexes on a Table

Problem 8: Use of OR Instead of UNION

Problem 9: Tables and Indexes with Many Deletes

Other Problems: Heavy Usage of Views

Other Problems: Joining Too Many Tables



Handy SQL Tuning Tips

Identify Bad SQL

Identify Long-Running SQL Statements

Use DECODE for IF/ELSE Selection

Encourage Bind Variables



Using SQL Hints

When Are Hints Ignored?

Using Hints in Views

Available Hints



Using DBMS_STATS to Manage Statistics

Using DBMS_STATS to Analyze Faster

Copying Statistics Using DBMS_STATS

Manipulating Statistics Using DBMS_STATS

Reverting to Previous Statistics



Using Outlines for Consistent Execution Plans

Recording Outlines

Enabling Outlines

Managing Outlines