Oracle Database 19c DBA Performance Tuning & Management

Oracle Database 19c DBA Performance Tuning & Management - 5 days Online Classroom

Master the techniques used by experienced Oracle DBAs to optimise database performance, eliminate bottlenecks, and maximise system efficiency. The Oracle Database 19c DBA Performance Tuning & Management course provides practical, hands-on training in diagnosing performance issues, tuning SQL statements, optimising memory and storage, and using Oracle's powerful diagnostic tools such as AWR, ADDM, ASH, and SQL Trace. Through real-world labs, you'll learn how to proactively monitor Oracle databases, improve query execution, and apply proven tuning strategies that reduce downtime and support high-performing enterprise environment

Key Features

• Course and material in english
• Intermediate level
• 5 days of live instructor-led virtual training (8 hours / day)
• Interactive hands-on live labs
• All relevant course materials
• 60+ recommended study hours
• Certificate included

Learning Outcomes

• Performance monitoring and diagnostic tools: Learn to use Oracle's built-in performance tools, including AWR, ADDM, ASH, and Performance Hub, to monitor database health and diagnose performance issues. You'll interpret reports, analyse snapshots and baselines, configure alerts, and use performance metrics to proactively identify and resolve bottlenecks before they impact users.
• Real-time performance analysis: Develop the skills to monitor Oracle databases in real time using tools such as DBMS_MONITOR and Active Session History. You'll track database sessions, workloads, and active operations, enabling you to quickly detect performance issues, investigate resource usage, and respond effectively to live system activity.
• Optimising enterprise and data warehouse workloads: Discover advanced performance tuning techniques for high-volume Oracle environments. You'll explore automatic indexing, data compression, real-time statistics collection, scalable sequences, bitmap-based functions, and other optimisation features that improve SQL performance, reduce resource consumption, and enhance large-scale database efficiency.

Target Audience

• Oracle Database Administrators (DBAs)
• Database Performance Engineers
• Oracle Database Support Engineers
• Oracle Infrastructure and Operations Specialists
• Technical Consultants responsible for Oracle environments
• Experienced Oracle professionals looking to strengthen performance tuning expertise

Prerequisites

• A solid understanding of Oracle Database administration
• Experience working with Oracle SQL
• Knowledge equivalent to Oracle Database 19c Administration courses
• Familiarity with Oracle database architecture and management
• Practical experience administering Oracle databases in production environments

Course Outline

Day 1: Oracle Performance Tuning Fundamentals

• Understand Oracle performance tuning principles, objectives, and DBA best practices.
• Develop a structured monitoring strategy with measurable performance goals.
• Analyse SQL execution using Explain Plan, Autotrace, SQL Trace, and execution plans.
• Explore the Oracle Optimizer, execution strategies, and statistics management with DBMS_STATS.
• Learn how sorting operations affect query performance and temporary storage usage.
• Optimise database performance using effective indexing strategies, access paths, and index tuning techniques.

Day 2: Advanced SQL Optimisation

• Implement advanced indexing techniques, including bitmap, function-based, compressed, and partitioned indexes.
• Optimise table joins using the most effective join methods and access paths.
• Analyse and improve SQL performance with SQL Tuning Advisor and SQL Developer.
• Enhance query efficiency using sequences, views, inline views, and the WITH clause.
• Control execution plans with Oracle optimizer hints and query transformation techniques.
• Apply SQL and PL/SQL performance best practices, including result caching, bind variables, and cursor optimisation.

Day 3: Performance Diagnostics and Memory Tuning

• Diagnose database performance issues using Oracle monitoring tools, wait events, and dynamic performance views.
• Improve SQL efficiency by maintaining indexes, tables, and storage structures.
• Analyse SQL workloads and compare tuning results using SQL Performance Analyzer.
• Optimise execution plans with SQL Performance Management, SQL Monitoring, and the SQL Access Advisor.
• Configure and manage Automatic Memory Management (AMM) and Shared Memory (SGA).
• Tune the Shared Pool, library cache, and SQL result cache to improve database performance.

Day 4: Oracle Monitoring and Workload Analysis

• Optimise the Database Buffer Cache to improve memory utilisation and query response times.
• Configure PGA memory and temporary tablespaces for efficient SQL processing.
• Create and analyse AWR snapshots, reports, and performance baselines.
• Use AWR, Performance Hub, ADDM, and ASH to identify and troubleshoot performance bottlenecks.
• Configure metrics, alerts, and thresholds for proactive database monitoring.
• Monitor performance trends and automate database health analysis using Oracle diagnostic tools.

Day 5: Enterprise Performance Optimisation

• Monitor database operations and application workloads in real time using Oracle monitoring tools.
• Trace sessions and applications with DBMS_MONITOR to diagnose performance issues.
• Apply advanced tuning techniques for large-scale databases, including automatic indexing, compression, and real-time statistics.
• Optimise database I/O performance through storage configuration, ASM, and efficient file management.
• Improve workload efficiency using scalable sequences, bitmap functions, and SQL optimisation features.
• Consolidate Oracle performance tuning best practices for memory, storage, statistics, and enterprise database optimisation.

FAQ

When is the class held?

The majority of the class is a full-day online course from morning to afternoon CET with 8 hours / day straight. However, please contact us to confirm the final detailed schedules.

Will I learn to use SQL Trace, Autotrace, and Explain Plan?

Yes. You'll gain hands-on experience using SQL Trace, Autotrace, and Explain Plan to evaluate SQL execution and identify performance bottlenecks. The course teaches you how to interpret execution plans and trace data so you can optimise queries and improve overall database performance.

How does the course cover memory and storage tuning?

You'll learn how to optimise Oracle memory components, including the System Global Area (SGA), Program Global Area (PGA), and Database Buffer Cache. The course also covers temporary tablespace management, storage configuration, and I/O tuning techniques to improve database efficiency and minimise performance bottlenecks.

Will I get practical experience with Oracle performance tuning?

Yes. The course includes hands-on labs that allow you to apply Oracle performance tuning techniques in realistic scenarios. You'll use industry-standard tools such as Explain Plan, SQL Trace, AWR, ADDM, and other Oracle diagnostic utilities to analyse workloads, identify bottlenecks, and implement effective performance improvements.