Oracle 11g SQL Tuning

Length: 3 days
Audience: Database Administrators, Application Designers, and Developers.
Prerequisites: Oracle 11g SQL Fundamentals.
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:
  • 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
  • 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
    • Invisible Indexes
  • 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
  • Influencing the Optimizer
    • Query Result Cache
    • Optimizer Hints
    • Histograms
  • Tuning Tools
    • SQL Trace and TKPROF
    • DBMS_PROFILER
    • PL/SQL Hierarchical Profiler
    • End to End Application Tracing
    • DBMS_MONITOR
  • SQL Plan Management
    • SQL Plan Baselines
    • SQL Profiles
  • Locking and Concurrency
    • Types of Locks
    • Transaction Isolation Levels
    • Redo and Undo
Back to Oracle 11g Courses