Oracle Performance Tuning

Reading Time: 3 minutes

Oracle Performance Tuning

    Module 1: Performance Tuning Overview
    • Introduction to Performance Tuning
    • Introduction to Performance Tuning Features and Tools

    Module 2: Designing and Developing for Performance

    • Oracle Methodology
    • Understanding Investment Options
    • Understanding Scalability
    • System Architecture
    • Application Design Principles
    • Workload Testing, Modeling, and Implementation
    • Deploying New Applications

    Module 3: Performance Improvement Methods

    • The Oracle Performance Improvement Method
    • Emergency Performance Methods

    Module 4: Configuring a Database for Performance

    • Performance Considerations for Initial Instance Configuration
    • Creating and Maintaining Tables for Good Performance
    • Performance Considerations for Shared Servers

    Module 5: Automatic Performance Statistics

    • Overview of Data Gathering
    • Automatic Workload Repository

    Module 6: Automatic Performance Diagnostics

    • Introduction to Database Diagnostic Monitoring
    • Automatic Database Diagnostic Monitor

    Module 7: Memory Configuration and Use

    • Understanding Memory Allocation Issues
    • Configuring and Using the Buffer Cache
    • Configuring and Using the Shared Pool and Large Pool
    • Configuring and Using the Redo Log Buffer
    • PGA Memory Management

    Module 8: I/O Configuration and Design

    • Understanding I/O
    • Basic I/O Configuration

    Module 9: Understanding Operating System Resources

    • Understanding Operating System Performance Issues
    • Solving Operating System Problems
    • Understanding CPU
    • Finding System CPU Utilization

    Module 10: Instance Tuning Using Performance Views

    • Instance Tuning Steps
    • Interpreting Oracle Statistics
    • Wait Events Statistics
    • Idle Wait Events

    Module 11: Tuning Networks

    • Understanding Connection Models
    • Detecting Network Problems
    • Solving Network Problems

    Module 12: SQL Tuning Overview

    • Introduction to SQL Tuning
    • Goals for Tuning
    • Identifying High-Load SQL
    • Automatic SQL Tuning Features
    • Developing Efficient SQL Statements

    Module 13: Automatic SQL Tuning

    • Automatic SQL Tuning Overview
    • SQL Tuning Advisor
    • Managing SQL Profiles with APIs
    • SQL Tuning Sets
    • SQL Tuning Information Views

    Module 14: The Query Optimizer

    • Optimizer Operations
    • Choosing an Optimizer Goal
    • Enabling and Controlling Query Optimizer Features
    • Understanding the Query Optimizer
    • Understanding Access Paths for the Query Optimizer
    • Understanding Joins

    Module 15: Managing Optimizer Statistics

    • Understanding Statistics
    • Automatic Statistics Gathering
    • Manual Statistics Gathering
    • System Statistics
    • Managing Statistics
    • Viewing Statistics

    Module 16: Using Indexes and Clusters

    • Understanding Index Performance
    • Using Function-based Indexes for Performance
    • Using Partitioned Indexes for Performance
    • Using Index-Organized Tables for Performance
    • Using Bitmap Indexes for Performance
    • Using Bitmap Join Indexes for Performance
    • Using Domain Indexes for Performance
    • Using Clusters for Performance
    • Using Hash Clusters for Performance

    MOdule 17: Optimizer Hints

    • Understanding Optimizer Hints
    • Using Optimizer Hints

    Module 18: Using Plan Stability

    • Using Plan Stability to Preserve Execution Plans
    • Using Plan Stability with Query Optimizer Upgrades

    Module 19: Using EXPLAIN PLAN

    • Understanding EXPLAIN PLAN
    • The PLAN_TABLE Output Table
    • Running EXPLAIN PLAN
    • Displaying PLAN_TABLE Output
    • Reading EXPLAIN PLAN Output
    • Viewing Parallel Execution with EXPLAIN PLAN
    • Viewing Bitmap Indexes with EXPLAIN PLAN
    • Viewing Partitioned Objects with EXPLAIN PLAN

    Module 20: Using Application Tracing Tools

    • End to End Application Tracing
    • Using the trcsess Utility
    • Understanding SQL Trace and TKPROF
    • Using the SQL Trace Facility and TKPROF
    • Avoiding Pitfalls in TKPROF Interpretation
    • Sample TKPROF Output