Using In-Memory Option with SQL Plan Baselines, SQL Profiles and SQL Hints

Monday, March 6, 2017

Oracle database In-Memory option was introduced in 12.1.0.2 patchset. It is great feature to improve performance of analytic queries. For mixed workload OLTP environments In-Memory option could improve performance of analytic queries without significant negative affect on quick OLTP queries or DML operations.

So you have decided that In-Memory option could be great for you and now you want to implement this option for your critical production database.

But in your code you have many SQL hints hard-coded, SQL Profiles implemented or SQL Plan baselines created to solve problems with unstable query performance. What will happen with execution plans if you populate In-Memory column store with critical tables in the database.

Example:
Version : Oracle 12.1.0.2

For test I will use query with fixed plan using both SQL profile and SQL plan baseline.

select object_type, count(*)
from admin.big_table
group by object_type;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
PACKAGE                      14858
PACKAGE BODY                 13724
PROCEDURE                     2254
PROGRAM                        110


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID  8g28yt7c1nacr, child number 0
-------------------------------------
select object_type, count(*) from admin.big_table group by object_type

Plan hash value: 1753714399

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  4819 (100)|          |
|   1 |  HASH GROUP BY     |           |    39 |   351 |  4819   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |  1000K|  8789K|  4795   (1)| 00:00:01 |
--------------------------------------------------------------------------------

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '8g28yt7c1nacr');
END;
/

@coe_xfr_sql_profile 8g28yt7c1nacr 1753714399
@coe_xfr_sql_profile_8g28yt7c1nacr_1753714399.sql


select object_type, count(*)
from admin.big_table
group by object_type;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));


SQL_ID  8g28yt7c1nacr, child number 0
-------------------------------------
select object_type, count(*) from admin.big_table group by object_type

Plan hash value: 1753714399

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  4819 (100)|          |
|   1 |  HASH GROUP BY     |           |    39 |   351 |  4819   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |  1000K|  8789K|  4795   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - SQL profile coe_8g28yt7c1nacr_1753714399 used for this statement
   - SQL plan baseline SQL_PLAN_1wn92bz7gqvxx73be0962 used for this statement

Note section in execution plan output says that I’m using both SQL profile and SQL plan baseline for this query.

I have previously enabled In-Memory Column Store and now I will populate table data into the in-memory column store.

alter table admin.big_table inmemory priority critical;

col segment_name for a15
select segment_name, 
       inmemory_size/1024/1024 im_size_mb,
       bytes/1024/1024 size_mb,
       bytes_not_populated,
       inmemory_compression
from v$im_segments;

SEGMENT_NAME    IM_SIZE_MB    SIZE_MB BYTES_NOT_POPULATED INMEMORY_COMPRESS
--------------- ---------- ---------- ------------------- -----------------
BIG_TABLE          27.1875        144                   0 FOR QUERY LOW

1 row selected.

Run query again.

select object_type, count(*)
from admin.big_table
group by object_type;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID  8g28yt7c1nacr, child number 0
-------------------------------------
select object_type, count(*) from admin.big_table group by object_type

Plan hash value: 1753714399

--------------------------------------------------------------------------------------
|Id | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |           |       |       |   257 (100)|         |
| 1 |  HASH GROUP BY              |           |    39 |   351 |   257  (13)| 00:00:01|
| 2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |  1000K|  8789K|   233   (4)| 00:00:01|
--------------------------------------------------------------------------------------

Note
-----
   - SQL profile coe_8g28yt7c1nacr_1753714399 used for this statement
   - SQL plan baseline SQL_PLAN_1wn92bz7gqvxx73be0962 used for this statement

Notice "TABLE ACCESS INMEMORY FULL" operation is used instead of "TABLE ACCESS FULL" and both SQL profile and SQL plan baselines are used for this query.

In this case Oracle used in-memory column store to read data without any intervention on SQL profile or SQL plan baseline. Plan hash value remained the same in both cases.


But what if we have index operations involved in execution plan.

-- Temporary disable IM column store to optimise queries
SQL> alter system set inmemory_query=DISABLE;

-- Force Oracle to use index
SQL> alter session set optimizer_index_caching=100;
SQL> alter session set optimizer_index_cost_adj=1;


select object_type, count(*)
from admin.big_table
where object_type > 'C'
group by object_type;



SQL_ID  8xvfvz3axf5ct, child number 0
-------------------------------------
select object_type, count(*) from admin.big_table where object_type >
'C' group by object_type

Plan hash value: 3149057435

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |    28 (100)|          |
|   1 |  SORT GROUP BY NOSORT|              |    39 |   351 |    28   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJ_TYPE |  1000K|  8789K|    28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE">'C')


-- Create SQL plan baseline 

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '8xvfvz3axf5ct');
END;
/

-- Create SQL profile

SQL>@coe_xfr_sql_profile 8xvfvz3axf5ct 3149057435
SQL>@coe_xfr_sql_profile_8xvfvz3axf5ct_3149057435.sql


I have slightly different query with "INDEX RANGE SCAN" operation in execution plan. SQL plan baseline and SQL profile are both created for this query.


In Note section you can see that SQL profile and SQL plan baseline are both used.

select object_type, count(*)
from admin.big_table
where object_type > 'C'
group by object_type;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID  8xvfvz3axf5ct, child number 0
-------------------------------------
select object_type, count(*) from admin.big_table where object_type >
'C' group by object_type

Plan hash value: 3149057435

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |    28 (100)|          |
|   1 |  SORT GROUP BY NOSORT|              |    39 |   351 |    28   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJ_TYPE |  1000K|  8789K|    28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE">'C')

Note
-----
   - SQL profile coe_8xvfvz3axf5ct_3149057435 used for this statement
   - SQL plan baseline SQL_PLAN_76jwvc1sug4k44391ca35 used for this statement


Enable IM column store to optimise queries.

SQL> alter system set inmemory_query=ENABLE;

System altered.


select object_type, count(*)
from admin.big_table
where object_type > 'C'
group by object_type;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID  8xvfvz3axf5ct, child number 1
-------------------------------------
select object_type, count(*) from admin.big_table where object_type >
'C' group by object_type

Plan hash value: 3149057435

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |    28 (100)|          |
|   1 |  SORT GROUP BY NOSORT|              |    39 |   351 |    28   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJ_TYPE |  1000K|  8789K|    28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE">'C')

Note
-----
   - SQL profile coe_8xvfvz3axf5ct_3149057435 used for this statement
   - SQL plan baseline SQL_PLAN_76jwvc1sug4k44391ca35 used for this statement

This time in-memory option is not used to improve performance of the query.

Let’s drop SQL profile and leave SQL plan baseline enabled.

exec dbms_sqltune.drop_sql_profile('coe_8xvfvz3axf5ct_3149057435');


elect object_type, count(*)
from admin.big_table
where object_type > 'C'
group by object_type;


Plan hash value: 1753714399

--------------------------------------------------------------------------------------
| Id| Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |           |    39 |   351 |   255  (12)| 00:00:01|
| 1 |  HASH GROUP BY              |           |    39 |   351 |   255  (12)| 00:00:01|
|*2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |  1000K|  8789K|   231   (3)| 00:00:01|
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("OBJECT_TYPE">'C')
       filter("OBJECT_TYPE">'C')

Note
-----
   - SQL plan baseline "SQL_PLAN_76jwvc1sug4k473be0962" used for this statement

Note section says that SQL plan baseline is used for this statement, but different than before.
I have "TABLE ACCESS INMEMORY FULL" operation and plan has changed automatically.

In Oracle 12cR1 Adaptive SQL Plan Management is enabled by default. Oracle calculated more efficient plan using in-memory column store and automatically accepted new SQL execution plan for this query. As new SQL plan is added and accepted Oracle was able to change execution plan.

set lines 200
set pages 999
col plan_name for a30
col sql_text for a50 wrap

select plan_name, sql_text, enabled, accepted
from dba_sql_plan_baselines
where sql_text like '%object_type > %';


PLAN_NAME                      SQL_TEXT                                ENA ACC
------------------------------ --------------------------------------- --- ---
SQL_PLAN_76jwvc1sug4k4ebe5b30f select object_type, count(*)            YES NO
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type

SQL_PLAN_76jwvc1sug4k473be0962 select object_type, count(*)            YES YES
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type

SQL_PLAN_76jwvc1sug4k44391ca35 select object_type, count(*)            YES YES
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type

What if I disable adaptive sql plan management to forbid automatically evolving existing baselines.

-- Disable automatic evolving
BEGIN
  DBMS_SPM.set_evolve_task_parameter(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
    parameter => 'ACCEPT_PLANS',
    value     => 'FALSE');
END;
/

-- Drop SQL plan baseline used for in-memory full scan
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SQL_PLAN_76jwvc1sug4k473be0962');
END;
/

In-memory full scan is not used as index range scan operation was specified in existing baseline which is used for query.

select object_type, count(*)
from admin.big_table
where object_type > 'C'
group by object_type;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID 8xvfvz3axf5ct, child number 1
-------------------------------------
select object_type, count(*) from admin.big_table where object_type >
'C' group by object_type

Plan hash value: 3149057435

-------------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     |     |  28 (100)|     |
|   1 |  SORT GROUP BY NOSORT|      |  39 | 351 |  28   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJ_TYPE |  1000K|  8789K|  28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE">'C')

Note
-----
   - SQL plan baseline SQL_PLAN_76jwvc1sug4k44391ca35 used for this statement

New plan was added but this time it is not accepted automatically and taken in consideration by the optimizer. We have to manually validate and accept new plan to use it for query executions.

set lines 200
set pages 999
col plan_name for a30
col sql_text for a50 wrap

select plan_name, sql_text, enabled, accepted
from dba_sql_plan_baselines
where sql_text like '%object_type > %';

PLAN_NAME                      SQL_TEXT                                 ENA ACC
------------------------------ ---------------------------------------- --- ---
SQL_PLAN_76jwvc1sug4k4ebe5b30f select object_type, count(*)             YES NO
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type

SQL_PLAN_76jwvc1sug4k473be0962 select object_type, count(*)             YES NO
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type

SQL_PLAN_76jwvc1sug4k44391ca35 select object_type, count(*)             YES YES
                               from admin.big_table
                               where object_type > 'C'
                               group by object_type


What will happen if I have query with hint.

select /*+index(t IDX_OBJ_TYPE)*/  
       object_type, count(*)
from admin.big_table t
where object_type > 'C'
group by object_type;


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

SQL_ID  8k7fykgphx8ra, child number 0
-------------------------------------
select /*+index(t IDX_OBJ_TYPE)*/        object_type, count(*) from
admin.big_table t where object_type > 'C' group by object_type

Plan hash value: 3149057435

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |  2770 (100)|          |
|   1 |  SORT GROUP BY NOSORT|              |    39 |   351 |  2770   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJ_TYPE |  1000K|  8789K|  2770   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE">'C')

In-memory data access is ignored as we have hint forcing usage of the index.

select /*+full(t)*/
       object_type, count(*)
from admin.big_table t
where object_type > 'C'
group by object_type;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));

Plan hash value: 1753714399

--------------------------------------------------------------------------------------
|Id | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |           |    39 |   351 |   255  (12)| 00:00:01|
| 1 |  HASH GROUP BY              |           |    39 |   351 |   255  (12)| 00:00:01|
|*2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |  1000K|  8789K|   231   (3)| 00:00:01|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("OBJECT_TYPE">'C')
       filter("OBJECT_TYPE">'C')

In case we have hint forcing full scan, query will read data from in-memory column store as "TABLE ACCESS INMEMORY FULL" and "TABLE ACCESS FULL" are the same full table scan operations for the optimizer.


Conclusion
If your production application is heavily dependent on SQL profiles and SQL hints it would be hard to use full potential of in-memory column store option in a short time.
With SQL plan baselines it is slightly easier because you could use Adaptive SQL Plan Management to alter plans.

But you must dedicate some time for proper testing, because changing plans and dropping indexes blindly could cause many performance problems.




Share/Bookmark

0 comments:

Post a Comment