Oracle 18c新特性-MemOptimized RowStore

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

分享到: 更多