Parallel Processing in Oracle Database 11g - Seminar DB-11g-PARP

Audience:

• Application Developers
• Data Warehouse Administrator
• Database Administrators
• Developer

Prerequisites:

• Knowledge of database administration

Objectives:

• Use Database Resource Manager to complement the control of parallel processing usage
• Define why and when to use parallel processing
• List the SQL statements and data loading utilities that can benefit from parallel processing
• Explain the basic concepts and theory associated with parallel execution
• Use a variety of parallel execution features with different SQL statements
• Read and evaluate execution plans for parallelized statements
• Use new Oracle 11g, Release 2 parallel processing features including Auto DOP, statement-queuing, and in-memory parallel execution
• Examine specific configurations such as RAC database to and Database Machine with parallel execution
• Troubleshoot parallel processing issues such as no parallel execution, unexpected DOP, or performance decrease
• Trace parallel execution to provide information to Oracle customer support

Topics:

Parallel execution concepts

• Benefits of parallel processing
• When to use parallel processing?
• Query Coordinator and parallel execution (PX) servers
• Parallel execution communication
• The producer / consumer model
• Execution plan basics

Using Manual DOP

• Statements that can be parallelized
• SELECT with single parallel table scan and the explain plan
• SELECT with parallel hash join and the explain plan
• Parallelized SELECT with partition wise join and the advantages
• Parallelized DML operations

Using Auto DOP

• Auto DOP versus manual DOP
• Auto DOP parameters
• Auto DOP behavior
• Impact of Auto DOP on other parameters
• SELECT explain plan with Auto DOP
• Auto DOP in RAC environment

Using Statement Queuing

• Comparing statement queuing to minimal DOP guarantee
• Setting parameters
• When to choose statement queuing
• Using Database Resource Manager with statement queuing
• Viewing queued statements

In-Memory Parallel Execution

• The goal of In-Memory Parallel Execution
• How SELECT works with and without in-memory parallel execution
• Set parameters to use In-Memory parallel execution
• In-memory PX examples

Parallel Execution and Data Loading

• DataPump export / import
• SQL*Loader
• External tables: applying parallel execution as best practice

Troubleshoot situations when parallel processing does not proceed as desired

• When parallel processing does not proceed as desired
• When no parallel processing occurs
• When Auto DOP computes unexpected DOP
• When Statement Queuing starts unexpectedly
• When performance decreases due to parallel execution
• Tracing parallel execution

Managing a Mixed Workload with DBRM

• Take advantage of DBRM to manage concurrent parallel executions with mixed workloads
• Use plan directives to manage concurrent parallel executions
• How to reject queries
• Using dynamic switching
• Understanding how directives interact