Parallel Execution Masterclass (ENG)

Why attend this workshop?

You're generally interested in using the Oracle SQL Parallel Execution feature and therefore want to understand more about it? Or you're already using it regularly, for example in a Data Warehouse environment or as part of batch processing?

In that context, have you sometimes asked yourself questions like the following:

- How do I read execution plans? Are there any special rules for parallel execution plans? And if yes, how do these look like?

- How does Oracle execute such an execution plan at runtime?

- What are all those PX operations for in a parallel execution plan? And what do these additional columns in a parallel execution plan tell me?

- How does this automatic parallel execution work at runtime? What gets executed in parallel and how is the work automatically distributed? What can go wrong with this distribution? If something goes wrong, can I influence this, and if yes, how?

- Why are sometimes parallel executions not much faster than serial ones, why sometimes even slower, and why do parallel executions sometimes require so much more PGA memory and TEMP space than corresponding serial executions?

- How can I analyze parallel executions at runtime in order to determine if I make good use of parallel execution or not? Why are the known methods for analysing serial execution not well suited for parallel execution?

- How can I speed up parallel execution from ground up at design time?

- Which new parallel execution features were added since Oracle database release 12c/18/19c?

This workshop provides comprehensive answers to these questions. To a significant degree the content is exclusively researched by the conductor of this workshop and not available from other sources, not even from official Oracle courses.

So if you already have solid knowledge of how SQL gets executed in the database,
... so if you already have a basic understanding of what an execution plan is but you might unsure how to exactly read it
... if you're looking for exclusive material about the Parallel Execution feature
... then come and attend this workshop!

Objectives

- Get a deep and thorough understanding of Oracle's Parallel Execution feature
- Learn how to properly troubleshoot Parallel Execution

Required skills

Thorough understanding of Oracle Architecture, SQL, SQL*Plus scripting

Target audience

Experienced DBAs and developers

Course description

In order to process large amounts of data as quickly as possible and leverage the capabilities of today's hardware Oracle offers its Parallel Execution feature as part of the Enterprise Edition license. However as with most features there's no "go_faster = true" switch, and Parallel Execution is no exception to this rule. There might be a lot of cases where simply adding parallelism to a statement execution doesn't deliver the expected reduction in runtime. It's therefore crucial to understand how Oracle Parallel Execution works, what its building blocks are, what kind of diagnostic possibilities are offered to systematically analyze Parallel Execution performance and how to apply appropriate measures.

 Topics covered

- Execution plans: How to read and understand serial execution plans

- Introduction to Oracle's Parallel Execution feature

- How Oracle executes a statement in parallel

- Parallel distribution methods / partitioning

- Execution plans: How to read and understand parallel execution plans

- Runtime analysis of Parallel Execution with / without Diagnostics / Tuning Pack

- Understanding, analysing and addressing Parallel Execution skew

- 12c/18c/19c new features