Course Outline

Application Tuning Methodology

  • Identifying the Problem
  • Diagnosing the Cause
  • Applying the Solution

SQL Command Execution Process

  • Parsing and Sharing Cursors
  • Adaptive Cursors

Query Execution Plan Analysis

  • Methods for Obtaining Hypothetical and Actual Query Plans (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace)
  • Marking the Order of Algorithm Steps
  • Interpreting the Content of the Execution Plan Tree
  • Adaptive Plans

Cost-Based Optimization Process and Controlling the Cost-Based Optimizer

  • Properties of Cost-Based and Rule-Based Optimization
  • Session and Instance Parameters
  • Hints
  • Query Plan Patterns (Outlines)
  • Query Plan Management (Baselines)
  • Patching Commands
  • Profiles and SQL Tuning Advisor

Statistics and Histograms

  • The Impact of Statistics and Histograms on Performance
  • Methods for Collecting Statistics and Histograms
  • Strategies for Counting and Estimating Statistics, Ad Hoc Sampling
  • Managing Statistics: Locking, Copying, Editing, Automating Collection, Monitoring Changes
  • Multicolumn Statistics, Expression-Based Statistics
  • System and Dictionary Statistics
  • Adaptive Statistics

Full Scan Optimization through Proper Space Management

  • When to Use Full Scans
  • Space Allocation in Blocks and Segments, High Water Mark, PCTFREE
  • The Impact of DML Operations and Space Allocation Methods on Read Performance
  • Loading Data via Conventional and Direct Paths
  • Physical Reorganization of Data, Truncation, Defragmentation, Reconstruction

Full Scan Optimization through Physical Partitioning of "Hot Data"

  • Temporary Tables
  • Partitioning
  • Materialized Views

Full Scan Optimization through Data Compression

  • OLTP Compression
  • OLAP Compression

Index-Based Read Optimization

  • The Concept of ROWID
  • BTREE Index Structure
  • Comparing the Efficiency of Accessing Data via BTREE Indexes and FULL SCAN
  • The Impact of Indexes on DML Operations
  • Strategies for Creating and Dropping Indexes
  • Good and Bad Indexes, the Impact of Physical Data Distribution Entropy on Index Usage Costs
  • Properties and Statistics of Indexes
  • Types of Reads: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
  • Types of Indexes: Unique, Functional, Multicolumn, Reversed Key, Local/Global, Virtual, Private
  • Index-Organized Tables (IOT)
  • Bitmap and Concatenated Indexes

Sorting Process Optimization

  • In-Memory Sorting
  • Indexed Sorting
  • Linguistic Sorting

Join and Subquery Optimization

  • Join Methods: MERGE, HASH, NESTED LOOP
  • Joins in OLTP and OLAP Systems
  • Star Joins
  • Order of Joins
  • Outer Joins

Performance Monitoring and Identifying Bottlenecks through SQLTrace

  • Tracing Database Sessions/Processes
  • Tracing Application Sessions/Users in the Connection Pooling Model to the Database
  • Tools: TkProf, TrcSess

PL/SQL Code Performance

  • Using Literal Values in SQL Commands
    - Cursor Sharing Rules
    - Adaptive Cursors
  • Proper SQL and PL/SQL Communication
    - Cursors and Bulk Operations
    - User-Defined Functions in SQL
    - Function Result Caching
  • Passing Parameters by Copy/Reference
  • Inlining Short Procedures at Compile Time
  • Compiler Optimization Levels
  • Native Compilation
  • Other Aspects of PL/SQL Optimization

Requirements

Fluent use of the SQL and PL/SQL languages. Practical experience working with Oracle or another relational database management system.

 28 Hours

Number of participants


Price Per Participant (Exc. Tax)

Open Training Courses require 5+ participants.

Testimonials (7)

The teaching style and substantive content of the trainer

Lukasz - Sygnity
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

The instructor provided the opportunity to perform examples on one's own environment, although this was not required. Thanks to this, there was no problem that I didn't keep up (on other courses with different instructors, I sometimes had trouble keeping up with more complex topics)

Pawel - Sygnity
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

Practical knowledge of the instructor

Piotr - Sygnity
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

The method of translating the material and the examples shown.

Marek Jakimowicz
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

Extensive knowledge based on experienced leadership

Angelika
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

Many experiments and interesting facts from the instructor's experience to support theoretical knowledge.

Przemyslaw Piatek - SOFTLOG Sp. z o.o. Sp.k.
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

A solid base of theoretical and practical knowledge, supported by examples from daily work.

Marek Gregorczyk - Biuro Projektowania Systemow Cyfrowych sp. z o.o.
Course - Tuning SQL i PL/SQL dla programistów

Machine Translated

Provisional Courses

Tuning SQL and PL/SQL for Developers

2026-02-23 09:00
28 hours
Katowice
4130 PLN (Online)
4530 PLN (Classroom)

Tuning SQL and PL/SQL for Developers

2026-03-09 09:00
28 hours
Kielce
4130 PLN (Online)
4530 PLN (Classroom)

Tuning SQL and PL/SQL for Developers

2026-03-23 09:00
28 hours
Ostrowiec Świętokrzyski
4130 PLN (Online)
4530 PLN (Classroom)

Tuning SQL and PL/SQL for Developers

2026-04-06 09:00
28 hours
Olsztyn
4130 PLN (Online)
4330 PLN (Classroom)

Related Courses

Oracle Database: Program with PL/SQL

 21 Hours

PL/SQL (Procedural Language/SQL) is Oracle’s extension to SQL that enables developers to write procedural logic directly inside the database.

This instructor-led, live training (online or onsite) is aimed at intermediate-level developers and technical professionals who wish to write robust PL/SQL programs, manage exceptions, and create reusable procedures, functions, and packages in Oracle Database.

By the end of this training, participants will be able to:

  • Create PL/SQL blocks, procedures, and functions to encapsulate business logic.
  • Use control structures, cursors, and exception handling effectively.
  • Define and manage packages, triggers, and stored logic securely.
  • Debug and test PL/SQL code using standard Oracle tools.

Format of the Course

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.

Course Customization Options

  • To request a customized training for this course, please contact us to arrange.
Read more...

Oracle Database 19c PL and Advanced SQL

28 Hours

This instructor-led, live training in Poland (online or onsite) is aimed at intermediate-level database administrators who wish to have an in-depth understanding of Oracle Database 19c PL/SQL programming and advanced SQL techniques.

By the end of this training, participants will be able to:

  • Understand the fundamentals and advanced features of PL/SQL.
  • Develop advanced SQL queries to retrieve and manipulate data efficiently.
  • Implement PL/SQL programming constructs to manage data and database operations.
  • Optimize SQL queries for better performance.
  • Use advanced PL/SQL features like collections, bulk processing, and error handling.
  • Learn to debug and manage PL/SQL programs effectively.
Read more...

Oracle: Programming with PL/SQL I

 28 Hours

This instructor-led, live training in Poland (online or onsite) is aimed at beginner-level developers and database administrators who wish to learn PL/SQL to automate database tasks, implement business logic, and develop robust database applications.

By the end of this training, participants will be able to:

  • Develop efficient PL/SQL programs to automate database operations and integrate SQL commands effectively.
  • Create reusable program units, including procedures, functions, packages, and triggers, for modular and scalable applications.
  • Implement advanced data structures like associative arrays and manage query results using cursors.
  • Handle errors robustly and secure code with encryption, obfuscation, and conditional compilation techniques.
  • Apply PL/SQL in real-world scenarios, leveraging built-in packages for file handling, email automation, and other advanced functionalities. 
Read more...

Oracle: Programming with PL/SQL II

 21 Hours

This instructor-led, live training in Poland (online or onsite) is aimed at advanced-level developers and database administrators who wish to use PL/SQL to develop high-performing, secure, and scalable database solutions while leveraging advanced programming techniques and integrations.

By the end of this training, participants will be able to:

  • Work with complex data types, collections, and bulk operations for efficient data management.
  • Develop object types, table functions, and custom aggregates to enhance database functionality.
  • Apply performance tuning techniques, use native compilation, and safeguard against SQL injection.
  • Implement application contexts, VPD, and secure program units for robust database solutions.
Read more...

Oracle PL/SQL - introduction to programming

 14 Hours

The training is intended for people who work with databases as programmers using the SQL language and need to extend the possibilities of work with the mechanisms of the procedural language.

Read more...

Oracle PL/SQL advanced programming

 21 Hours

The training is intended for programmers who know the basics of SQL and PL/SQL, but want to learn more advanced mechanisms of the language, and want to create database applications based on stored PL/SQL programs.

Read more...

Oracle - Tuning SQL commands and performance of PL/SQL programs

 28 Hours

The training clearly and extensively presents issues that allow you to understand the internal mechanisms that occur when executing SQL commands and PL/SQL programs, monitor and diagnose performance problems and implement solutions. It enables effective management of the process of optimizing SQL commands and PL/SQL programs.

Read more...

PL/SQL in Oracle 19c

 21 Hours

This instructor-led, live training in Poland (online or onsite) is aimed at intermediate-level developers and administrators who wish to have a solid PL/SQL programming foundation to develop efficient and robust database applications using Oracle 19c.

By the end of this training, participants will be able to:

  • Gain a comprehensive understanding of what PL/SQL is and its advantages over standard SQL.
  • Learn how to declare and use variables and different data types within PL/SQL blocks.
  • Apply control structures like IF-THEN-ELSE, CASE statements, and loops to create robust PL/SQL programs.
  • Understand and implement explicit and implicit cursors for data retrieval.
  • Handle exceptions effectively using predefined and user-defined exceptions.
  • Develop and manage triggers to automate and enforce business rules.
  • Create and use PL/SQL packages to encapsulate and modularize code for reuse and maintainability.
Read more...

Related Categories

PL/SQL
PL/SQL
Oracle PL/SQL
Oracle PL/SQL