X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Tips on SQL Plan Management and Oracle Database In-Memory – Part 3

Nigel Bayliss
Product Manager

In Part 1 of this series of tips on SQL Plan Management (SPM) and Oracle Database In-Memory I covered an example where a full table scan query made use of the In-Memory column store immediately without changing the SQL execution plan. In Part 2 I presented an example where the In-Memory column store made an alternative SQL execution plan viable, and where there was a corresponding SQL plan baseline already in place so that this plan could be used immediately. 

In this post I will consider a slightly different scenario:
  • SQL plan management is used to stabilize the SQL execution plans for our critical application (that until now has not been using the In-Memory column store).
  • The In-Memory column store is subsequently enabled and populated with application tables. 
  • The Optimizer identifies new SQL execution plans.
  • Most of the new execution plans have never been chosen by the Optimizer before.
Let me say right away; SPM behaves in a business-as-usual manner: 
  • New plans for existing baselines are captured by SPM but they will not be used until they are accepted.
  • Existing SQL plan baselines are used, so queries continue to use "approved" SQL execution plans.
  • The database administrator chooses how and when to evolve the SQL plan baselines to take full advantage of In-Memory SQL execution plans.
This is probably one of the most common scenarios you’ll encounter if you use SPM and you start to use Oracle Database In-Memory. As Andy Rivenes pointed out in his blog post, SPM is a very good way to avoid query regressions by controlling how and when queries are affected as you populate the In-Memory column store with more and more tables. I'll use the following example to show you how SPM behaves:
  • There is a query called Q3.
  • Q3 queries a table called MYSALES. 
  • MYSALES is not yet populated into the In-Memory column store.
  • Q3 filters rows in MYSALES using a column called SALE_TYPE.
  • SALE_TYPE has relatively low cardinality, but an index is still useful.
  • There is a SQL plan baseline for Q3 to ensure that it will uses an index range scan and not a full table scan.

This is the plan before the In-Memory column store is populated with MYSALES:

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  8xkx5abshb4rz, child number 2
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type in (2,3)
 
Plan hash value: 719460714
 
-------------------------------------------------------------------------------------------------
|     | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |       |       |   246 (100)|          |
|   1 |  SORT AGGREGATE                       |         |     1 |     7 |            |          |
|   2 |   INLIST ITERATOR                     |         |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES | 20000 |   136K|   246   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | SI      | 20000 |       |    40   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(("SALE_TYPE"=2 OR "SALE_TYPE"=3))
 
Note
-----
   - SQL plan baseline SQL_PLAN_bk42daz2f53zwb9fe04b5 used for this statement
 

It's an index range scan, and the “note” section (above) shows us that a SQL plan baseline is being used (it's name ending in "4b5" ). Let's take a look at our baselines- there's just one: 

SELECT plan_name,sql_handle,sql_text,enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%';
PLAN_NAME                       SQL_HANDLE           SQL_TEXT                            ENA ACC
------------------------------- -------------------- ----------------------------------- --- ---
SQL_PLAN_bk42daz2f53zwb9fe04b5  SQL_b9104d57c4e28ffc select /* SPM */ count(*),sum(val)  YES YES
                                                     from mysales
                                                     where sale_type in (2,3)

Now, populate the In-Memory column store:  

-- Mark MYSALES with the In-Memory attribute

ALTER TABLE mysales INMEMORY;

-- Access MYSALES to trigger population into In-Memory column store

SELECT count(*) FROM mysales; 

If we re-run Q3, we still get an index range scan rather than the INMEMORY FULL scan we might have anticipated (because an In-Memory scan can be more efficient than an index range scan in some cases): 

-------------------------------------------------------------------------------------------------
|     | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |       |       |   246 (100)|          |
|   1 |  SORT AGGREGATE                       |         |     1 |     7 |            |          |
|   2 |   INLIST ITERATOR                     |         |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES | 20000 |   136K|   246   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | SI      | 20000 |       |    40   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Has the Optimizer decided that an index range scan is still the best option? We can answer that if we take another look at the SQL plan baselines: 

SELECT plan_name,sql_handle,sql_text,enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%';
PLAN_NAME                       SQL_HANDLE           SQL_TEXT                            ENA ACC ------------------------------- -------------------- ----------------------------------- --- --- SQL_PLAN_bk42daz2f53zwc69cec1f  SQL_b9104d57c4e28ffc select /* SPM */ count(*),sum(val)  YES NO                                                      from mysales                                                      where sale_type in (2,3) SQL_PLAN_bk42daz2f53zwb9fe04b5  SQL_b9104d57c4e28ffc select /* SPM */ count(*),sum(val)  YES YES                                                      from mysales                                                      where sale_type in (2,3)  

OK. There’s a new baseline, but it isn’t accepted (the value “NO” appears in the accepted column). This is exactly what SPM is supposed to do: we continue to use accepted and "approved" plans until we have verified or chosen to use alternatives. What is the new baseline plan in this case?

SELECT PLAN_TABLE_OUTPUT
FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b,
        TABLE(
          DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
        ) t
WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND    b.PLAN_NAME='SQL_PLAN_bk42daz2f53zwc69cec1f';
 
PLAN_TABLE_OUTPUT
-----------------
SQL handle: SQL_b9104d57c4e28ffc
SQL text: select /* SPM */ count(*),sum(val) from mysales where sale_type in
         (2,3)
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bk42daz2f53zwc69cec1f         Plan id: 3332172831
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 3292460164
 
-----------------------------------------------
| Id  | Operation                   | Name    |
-----------------------------------------------
|   0 | SELECT STATEMENT            |         |
|   1 |  SORT AGGREGATE             |         |
|   2 |   TABLE ACCESS INMEMORY FULL| MYSALES |
-----------------------------------------------
 

There it is! The Optimizer has established that the In-Memory full table scan is a good choice, but it will not be used until the new SQL plan baseline has been accepted. Let's go ahead and accept it, but take note that in this example there’s a good chance that the difference in performance will be very small because, after all, it’s only a simple query on a small dataset. If the performance difference is small then automatic plan evolution won’t deem the performance improvement to be sufficient to trigger automatic acceptance of the new baseline. It's worth remembering this if you find that you have a bunch of new baselines that are not accepted automatically. I'll use “verify=>’NO’” to force acceptance: 

cVal := dbms_spm.evolve_sql_plan_baseline(sql_handle=>' SQL_b9104d57c4e28ffc',verify=>'NO');

 
SELECT plan_name,sql_handle,sql_text,enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%';
 
PLAN_NAME                       SQL_HANDLE           SQL_TEXT                            ENA ACC
------------------------------- -------------------- ----------------------------------- --- ---
SQL_PLAN_bk42daz2f53zwc69cec1f  SQL_b9104d57c4e28ffc select /* SPM */ count(*),sum(val)  YES YES
                                                     from mysales
                                                     where sale_type in (2,3)
SQL_PLAN_bk42daz2f53zwb9fe04b5  SQL_b9104d57c4e28ffc select /* SPM */ count(*),sum(val)  YES YES
                                                     from mysales
                                                     where sale_type in (2,3)

Now, re-run the query:

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  8xkx5abshb4rz, child number 0
-------------------------------------
select /* SPM */ count(*),sum(val) from mysales where sale_type in (2,3)
 
Plan hash value: 3292460164
 
---------------------------------------------------------------------------------------
|     | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |    24 (100)|          |
|   1 |  SORT AGGREGATE             |         |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| MYSALES | 20000 |   136K|    24   (9)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - inmemory(("SALE_TYPE"=2 OR "SALE_TYPE"=3))
       filter(("SALE_TYPE"=2 OR "SALE_TYPE"=3))
 
Note
-----
   - SQL plan baseline SQL_PLAN_bk42daz2f53zwc69cec1f used for this statement
 

That’s more like it! We have accepted the new In-Memory execution plan and we’ve done it in a controlled manner. We are using the new SQL plan baseline (the name ends in "c1f" ).

In reality, you might have thousands of SQL plan baselines to evolve, but you can use the SPM evolve advisor task to automate the process of verification and acceptance. If you use this feature, then any SQL statements in your baseline that don’t benefit from the In-Memory column store significantly will continue to use their existing SQL execution plans.  

In this series of posts I don’t pretend to have covered every possible scenario, but I hope that this has given some idea of how SPM will behave if you choose to use Oracle Database In-Memory. I'm still not absolutely sure that "Part 3" will be the last part, so this might end up being a trilogy in four or five parts (to steal a quote from a famous author).You can take and develop the scripts I wrote to try out scenarios of your own. They are available on GitHub. So go ahead and check them out, and post any questions you have in the comments section below.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services