In-Memory 混合扫描介绍
此页面提供有关在 Oracle Database 21c 中引用“INMEMORY”和“NO INMEMORY”列的查询的更多详细信息。
在 Oracle Database 21c 之前，如果查询引用了具有“NO INMEMORY”属性的任何列，则该查询会访问行存储（缓冲区缓存）中的所有数据。因此，表扫描无法利用列格式、谓词下推和其他内存中特性。
从 Oracle Database 21c 开始，引用“INMEMORY”和“NO INMEMORY”列的查询可以访问列数据。
在某些情况下，IM 混合扫描可以将性能提高几个数量级。当查询具有选择性过滤器时，会产生最大的性能优势。在这种情况下，IM 列存储可以快速过滤掉大部分行，以便行存储仅投影少量行。
为了达到最佳性能，优化器会比较不同的访问方法。如果优化器选择表扫描，则存储引擎会自动确定 IM 混合扫描是否比常规行存储扫描执行得更好。当满足以下条件时，优化器会考虑混合扫描：
- 谓词仅包含 `INMEMORY` 列。
- `SELECT`列表包含`INMEMORY`和`NO INMEMORY`列的任意组合。
IM混合扫描在逻辑上将工作分为两部分：一部分处理IM列存储上的查询，另一部分处理行存储上的查询。在执行计划中，名为 `TABLE ACCESS INMEMORY FULL (HYBRID)` 的操作表示混合扫描。请注意，如果运行时统计表明仅访问行存储会提高性能，那么数据库可以在运行时禁用 IM 混合扫描。
实践：在查询中使用In-Memory混合扫描
这种做法展示了引用“INMEMORY”和“NO INMEMORY”列的查询如何访问列数据。 这种称为 IM 混合扫描的优化器访问方法可以将性能提高几个数量级。 如果优化器选择表扫描，存储引擎会自动确定 IM 混合扫描是否比缓冲区缓存中的常规行存储扫描执行得更好。
当满足以下条件时，优化器会考虑混合扫描：
- 谓词仅包含“INMEMORY”列。
- `SELECT` 列表包含 `INMEMORY` 和 `NO INMEMORY` 列的任意组合。
第 1 步：使用 In-Memory Column Store 搭建环境
`IM_Hybrid_setup.sh` shell 脚本将 IM 列存储配置为 110M，创建一个名为 `IMU.IMTAB` 的内存表，其中包含两个 `INMEMORY` 列和一个 `NO INMEMORY` 列，并填充该表。 shell 脚本在 Oracle Database 19c 和 Oracle Database 21c 中执行相同的操作。
- 运行`IM_Hybrid_setup.sh`脚本。
$ cd /home/oracle/labs/M104783GC10
$ /home/oracle/labs/M104783GC10/IM_Hybrid_setup.sh
...
SQL> ALTER SYSTEM SET sga_target=812M SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET inmemory_size=110M SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
...
SQL> CREATE TABLESPACE imtbs DATAFILE SIZE 500M;
Tablespace created.
SQL> CREATE USER imu IDENTIFIED BY password DEFAULT TABLESPACE imtbs;
USER created.
SQL> GRANT CREATE SESSION, CREATE TABLE, unlimited tablespace TO imu;
GRANT succeeded.
SQL>
SQL> CREATE TABLE imu.imtab (c1_noinmem NUMBER, c2_inmem NUMBER, c3_inmem VARCHAR2(4000))
2 INMEMORY PRIORITY high MEMCOMPRESS FOR capacity low NO INMEMORY(c1_noinmem);
TABLE created.
SQL> INSERT INTO imu.imtab VALUES (3,4,'Test21c');
1 ROW created.
SQL> INSERT INTO imu.imtab SELECT c1_noinmem + (SELECT MAX(c1_noinmem) FROM imu.imtab),
2 c2_inmem + (SELECT MAX(c2_inmem) FROM imu.imtab),
3 c3_inmem|| (SELECT MAX(c2_inmem) FROM imu.imtab) FROM imu.imtab;
1 ROW created.
...
131072 ROWS created.
SQL> COMMIT;
Commit complete.
SQL> exit
$
$ /home/oracle/labs/M104783GC10/IM_Hybrid_setup.sh
...
SQL> ALTER SYSTEM SET sga_target=812M SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET inmemory_size=110M SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
ORA-01109: DATABASE NOT OPEN
DATABASE dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
...
SQL> CREATE TABLESPACE imtbs DATAFILE SIZE 500M;
Tablespace created.
SQL> CREATE USER imu IDENTIFIED BY password DEFAULT TABLESPACE imtbs;
USER created.
SQL> GRANT CREATE SESSION, CREATE TABLE, unlimited tablespace TO imu;
GRANT succeeded.
SQL>
SQL> CREATE TABLE imu.imtab (c1_noinmem NUMBER, c2_inmem NUMBER, c3_inmem VARCHAR2(4000))
2 INMEMORY PRIORITY high MEMCOMPRESS FOR capacity low NO INMEMORY(c1_noinmem);
TABLE created.
SQL> INSERT INTO imu.imtab VALUES (3,4,'Test21c');
1 ROW created.
SQL> INSERT INTO imu.imtab SELECT c1_noinmem + (SELECT MAX(c1_noinmem) FROM imu.imtab),
2 c2_inmem + (SELECT MAX(c2_inmem) FROM imu.imtab),
3 c3_inmem|| (SELECT MAX(c2_inmem) FROM imu.imtab) FROM imu.imtab;
1 ROW created.
...
131072 ROWS created.
SQL> COMMIT;
Commit complete.
SQL> exit
$
步骤2：填充In-Memory表
- 作为`SYSTEM`连接到`PDB21`并设置查询列的格式。
$ sqlplus system@PDB21
Copyright (c) 1982, 2019, Oracle. ALL rights reserved.
Enter password:
LAST Successful login TIME: Wed Jan 08 2020 12:03:56 +00:00
Connected TO:
SQL> COL TABLE_NAME FORMAT A10
SQL> COL inmemory_compression FORMAT A11
SQL> COL COL_NO_INMEM FORMAT 9999999999999999999999
SQL> COL COL_INMEM FORMAT 9999999999999999999999
SQL> COL segment_name FORMAT A12
SQL>
Copyright (c) 1982, 2019, Oracle. ALL rights reserved.
Enter password:
LAST Successful login TIME: Wed Jan 08 2020 12:03:56 +00:00
Connected TO:
SQL> COL TABLE_NAME FORMAT A10
SQL> COL inmemory_compression FORMAT A11
SQL> COL COL_NO_INMEM FORMAT 9999999999999999999999
SQL> COL COL_INMEM FORMAT 9999999999999999999999
SQL> COL segment_name FORMAT A12
SQL>
- 显示`IMU.IMTAB`表和该表所有列的内存属性。
SQL> SELECT TABLE_NAME, inmemory_compression "COMPRESSION", inmemory_priority "PRIORITY"
FROM dba_tables WHERE owner='IMU';
TABLE_NAME COMPRESSION PRIORITY
---------- ----------------- --------
IMTAB FOR CAPACITY LOW HIGH
SQL> SELECT obj_num, segment_column_id, inmemory_compression FROM v$im_column_level im, dba_objects o
WHERE im.obj_num = o.object_id
AND o.object_name='IMTAB';
OBJ_NUM SEGMENT_COLUMN_ID INMEMORY_CO
---------- ----------------- -----------
74869 1 NO INMEMORY
74869 2 DEFAULT
74869 3 DEFAULT
SQL>
FROM dba_tables WHERE owner='IMU';
TABLE_NAME COMPRESSION PRIORITY
---------- ----------------- --------
IMTAB FOR CAPACITY LOW HIGH
SQL> SELECT obj_num, segment_column_id, inmemory_compression FROM v$im_column_level im, dba_objects o
WHERE im.obj_num = o.object_id
AND o.object_name='IMTAB';
OBJ_NUM SEGMENT_COLUMN_ID INMEMORY_CO
---------- ----------------- -----------
74869 1 NO INMEMORY
74869 2 DEFAULT
74869 3 DEFAULT
SQL>
- 对 `IMU.IMTAB` 表执行完整扫描，以便将表填充到 IM 列存储中。
SQL> SELECT /*+ FULL(imu.imtab) NO_PARALLEL(imu.imtab) */ COUNT(*) FROM imu.imtab;
COUNT(*)
----------
262144
SQL>
COUNT(*)
----------
262144
SQL>
- 验证 `IMU.IMTAB` 表是否已填充到 IM 列存储中。
SQL> COL segment_name FORMAT A12
SQL> SELECT segment_name, bytes, inmemory_size, bytes_not_populated
FROM v$im_segments;
SEGMENT_NAME BYTES INMEMORY_SIZE BYTES_NOT_POPULATED
------------ ---------- ------------- -------------------
IMTAB 17481728 4456448 0
SQL>
SQL> SELECT segment_name, bytes, inmemory_size, bytes_not_populated
FROM v$im_segments;
SEGMENT_NAME BYTES INMEMORY_SIZE BYTES_NOT_POPULATED
------------ ---------- ------------- -------------------
IMTAB 17481728 4456448 0
SQL>
第 3 步：完成In-Memory扫描
- 对`IMU.IMTAB`表执行查询。 `SELECT` 列表包含`NO INMEMORY` 列，谓词仅包含`NO INMEMORY` 列。 然后检查执行计划。
SQL> SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab
WHERE c1_noinmem BETWEEN 5 AND 1258291;
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 1dpya5ws8gbvx, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c1_noin
mem BETWEEN 5 AND 1258291
Plan hash VALUE: 360700294
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 547 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| IMTAB | 292K| 3712K| 547 (1)| 00 :00:01 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 ROWS selected.
SQL>
WHERE c1_noinmem BETWEEN 5 AND 1258291;
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 1dpya5ws8gbvx, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c1_noin
mem BETWEEN 5 AND 1258291
Plan hash VALUE: 360700294
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 547 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| IMTAB | 292K| 3712K| 547 (1)| 00 :00:01 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 ROWS selected.
SQL>
两个会话中的优化器都选择了`TABLE ACCESS FULL`方法，因为谓词不仅仅包含`INMEMORY`列。
- 对 `IMU.IMTAB` 表执行第二个查询。 `SELECT` 列表包含`NO INMEMORY` 列，谓词包含`NO INMEMORY` 列和`INMEMORY` 列。 然后检查执行计划。
SQL> SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab
WHERE c1_noinmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%';
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID afz9bm3rscr3y, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c1_noinmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%'
Plan hash VALUE: 360700294
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 582 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2015 | | |
|* 2 | TABLE ACCESS FULL| IMTAB | 230K| 443M| 582 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291 AND "C3_INMEM" LIKE 'Test21c%'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 ROWS selected.
SQL>
WHERE c1_noinmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%';
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID afz9bm3rscr3y, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c1_noinmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%'
Plan hash VALUE: 360700294
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 582 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2015 | | |
|* 2 | TABLE ACCESS FULL| IMTAB | 230K| 443M| 582 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291 AND "C3_INMEM" LIKE 'Test21c%'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 ROWS selected.
SQL>
两个会话中的优化器都选择了`TABLE ACCESS FULL`访问方法，因为谓词不仅仅包含`INMEMORY`列。 它包含一个`INMEMORY`列和一个`NO INMEMORY`列。
- 对 `IMU.IMTAB` 表执行第三次查询。 `SELECT` 列表包含`NO INMEMORY` 列，谓词仅包含`INMEMORY` 列。 然后检查执行计划。
SQL> SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab
WHERE c2_inmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%';
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID f07n4gc330rhz, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c2_inmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%'
Plan hash VALUE: 360700294
---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 582 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2028 | | |
|* 2 | TABLE ACCESS INMEMORY FULL (HYBRID)| IMTAB | 230K| 445M| 582 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C2_INMEM">=5 AND "C2_INMEM"<=1258291 AND "C3_INMEM"
LIKE 'Test21c%'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 ROWS selected.
SQL>
WHERE c2_inmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%';
COL_NO_INMEM
-----------------------
103079608317
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID f07n4gc330rhz, child NUMBER 0
-------------------------------------
SELECT SUM(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE c2_inmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test21c%'
Plan hash VALUE: 360700294
---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 582 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2028 | | |
|* 2 | TABLE ACCESS INMEMORY FULL (HYBRID)| IMTAB | 230K| 445M| 582 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C2_INMEM">=5 AND "C2_INMEM"<=1258291 AND "C3_INMEM"
LIKE 'Test21c%'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 ROWS selected.
SQL>
两个会话中的优化器选择了不同的访问方法。 在 21c 中，选择了 `TABLE ACCESS INMEMORY FULL (HYBRID)` 访问方法，因为谓词仅包含 `INMEMORY` 列，而 `SELECT` 列出了 `NO INMEMORY` 列。
第 4 步：删除用户
- 删除`imu`用户。
SQL> DROP USER imu CASCADE;
USER dropped.
SQL> EXIT
$
USER dropped.
SQL> EXIT
$
Speak Your Mind