32
Oracle 18c新特性-MemOptimized RowStore
source link: http://www.dboracle.com/archivers/oracle-18c新特性-memoptimized-rowstore.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Oracle Database 18c中引入的MemOptimized RowStore，该功能可以提高通过主键列访问数据的查询性能。比如我们经常使用的这样的SQL语句：SELECT value FROM t WHERE key=:key，其中key是表中的唯一主键。
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.该表上有一个主键。
3.该表未压缩。
开启该功能，会出现一些新的等待事件,总共有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>
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK