If you have started to play around with Database In-Memory, chances are you have been examining execution plans to see if the optimizer has chosen to use the In-Memory column store (IM column store) or not. In addition to the execution plan itself, you should also check out the NOTE section under the plan, which contains more information about how a query was executed, such as if dynamic sampling was used during the query optimization.
The information in the NOTE section is especially useful when you start executing queries in parallel in a RAC environment.
If you recall, in our previous series on RAC we explained that each RAC instance has its own IM column store and when data is populated in-memory in RAC it is distributed across all of the IM column stores in the cluster. In fact the data is affinitized to a specific RAC instance, which means that we need to access that piece of the data via the IM column store(s) it resides in. We don’t ship in-memory data (IMCUs) across the interconnect to the RAC instance on which the query is being executed, which is why all in-memory queries on RAC must be parallelized with a degree equal to at least the number of instances that were involved in the distributed population.
We also said, the best way to parallelize your statements to ensure this data affinity is taken into consideration was to use AutoDOP.
So, how can you tell if you are using AutoDOP and if you got data affinity?
Information on what parallel degree was chosen for a SQL statement and why is now shown in the NOTE section of the execution plan, as are the details on whether affinity was used or not.
If you prefer to use SQL Monitor to examine execution plans then you can access the NOTE section by clicking on the yellow Plan Note just above the plan in an active report.
In this example when we click on the to Plan Note we get the following popup window.
Not exactly the same format we got in the NOTE section when we displayed the plan using the DBMS_XPLAN package in SQL*Plus. But it does have the same information, once you know how to decode it.
Let’s start with whether or not AutoDOP kicked in.
The first two lines of the Plan Note let us know what Degree of Parallelism (DOP) was used for the statement and why. In this example the DOP used was 2 and it was chosen because scan of object SSB.LINEORDER. What does that mean?
Remember with AutoDOP, Oracle will automatically determine the DOP for a statement based on a number of criteria including if there are any scan operations in the execution plan. In this case, there was a full table scan of the LINEORDER table in the plan, which the optimizer considered expensive and so it decided to employ parallel execution. The degree chosen is based on the amount of work the optimizer believes needs to be done to complete the scan and the maximum DOP allowed as specified by the parameter PARALLEL_DEGREE_LIMIT.
What about affinity? Did we get data affinity for this statement?
Yes, the third line in the note, PX In Memory Imc yes indicates that the parallel scan of the LINEORDER table was affinitized.
So, that just leaves the last line of the Plan Note PX In Memory no. What does it mean?
Strange as this might sound, that entry in the Plan Note has nothing to do with Database In-Memory. It’s actually referring to the use of parallel execution within the buffer cache in a RAC environment. Yasin Baskan, the Product Manager for parallel execution, gives a great explanation of what that particular note actually means in his post on the Data Warehouse Insider blog.
Now that you have the decode key for the SQL Monitor note, hopefully it will make your experiments with Database In-Memory easier to interpret!