Oracle Database 12c/18c/19c Performance Tuning

Day 1

• Lesson 1 : Introduction
• Lesson 2 : Basic Tuning Tools
• Lesson 3 : Using Automatic Workload Repository
• Lesson 4 : Defining Problems

Day 2

• Lesson 5 : Using Metrics and Alerts
• Lesson 6 : Baselines
• Lesson 7 : Using AWR-Based Tools
• Lesson 8 : Monitoring an Application

Day 3

• Lesson 9 : Identifying Problem SQL Statements
• Lesson 10 : Influencing the Optimizer
• Lesson 11 : Using SQL Performance Analyzer
• Lesson 12 : SQL Performance Management

Day 4

• Lesson 13 : Using Database Replay
• Lesson 14 : Tuning the Shared Pool
• Lesson 15 : Tuning the Buffer Cache
• Lesson 16 : Tuning PGA and Temporary Space

Day 5

• Lesson 17 : Automatic Memory Management
• Lesson 18 : Tuning Segment Space Usage
• Lesson 19 : Tuning I/O
• Lesson 20 : Performance Tuning Summary

Lesson 1 : Introduction

• Identify tuning tools
• Utilize a tuning methodology

Lesson 2 : Basic Tuning Tools

• View the top wait events to determine the highest wait
• View the time model to diagnose performance issues
• Use dynamic performance views to view statistics and wait events
• Use Enterprise Manager Monitoring
• Identify the key tuning components of the alert logs
• Identify the key tuning components of user trace files

Lesson 3 : Using Automatic Workload Repository

• Create and manage AWR snapshots
• Generate AWR reports
• Create Compare Periods reports

Lesson 4 : Defining Problems

• Identify performance issues
• Set tuning priorities
• Interpret tuning diagnostics
• Tune for life cycle phase

Lesson 5 : Using Metrics and Alerts

• View metrics by using the metrics history views
• Create metric thresholds
• View alerts

Lesson 6 : Baselines

• Create AWR baselines
• Enable adaptive thresholds
• Create AWR baselines for future time periods

Lesson 7 : Using AWR-Based Tools

• Describe tuning automatic maintenance tasks
• Generate ADDM reports
• Generate Active Session History (ASH) reports

Lesson 8 : Monitoring an Application

• Configure and manage services
• Use services with client applications
• Use services with the Database Resource Manager
• Use services with the Scheduler
• Set performance-metric thresholds on services
• Configure services aggregation and tracing

Lesson 9 : Identifying Problem SQL Statements

• Describe SQL statement processing
• Describe the role of the optimizer
• View the SQL statement statistics\
• Identify the SQL statements that perform poorly
• Generate and view an execution plan
• Generate a tkprof report
• Generate an optimizer trace

Lesson 10 : Influencing the Optimizer

• Describe the optimizer’s behavior
• Adjust parameters to influence the optimizer
• Adjust data structures to influence the optimizer

Lesson 11 : Using SQL Performance Analyzer

• Identify the benefits of using SQL Performance Analyzer
• Describe the SQL Performance Analyzer workflow phases
• Use SQL Performance Analyzer to ascertain performance gains following a database
  change
• Use SQL Performance Analyzer to test the impact of proposed changesr

Lesson 12 : SQL Performance Management

• Manage changes to optimizer statistics
• Capture SQL profiles
• Use SQL Access Advisor
• Set up SQL Plan Management
• Set up various SQL Plan Management scenarios

Lesson 13 : Using Database Replay

• Identify the benefits of using Database Replay
• List the steps involved in Database Replay
• Use Enterprise Manager to record and replay workloads

Lesson 14 : Tuning the Shared Pool

• Diagnose and resolve hard-parsing problem
• Diagnose and resolve soft-parsing problem
• Size the shared pool
• Diagnose and resolve shared pool fragmentation
• Keep objects in the shared pool
• Size the reserved area
• Manage the results cache

Lesson 15 : Tuning the Buffer Cache

• Describe the buffer cache architecture
• Size the buffer cache
• Resolve common performance issues related to the buffer cache
• Use common diagnostic indicators to suggest a possible solution

Lesson 16 : Tuning PGA and Temporary Space

• Diagnose PGA memory issues
• Size the PGA memory
• Diagnose temporary space issues
• Specify temporary tablespace parameters for efficient operation

Lesson 17 : Automatic Memory Management

• Use memory advisors to size dynamic memory areas
• Enable Automatic Shared Memory Manager
• Enable Enterprise Manager memory parameters
• Set minimum size of auto-tuned SGA components
• Use the SGA advisor to set SGA_TARGET
• Enable Automatic Memory Management
• Use the Memory Advisor to set overall memory parameters

Lesson 18 : Tuning Segment Space Usage

• Tune segment space management
• Use Segment Space Advisor
• Convert to Automatic Segment Space Management
• Tune block space management
• Diagnose and correct row migration
• Diagnose table fragmentation
• Use table compression