Oracle 12c SQL Tuning for Developers OR403

Days: 3 day
Price: $1895

Course Details

This course will introduce the developer to techniques used to provide minimize response times in and Oracle database.

Course Agenda

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

Details

Options