Detailed Course Outline
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