Oracle 10g Database Performance and Tuning

Length: 5 Days
Audience: Database administrators and application designers and developers.
Prerequisites: Oracle10g Database Administration or equivalent experience.
Overview: 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. The automatic performance diagnostic and tuning features available in Oracle 10g are presented.
Hands-on workshops provide students with a solid understanding of the concepts presented in the lectures.
This course might also be suitable for application designers and developers who need a deeper understanding of Oracle database performance.
Topics discussed include:
  • Tuning Overview
    • What is Database Performance Tuning
    • Tuning-Related Roles & Considerations
    • Tuning Process and Tools
    • Different Tuning Goals
  • Oracle Architecture
    • Memory Structures
    • Server Processes
    • Background Processes
  • 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
    • Statistics Package and STATSPACK Procedures
  • SQL Statement Processing
    • Parsing
    • Bind Variables
    • CURSOR_SHARING Parameter
  • SQL Statement Tuning
    • Optimizer Concepts
    • OPTIMIZER_MODE Parameter
    • Cost-Based Optimizer Architecture
    • EXPLAIN PLAN Statement
    • PLAN_TABLE Structure
    • SQL*Plus Autotrace
  • Indexes
    • Index Monitoring
    • Index Skip Scan
    • Function-Based Indexes
    • Query Rewrite
    • B-Tree Indexes
    • Bitmap Indexes
  • Cost-Based Optimizer
    • Access Paths
    • V$SQL_PLAN
    • Gathering Optimizer Statistics
    • DBMS_STATS Package
  • Influencing the Optimizer
    • Optimizer Hints
    • Histograms
  • Tuning Tools
    • SQL Trace and TKPROF
    • End to End Application Tracing
  • Optimizer Plan Stability
    • Uses of Optimizer Plan Stability
    • How Optimizer Plan Stability Works
    • Stored Outlines
  • Locking and Concurrency
    • Types of Locks
    • Transaction Isolation Levels
    • Redo and Undo
    • Undo Advisor
  • Memory Configuration
    • Oracle Memory Caches
    • Automatic Shared Memory Management
    • Dynamically Changing Cache Sizes
    • Buffer Cache Advisory Statistics
    • Considering Multiple Buffer Pools
    • Configuring and Using the Shared Pool
    • PGA Memory Management
    • Configuring Automatic PGA Memory
  • Automatic Performance Diagnostic and Tuning Features
    • Oracle Enterprise Manager Database Control
    • Automatic Workload Repository (AWR)
    • Workload Repository Views
    • Workload Repository Reports
    • Automatic Database Diagnostic Monitoring
    • Automatic SQL Tuning - SQL Tuning Advisor
    • SQLAccess Advisor
    • Automatic Performance Statistics
    • Segment Advisor
Back to Oracle 10g Courses