Duration: 3 Days
This course will introduce developers to techniques you can use to minimize response times in an Oracle 11g database.
What You Will Learn
Audience
Application developers and database administrators who need a fundamental understanding of tuning the Oracle architecture and SQL statements
Prerequistes
Oracle 11g SQL experience
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
Course 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
