Oracle Database: Performance Management and Tuning Training

Required:

• Knowledge of Oracle Database Administration Workshop content

• Practical experience with SQL and PL/SQL

• Understanding of Oracle architecture and basic monitoring

• Familiarity with Linux OS

Recommended:

• Oracle Database Install and Upgrade Workshop

By the end of this training, you will have gained knowledge and skills in the following areas:

• Apply Oracle’s tuning methodology

• Diagnose SQL and instance-level performance problems

• Utilize tools like AWR, ADDM, ASH, and SQL Tuning Advisor

• Analyze SQL execution plans and improve performance

• Control and influence the SQL Optimizer

• Monitor systems in real time and manage performance alerts

• Optimize memory configurations

• Leverage In-Memory Column Store for faster SQL performance

• Monitor applications and manage services using Oracle tools

1. Introduction to Performance Management

• Oracle's performance lifecycle methodology

• Identifying OS-level issues using Enterprise Manager

• Using the Time Model to analyze performance

• Diagnosing with Wait Events and statistics

• Monitoring through logs and trace files

2. Monitoring Tools and AWR

• Monitoring with Cloud Control and SQL Developer

• Installing and using Statspack: snapshots and reports

• Utilizing AWR and interpreting reports

• Managing server-generated alerts

• Creating and using performance baselines

• Configuring automated maintenance tasks

3. Using ADDM and ASH Effectively

• Analyzing workloads with ADDM

• Investigating incidents with ASH data

• Generating and reviewing ASH reports

• Using Emergency Monitoring and Real-Time ADDM

4. SQL Statement Lifecycle

• Index management and optimization

• Table management: block handling, shrinking, compression

• Introduction to Query Optimizer

• Reading execution plans, AUTOTRACE and EXPLAIN PLAN

• SQL Trace and TKPROF analysis

5. Optimizer Behavior and Plan Control

• Controlling Optimizer with initialization parameters

• Managing Optimizer statistics

• Leveraging SQL Plan Management

6. SQL Advisory Tools

• Improving SQL with SQL Tuning Advisor

• Enhancing access paths via SQL Access Advisor

7. Real Application Testing

• Using SQL Performance Analyzer to validate changes

• Performing full workload testing with Database Replay

• Monitoring real-time database operations

• Managing services to track and optimize applications

8. Memory Structures and Tuning

• Shared Pool tuning and sizing

• Buffer Cache performance management

• Configuring PGA and temporary tablespace

• Managing Large Pool and Automatic Memory Management

9. In-Memory Column Store

• Overview and setup of In-Memory Column Store

• Enhancing SQL performance using In-Memory features

• Integrating In-Memory with other Oracle tools