12c in memory option學習筆記二_資料訪問

talio發表於2014-09-17
筆記

要了解In Memory區資料的訪問方式，首先來看看In Memory區資料的組織結構。當enable某個表的inmemory屬性後，該表會在首次訪問或者資料庫啟動後載入到In Memory區(取決於priority屬性的設定)。資料在IM區使用壓縮列式儲存，存放在每個IMCU中。在IMCU內部，Oracle為每個IMCU維護著一個對應的In Memory Storage Index, 用於記錄IMCU單元列中的最大值，最小值等資訊。對於每個IMCU，還會有相應的metadata dictionary資訊，儲存在metadata塊中，也就是IM中的64K pool中，塊的大小固定為64K。metadata dictionary記錄物件資訊，列資訊等。

In Memory Storage Index的作用

In Memory Storage Index透過data pruning的機制來幫助提高查詢效能：由於In Memory Storage Index中記錄了該列的最小值，最大值資訊，當查詢語句的where條件中指定了某一列的值或範圍時，則根據該索引的資訊即可預判哪些IMCU需要繼續訪問，哪些可以直接跳過。

以下測試用來幫助理解In Memory Storage Index的使用： 

SQL> create table test_im_access tablespace users as select rownum id,systimestamp time from dual connect by level <=10000000;
SQL> alter table test_im_access inmemory MEMCOMPRESS FOR QUERY LOW;
SQL> select count(*) from test_im_access; 
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments where segment_name='TEST_IM_ACCESS';
SEGMENT_NAME                   INMEMORY_SIZE      BYTES POPULATE_
------------------------------ ------------- ---------- ---------
TEST_IM_ACCESS                     149028864  285212672 COMPLETED 

檢視V_$IM_HEADER記錄了每個IMCU的記憶體地址，分配大小等資訊： 

SQL> select IMCU_ADDR,ALLOCATED_LEN,USED_LEN from V_$IM_HEADER where OBJD=(select object_id from dba_objects where object_name='TEST_IM_ACCESS') order by IMCU_ADDR;
IMCU_ADDR        ALLOCATED_LEN   USED_LEN
---------------- ------------- ----------
00000003D81FFF88       7340032    1199852
00000003D88FFF88       7340032    6797528
00000003DBFFFFA0       7340032    6632157
00000003DC6FFFA0       8388608    7997296
00000003DCEFFFA0       8388608    7997296
00000003DD6FFFA0       8388608    7996030
00000003DDEFFFA0       8388608    7997304
00000003DE6FFFA0       8388608    7997296
00000003DEEFFFA0       8388608    7997376
00000003DF6FFFA0       8388608    7997320
00000003EC000000       8388608    8035475
00000003EC800000       8388608    7711008
00000003ED000000       8388608    7876120
00000003ED800000       8388608    7887481
00000003EE000000       8388608    7997296
00000003EE800000       8388608    7997304
00000003EF000000       8388608    7997352
00000003EF800000       8388608    7997304
18 rows selected.

檢視V_$IM_COL_CU記錄了每個IMCU的記錄條數，最小值，最大值等資訊。（將V_$IM_HEADER和V_$IM_COL_CU檢視中的結果比對可發現似乎有一個IMCU分配了空間，但並沒有載入資料，原因未知，測試了一些其他的壓縮方式，沒有重現該現象，這裡沒有深究。） 

SQL> select HEAD_PIECE_ADDRESS IMCU_ADDR,COLUMN_NUMBER,DICTIONARY_ENTRIES,UTL_RAW.CAST_TO_NUMBER(MINIMUM_VALUE) MINIMUM_VALUE,UTL_RAW.CAST_TO_NUMBER(MAXIMUM_VALUE) MAXIMUM_VALUE
from V_$IM_COL_CU 
where OBJD=(select object_id from dba_objects where object_name='TEST_IM_ACCESS') and COLUMN_NUMBER=1 order by 1;
IMCU_ADDR        COLUMN_NUMBER DICTIONARY_ENTRIES  MINIMUM_VALUE  MAXIMUM_VALUE
---------------- ------------- ------------------ -------------- --------------
00000003D81FFF88             1             591600        4736151        5623550
00000003DBFFFFA0             1             495190        9468851       10000000
00000003DC6FFFA0             1             591600        8877251        9764650
00000003DCEFFFA0             1             591600        8285651        9173050
00000003DD6FFFA0             1             591500        7694151        8581450
00000003DDEFFFA0             1             591600        7102551        7989950
00000003DE6FFFA0             1             591600        6510951        7398350
00000003DEEFFFA0             1             591600        5919351        6806750
00000003DF6FFFA0             1             591600        5327751        6215150
00000003EC000000             1             642753              1         642753
00000003EC800000             1             596867         642754        1239620
00000003ED000000             1             582624        1239621        1822244
00000003ED800000             1             583466        1822245        2665550
00000003EE000000             1             591600        2369751        3257150
00000003EE800000             1             591600        2961351        3848750
00000003EF000000             1             591600        3552951        4440350
00000003EF800000             1             591600        4144551        5031950
17 rows selected. 

接下來，針對已載入IM區的test_im_access表作id=3的條件查詢，並記錄相關統計資訊： 

select name,value from v$mystat, v$statname 
where v$mystat.statistic# = v$statname.statistic# 
  and v$statname.name in 
  ('CPU used by this session',
   'IM scan rows',
   'IM scan rows valid',
   'IM scan CUs memcompress for query low',
   'IM scan CUs pruned'
  );
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                  2
IM scan CUs memcompress for query low                                     0
IM scan rows                                                              0
IM scan rows valid                                                        0
IM scan CUs pruned                                                        0
 
select * from test_im_access where id=3;
--上述語句的執行計劃如下：
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |   387 | 13545 |   438  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS INMEMORY FULL| TEST_IM_ACCESS |   387 | 13545 |   438  (11)| 00:00:01 |
---------------------------------------------------------------------------------------------
--check stats again:
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                 12
IM scan CUs memcompress for query low                                    17
IM scan rows                                                       10000000
IM scan rows valid                                                   642753
IM scan CUs pruned                                                       16

統計結果如下：

Stat name

Value

CPU used by this session

10

IM scan CUs memcompress for query low

17

IM scan rows

10000000

IM scan rows valid

642753

IM scan CUs pruned

16

從統計結果可以看到，雖然test_im_access表採用’memcompress for query low’壓縮列儲存後佔用的記憶體IMCU個數為17，但這裡實際發生了完全掃描的IMCU個數只有一個（IM scan CUs memcompress for query low - IM scan CUs pruned），其中的16個塊都發生了pruning。也就是隻要訪問這16個IMCU中的In Memory Storage Index, 即可確定我們要查詢的記錄不在這些IMCU中，從而跳過他們，而不用完全掃描整個IMCU。此外，該表有1千萬條記錄，由於IMCU data pruning的作用，我們實際只需要掃描其中一個IMCU的642753條記錄。從該例也可以看出IM scan rows統計的並不是真正訪問了的行數，它更像是一個估計的行數。

針對in-memory storage indexes，oracle提供了INMEMORY_PRUNING和NO_INMEMORY_PRUNING兩個hint來控制這種索引的使用。繼續上面的例子: 

select /*+ NO_INMEMORY_PRUNING */* from test_im_access where id=3; 

Stat name

Value

CPU used by this session

40

IM scan CUs memcompress for query low

17

IM scan rows

10000000

IM scan rows valid

10000000

IM scan CUs pruned

0

可以看到，在使用NO_INMEMORY_PRUNING hint來禁用in-memory storage indexes的使用後，發生了pruning的CU個數為0，同樣的查詢需要訪問IM中所有的17個IMCU,全部1千萬條資料，CPU time也增加到了40。

實際上，Storage Index並不是12C In Memory Option中才有的新技術，瞭解過Exadata的應該知道這是在Exadata中就已經引入的技術了。Exadata的儲存索引中除了標識最大值和最小值之外，還有一個標識用來表示在該儲存單元中是否包含空值(null)，從而使得尋找空值的查詢效率更高。那麼In Memory Storage Index是否也標識了空值呢？從已公開的資料我沒有查詢到這一點，還是用測試來驗證一下： 

insert into test_im_access select null,systimestamp time from dual connect by level <=10000;
commit;

等待片刻，待oracle將新的資料載入IM區後，執行以下語句並記錄統計結果： 

select count(*) from test_im_access where id is null; 

Stat name

Value

CPU used by this session

7

IM scan CUs memcompress for query low

17

IM scan rows

10010000

IM scan rows valid

1096690

IM scan CUs pruned

15

結果表明，在我們作空值查詢時，In Memory Storage Index仍舊發揮了作用，其中的15個IMCU發生了data pruning,說明了In Memory Storage Index同樣有對空值的標識。

關於data pruning, Oracle In Memory白皮書中還提到了透過metadata dictionary可以實現另一個級別的data pruning。但在測試中並沒有觀察到該技術的使用。

SIMD Vector Processing

從前面的測試統計資訊來看，無論是否使用了data pruning技術，從IM區訪問資料所佔用的CPU time都是很低的。這是如何實現的呢？

Oracle在IM option中引入了SIMD(Single Instruction processing Multiple Data values)向量處理技術用於提高CPU效率，簡單來說就是透過單條CPU指令作批次資料比對。Oracle給出了下圖用於簡單說明該技術的原理，即每次load一批資料到CPU上的SIMD暫存器(register)上,透過單條CPU指令來比較整批資料，將匹配的結果記錄後，再接著比較下一批資料…

 In Memory Join操作

按Oracle的說法，IM區資料列式儲存比較適用於用bloom filter方法來提升連線操作的效率。繼續用測試來觀察這一點:

建立測試表： 

create table small_table tablespace users as select rownum id,systimestamp time from dual connect by level <=1000000; 
create table big_table (id number, TIME TIMESTAMP) tablespace users; 
 
Begin
  for i in 1..50 loop
    insert into big_table select rownum+1000000*(i-1) id,systimestamp time from dual connect by level <=1000000;
    commit;
  end loop;
end;
/ 
 
BEGIN
  dbms_stats.gather_table_stats(ownname =>'SYS',
    tabname => 'BIG_TABLE',
    degree=>8,
    method_opt => 'for all columns size auto',
    cascade => TRUE);
END;
/ 
 
BEGIN
  dbms_stats.gather_table_stats(ownname =>'SYS',
    tabname => 'SMALL_TABLE',
    degree=>8,
    method_opt => 'for all columns size auto',
    cascade => TRUE);
END;
/ 

將測試表載入IM區和keep buffer cache中，用於比較訪問IM區和buffer cache時連線查詢的不同： 

alter table big_table inmemory;
alter table small_table inmemory;
alter table big_table storage (buffer_pool keep);
alter table small_table storage (buffer_pool keep);
select count(*) from big_table;
select count(*) from small_table;
 
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments;
SEGMENT_NAME                             INMEMORY_SIZE      BYTES POPULATE_
---------------------------------------- ------------- ---------- ---------
BIG_TABLE                                    792788992 1342177280 COMPLETED
SMALL_TABLE                                   13828096   28311552 COMPLETED
--資料已載入到IM中
 
SQL> select b.object_name,sum(NUM_BUF) from X$KCBOQH a, dba_objects b where a.OBJ#=b.object_id and b.object_name in ('BIG_TABLE','SMALL_TABLE') group by b.object_name;
OBJECT_NAME                    SUM(NUM_BUF)
------------------------------ ------------
SMALL_TABLE                            3322
BIG_TABLE                            158863
--資料已載入到cache中

 

對測試表作連線查詢，觀察執行時間，執行計劃和consistent gets的變化： 

--這裡取第二次執行時作觀察，因為第一次執行會有一些硬解析的成本
SQL> set autotrace on
SQL> select count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912'; 
  COUNT(*)
----------
   1000000
Elapsed: 00:00:09.35
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    24 |  2632  (35)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |    24 |            |          |
|*  2 |   HASH JOIN                   |             | 10012 |   234K|  2632  (35)| 00:00:01 |
|   3 |    JOIN FILTER CREATE         | :BF0000     | 10000 |   175K|   114  (54)| 00:00:01 |
|*  4 |     TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 |   175K|   114  (54)| 00:00:01 |
|   5 |    JOIN FILTER USE            | :BF0000     |    50M|   286M|  2115  (21)| 00:00:01 |
|*  6 |     TABLE ACCESS INMEMORY FULL| BIG_TABLE   |    50M|   286M|  2115  (21)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ID"="B"."ID")
   4 - inmemory(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
       filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
   6 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"A"."ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"."ID"))
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

這裡，測試語句的執行時間為9.35秒，consistent gets的個數為17，從執行計劃可看到啟用了bloom filter計算方法.

若是禁用bloom filter會有什麼變化呢？ 

禁用bloom filter:
alter session set "_bloom_filter_enabled"=FALSE;
SQL> select count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912';
  COUNT(*)
----------
   1000000
Elapsed: 00:00:45.18
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    24 |  2632  (35)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |    24 |            |          |
|*  2 |   HASH JOIN                  |             | 10012 |   234K|  2632  (35)| 00:00:01 |
|*  3 |    TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 |   175K|   114  (54)| 00:00:01 |
|   4 |    TABLE ACCESS INMEMORY FULL| BIG_TABLE   |    50M|   286M|  2115  (21)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ID"="B"."ID")
   3 - inmemory(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
       filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

禁用bloom filter後，雖然仍是訪問IM區，consistent gets的個數仍為17，但語句的執行時間已增長為45.18秒.

如果使用hint來禁用IM訪問，強制連線在傳統的buffer cache中執行又會是什麼結果呢： 

SQL> select /*+ NO_INMEMORY */ count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912';
  COUNT(*)
----------
   1000000
Elapsed: 00:00:52.11
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |    24 |       | 90645   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE     |             |     1 |    24 |       |            |          |
|*  2 |   HASH JOIN         |             |   977K|    22M|    27M| 90645   (2)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| SMALL_TABLE |   976K|    16M|       |   978   (7)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIG_TABLE   |    50M|   286M|       | 44899   (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ID"="B"."ID")
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     162214  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

雖然都是在記憶體執行的，但這裡的consistent gets急劇增長為162214，語句執行時間也增加到了52.11秒.

以上測試結果總結如下表:

Join Method

Consistent Gets

Elapsed Time

1.Join In IM, with BF enabled

17

9.35

2.Join In IM, with BF disabled

17

45.18

2.Join In Buffer Cache

162214

52.11

從測試結果可看到，Oracle IM option透過將資料作列式壓縮儲存在記憶體中，並啟用bloom filter後，連線查詢的效率得到了極大的提升。

關於Bloom Filter

Bloom filter 是由 Howard Bloom 在 1970 年提出的一種計算方法，用於檢測一個元素是不是集合中的一個成員。Oracle從10g開始引入該演算法。其基本思想就是用一個或多個hash函式對資料集A中的每個成員做hash計算，計算結果對映到一個位向量（bit vector）中。位向量所有位初始值都為0，根據hash結果將位向量中相應位置1。對集合A中的所有成員的hash計算完成後，就得到了該資料集的位向量。當需要判斷集合B中的元素是否屬於該資料集時，也用相同的hash函式對其對映得到它的位向量，然後將其位向量上所有為1的位與資料集位向量上相應位比較，如果發現資料集的位向量上某個位為0的話，可以判斷這個元素不屬於該資料集，從而將這些元素排除出去。而如果所有相應位都為1的話，那麼該元素可能屬於這個資料集A，也可能不屬於。也就是說Bloom Filter計算後的結果還不是最終的結果，它能幫助快速排除那些不符合條件的記錄，接下來還需要使用其他連線方法來保證最終結果的正確性。

以前面測試結果中的執行計劃為例，它首先訪問表small_table,建立bloom filter :BF0000,接下來使用該filter對big_table作過濾操作，由於Bloom Filter演算法自身的限制，最終還需要使用hash join來保證執行結果的正確性。 

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    24 |  2632  (35)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |    24 |            |          |
|*  2 |   HASH JOIN                   |             | 10012 |   234K|  2632  (35)| 00:00:01 |
|   3 |    JOIN FILTER CREATE         | :BF0000     | 10000 |   175K|   114  (54)| 00:00:01 |
|*  4 |     TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 |   175K|   114  (54)| 00:00:01 |
|   5 |    JOIN FILTER USE            | :BF0000     |    50M|   286M|  2115  (21)| 00:00:01 |
|*  6 |     TABLE ACCESS INMEMORY FULL| BIG_TABLE   |    50M|   286M|  2115  (21)| 00:00:01 |
---------------------------------------------------------------------------------------------

 

In Memory 聚合(aggregation)查詢：Vector Group By

Oracle在12.1.0.2版本中引入了一種新的查詢轉換技術，稱為Vector Group By, 據稱這種演算法可以更高效的使用CPU資源。這種轉換多應用於資料倉儲型別應用的分析查詢中，這裡就以資料倉儲應用中的查詢場景來介紹這種查詢轉換技術的實現：

這種查詢轉換可分為兩個階段：

Phase 1

1． 掃描“維度表”（dimension tables），也就是小表，根據掃描結果在連線列上建立key vectors，也就是一個一維陣列；

2． 根據key vectors的結果，以及原有的維度表掃描結果，建立一個新的稱為IM Accumulator的資料結構。IM Accumulator是一個多維陣列，存放在PGA中。其作用是使得在掃描“事實表”(fact table)期間就可以作聚合或group by計算，而不用等到所有的結果都返回以後；

3． 將“維度表”中涉及到的select列的結果存放在一個臨時表中。

Phase 2

4． 掃描“事實表”和前面生成的key vectors，利用key vectors來過濾“事實表”中的記錄，將匹配連線條件的結果放到前面的IM Accumulator中。如果該值已存在，則其對應的統計值就會被更新；

5． 最後，對“事實表”的掃描結果會與第3步中的臨時表作join，生成最終的結果。

既然說這種演算法能夠更有效地使用CPU資源，這裡就透過測試觀察資料庫中的CPU used by this session統計指標的變化來驗證這一說法。

Oracle在Sample Schema示例模式中有類似於資料倉儲應用的測試資料。這裡的測試思路如下：

在SH.SALES等表上作如下查詢，該查詢用以檢索出從1999年12月至2000年2月間Florida州所有城市直銷形式的每月銷售額。 

SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount 
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch 
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'FL' AND ch.channel_desc = 'Direct Sales' AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') 
GROUP BY c.cust_city, t.calendar_quarter_desc;

測試的場景分別為：

1.  常規執行；

2.  使用hint /*+ VECTOR_TRANSFORM */來啟用Vector Group By轉換；

3.  將查詢表載入In Memory區，並用hint /*+ VECTOR_TRANSFORM */啟用Vector Group By轉換

他們的執行計劃分別為：

1． 

-----------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Rows  |Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |   607 |  490   (2)|
|   1 |  HASH GROUP BY                          |                   |   607 |  490   (2)|
|   2 |   NESTED LOOPS                          |                   |   812 |  489   (2)|
|   3 |    NESTED LOOPS                         |                   | 14975 |  489   (2)|
|   4 |     VIEW                                | VW_GBC_13         | 14975 |  487   (1)|
|   5 |      HASH GROUP BY                      |                   | 14975 |  487   (1)|
|   6 |       NESTED LOOPS                      |                   | 43094 |  484   (1)|
|   7 |        NESTED LOOPS                     |                   | 43094 |  484   (1)|
|   8 |         MERGE JOIN CARTESIAN            |                   |   274 |   21   (0)|
|*  9 |          TABLE ACCESS FULL              | CHANNELS          |     1 |    3   (0)|
|  10 |          BUFFER SORT                    |                   |   274 |   18   (0)|
|* 11 |           TABLE ACCESS FULL             | TIMES             |   274 |   18   (0)|
|  12 |         PARTITION RANGE ITERATOR        |                   |       |           |
|  13 |          BITMAP CONVERSION TO ROWIDS    |                   |       |           |
|  14 |           BITMAP AND                    |                   |       |           |
|* 15 |            BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |       |           |
|* 16 |            BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |       |           |
|  17 |        TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |   157 |  484   (1)|
|* 18 |     INDEX UNIQUE SCAN                   | CUSTOMERS_PK      |     1 |    0   (0)|
|* 19 |    TABLE ACCESS BY INDEX ROWID          | CUSTOMERS         |     1 |    0   (0)|
----------------------------------------------------------------------------------------- 

2．  

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Rows  |Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |  6130 |  972   (4)|
|   1 |  TEMP TABLE TRANSFORMATION         |                           |       |           |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6677_381357 |       |           |
|   3 |    VECTOR GROUP BY                 |                           |    20 |   19   (6)|
|   4 |     KEY VECTOR CREATE BUFFERED     | :KV0000                   |       |           |
|*  5 |      TABLE ACCESS FULL             | TIMES                     |   274 |   18   (0)|
|   6 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6678_381357 |       |           |
|   7 |    VECTOR GROUP BY                 |                           |   613 |  426   (1)|
|   8 |     KEY VECTOR CREATE BUFFERED     | :KV0001                   |       |           |
|*  9 |      TABLE ACCESS FULL             | CUSTOMERS                 |  2734 |  425   (1)|
|  10 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6679_381357 |       |           |
|  11 |    VECTOR GROUP BY                 |                           |     1 |    4  (25)|
|  12 |     HASH GROUP BY                  |                           |     1 |    4  (25)|
|  13 |      KEY VECTOR CREATE BUFFERED    | :KV0002                   |       |           |
|* 14 |       TABLE ACCESS FULL            | CHANNELS                  |     1 |    3   (0)|
|  15 |   HASH GROUP BY                    |                           |  6130 |  523   (5)|
|* 16 |    HASH JOIN                       |                           |  6130 |  522   (5)|
|  17 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6678_381357 |   613 |    3   (0)|
|* 18 |     HASH JOIN                      |                           |  6130 |  518   (5)|
|  19 |      MERGE JOIN CARTESIAN          |                           |    20 |    4   (0)|
|  20 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6679_381357 |     1 |    2   (0)|
|  21 |       BUFFER SORT                  |                           |    20 |    2   (0)|
|  22 |        TABLE ACCESS FULL           | SYS_TEMP_0FD9D6677_381357 |    20 |    2   (0)|
|  23 |      VIEW                          | VW_VT_0737CF93            |  6130 |  514   (5)|
|  24 |       VECTOR GROUP BY              |                           |  6130 |  514   (5)|
|  25 |        HASH GROUP BY               |                           |  6130 |  514   (5)|
|  26 |         KEY VECTOR USE             | :KV0001                   | 16697 |  514   (5)|
|  27 |          KEY VECTOR USE            | :KV0002                   | 43110 |  514   (5)|
|  28 |           KEY VECTOR USE           | :KV0000                   |   172K|  514   (5)|
|  29 |            PARTITION RANGE SUBQUERY|                           |   918K|  513   (5)|
|* 30 |             TABLE ACCESS FULL      | SALES                     |   918K|  513   (5)|
-------------------------------------------------------------------------------------------- 

3． 

-----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      | Rows  |Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |  6130 |  313  (14)|
|   1 |  TEMP TABLE TRANSFORMATION            |                           |       |           |
|   2 |   LOAD AS SELECT                      | SYS_TEMP_0FD9D667A_381357 |       |           |
|   3 |    VECTOR GROUP BY                    |                           |    20 |    5  (20)|
|   4 |     KEY VECTOR CREATE BUFFERED        | :KV0000                   |       |           |
|*  5 |      TABLE ACCESS INMEMORY FULL       | TIMES                     |   274 |    4   (0)|
|   6 |   LOAD AS SELECT                      | SYS_TEMP_0FD9D667B_381357 |       |           |
|   7 |    VECTOR GROUP BY                    |                           |   613 |   30  (10)|
|   8 |     KEY VECTOR CREATE BUFFERED        | :KV0001                   |       |           |
|*  9 |      TABLE ACCESS INMEMORY FULL       | CUSTOMERS                 |  2734 |   29   (7)|
|  10 |   LOAD AS SELECT                      | SYS_TEMP_0FD9D667C_381357 |       |           |
|  11 |    VECTOR GROUP BY                    |                           |     1 |    2  (50)|
|  12 |     HASH GROUP BY                     |                           |     1 |    2  (50)|
|  13 |      KEY VECTOR CREATE BUFFERED       | :KV0002                   |       |           |
|* 14 |       TABLE ACCESS INMEMORY FULL      | CHANNELS                  |     1 |    1   (0)|
|  15 |   HASH GROUP BY                       |                           |  6130 |  277  (13)|
|* 16 |    HASH JOIN                          |                           |  6130 |  275  (13)|
|  17 |     TABLE ACCESS FULL                 | SYS_TEMP_0FD9D667B_381357 |   613 |    3   (0)|
|* 18 |     HASH JOIN                         |                           |  6130 |  272  (13)|
|  19 |      MERGE JOIN CARTESIAN             |                           |    20 |    4   (0)|
|  20 |       TABLE ACCESS FULL               | SYS_TEMP_0FD9D667C_381357 |     1 |    2   (0)|
|  21 |       BUFFER SORT                     |                           |    20 |    2   (0)|
|  22 |        TABLE ACCESS FULL              | SYS_TEMP_0FD9D667A_381357 |    20 |    2   (0)|
|  23 |      VIEW                             | VW_VT_0737CF93            |  6130 |  268  (13)|
|  24 |       VECTOR GROUP BY                 |                           |  6130 |  268  (13)|
|  25 |        HASH GROUP BY                  |                           |  6130 |  268  (13)|
|  26 |         KEY VECTOR USE                | :KV0001                   | 16697 |  268  (13)|
|  27 |          KEY VECTOR USE               | :KV0002                   | 43110 |  268  (13)|
|  28 |           KEY VECTOR USE              | :KV0000                   |   172K|  268  (13)|
|  29 |            PARTITION RANGE SUBQUERY   |                           |   918K|  267  (13)|
|* 30 |             TABLE ACCESS INMEMORY FULL| SALES                     |   918K|  267  (13)|
-----------------------------------------------------------------------------------------------

以上執行計劃中，藍色部分即是前面描述的Vector Group By轉換中的Phase 1，紅色部分即為Phase 2.

測試結果如下表，可以看到，將資料載入IM區並啟用Vector Group By轉換後，CPU資源使用量急劇下降，可見這種Vector Group By轉換查詢尤其適用於IM列式儲存的資料。統計結果中還有一個有趣的發現，那就是啟用Vector Group By後，DB Block Gets和Physical Reads統計指標都是非0值，跟蹤發現Physical Reads是來自於對臨時檔案的讀取，因為Phase 1的結果是要寫到臨時檔案中的。而DB Block Gets則猜測是來自於對Key Vector的current mode讀取。

測試場景

CPU Time

Consistent Gets

DB Block Gets

Physical Reads

常規執行

118

11884

0

0

啟用Vector Group By轉換

38

1859

24

3

In Memory，並啟用Vector Group By轉換 

9

31

24

3

 其實針對這樣的查詢，在Vector Group By之外，Oracle還有其他的轉換技術，那就是star transformation, 這是在8i時期就引入的技術，孰優孰略，可能就要具體問題具體分析了，這裡沒有作進一步比較。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27243841/viewspace-1271299/，如需轉載，請註明出處，否則將追究法律責任。

相關文章