Oracle Database: Performance Management and Tuning Training in Switzerland

The Oracle Database: Performance Management and Tuning workshop is an advanced-level training designed for database administrators who aim to identify, diagnose, and optimize performance issues in Oracle databases. Participants will explore Oracle's comprehensive tuning methodologies, monitoring tools, SQL performance techniques, memory configuration strategies, AWR/ASH diagnostics, and In-Memory capabilities in depth.

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 the course, participants will:

• 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