Oracle 19c SQL Performance Tuning - 2 days Online Classroom

Oracle 19c SQL Performance Tuning - 2 days Online Classroom

Improve the speed and efficiency of your Oracle databases with the Oracle 19c SQL Performance Tuning course. This practical two-day training equips SQL developers, database professionals, and application support teams with the techniques needed to identify, analyse, and resolve SQL performance issues. Through hands-on exercises, you'll learn how Oracle's optimizer works, interpret execution plans, use SQL Trace and Autotrace, optimise joins and indexes, and apply SQL Tuning Advisor recommendations. By the end of the course, you'll be able to write faster, more efficient SQL that enhances application performance and database responsiveness.

Key Features

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

Learning Outcomes

• Understanding the Oracle Optimizer: Gain a clear understanding of how the Oracle Optimizer determines the most efficient execution plan for your SQL statements. You'll explore cost-based optimisation, optimizer modes, dynamic statistics, adaptive optimisation, and query transformations. By understanding how Oracle evaluates and executes queries, you'll be better equipped to improve SQL performance and influence execution plans effectively.
• Optimising joins and data access: Learn how Oracle processes different join methods, including nested loop, hash, merge, and star joins, and discover when each delivers the best performance. You'll also examine access paths, indexing strategies, partitioning, and optimizer statistics to improve query efficiency and reduce execution time across complex database workloads.
• Using hints, views, and query transformations: Develop the skills to fine-tune SQL execution by applying Oracle hints to influence optimizer decisions, join methods, and access paths. You'll also learn how Oracle handles views, inline views, and the WITH clause, enabling you to optimise query transformations and achieve more consistent, high-performing SQL execution.

Target Audience

• SQL Developers
• Oracle Database Developers
• Application Developers
• Oracle Database Administrators (DBAs)
• Technical Support Engineers
• IT professionals responsible for improving Oracle SQL performance

Prerequisites

• A working knowledge of Oracle SQL
• Experience writing SQL queries in Oracle Database
• Knowledge equivalent to an Oracle SQL fundamentals course is recommended
• Basic familiarity with Oracle database concepts and SQL Developer is beneficial.

Course Outline

Day 1: Oracle SQL Performance Tuning Fundamentals

• Understand Oracle SQL performance tuning concepts, roles, and best practices for developers and DBAs.
• Analyse SQL execution using Explain Plan, Autotrace, SQL Trace, and performance analysis tools.
• Explore the Oracle Optimizer, cost-based optimisation, adaptive features, and statistics management.
• Learn how Oracle processes sorting operations and manages temporary storage during query execution.
• Design and optimise indexes, including B-tree, composite, and descending indexes for improved performance.
• Identify optimal access paths and understand the factors that influence Oracle's execution plans.

Day 2: Advanced SQL Optimisation Techniques

• Implement advanced indexing strategies, including bitmap, function-based, compressed, invisible, and partitioned indexes.
• Optimise SQL joins by selecting the most efficient join methods and access paths.
• Improve SQL performance using SQL Tuning Advisor and the DBMS_SQLTUNE package.
• Enhance query design with sequence caching, views, inline views, and the WITH clause.
• Influence execution plans using Oracle hints, query transformations, and join optimisation techniques.
• Apply SQL performance best practices, including bind variables, result caching, cursor sharing, and PL/SQL 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.

Who should attend the Oracle 19c SQL Performance Tuning course?

This course is ideal for SQL developers, application developers, database designers, Oracle DBAs, and technical support professionals who want to improve the performance of SQL queries in Oracle 19c environments. It is particularly valuable for anyone responsible for developing, maintaining, or optimising Oracle database applications.

How do indexes improve SQL performance in Oracle 19c?

Indexes help Oracle locate data more efficiently by minimising full table scans and reducing query execution time. During the course, you'll learn how to select and implement the most appropriate index types—including B-tree, bitmap, and function-based indexes—and understand how they influence the Oracle Optimizer to deliver faster, more efficient queries.

What practical skills will I gain from this course?

By the end of the course, you'll be able to analyse SQL execution plans, identify performance bottlenecks, use Oracle tuning tools effectively, optimise indexing strategies, and apply optimizer hints to improve query performance. These practical skills will help you diagnose and resolve real-world Oracle SQL performance issues with greater confidence and efficiency.