Oracle Database DBA Performance Tuning & Management Training Course Outline

Module 1: Introduction to Oracle Performance Tuning

• Performance Planning
• Understanding Investment Options
• Understanding Scalability
• System Scalability
• System Architecture
• Hardware and Software Components
• Application Design Principles
• Deploying New Applications
• Instance Tuning
• Performance Principles
• Baselines
• Proactive Monitoring
• Bottleneck Elimination
• SQL Tuning
• Query Optimiser and Execution Plans

Module 2: Using Application Tracing Tools

• End-to-End Application Tracing
• Using the trcsess Utility
• Understanding SQL Trace and TKPROF

Module 3: SQL Optimiser

• Optimiser Operations
• Components of the Query Optimiser
• Optimiser Access Paths
• Joins
• Execution Plans

Module 4: Sorts

• Sort Merge Joins

Module 5: Indexes

• Index Characteristics
• Keys and Columns
• Unique and Nonunique Indexes

Module 6: Advanced Indexes

• Types of Indexes
  • Categories of Indexes

Module 7: Joins Operations

• Joins
  • Join Conditions
  • Types of Join

Module 8: Sequences and Views

• Managing Views
  • Creating Views
  • Replacing Views
  • Using Views in Queries
  • Updating a Join View
  • Altering Views
  • Dropping Views
• Managing Sequences
  • Creating Sequences
  • Altering Sequences
  • Dropping Sequences

Module 9: Using Hints

• Overview of Optimiser Hints

Module 10: Miscellaneous

• SQL Tuning Tips
• Array Size
• Virtual Columns
• WITH Clause
• Shared Pool
• Intelligent Cursor Sharing
• Bind Variable Usage
• Result Caching

Module 11: Basic Tuning Diagnostics

• Performance Tuning Diagnostics, Features, and Tools
• DB Time
• Dynamic Performance Views
• Database Statistics
• Wait Events 
• Using ADDM with Oracle Real Application Clusters

Module 12: Reduce the Cost of SQL Operations

• Identify Invalid Objects
• SQL Access Advisor
• Row Migration
• Manage Extents

Module 13: SQL Performance Analyser

• An Overview of the SQL Performance Analyser
• Capture a SQL Workload
• Create a SQL Performance Analyser Task
• Summary of the AWR Compare Periods Report
• About Database Memory Caches and Other Memory Structures
• Data Dictionary Cache Concepts

Module 14: SQL Performance Management

• Optimiser Statistics Collection
• Gathering Statistics for Fixed Objects
• Optimiser Statistics Advisor
• Expression Statistics
• Adaptive Query Optimisation
• Adaptive Query Plans
• Automatic SQL Tuning
• SQL Tuning Advisor
• SQL Access Advisor
• SQL Plan Management

Module 15: Automatic Memory Management

• Overview of Automatic Shared Memory Management
• Dynamic SGA
• Parameters for Sizing the SGA
• Enable and Disable Automatic Shared Memory Management
• Overview of Automatic Memory Management

Module 16: Tune the Shared Pool

• Basic of Shared Pool
• Latches and Mutexes
• Avoiding Hard Parses
• Pin the SQL in the Shared Pool Area
• SHARED_POOL_SIZE and Automatic Storage Management
• Data Dictionary

Module 17: Tune the Buffer Cache

• About Data Base Buffer Cache
• Database Smart Flash Cache
• Full Database Caching

Module 18: Tune PGA and Temporary Space

• Overview of the PGA
• Oracle Memory Usage
• Automatic PGA Memory Mode Configuration
• Histograms
• Temporary Tablespace Management
• Temporary Tablespace Group
• Multiple Temporary Tablespaces
• Temporary Tablespace Shrink
• Data Dictionary Views

Module 19: Create and Use Snapshots and Baselines with the Automatic Workload Repository

• Automatic Workload Repository
• Create Baselines
• Moving Window Baseline
• Baseline Templates
• Managing Baseline
• Compare Periods Report

Module 20: Use Metrics and Alerts

• Overview of Metrics and Alerts
• Server Generated and Event based Alerts
• Set Thresholds

Module 21: Use AWR-Based Tools

• Automatic Maintenance Tasks
• Configuring Maintenance Windows
• Enabling ADDM in a Pluggable Database
• Generate an ASH Report

Module 22: Real Time Database Operation Monitoring

• Overview of Real Time Database Operation Monitoring
• Database Operation Concepts
• Enable the Monitoring of Database Operations
• Database Operation Views
• Oracle Database 12cR2 New Features

Module 23: Monitor Applications

• Create Services
• End-to-End Application Tracing

Module 24: Big Data and Data Warehouse Features

• Online Statistics Gathering for Bulk Loads
• Real Time Statistics Gathering During DML Operations
• Basic and Advanced Table Compression
• Advanced Index Compression
• Automatic Indexing with DBMS_AUTO_INDEX
• Scalable Sequences
• Quarantine of Runaway SQL Statements

Module 25: Tune Database I/O

• Overview of Database I/O Management
• I/O Calibration
• About Data Files
• Manually Distributing I/O
• Sample Configurations
• Asynchronous and Synchronous I/O
• Automatic Storage Management (ASM)

Module 26: Summary of Oracle Performance Tuning

• Potential Impact of Initialisation Parameters on Performance
• Memory Management
• Tablespace Types
• DB_BLOCK_SIZE
• Size the Redo Log Buffer and the Redo Log Files