Recent site activity


Oracle Partitioning and Parallel Execution for Performance

Seminar overview

Oracle Database’s partitioning options can be used for help in three areas: VLDB information lifecycle manageability, data availability and performance. This seminar will mostly cover partitioning for performance, how to design partitioning for performance and how to write/troubleshoot SQL which should use partitioning for performance.

The second part of this section will cover parallel execution internals, tuning and troubleshooting, both in partitioned and non-partitioned environments and we also touch the topic of configuring parallel execution right on RAC.

Seminar duration and details

  • Seminar duration: 4 x 4 hour deep dives (4 half-days in a single week)
  • Venue: Online, virtual seminar at your desk (no travel!)
  • Audience: Senior DBAs, senior developers, database designers
  • Skill level: Intermediate to Advanced
  • Speaker: Tanel Poder
  • Price: 1196 USD per attendee
Check the next seminar dates and sign up here:

You will learn the following...

  • Best practices for partitioning (in different circumstances) and how to avoid common pitfalls
  • How to control partition pruning and measure whether desired partition pruning is happening
  • How to achieve partition-wise joins for joining large datasets
  • Things to be aware of when performing partition exchange loads
  • Understand how the parallel execution engine and row distribution works
  • Best practices for parallelism (in different circumstances) and how to avoid common pitfalls
  • How to troubleshoot parallel execution plans and find where the bottleneck is
Note that this seminar does not aim to cover every single different partitioning combination and teach the syntax of every partition maintenance command. You should already know what partitioning is and how to create partitioned tables. However you will learn the fundamental principles and internals behind partitioning and the Oracle features using it, so you will be able to decide what is best for your application and dataset yourself.

In this seminar we are not going to go deep into Exadata features as not everyone is using Exadata. So there will be a different class for that, which focuses solely on Exadata specific performance and design issues (but it is still based on the fundamentals and internals learned from this Partitioning and Parallel Execution class).

Seminar contents

1) Designing applications and databases with partitioning in mind

  • Designing for partition pruning
  • Designing for partition-wise joins
  • Choosing between local vs. global indexes
  • Designing for massive data loads
  • Using partition exchange loads
  • Partitioned objects, CBO and optimizer statistics collection

2) Troubleshooting partition pruning and partition exchange issues

  • Measuring which exact partitions are visited during the SQL execution
  • Why isn’t the desired partition pruning happening?
  • What causes the ORA-8103: object no longer exists and ORA-1410: invalid ROWID errors when exchanging partitions?

3) Understanding data flow in parallel execution plans

  • Understanding Data Flow Operators (DFO) and producer-consumer hierarchy
  • Monitoring long-running parallel queries
  • Monitoring data distribution and table queues

4) Achieving optimal data distribution in parallel execution plans

  • Avoiding data broadcasts and many-to-many distribution
  • Combining multiple rowsource operators onto a single level in parallel execution hierarchy
  • Parallel partition-wise joins
  • Best PX distribution for partitioned tables
  • Parallel execution considerations in RAC

5) Advanced Parallel Execution Troubleshooting

  • Tracing parallel execution and gathering detailed parallel execution stats
  • Monitoring IO throughput and detecting IO subsystem saturation

Next seminar dates

Check the next seminar dates and sign up here!