Course Description

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

Audience Profile

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

Prerequisites

Oracle 12c PL/SQL Foundations

Learning Objectives

Write faster, more efficient code by optimizing SQL statements and the Oracle architecture

Course Outline

Module 1: Subqueries
Lessons
• Where Clause Subqueries
• From Clause Subqueries
• Correlated Subqueries
• Scalar Subqueries
• Exists Subqueries
• Tree-Structured queries

Module 2: Overview of Tuning Environment
Lessons
• Performance Tuning Duties
• Steps for Tuning
• Tuning Methodology
• The Tuning Team
• Tuning Tools

Module 3: Tune Database Operations
Lessons
• SQL Parsing and Execution
• Shared SQL Statements
• The SQL Area
• Automatic Segment Space Management
• Statement Transformation
• Using SQL Syntax

Module 4: Tuning the Logical Structure
Lessons
• Tuning Overview
• Tablespace Considerations
• Table Considerations
• Free Space Management
• Chaining and Migrated Data
• Choosing the Right Index
• Index Usage Monitoring
• Materialized Views

Module 5: SQL Tuning Tools
Lessons
• 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

Module 6: Application Memory Use
Lessons
• Memory Tuning Goals
• Tuning the Buffer Cache
• Memory Subcaches
• Partitioned Tables and Indexes

Module 7: PL/SQL Packages for the Tuner
Lessons
• DBMS_PROFILER
• DBMS_SESSION
• DBMS_TRACE
• DBMS_STATS

Module 8: Performance Tuning Checklist
Lessons
• Data Model Review
• Object Management
• Architectural Issues
• Shared Pool
• Buffer Cache
• File I/O
• Redo Log Buffer and Checkpoints
• Sort Activity