By nbayliss-Oracle on Aug 25, 2015
In Part 1 of this series of tips on SQL Plan Management (SPM) and Oracle Database In-Memory, I covered what would happen if we have a SQL plan baseline for a full table scan query when the table was populating the In-Memory column store.
In this part I’m going to cover a scenario where a query has more than one SQL plan baseline:
- There is a query (called Q2, for short).
- Q2 queries a table called MYSALES, which is not yet populating the In-Memory column store.
- Q2 filters rows in MYSALES using a predicate on the SALE_TYPE column.
- Data in SALE_TYPE is skewed, so there’s an index and a histogram on this column.
- Because there is data skew, Q2 has two accepted SQL plan baselines; one with a full table scan and one with an index range scan.
You’ve probably come across this situation many times: the Oracle Optimizer must choose between a full table scan or an index range scan depending on predicate selectivity. The ability to change the execution plan based on the value of bind variables is called adaptive cursor sharing. If you’ve not come across that, then you’ll find it useful to check out the section on this topic in the Database SQL Tuning Guide.
What’s great about SPM is that it allows you to have multiple SQL plan baselines for individual queries, so you're not forced to pick one plan in preference to another. This capability is most relevant in environments where SQL statements use bind variables and there is a good deal of data skew. Queries like this are likely to have their plans affected by Oracle In-Memory Database because in-memory full table scans will have a lower cost than storage-resident table scans. Clearly, the In-Memory column store will affect the point of inflection where a full table scan will become more efficient than an index range scan. How is this going to work with SPM?
Take a look at the following example. Q2 executes and matches 2 million rows because I picked the value of bind variable “:val” to do just that. The Optimizer chooses a full table scan:
2 - filter("SALE_TYPE"=:VAL)
For the second execution, the value “:val” is set so that it would match only 20,001 rows. This time the Optimizer chooses an index range scan:
As you will have figured out, the Optimizer has calculated that the index is less efficient than a full table scan when Q2 matches a large number of rows (2 million in this case) so we have two viable SQL execution plans for this query. Before I ran the queries above, I accepted two SQL plan baselines for Q2. You can see in the “note” sections above that two different baselines are used (one ending in “80c” and one ending in “c1f”). They can be seen in the dba_sql_plan_baselines view:
from mysales where sale_type = :val
from mysales where sale_type = :val
What happens if we populate MYSALES into the In-Memory column store?
The full table scan is now annotated with INMEMORY, so we know that some or all of the data for MYSALES is scanned via the In-Memory column store. The “note” section reports that the same baseline is being used as before (ending in “c1f”). This is good news, and it’s the scenario that was covered in Part 1 of this series.
What if we executed the query to match 20,001 rows? You can probably guess what’s coming; the Optimizer judges that the In-Memory scan is more efficient than the index range scan:
Since there is a SQL plan baseline that allows a full table scan to be used, Q2 can use this access method straight away and we get immediate benefit from scanning the In-Memory column store!
Hold on a minute! Wasn’t that just a little bit too convenient? I arranged it so that there was a handy full-table-scan SQL plan baseline ready and waiting for when I "flipped the switch" and started using the In-Memory column store. This example might seem a little contrived, but it is a real-world example and I chose it to illustrate how SPM works together with both Oracle In-Memory Database and adaptive cursor sharing (and if you want more, there's an earlier blog on how adaptive cursor sharing interacts with SPM).
If, instead, I had started out with a single baseline that specified an index range scan, then this is the plan that would have been used even after MYSALES populated the In-Memory column store (and we would not have had an INMEMORY FULL scan). That’s not a bad thing; it is exactly what plan stability means and it is how SPM is meant to work. In the example above I made use of a couple of SQL execution plans that were validated and accepted before I initiated the In-Memory column store. In the more general case, where the Optimizer identifies a brand new execution plan for use with the In-Memory column store, we might want to validate it before we allow the database to use it in our critical application. How can we do that? Happily, it's what SPM evolution was built for, and it goes all the way back to the initial scenario I mentioned in Part 1. I'll cover the details in Part 3 (coming soon).
If you want to try out this example for yourself, the scripts are in GitHub.