Database Reference
In-Depth Information
In addition to the default behavior just described, the Resource Manager provides several directives that allow
you to set up resource plans that take advantage of the following features:
•
Managing the order in which the sessions in the waiting list are dequeued
•
Limiting the slave processes usage at the consumer group level
•
Specifying a timeout
•
Defining critical SQL statements that bypass statement queuing (version 12.1 only)
•
Managing statement queuing in multitenant environments (version 12.1 only)
Detailed information about these features is provided in the
Oracle Database VLDB and Partitioning Guide
manual.
Parallel Queries
The following operations, in both queries and subqueries, can be executed in parallel:
•
Full table scans, full partition scans, and fast full index scans
•
Index full and range scans, but only if the index is partitioned (at a given time, a partition can
be accessed by a single slave process only—as a side effect, the degree of parallelism is limited
by the number of accessed partitions)
•
Joins (Chapter 14 also provides some examples)
•
Set operators
•
Sorts
•
Aggregations
■
Full table scans, full partition scans, and fast full index scans executed in parallel generally use direct reads
and, therefore, bypass the buffer cache. an exception is when, from version 11.2 onward, in-memory parallel execution is
active. In fact, the aim of in-memory execution is precisely to avoid direct reads and to cache as much data as possible.
note that the database engine, for index full and range scans, always performs regular physical reads.
Note
Queries can't be executed in parallel when they reference a user-defined function that doesn't support parallel
processing. Basically, to support parallel processing, a user-defined function must neither write to the database nor
read or modify package variables. When writing PL/SQL code, you should mark user-defined functions that support
parallel processing with the
PARALLEL_ENABLE
clause. Note that in some situations the capability of executing a
user-defined function in parallel depends not only on the user-defined function itself, but also on the query (and, in
the end, on the execution plan itself ). The script
px_query_udf.sql
provides an example where one function prevents
one query from running in parallel while it doesn't place such a restriction on another query.
Parallel queries are
enabled
by default. At the session level, you can enable and disable them with the following
SQL statements:
ALTER SESSION ENABLE PARALLEL QUERY
