My Oracle Support Banner

Configuring Parallel Statement Queuing to Manage Mixed Priority Workloads in a Data Warehouse (Doc ID 1359043.1)

Last updated on MARCH 12, 2021

Applies to:

Oracle Database Products > Oracle Database Suite > Oracle Database
Oracle Database Products > Exadata
Information in this document applies to any platform.

Purpose

A common data warehouse scenario is to run the following two workloads concurrently: 

  1. Batch jobs.  A batch job is typically comprised of multiple queries, such as a collection of reports that a marketer initiates in the morning.  Performance is typically measured as the time needed to complete the entire batch job. 
  2. Tactical queries.  A tactical query is a high priority operation, typically generated by a real-time application.  It needs to complete as quickly as possible. 

The database often does not have sufficient resources to run all queries at the same time at their ideal degree-of-parallelism.  When this happens, parallel queries are downgraded.  When queries in the batch job are downgraded, the elapsed time for the batch job can grow considerably.  This is because the downgraded parallel queries take a much longer time to complete and drag out the end time of the batch job.  When tactical queries are downgraded, their response time increases, potentially resulting in unacceptable quality of service.

The solution to this problem is to use Parallel Statement Queuing.  It was introduced in 11.2.0.2 and offers the following features:

  1. When no more parallel servers are available, the parallel statement is queued.  The parallel statement is dequeued as parallel servers are freed up.
  2. A resource plan can be used to control the order of the parallel statement queue.  When parallel servers are freed up, the resource plan is used to select a consumer group.  The parallel query at the head of this consumer group's queue is run next. 
  3. Parallel servers can be reserved for critical consumer groups.

This document illustrates how to configure Parallel Statement Queuing for this scenario.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Details
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.