This course will introduce developers to techniques you can use to minimize response times in an Oracle 11g database.

Who Needs to Attend

Application developers and database administrators who need a fundamental understanding of tuning the Oracle architecture and SQL statements

Prerequisites

Oracle 11g SQL experience

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Subqueries

  • Where Clause Subqueries
  • From Clause Subqueries
  • Correlated Subqueries
  • Scalar Subqueries
  • Exists Subqueries
  • Tree-Structured Queries

2. Overview of the Tuning Environment

  • Performance Tuning Duties
  • Steps for Tuning
  • Tuning Methodology
  • The Tuning Team
  • Tuning Tools

3. Tune Database Operations

  • SQL Parsing and Execution
  • Shared SQL Statements
  • The SQL Area
  • Automatic Segment Space Management
  • Statement Transformation
  • Using SQL Syntax

4. Tuning the Logical Structure

  • Tuning Overview
  • Tablespace Considerations
  • Table Considerations
  • Free Space Management
  • Chaining and Migrated Data
  • Choosing the Right Index
  • Index Usage Monitoring
  • Materialized Views

5. SQL Tuning Tools

  • Optimizer Overview
  • Execution Plans
  • Rule-Based Optimization (Desupported)
  • Cost-Based Optimization
  • Table and Index Statistics
  • DBMS_STATS
  • Execution Plans
  • Autotrace
  • SQL_TRACE and TKPROF
  • Using Hints
  • Stored Outlines and Plan Stability

6. Application Memory Use

  • Memory Tuning Goals
  • Tuning the Buffer Cache
  • Memory Subcaches
  • Partitioned Tables and Indexes

7. PL/SQL Packages for the Tuner

  • DBMS_PROFILER
  • DBMS_SESSION
  • DBMS_TRACE
  • DBMS_STATS

8. Performance Tuning Checklist

  • Data Model Review
  • Object Management
  • Architectural Issues
  • Shared Pool
  • Buffer Cache
  • File I/O
  • Redo Log Buffer and Checkpoints
  • Sort Activity

Labs

Lab 1: Subqueries

Lab 2: SQL Statement Processing

Lab 3: Logical Structures

Lab 4: Statistics and Execution Plans

Lab 5: SQL_TRACE, TKPROF, Stored Outlines, and Hints

Lab 6: Application Memory Use