适用于:
Oracle Database - Enterprise Edition - 版本 12.1.0.2 和更高版本本文档所含信息适用于所有平台
目标
如何验证/启用内存数据库配置？
解决方案
检查数据库的版本是 12.1.0.2：
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
inmemory_size 的设置应大于0
在设置 inmemory_size 参数之前先检查：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
SQL> select name, value from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 2927336
Variable Size 201327896
Database Buffers 402653184
Redo Buffers 5459968
配置数据库参数：
System altered.
设置 inmemory_size 参数后（需要重新启动数据库让参数生效）
SQL> show parameter inmemory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 2
inmemory_query string ENABLE
inmemory_size big integer 200M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
SQL> select name, value from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 2927336
Variable Size 264242456
Database Buffers 130023424
Redo Buffers 5459968
In-Memory Area 209715200
5 rows selected.
使用 SH 用户进行测试
SQL> conn sh/oracle
Connected.
放置表到内存之前先检查：
SQL> SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name='CUSTOMERS';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
------------------------------ -------- -------- --------------- ----------------- -------------
CUSTOMERS DISABLED
1 row selected.
SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL 0 0 OUT OF MEMORY
64KB POOL 0 0 OUT OF MEMORY
SQL> select owner, segment_name, populate_status from v$im_segments;
no rows selected
放置表到内存的命令：
SQL> alter table CUSTOMERS inmemory;
Table altered.
放置表到内存以后，检查：
SQL> SELECT cust_valid, Count(*)
FROM customers
GROUP BY cust_valid; 2 3
C COUNT(*)
- ----------
I 44879
A 10621
2 rows selected.
SQL> SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name='CUSTOMERS';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
------------------------------ -------- -------- --------------- ----------------- -------------
CUSTOMERS ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
1 row selected.
SQL> select owner, segment_name, populate_status from v$im_segments;
OWNER SEGMENT_NAME POPULATE_
------------------------- ------------------------- ---------
SH CUSTOMERS COMPLETED
1 row selected.
SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL 166723584 4194304 DONE
64KB POOL 25165824 131072 DONE
2 rows selected.
验证使用的执行计划：
SQL> set autotrace traceonly;
SQL> SELECT cust_valid, Count(*)
FROM customers
GROUP BY cust_valid;
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 28 (11)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 4 | 28 (11)| 00:00:01 |
| 2 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 55500 | 108K| 26 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
273 consistent gets
5 physical reads
0 redo size
676 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
2 rows processed
功能使用情况的统计数据将在数据库创建的7天后生成，否则将只返回2行。虽然解释计划可能会显示’IN MEMORY FULL’，最终的执行可能会也可能不会使用 IN-Memory。如果表没有被生成到列存储里（没有足够的空间，第一次访问等），此时将会使用 Buffer Cache。可以使用 SQL Monitor 或日志跟踪来实际确认它的使用情况。
from dba_feature_usage_statistics u1
where version=(select max(version)
from dba_feature_usage_statistics u2
where u2.name=u1.name and u1.name like 'In-%')