Course Details
This course will introduce developers to techniques you can use to minimize response times in an Oracle 11g database.
Course Prerequisites
Oracle 11g SQL experience
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