数据oracle im,（四）查询IM中的数据

(四)查询IM中的数据

之前的三篇我们分别介绍了以下三部分内容，感兴趣的朋友可以去看一下：

加载数据到IM：http://blog.csdn.net/badly9/article/details/49777993

IM压缩：http://blog.csdn.net/badly9/article/details/49815643

4.1加载测试表到IM中

将测试表设置IM属性，同时保证她们被加载到IM中。

1.开启测试表的IM属性。

SQL> ALTER TABLE DWB_RTL_SLS_RETRN_LINE_ITEM INMEMORY;

Table altered.

SQL> ALTER TABLE DWB_RTL_TNDR_LI INMEMORY;

Table altered.

SQL> ALTER TABLE DWB_RTL_TRX INMEMORY;

Table altered.

SQL> ALTER TABLE DWR_ORG_BSNS_UNIT INMEMORY;

Table altered.

SQL> ALTER TABLE DWR_SKU_ITEM INMEMORY;

Table altered.

2.确认这些表已经开启IM属性

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY,

2 INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION

3 FROM USER_TABLES;

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS

------------------------------ -------- -------- --------------- -----------------

DWB_RTL_SLS_RETRN_LINE_ITEM

DWB_RTL_TNDR_LI

DWB_RTL_TRX

DWR_ORG_BSNS_UNIT ENABLED NONE AUTO FOR QUERY LOW

DWR_SKU_ITEM ENABLED NONE AUTO FOR QUERY LOW

可以看到有三张表的IM属性显示为空，因为它们是分区表，所以要从USER_TAB_PARTITIONS中查看：

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY,

2 INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION

3 FROM USER_TAB_PARTITIONS;

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS

---------------------------------------- -------- -------- --------------- -----------------

DWB_RTL_SLS_RETRN_LINE_ITEM ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_SLS_RETRN_LINE_ITEM ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_SLS_RETRN_LINE_ITEM ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_SLS_RETRN_LINE_ITEM ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TNDR_LI ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TNDR_LI ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TNDR_LI ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TNDR_LI ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TRX ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TRX ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TRX ENABLED NONE AUTO FOR QUERY LOW

DWB_RTL_TRX ENABLED NONE AUTO FOR QUERY LOW

12 rows selected.

3.将IM中其他表置为NO INMEMORY。

查询IM中的表

SQL> COL OWNER FORMAT A15

SQL> COL SEGMENT_NAME FORMAT A20

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_SIZE, POPULATE_STATUS

2 FROM V$IM_SEGMENTS;

OWNER SEGMENT_NAME INMEMORY_SIZE POPULATE_

--------------- -------------------- ------------- ---------

BADLY9 SALES_INM3 2228224 COMPLETED

BADLY9 SALES_INM_2 5439488 COMPLETED

BADLY9 SALES_INM_4 32309248 COMPLETED

BADLY9 COSTS 1179648 COMPLETED

BADLY9 SALES_INM_1 28114944 COMPLETED

BADLY9 SALES_INM_3 4325376 COMPLETED

BADLY9 SALES_INM2 3342336 COMPLETED

BADLY9 SALES 7536640 COMPLETED

8 rows selected.

置为NO INMEMORY

ALTER TABLE BADLY9.SALES_INM3 NO INMEMORY;

ALTER TABLE BADLY9.SALES_INM_2 NO INMEMORY;

ALTER TABLE BADLY9.SALES_INM_4 NO INMEMORY;

ALTER TABLE BADLY9.COSTS NO INMEMORY;

ALTER TABLE BADLY9.SALES_INM_1 NO INMEMORY;

ALTER TABLE BADLY9.SALES_INM_3 NO INMEMORY;

ALTER TABLE BADLY9.SALES_INM2 NO INMEMORY;

ALTER TABLE BADLY9.SALES NO INMEMORY;

确认一下：

SQL> COL OWNER FORMAT A15

SQL> COL SEGMENT_NAME FORMAT A20

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_SIZE, POPULATE_STATUS

2 FROM V$IM_SEGMENTS;

no rows selected

4.通过查询将测试表加载到IM中

SELECT COUNT(*) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

SELECT COUNT(*) FROM DWB_RTL_TNDR_LI;

SELECT COUNT(*) FROM DWB_RTL_TRX;

SELECT COUNT(*) FROM DWR_ORG_BSNS_UNIT;

SELECT COUNT(*) FROM DWR_SKU_ITEM;

确认表已经加载到IM中

SQL> COL OWNER FORMAT A15

SQL> COL SEGMENT_NAME FORMAT A30

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_SIZE, POPULATE_STATUS

2 FROM V$IM_SEGMENTS;

OWNER SEGMENT_NAME INMEMORY_SIZE POPULATE_

--------------- ------------------------------ ------------- ---------

RTL DWB_RTL_SLS_RETRN_LINE_ITEM 165609472 COMPLETED

RTL DWR_SKU_ITEM 5373952 COMPLETED

RTL DWB_RTL_TNDR_LI 30605312 COMPLETED

RTL DWB_RTL_SLS_RETRN_LINE_ITEM 166723584 COMPLETED

RTL DWB_RTL_TRX 23265280 COMPLETED

RTL DWR_ORG_BSNS_UNIT 1179648 COMPLETED

RTL DWB_RTL_TNDR_LI 23265280 COMPLETED

RTL DWB_RTL_SLS_RETRN_LINE_ITEM 166723584 COMPLETED

RTL DWB_RTL_TRX 23265280 COMPLETED

RTL DWB_RTL_TNDR_LI 23265280 COMPLETED

RTL DWB_RTL_TRX 23265280 COMPLETED

11 rows selected.

4.2 简单聚合函数查询

1.查看使用IM查询情况下的时间

SQL> set timing on

SQL> SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

MAX(EXTENDED_AMT)

-----------------

10.55

Elapsed:00:00:00.34

2.禁用INMEMORY_QUERY，重新查询

SQL> ALTER SESSION SET INMEMORY_QUERY=DISABLE;

Session altered.

Elapsed: 00:00:00.13

SQL> SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

MAX(EXTENDED_AMT)

-----------------

10.55

Elapsed:00:00:13.75

3.恢复环境开启INMEMORY_QUERY

SQL> ALTER SESSION SET INMEMORY_QUERY=ENABLE;

Session altered.

Elapsed: 00:00:00.00

SQL> set timing off

4.比较两次的执行计划

(1)

SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

(2)

ALTER SESSION SET INMEMORY_QUERY=DISABLE;

SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

ALTER SESSION SET INMEMORY_QUERY=ENABLE;

具体输出：

(1)

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 8q5xwrfukc71s, child number 2

-------------------------------------

SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM

Plan hash value: 1560629232

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 57829 (100)| | | |

| 1 | SORT AGGREGATE | | 1 | 4 | | | | |

| 2 | PARTITION RANGE ALL | | 44 | 176 | 57829 (1)| 00:00:03 | 1 |1048575|

| 3 |TABLE ACCESS INMEMORY FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 44 | 176 | 57829 (1)| 00:00:03 | 1 |1048575|

----------------------------------------------------------------------------------------------------------------------------

Note

-----

- statistics feedback used for this statement

(2)

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 8q5xwrfukc71s, child number 1

-------------------------------------

SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM

Plan hash value: 1560629232

--------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 58010 (100)| | | |

| 1 | SORT AGGREGATE | | 1 | 4 | | | | |

| 2 | PARTITION RANGE ALL| | 27M| 105M| 58010 (1)| 00:00:03 | 1 |1048575|

| 3 |TABLE ACCESS FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 27M| 105M| 58010 (1)| 00:00:03 | 1 |1048575|

--------------------------------------------------------------------------------------------------------------------

可以看到一个是TABLE ACCESS INMEMORY FULL，一个是TABLE ACCESS FULL

4.3关于IM的统计信息

我们可以通过结合V$MYSTAT和V$STATNAME来查看关于IM的统计信息

实验过程如下：

1.通过V$STATNAME视图查看IM统计项

SQL> SELECT DISPLAY_NAME

2 FROM V$STATNAME

3 WHERE DISPLAY_NAME LIKE 'IM %';

DISPLAY_NAME

----------------------------------------------------------------

IM populate blocks invalid

IM populate transactions check

IM populate undo segheader rollback

IM populate undo records applied

......

IM zzzz spare9

IM zzzz spare10

198 rows selected.

可以看到有198个相关的统计项，然而在日常管理中我们并不是都需要，主要用到的有以下几项：

IM scan bytes uncompressed：在未优化扫描优先级的情况下，在所有CU(压缩单元)中未压缩的大小，单位为bytes。

IM scan bytes in-memory：在未优化扫描优先级的情况下，在所有CU中压缩的大小，单位为bytes。

IM scan CUs columns accessed:扫描中总共访问的CU的总数。

IM scan CUs columns theoretical max：如果每个扫描都需要访问IMCU中所有的列，那么理论上最多访问的CU数。

2.查看一下需要关注的统计项

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan bytes in-memory',

6 'IM scan bytes uncompressed',

7 'IM scan CUs columns accessed',

8 'IM scan CUs columns theoretical max');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan bytes in-memory 0

IM scan bytes uncompressed 0

IM scan CUs columns accessed 0

IM scan CUs columns theoretical max 0

3.执行一下测试查询：

SQL> SELECT MAX(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM;

MAX(EXTENDED_AMT)

-----------------

10.55

4.再查看一下关注的统计项

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan bytes in-memory',

6 'IM scan bytes uncompressed',

7 'IM scan CUs columns accessed',

8 'IM scan CUs columns theoretical max');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan bytes in-memory 481927401

IM scan bytes uncompressed 3044446804

IM scan CUs columns accessed 44

IM scan CUs columns theoretical max 704

注：该测试表有16列704=44*16

4.4带过滤条件的查询

对于带过滤条件的查询，IM还有额外的技术来进行加速，那就是存储索引，如果在where条件中指定了关于min/max的范围，那么一个CU中包含的值并不在这个min/max范围内，则会被跳过，不进行扫描，以提升查询速度。

相关的统计项我们关注以下项：

IM scan segments minmax eligible：所有需要扫描的包含数据的CU数量。

IM scan CUs pruned：通过存储索引被筛选掉的CU数量。

实验过程如下：

1.执行查询：

SQL> SET TIMING ON

SQL> SELECT SUM(EXTENDED_AMT)

2 FROM RTL.DWB_RTL_SLS_RETRN_LINE_ITEM

3 WHERE ACTN_CD = 'Return';

SUM(EXTENDED_AMT)

-----------------

112775.59

Elapsed: 00:00:00.03

SQL> SET TIMING OFF

2.查看关注的统计项：

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan segments minmax eligible',

6 'IM scan CUs pruned',

7 'IM scan CUs columns accessed');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan CUs columns accessed 6

IM scan CUs pruned 41

IM scan segments minmax eligible 44

可以看到最终实际访问的CU数量只有6，过滤掉了41个CU，原本要访问的是44个CU，这里可以看到44-41明明是3，为啥最后访问的是6呢，那多出来的3是访问存储索引等必要的开销。

4.5多过滤条件查询

4.4介绍了对于单个过滤条件，IM可以使用存储索引来加速查找速度，那么对于多个过滤条件，IM是否还能使用存储索引来加快查找速度呢？答案是肯定的，接下来看看我们下边的实验：

实验过程：

1.在select语句中使用2个查询条件：

SQL> SELECT SUM(EXTENDED_AMT)

2 FROM RTL.DWB_RTL_SLS_RETRN_LINE_ITEM

3 WHERE ACTN_CD = 'Return'

4 AND QTY = 2;

SUM(EXTENDED_AMT)

-----------------

6406.7

2.查询关注的IM统计项：

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan segments minmax eligible',

6 'IM scan CUs pruned',

7 'IM scan CUs columns accessed',

8 'IM scan rows',

9 'IM scan rows valid',

10 'IM scan rows optimized');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan CUs columns accessed 9

IM scan rows 27541321

IM scan rows valid 1570478

IM scan rows optimized 25970843

IM scan CUs pruned 41

IM scan segments minmax eligible 44

6 rows selected.

这里我们多加了几个统计项：

IM scan rows：IM中应该扫描的行数

IM scan rows optimized IM中被优化掉的扫描行数

IM scan rows valid：IM中实际扫描的行数

可以看到在本次查询中IM依然使用了存储索引IM scan segments minmax eligible为44，IM scan CUs pruned为41，相差仍未3，但.最后真正扫描的CU数目为9，有所升高。

3.那么再测试一下使用3个过滤条件的查询

SQL> SELECT SUM(EXTENDED_AMT)

2 FROM RTL.DWB_RTL_SLS_RETRN_LINE_ITEM

3 WHERE ACTN_CD = 'Return'

4 AND QTY = 2

5 AND BEGIN_DT_TIMESTAMP =

6 TO_TIMESTAMP('01/01/2014 08:00','MM/DD/YYYY HH24:MI');

SUM(EXTENDED_AMT)

-----------------

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan segments minmax eligible',

6 'IM scan CUs pruned',

7 'IM scan CUs columns accessed',

8 'IM scan rows',

9 'IM scan rows valid',

10 'IM scan rows optimized');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan CUs columns accessed 5

IM scan rows 27541321

IM scan rows valid 1064656

IM scan rows optimized 26476665

IM scan CUs pruned 42

IM scan segments minmax eligible 44

6 rows selected.

可以看到加上第3个过滤条件后，最后扫描的CU数量又降回了5，说明并非过滤条件越多开销就一定越大，oracle会根据实际情况更改过滤顺序，减少消耗。

4.6非等值过滤

前边我们都是测试了等值条件的过滤，那么对于非等值条件是否也是一样呢，具体看测试吧~

实验过程：

1.通过between条件对数据进行过滤

SQL> SET TIMING ON;

SQL> SELECT SUM(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM

2 WHERE BEGIN_DT_TIMESTAMP BETWEEN

3 TO_TIMESTAMP('01/01/2014 08:00','MM/DD/YYYY HH24:MI')

4 AND TO_TIMESTAMP('01/01/2014 08:10','MM/DD/YYYY HH24:MI');

SET TIMING OFF;

SUM(EXTENDED_AMT)

-----------------

145956.45

Elapsed: 00:00:00.04

SQL> SET TIMING OFF;

2.查看关注的IM统计项：

SQL> SELECT DISPLAY_NAME, VALUE

2 FROM V$MYSTAT m, V$STATNAME n

3 WHERE m.STATISTIC# = n.STATISTIC#

4 AND n.DISPLAY_NAME IN (

5 'IM scan segments minmax eligible',

6 'IM scan CUs pruned',

7 'IM scan CUs columns accessed',

8 'IM scan rows',

9 'IM scan rows valid',

10 'IM scan rows optimized');

DISPLAY_NAME VALUE

---------------------------------------------------------------- ----------

IM scan CUs columns accessed 12

IM scan rows 27541321

IM scan rows valid 3655848

IM scan rows optimized 23885473

IM scan CUs pruned 38

IM scan segments minmax eligible 44

6 rows selected.

之前的实验已经解释过各个统计项，这里不再赘述。

3.对比不使用INMEMORY QUERY：

SQL> ALTER SESSION SET INMEMORY_QUERY=DISABLE;

Session altered.

SQL> SET TIMING ON;

SQL> SELECT SUM(EXTENDED_AMT) FROM DWB_RTL_SLS_RETRN_LINE_ITEM

2 WHERE BEGIN_DT_TIMESTAMP BETWEEN

3 TO_TIMESTAMP('01/01/2014 08:00','MM/DD/YYYY HH24:MI')

4 AND TO_TIMESTAMP('01/01/2014 08:10','MM/DD/YYYY HH24:MI');

SUM(EXTENDED_AMT)

-----------------

145956.45

Elapsed: 00:00:13.80

SQL> SET TIMING OFF;

SQL> ALTER SESSION SET INMEMORY_QUERY=ENABLE;

Session altered.

可以看到所用时间和前边使用INMEMORY QUERY的查询对比明显。