Oracle Database: Performance Management and Tuning Training in United States of America

Oracle Database: Performance Management and Tuning is designed for database professionals who need to identify, diagnose, and resolve performance issues in Oracle Database environments using a structured tuning approach.

Throughout the course, participants apply Oracle's tuning methodology and work with Oracle-provided tools for monitoring SQL and instance performance. The training also covers the use of database advisors for proactively addressing performance problems, identifying inefficient SQL statements, improving SQL execution, and monitoring instance behavior through Enterprise Manager.

A major part of the course focuses on optimizing database components by using instance parameters, memory management techniques, and Oracle performance analysis tools.

Suggested Prerequisites

• Previous attendance in Oracle Database: Install and Upgrade Workshop
• Familiarity with Oracle Database installation
• Familiarity with Oracle Database configuration concepts

Required Prerequisites

• Knowledge equivalent to Oracle Database: Administration Workshop
• Basic understanding of the Linux operating system
• Working knowledge of SQL and PL/SQL packages
• Basic knowledge of Oracle Database architecture
• Familiarity with fundamental database monitoring procedures

After completing the course, participants will be able to:

• Explain Oracle Database Performance Management concepts.
• Work with AWR Performance Statistics.
• Use AWR-based performance tools.
• Analyze and improve SQL statement performance.
• Influence Optimizer behavior when required.
• Use tuning analyzers and database advisors.
• Monitor database operations and application activity.
• Manage Oracle Database memory components.
• Configure and use the In-Memory Column Store feature for performance improvement.

Performance Management Overview and Database Preparation

• Defining the scope of performance problems
• Understanding the phases of the tuning life cycle
• Using Enterprise Manager to identify operating system performance issues
• Diagnosing performance problems with the Time Model
• Using statistics and Wait Events for performance analysis
• Monitoring performance through log and trace files
• Monitoring database performance with Enterprise Manager Cloud Control
• Using SQL Developer for performance monitoring
• Reviewing performance information with Statspack
• Installing Statspack
• Creating snapshots
• Generating Statspack reports
• Working with the Automatic Workload Repository
• Configuring and reviewing server-generated alerts
• Using performance baselines
• Configuring and managing Automated Maintenance Tasks
• Analyzing performance with ADDM
• Using AWR-based tools to identify performance issues
• Using Active Session History data for First Fault System Analysis
• Generating and reviewing ASH reports
• Working with Emergency Monitoring
• Using Real-Time ADDM to investigate performance problems

SQL Statement Processing Overview

• Maintaining indexes
• Maintaining tables
• Managing block space
• Shrinking segments
• Using Advanced Index Compression
• Analyzing the impact of excessive blocks
• Introduction to the Query Optimizer
• Controlling Optimizer behavior with initialization parameters
• Understanding execution plans
• Using AUTOTRACE
• Using EXPLAIN PLAN
• Reviewing execution plans with SQL Trace and TKPROF
• Managing Optimizer Statistics
• Using Automatic SQL Tuning
• Working with SQL Plan Management

SQL Advisors Overview

• Using SQL Tuning Advisor
• Using SQL Access Advisor
• Applying advisor recommendations to improve SQL performance

Real Application Testing Components

• Using SQL Performance Analyzer to assess the impact of changes
• Testing system performance with Database Replay
• Implementing Real-Time Database Operation Monitoring
• Monitoring applications by using Services

Memory Structures Overview

• Managing Shared Pool performance
• Managing Buffer Cache performance
• Managing PGA performance
• Managing Temporary Space performance
• Configuring the Large Pool
• Using Automatic Shared Memory Management
• Introduction to In-Memory Column Store
• Configuring the In-Memory Column Store feature
• Improving SQL performance with In-Memory Column Store
• Using In-Memory Column Store together with other Oracle Database features