版权声明:本文为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