Oracle 10g Application Tuning

Length: 3 Days
Audience: Database administrators and application designers and developers.
Prerequisites: Oracle 10g SQL & SQL*Plus and Oracle 10g Database Administration or Oracle 9i Database Administration
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.
Hands-on workshops provide students with a solid understanding of the concepts presented in the lectures.
Topics discussed include:
  • 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
  • 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
    • OPTIMIZER_FEATURES_ENABLE Parameter
    • V$SQL_PLAN
    • Gathering Optimizer Statistics
    • DBMS_STATS Package
  • Influencing the Optimizer
    • Optimizer Hints
    • Histograms
  • Tuning Tools
    • SQL Trace and TKPROF
    • DBMS_PROFILER
    • End to End Application Tracing
    • DBMS_MONITOR
  • 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
Back to Oracle 10g Courses