Often it is important for a report or batch job that consists of multiple parallel statements to complete as quickly as possible. For example, when many reports are launched simultaneously, you may want all of the reports to complete as quickly as possible. However, you also want some specific reports to complete first, rather than all reports finishing at the same time.
If a report contains multiple parallel statements and
PARALLEL_DEGREE_POLICY is set to
AUTO, then each parallel statement may be forced to wait in the queue on a busy database. For example, the following steps describe a scenario in SQL statement processing:
serial statement parallel query - dop 8 -> wait in queue serial statement parallel query - dop 32 -> wait in queue parallel query - dop 4 -> wait in queue
For a report to be completed quickly, the parallel statements must be grouped to produce the following behavior:
start SQL block serial statement parallel query - dop 8 -> first parallel query: ok to wait in queue serial statement parallel query - dop 32 -> avoid or minimize wait parallel query - dop 4 -> avoid or minimize wait end SQL block
To group the parallel statements, you can use the
END_SQL_BLOCK procedures in the
DBMS_RESOURCE_MANAGER PL/SQL package. For each consumer group, the parallel statement queue is ordered by the time associated with each of the consumer group's parallel statements. Typically, the time associated with a parallel statement is the time that the statement was enqueued, which means that the queue appears to be FIFO. When parallel statements are grouped in a SQL block with the
END_SQL_BLOCK procedures, the first queued parallel statement also uses the time that it was enqueued. However, the second and all subsequent parallel statements receive special treatment and are enqueued using the enqueue time of the first queued parallel statement within the SQL block. With this functionality, the statements frequently move to the front of the parallel statement queue. This preferential treatment ensures that their wait time is minimized.
Oracle Database PL/SQL Packages and Types Reference for information about the