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.
0 Comments:
Post a Comment