About this Course

This course provides students with an introduction to application and database tuning. Students will learn how the cost-based optimizer works. A discussion of basic Oracle architecture will provide the foundation for understanding both SQL statement and system performance. Students will use EXPLAIN PLAN and AUTOTRACE for evaluating execution strategies and the DBMS_STATS package for gathering optimizer statistics. Also discussed is how to influence the behavior of the optimizer with hints, physical schema changes, and alternative SQL statement syntax. Factors that affect overall system performance such as the buffer cache, SGA structures, and waits due to locks and latches are presented.

Hands-on workshops provide students with a solid understanding of the concepts presented in the lectures.

This course can be taught for any Oracle version.

Audience Profile

Database administrators and application designers and developers.

Prerequisites

OR5450 Oracle SQL or equivalent experience.

Course Outline

1. Tuning Overview

• What is Database Performance Tuning?
• Tuning-Related Roles & Considerations
• Tuning Process and Tools
• Different Tuning Goals

2. Oracle Architecture

• Memory Structures
• Server Processes
• Background Processes

3. ALERT Logs, Trace Files, and Events

• Location and Use of the ALERT Log
• Location and Use of Trace Files
• Retrieving and Displaying Wait Events
• Using dynamic Performance Views
• TIMED_STATISTICS Parameter to Collect Statistics

4. SQL Statement Processing

• Parsing
• Blind Variables
• CURSOR_SHARING Parameter

5. SQL Statement Tuning

• Optimizer Concepts
• OPTIMIZER_MODE Parameter
• Cost-Based Optimizer Architecture
• EXPLAIN PLAN  Statement
• PLAN_TABLE Structure
• SQL*Plus Autotrace

6. Indexes

• Index Monitoring
• Index Skip Scan
• Function-Based Indexes
• Query Rewrite
• B-Tree Indexes
• Bitmap Indexes
• Invisible Indexes

7. Cost-Based Optimizer

• Access Paths
• OPTIMIZER_FEATURES_ENABLE Parameter
• PL/SQL InLining Optimization
• Multi-Column Statistics
• V$SQL_PLAN
• Gathering Optimizer Statistics
• DBMS_STATS Package

8. Influencing the Optimizer

• Query Result Cache
• Optimizer Hints
• Histograms

9. Tuning Tools

• SQL Trace and TKPROOF
• DBMS_PROFILER
• PL/SQL Hierarchical Profiler
• End to End Application Tracing
• DBMS_MONITOR

10. SQL Plan Management

• SQL Plane Baselines
• SQL Profiles

11. Locking and Concurrency

• Types of Locks
• Transaction Isolation Levels
• Redo and Undo