版权声明：本文为Buddy Yuan原创文章，未经允许不得转载。原文地址：Oracle 18c新特性-MemOptimized RowStore
Oracle Database 18c中引入的MemOptimized RowStore，该功能可以提高通过主键列访问数据的查询性能。比如我们经常使用的这样的SQL语句：SELECT value FROM t WHERE key=:key，其中key是表中的唯一主键。
MemOptimized RowStore的想法是将堆表完全存储在SGA的一块区域内。这块区域被称作：Memoptimized Pool。它主要由以下两个部分构成：
1.Memoptimize Buffer Area
这是表块的专用缓冲区缓存，memoptimized Pool的75%是为此缓冲区缓存保留的。
2.Hash Index
哈希索引就是在内存区域做的一个映射表，主键就是映射键，并且指向Memoptimize Buffer Area中的块。哈希索引使用memoptimized Pool的另外25%。
如下图所示：
数据库运行SQL语句的时候，将会绕过SQL层，利用内存中的哈希索引直接访问所需的数据。减少了物理访问，避免了磁盘I/O。memoptimized pool的大小由初始化参数MEMOPTIMIZE_POOL_SIZE定义的。默认大小是0。更改值需要重新启动数据库。最小尺寸是100M。
使用MemOptimized RowStore必须满足以下条件：
1.该表上有一个主键。
2.该表未压缩。
开启该功能，会出现一些新的等待事件,总共有65个。如下所示:
SQL> select * from v$statname where name like '%memopt%';
STATISTIC# NAME CLASS STAT_ID DISPLAY_NAME CON_ID
---------- ---------------------------------------------------------------- ---------- ---------- ---------------------------------------------------------------- ----------
1682 memopt r fail to pin buffer 128 311744847 memopt r fail to pin buffer 1
1683 memopt r entries deleted 128 1246583635 memopt r entries deleted 1
1684 memopt r lookups 128 2449760253 memopt r lookups 1
1685 memopt r hits 128 2704996161 memopt r hits 1
1686 memopt r misses 128 2658693813 memopt r misses 1
1687 memopt r tag collisions 128 1615233916 memopt r tag collisions 1
1688 memopt r lookup skipped deleted rows 128 4063719983 memopt r lookup skipped deleted rows 1
1689 memopt r lookup skipped locked rows 128 1501413709 memopt r lookup skipped locked rows 1
1690 memopt r lookup skipped chained rows 128 2134479771 memopt r lookup skipped chained rows 1
1691 memopt r failed reads on buckets 128 2119256684 memopt r failed reads on buckets 1
1692 memopt r failed reads on blocks 128 3038931328 memopt r failed reads on blocks 1
1693 memopt r lookup detected CR buffer 128 2118516459 memopt r lookup detected CR buffer 1
1694 memopt r puts 128 12188864 memopt r puts 1
1695 memopt r puts:buckets full 128 860399351 memopt r puts:buckets full 1
1696 memopt r successful puts 128 2284911377 memopt r successful puts 1
1697 memopt r successful puts:with evictions 128 835169295 memopt r successful puts:with evictions 1
1698 memopt r successful puts:with cuckoo 128 3118361144 memopt r successful puts:with cuckoo 1
1699 memopt r successful puts:cuckoo deadend 128 4283018875 memopt r successful puts:cuckoo deadend 1
1700 memopt r successful puts:max cuckoo 128 606780851 memopt r successful puts:max cuckoo 1
1701 memopt r failed puts 128 141088098 memopt r failed puts 1
1702 memopt r failed puts:bucket in flux 128 1843422109 memopt r failed puts:bucket in flux 1
1703 memopt r failed puts:no space 128 2311881927 memopt r failed puts:no space 1
1704 memopt r populate tasks accepted 128 3727334836 memopt r populate tasks accepted 1
1705 memopt r populate tasks not accepted 128 236934488 memopt r populate tasks not accepted 1
1706 memopt r populate skipped locked rows 128 3833166773 memopt r populate skipped locked rows 1
1707 memopt r populate skipped deleted rows 128 2993279601 memopt r populate skipped deleted rows 1
1708 memopt r populate skipped chained rows 128 3189475334 memopt r populate skipped chained rows 1
1709 memopt r rows populated 128 2576444784 memopt r rows populated 1
1710 memopt r populate 128 1331937481 memopt r populate 1
1711 memopt r blocks populated 128 99459750 memopt r blocks populated 1
1712 memopt r failed to get tbs drop EQ 128 2581303612 memopt r failed to get tbs drop EQ 1
1713 memopt r failed to get tbs offline EQ 128 4132756765 memopt r failed to get tbs offline EQ 1
1714 memopt r failed to get segment drop EQ 128 2711296718 memopt r failed to get segment drop EQ 1
1715 memopt r repopulate tasks accepted 128 55882086 memopt r repopulate tasks accepted 1
1716 memopt r repopulate tasks not accepted 128 308832077 memopt r repopulate tasks not accepted 1
1717 memopt r repopulate 128 4292673878 memopt r repopulate 1
1718 memopt r rows repopulated 128 412578977 memopt r rows repopulated 1
1719 memopt r blocks repopulated 128 4086731426 memopt r blocks repopulated 1
1720 memopt r repopulate skipped locked rows 128 1039941343 memopt r repopulate skipped locked rows 1
1721 memopt r repopulate skipped deleted rows 128 1453092269 memopt r repopulate skipped deleted rows 1
1722 memopt r repopulate skipped chained rows 128 1283143522 memopt r repopulate skipped chained rows 1
1723 memopt r repopulate invalidated entries 128 2406172163 memopt r repopulate invalidated entries 1
1724 memopt r cleanup 128 1970750 memopt r cleanup 1
1725 memopt r NO IM tasks accepted 128 1465237793 memopt r NO IM tasks accepted 1
1726 memopt r NO IM tasks not accepted 128 3768980909 memopt r NO IM tasks not accepted 1
1727 memopt r DROP IM tasks accepted 128 2574115598 memopt r DROP IM tasks accepted 1
1728 memopt r DROP IM tasks not accepted 128 2139934761 memopt r DROP IM tasks not accepted 1
1729 memopt w buffer gets 128 944697423 memopt w buffer gets 1
1730 memopt w rows written 128 3945680858 memopt w rows written 1
1731 memopt w rows flushed 128 1806100090 memopt w rows flushed 1
1732 memopt w flush tasks 128 945910151 memopt w flush tasks 1
1733 memopt w flush tasks deferred 128 2737332709 memopt w flush tasks deferred 1
1734 memopt w buffer miss space 128 2208213025 memopt w buffer miss space 1
1735 memopt w buffer miss latch 128 3552849954 memopt w buffer miss latch 1
1736 memopt w buffer miss waits 128 315661965 memopt w buffer miss waits 1
1737 memopt w buffer miss spc nolatch 128 1789953959 memopt w buffer miss spc nolatch 1
1738 memopt w buffer miss wait unq 128 196625028 memopt w buffer miss wait unq 1
1739 memopt w buffer gotcur 128 3639906483 memopt w buffer gotcur 1
1740 memopt w buffer miss spc unq nolat 128 3892484010 memopt w buffer miss spc unq nolat 1
1741 memopt w buffer miss nobuf 128 1059644383 memopt w buffer miss nobuf 1
1742 memopt w buffer hit bucket 0 128 1458260122 memopt w buffer hit bucket 0 1
1743 memopt w buffer wake post 128 1524849321 memopt w buffer wake post 1
1744 memopt w drain sleep work 128 3595850574 memopt w drain sleep work 1
1745 memopt w drain sleep 128 1578572087 memopt w drain sleep 1
1746 memopt w drain sleep wake post 128 2681285354 memopt w drain sleep wake post 1
下面我们就来做一些测试。
1.首先需要调整memoptimize_pool_size参数.
ALTER SYSTEM SET memoptimize_pool_size = 200M SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP
2.创建表并插入数据
CREATE TABLE t1
(
key INTEGER NOT NULL,
value VARCHAR2(20) NOT NULL,
CONSTRAINT pk_key PRIMARY KEY (key)
)
SQL> insert into t1 select rownum,'value'||to_char(rownum) from dual connect by level<=1000000; 1000000 rows created. SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SYS','T1');
PL/SQL procedure successfully completed.
3.启用MemOptimized RowStore,创建hash index
SQL> alter table t1 memoptimize for read; Table altered. SQL> exec dbms_memoptimize.populate(schema_name=>'SYS',table_name=>'T1'); PL/SQL procedure successfully completed.
4.执行查询,查看执行计划
SQL> set linesize 175 pagesize 1000
SQL> set autotrace traceonly
SQL> SELECT * FROM t1 WHERE key = 99;
Execution Plan
----------------------------------------------------------
Plan hash value: 3650286101
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID READ OPTIM| T1 | 1 | 17 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN READ OPTIM | PK_KEY | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("KEY"=99)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
628 bytes sent via SQL*Net to client
623 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到这里没有物理读，而执行计划显示2步分别是“INDEX UNIQUE SCAN READ OPTIM”，“TABLE ACCESS BY INDEX ROWID READ OPTIM”。 我们还可以通过下列查询来查该操作的一些统计值，再执行一次，memopt r lookups和memopt r hint都增加了。代表这访问哈希索引（1 memopt r lookups），找到内存的数据，hint命中(memopt r hint)
SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0;
NAME VALUE
---------------------------------------------------------------- ----------
memopt r lookups 3
memopt r hits 3
memopt r populate tasks accepted 1
SQL> SELECT * FROM t1 WHERE key = 99;
KEY VALUE
---------- --------------------
99 value99
SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0;
NAME VALUE
---------------------------------------------------------------- ----------
memopt r lookups 4
memopt r hits 4
memopt r populate tasks accepted 1
那么我们在来看其他几个例子
1.使用大于或者小于.
SQL> set autotrace ON explain SQL> select * from t1 WHERE key <10; Execution Plan ---------------------------------------------------------- Plan hash value: 1458221975 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 153 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 9 | 153 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_KEY | 9 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- SQL> set autotrace off SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0; NAME VALUE ---------------------------------------------------------------- ---------- memopt r lookups 4 memopt r hits 4 memopt r populate tasks accepted 1
可以看到使用>和<并没有使用MemOptimized RowStore特性。
2.使用多个=
SQL> select * from t1 WHERE key=10 or key=11;
Execution Plan
----------------------------------------------------------
Plan hash value: 2536766119
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 34 | 5 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_KEY | 2 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("KEY"=10 OR "KEY"=11)
SQL> select * from t1 WHERE key=10 and key=11;
Execution Plan
----------------------------------------------------------
Plan hash value: 3010271221
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_KEY | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - access("KEY"=10)
3.使用多个列
SQL> select * from t1 WHERE key=10 and value='value10';
Execution Plan
----------------------------------------------------------
Plan hash value: 3650286101
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_KEY | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VALUE"='value10')
2 - access("KEY"=10)
可以看到，使用其他的条件都不能使用MemOptimized RowStore。
参考文档
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/memory-architecture.html#GUID-D58DC90F-0ABB-4B1E-96C1-6094A04A5E12
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/tuning-system-global-area.html#GUID-4434D082-4748-47C3-A410-B7E2B443DD16