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