Database Reference
In-Depth Information
In-Memory Parallelism
Traditionally, PX servers read blocks directly from the disk to PGA, bypassing buffer cache. In a few cases, it may
be better to buffer the blocks in the database buffer cache so that subsequent PX server processes can reuse the
buffers. This feature is pertinent in machines with huge swaths of memory allocated for SGA. Of course, buffering
in SGA requires global cache locks, which might induce global cache event waits for the PX servers reading the
block from disk.
From Oracle Database version 11.2 onward, if the parallel_degree_policy parameter is set to auto, then the
in-memory parallelism feature is enabled. Objects are chosen to be cached in the buffer cache if the object is not too
small or too big. If the segment is too small, then that segment can be cached in PGA itself; if the segment is too big,
then buffering will overrun the buffer cache. Thus, this feature cleverly chooses segments of the right size.
The advantage of this feature is that subsequent processes accessing the same table do not need to repeat the work
of reading the blocks from disk.
The algorithm uses the following directives to determine if the objects can be buffered or not:
1.
Size of the object (must fit in the buffer cache of one instance; the parameter _parallel_
cluster_cache_pct limits the maximum percentage of buffer cache that can be used
for affinity and defaults to 80%).
2.
Object access frequency.
3.
Object change frequency.
In RAC, object fragments are affinitized among active instances. For example, if there are three instances in a
PX execution, then approximately 1/3rd of the object is affinitized to each instance. Affinitization is performed on a
per-extent basis for a non-partitioned table; 1/3rd of extents are affinitized to each node in a three-node cluster. If the
table is hash partitioned, then the object is affinitized per partition basis. PX server processes are also affinitized such
a way that PX servers access extents/partitions in the local buffer cache. For example, if partition P1 is affinitized to
the ORCL2 instance, then PX processes are allocated in the ORCL2 instance if the execution plan requires access to
partition P1. Affinitization is designed to reduce cache fusion network traffic.
This feature can be enabled at the session level also:
alter session set "_parallel_cluster_cache_policy"=cached;
This feature can be disabled in session level using the following statement. Of course, it is always better to check
with Oracle Support before using underscore parameters in production code. Also, instead of hard-coding these SQL
statements in production code, wrap this code in a PL/SQL procedure and call the procedure from application code.
This enables you to change the code in the database without altering the application.
alter session set "_parallel_cluster_cache_policy"=adaptive;
The following SQL statistics show a comparison between parallel execution of a statement with caching enabled
and disabled. The comparison shown here is for the second execution of the statement for each mode in a production
database. If the caching is enabled, subsequent executions of the statement perform almost no disk I/O and reuse
cached buffers. If the caching is disabled, then each execution performs direct I/O to read blocks from the disk. In the
following example, SQL statement performed 232K reads with an increased elapsed time of 81 seconds, compared to
18 seconds when caching was enabled.
