Oracle11g SQL Tuning For Developers

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

    No classes are currenty scheduled for this course.

    Call (919) 283-1653 to get a class scheduled online or in your area!

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

*Please Note: Course Outline is subject to change without notice. Exact course outline will be provided at time of registration.

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

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

Ready to Jumpstart Your IT Career?

CONTACT US NOW!