(四)查询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的查询对比明显。