Wednesday May 20, 2015

PX In Memory, PX In Memory IMC?

In my previous post I talked about how to use SQL Monitor to monitor parallel statements. The part of that post which talks about In-Memory Parallel Execution triggered some questions and I want to provide more information on that in a separate post here.

In-Memory Database? In-Memory Parallel Execution? In-Memory Everywhere

Back in the 11.2 days when there was no Oracle Database In-Memory Option (DBIM) there was no confusion about in-memory because there was only one way to use the database memory for parallel execution and that was In-Memory Parallel Execution (IMPX, also known as IMPQ). Starting with DBIM boosted the performance and also the confusion about the term in-memory parallel execution.

I will not go into the details of how these two features work in this post but let us look at the plan note from the previous post and try to understand what it shows about these.

As we see there are two notes about in-memory, Px In Memory Imc and Px In Memory. Let us look at what these two mean.

Px In Memory

This plan note is only related to IMPX and not related to DBIM. It shows whether the statement uses IMPX or not. If you see yes here it means some or all of the tables in the query are accessed through the buffer cache rather than doing direct reads from disk. IMPX looks at the table sizes and the buffer cache size and decides whether to read the blocks into the buffer cache or not. Once the blocks are in the buffer cache subsequent parallel statements can read the data from there rather than going to the disk. In the case of RAC, IMPX makes sure that PX servers are allocated on the nodes where table blocks reside, as a result of this cache fusion is not used to transfer blocks between nodes which in turn makes performance better.

This plan note in SQL Monitor maps to the note parallel scans affinitized for buffer cache in the DBMS_XPLAN output. Here is an example showing IMPX will be used.

The first access to the CUSTOMERS table will read the data from the disk to the buffer cache, subsequent accesses to this table will read data from the buffer cache. The term affinitized here means that specific blocks are assigned to specific nodes and PX servers on those nodes will read the data through the buffer cache.

You will see this note if IMPX is used independent of whether you are on a single-instance database or a RAC database.

So, if you see the note parallel scans affinitized for buffer cache in the DBMS_XPLAN output you will see Px In Memory=yes in SQL Monitor.

Px In Memory Imc

This plan note is only related to DBIM and not related to IMPX. IMC here means In-Memory Columnar which indicates DBIM. The IMC store provides metadata that shows which part of an in-memory table is in which node's memory in a RAC database. Parallel Execution uses this information to allocate PX servers on those nodes so that they can read the data from the IMC store without going to disk. The plan note Px In Memory Imc shows whether the statement accesses an in-memory table and whether PX servers are affinitized to RAC nodes. This does not indicate if DBIM is used or not, that is indicated by the operations in the plan like TABLE ACCESS INMEMORY FULL.

This note maps to the note parallel scans affinitized for inmemory in the DBMS_XPLAN output. Here is an example showing in-memory affinity is used.

In a single-instance database you will not see this note because any PX server can read the data from the single node's IMC store and there is no concept of affinity of PX servers to nodes.

So, if you see the note parallel scans affinitized for inmemory in the DBMS_XPLAN output you will see Px In Memory Imc=yes in SQL Monitor.

One or The Other?

It is possible to have parallel statements using both IMPX and DBIM. If your statement accesses both in-memory tables and tables determined to be read via the buffer cache you can see both notes in the plan output.

In this example, CUSTOMERS table is an in-memory table and since this is a RAC database PX servers are affinitized to nodes. CUSTOMERSPART table is not an in-memory table but Parallel Execution decided to read it through the buffer cache.

There is a lot of information about how DBIM works in the In-Memory blog, I will talk about how IMPX works in a future post. In the meantime please comment here if you have any questions related to it.

Sunday Sep 27, 2009

In-Memory Parallel Execution in Oracle Database 11gR2

As promised, the next entry in our 11gR2 explorations is In-Memory Parallel Execution. If you are going to Oracle OpenWorld next month make sure you check out the following session:

Tuesday, October 13 2009 5:30PM, Moscone South Room 308
Session S311420
Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution.

In this session you will get more details and insight from the folks who actually built this functionality! A must see if this is of any interest, so book that ticket now and register!

Down to business, what is "In-Memory Parallel Execution"?

Let's begin by having a quick trip down memory-lane back to Oracle Database 7 when Parallel Execution (PX) was first introduced. The goal of PX then and now is to reduce the time it takes to complete a complex SQL statement by using multiple processes to go after the necessary data instead of just one process. Up until now these parallel server processes, typically by-passed the buffer cache and read the necessary data directly from disk. The main reasoning for this was that the objects accessed by PX were large and would not fit into the buffer cache. Any attempt made to read these large objects into the cache would have resulted in trashing the cache content.

However, as hardware systems have evolved; the memory capacity on a typical database server have become extremely large. Take for example the 2 CPU socket Sun server being used in new the Sun Oracle Database Machine. It has an impressive 72GB of memory, giving a full Database Machine (8 database nodes) over ½ a TB of memory. Suddenly using the buffer cache to hold large object doesn't seem so impossible any more.

In-Memory Parallel Execution (In-Memory PX) takes advantage of these larger buffer caches but it also ensures we don't trash the cache.

In-Memory PX begins by determining if the working set (group of database blocks) necessary for a query fits into the aggregated buffer cache of the system. If the working set does not fit then the objects will be accessed via direct path IO just as they were before. If the working set fits into the aggregated buffer cache then the blocks will be distributed among the nodes and the blocks will be affinitzed or associated with that node.

In previous releases, if the Parallel Execution of one statement read part of an object into the buffer cache, then subsequent SQL statement on other nodes in the cluster would access that data via Cache Fusion. This behavior could eventually result in a full copy of that table in every buffer cache in the cluster. In-Memory PX is notably different because Cache Fusion will not be used to copy the data from its original node to another node, even if a parallel SQL statement that requires this data is issued from another node. Instead Oracle uses the parallel server process on the same node (that the data resides on) to access the data and will return only the result to the node where the statement was issued.

The decision to use the aggregated buffer cache is based on an advanced set of heuristics that include; the size of the object, the frequency at which the object changes and is accessed, and the size of the aggregated buffer cache. If the object meets these criteria it will be fragmented or broken up into pieces and each fragment will be mapped to a specific node. If the object is hash partitioned then each partition becomes a fragment, otherwise the mapping is based on the FileNumber and ExtentNumber.




To leverage In-Memory PX you must set the initialization parameter PARALLEL_DEGREE_POLICY to AUTO (default MANUAL). Once this is set, the database controls which objects are eligible to be read into the buffer cache and which object will reside there at any point in time. It is not possible to manual control the behavior for specific statements.

Obviously this post is more of a teaser, for in-depth discussions on this, go to Openworld and/or keep an eye out for a new white paper called Parallel Execution Fundemental in Oracle Database 11gR2 that will be coming soon to This paper not only covers In-Memory PX but Auto-DOP and parallel statement queuing.

Stay tuned for more on 11gR2 coming soon...


The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.


« July 2016