Oracle???s SQL tools

• SQL*Plus
• iSQL*Plus

The Petsaver database

• Understanding the sample database

Introduction to tuning

• Kinds of performance problems
• Methods to measure performance
• Techniques to improve SQL performance

SQL statement processing

• Understanding SQL statement processing steps

The Oracle optimizers

• Rule based
• Cost based
• Version specific optimization (OPTIMIZER_FEATURES_ENABLE)

Optimizing SHARED_POOL utilization

• Identifying ways to minimize parsing
• Using bind variables
• Using PL/SQL packages

Effective Indexing

• Creating B*-Tree indexes
• Utilizing "super" indexes and index merging
• Indexes in the data dictionary
• Monitoring index usage

Using the EXPLAIN PLAN utility

• Creating a PLAN table
• Using the EXPLAIN PLAN command
• Interpreting EXPLAIN PLAN output
• Understanding row access methods

SQL*Plus tuning tools

• Using statement TIMING
• Invoking the SQL Autorace Facility
• Interpreting AUTOTRACE Statistics

Collecting Statistics

• Using the ANALYZE Command
• Using the DBMS_STATS Package

Creating histograms

• Understanding histograms
• Creating histograms
• Verifying histogram usage

TKPROF

• Prerequisites for TKPROF
• Formatting trace files with TKPROF
• Interpreting TKPROF output

Choosing the driving table in JOINS

• Creating the query diagram
• Rules to choose the driving table

Using Hints to influence the Optimizer

• Using Hints
• Optimizer goal hints
• Access method hints
• Join hints
• Other hints

Optimizing sorts

• When does Oracle sort
• Sorting efficiently

Advanced Indexes

• Creating bitmap indexes
• Creating function-based Indexes
• Creating reverse key Indexes

Optimizing PL/SQL

• The RETURN clause
• Using DBMS_PROFILER to measure PL/SQL execution
• Using PL/SQL hinting
• BULK operations
• Dynamic SQL
• Native PL/SQL compilation
• Using DBMS_SHARED_POOL