Oracle9i: SQL Tuning Workshop

Introduction to Tuning

• Describing the Causes of Performance Problems Identifying Performance Problems Using a Tuning Methodology Listing the Steps to Tune SQL SQL Statement Processing Listing the SQL Statement Processing Steps Identifying Means to Minimize Parsing Stating the Use of Bind Variables

Oracle memory Structures

• Overview Automated SQL memory management

SQL*Plus AUTOTRACE

• Using the EXPLAIN PLAN Command Identifying the AUTOTRACE Syntax Interpreting EXPLAIN Output Interpreting AUTOTRACE Statistics

SQL Trace and TKPROF

• Invoking the SQL Trace Facility Setting Up Appropriate Initialization Parameters Formatting Trace Files with TKPROF Interpreting the Output of the TKPROF Command

RBO Versus CBO

• Identifying Rule-Based Optimization Identifying Cost-Based Optimization Understanding the Cost of an Execution Plan

Introduction to Indexes

• Identifying Row Access Methods Creating B*-Tree Indexes Understanding B*-Tree Index Access and Index Merging

Collecting Statistics

• Using the ANALYZE Command Identifying Table, Column, and Index Statistics Using the DBMS_STATS Package Building Histograms

Influencing the Optimizer

• Setting Up Appropriate Initialization Parameters Using the ALTER SESSION Command Using Hints

Sorting and Joining

• Sorting Guidelines Using Top-N SQL Nested Loops Joins Sort/Merge Joins Outer Joins Star Joins Hash Joins

Optimizer Plan Stability

• Purpose and Benefits of Optimizer Plan Stability Creating Stored Outlines Using the OUTLN_PKG Package

Advanced Indexes

• Creating Bitmapped Indexes Creating Function-Based Key Indexes Optimizing Star Joins with Star Transformation

Materialized Views and Temporary Tables

• Using the CREATE MATERIALIZED VIEW Syntax Utilizing Query Rewrites Creating and Using Temporary Tables

Alternative Storage Techniques

• Creating Index-Organized Tables Creating Index Clusters Creating Hash Clusters

Datawarehousing Considerations

• WITH clause Grouping sets Multitable Inserts Merge External tables

Workshops:

• Single Table, Single Predicate Queries Index Usage in Execution Plans Effects of Expressions on Index Usage Effects of Wildcards Implicit Data Type Conversion NULL Values and Negations

Sorting, Aggregation, and Set Operators

• Order By Group Functions Top-N SQL Select Distinct Group By, Having Union, Minus, Intersect, and Union All

Joins

• Nested Loops Joins with and Without Indexes Sort-Merge Joins Outer Joins Hash Joins

Subqueries

• Regular Subqueries Correlated Subqueries Antijoins and Semijoins

Multiple Predicates

• AND OR Concatenated Indexes Bitmapped Indexes Function-Based Indexes