Which two are benefits of In-Memory Parallel Execution?
A.
Reduction in the duplication of block images across multiple buffer caches
B.
Reduction in CPU utilization
C.
Reduction in the number of blocks accessed
D.
Reduction in physical I/O for parallel queries
E.
Ability to exploit parallel execution servers on remote instance
Explanation:
Note: In-Memory Parallel Execution
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if
an object that is accessed using parallel execution would benefit from being cached in the SGA
(also called the buffer cache). The decision to cache an object is based on a well-defined set of
heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC
environment, Oracle Database maps pieces of the object into each of the buffer caches on the
active instances. By creating this mapping, Oracle Database automatically knows which buffer
cache to access to find different parts or pieces of the object. Using this information, Oracle
Database prevents multiple instances from reading the same information from disk over and over
again, thus maximizing the amount of memory that can cache objects. If the size of the object is
larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied
by the number of active instances in an Oracle RAC cluster, then the object is read using directpath reads.
Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works
A,D
“A” is correct (in Oracle RAC environment)
But I hesitate between C and D.
“D” seems to be correct in my opinion. By using in-memory, Oracle can decide to cache accessed objects (decision based on an algorithm. See Oracle doc) instead of read from disk via direct path IO (=physical IO).
About “C”, i think that’s incorrect.
What’s it’s true: In a Oracle RAC environnement, oracle maps pieces of object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects.
What’s it’s not true: If you need to accessed X blocks. You will accessed X block. If in-memory is used and you are in Oracle Rac environment, you just prevents multiple instances from reading the same information from disk over and over again. Perhaps, you will access block in cache but you will access it.
A,D
As for D:
In-Memory Parallel Execution (In-Memory PX) takes advantage of these larger buffer caches but it also ensures we don’t trash the cache.
The larger buffer is the less physical IO we have.
https://blogs.oracle.com/datawarehousing/entry/in-memory_parallel_execution_i
A C and D actually 🙂 3 not 2 correct answers