It’s been almost a year since I’ve had an opportunity to write a new blog post here due to my crazy schedule now that I'm the In-Memory Maven (thanks to Doug Burns & Graham Wood for the new and improved title). But this morning while I was writing a post for the In-Memory blog about controlling the use of the In-Memory column store (IM column store), I realized that the content on Optimizer hints really belonged over here.
So, I decided to split the blog post in two. I’ve put the information on the initialization parameter that control the use of the IM column store on the In-Memory blog and the information about the Optimizer hint that control the use of the IM column store here.
The first hint we need to discuss is the (NO_)INMEMORY hint. This hint is one of the most misunderstood hints in recent releases. I’ve heard a number of different explanations about what this hint is suppose to do, most of which aren’t true. The only thing the INMEMORY hint does is enables the IM column store to be used when the INMEMORY_QUERY parameter is set to DISABLE.
It won’t force a table or partition without the INMEMORY attribute to be populated into the IM column store. If you specify the INMEMORY hint in a SQL statement where none of the tables referenced in the statement are populated into memory, the hint will be treated as a comment since its not applicable to this SQL statement.
Nor will the INMEMORY hint force a full table scan via the IM column store to be chosen, if the default plan (lowest cost plan) is an index access plan. You will need to specify the FULL hint to see that plan change take effect.
Let’s take a look at the INMEMORY hint in action. In this example I’m using a simple query that asks the question, “What is the most expensive order we have received to date?”SELECT Max(lo_ordtotalprice) most_expensive_order
The LINEORDERS table has 23,996,604 rows and has been fully populated into the IM column store. In memory it's comprised of 44 IMCUs (In-Memory Compression Units). I’ve started a new session and set the INMEMORY_QUERY parameter to DISABLE. If I execute our simple query, you’ll notice the elapse time is over 36 seconds and the execution plan chosen is a full table scan but it wasn’t executed via the IM column store (keyword INMEMORY is missing from the TABLE ACCEESS FULL operation).
Now in the same session, let’s execute the query again, this time using the In-Memory hint.
By specifying the hint we were able to reduce the response time down to a sub-second and force the full table scan to occur in the IM column store.
The NO_INMEMORY hint does the same thing in reverse. It will prevent the access of an object from the IM column store; even if the object is full populated into the column store and the plan with the lowest cost is a full table scan.
Let’s look at another example, just to ensure I’ve cleared up any confusion about what the INMEMORY hint actually controls. In this example let’s looking for a specific order in the LINEORDER table based on the order key.
SELECT lo_orderkey, lo_custkey, lo_revenue
WHERE lo_orderkey = 5000000;
A b-tree index exists on the LO_ORDERKEY column. The LINEORDER table is fully populated into the IM column store and the INMEMORY_QUERY parameter is set to the default ENABLE. The lowest cost plan (default plan) is an INDEX RANGE SCAN followed by a TABLE ACCESS BY ROWID RANGE, as shown below.
Now let's add the INMEMORY hint to see if we can change the plan, so we can take advantage of the In-Memory column.
As I said before, specifying the INMEMORY hint is not the way to influence the access method chosen in a plan. To change the access method to a FULL TABLE SCAN we need to specify the FULL hint.
With the FULL hint the plan changes to allow the IM column store to be used but if we examine the cost of this new plan it is far more expense than the original plan, which is why it wasn't chosen by default.
The second hint introduced with Oracle Database In-Memory is (NO_)INMEMORY_PRUNING, which controls the use of In-Memory storage indexes. By default every query executed against the IM column store can take advantage of the In-Memory storage indexes (IM storage indxes), which enable data pruning to occur based on the filter predicates supplied in a SQL statement. As with most hints, the INMEMORY_PRUNING hint was introduced to help test the new functionality. In other words the hint was originally introduced to disable the IM storage indexes. Let's look at an example, so you can see the hint in action.
If we examine the new In-Memory session statistics (IM session statistics) for our previous query, we can see that 41 of the 44 IMCUs were pruned and only 3 IMCUs (44 - 41)were actually scanned to answer the query.
Let's start a new session, rerun the query with the NO_INMEMORY_PRUNING hint and examine the IM session statistics again.
After disabling the IM storage indexes with the NO_INMEMORY_PRUNING hint, the elapse time for the query has gone up and if we examine the IM statistics you can see that neither the IM scan CU pruned nor the IM scan segments minmax eligible statistics have been incremented for this execution.
Hopefully this post has helped clear up some of the confusion around what the new In-Memory hints control.