In-Memory Join & Aggregation
이번 섹션에서는 In-Memory Column Store에서의 조인과 집계처리에 대해 알아보겠습니다.
Bloom Filter 사례
먼저 간단한 조인/집계 쿼리의 IM Column Store 사용 여부에 따라 성능이 어떻게 차이 나는 지 확인해 봅니다.
| [oracle@New-Features-12c ~]$ sqlplus rtl/rtl@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 09:36:00 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Fri Aug 28 2015 09:14:53 +00:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL> set timing on SQL> select sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p where li.sku_item_key = p.sku_item_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’; SUM(LI.EXTENDED_AMT) ——————– 8342.11 Elapsed: 00:00:00.15 SQL> alter session set inmemory_query=disable; Session altered. Elapsed: 00:00:00.00 SQL> select sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p where li.sku_item_key = p.sku_item_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’; SUM(LI.EXTENDED_AMT) ——————– 8342.11 Elapsed: 00:00:08.40 SQL> alter session set inmemory_query=enable; Session altered. Elapsed: 00:00:00.00 |
실행계획을 살펴 보도록 하겠습니다. 실행계획과 Predicate 정보에서 나타나듯이 IM Column Store에서도 bloom filter가 사용되어 hash join의 부담을 줄여 줄 수 있습니다. 아래 filter가 생성되어 큰 테이블 스캔시, 앞서 생성한 flter를 적용하는 것을 볼 수 있습니다. Predicate Information에서도 Bloom Filter 사용을 확인할 수 있습니다.
| SQL> set pages 0 SQL> set lines 200 SQL> set timing on SQL> set echo on SQL> select sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p where li.sku_item_key = p.sku_item_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’; 8342.11 Elapsed: 00:00:00.10 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID 5q19hd33udc14, child number 0 ————————————- select sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwr_sku_item p where li.sku_item_key = p.sku_item_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’ Plan hash value: 117935668 —————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | —————————————————————————————————————————— | 0 | SELECT STATEMENT | | | | 58144 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 42 | | | | | |* 2 | HASH JOIN | | 615 | 25830 | 58144 (1)| 00:00:03 | | | | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 26 | 30 (4)| 00:00:01 | | | |* 4 | TABLE ACCESS INMEMORY FULL | DWR_SKU_ITEM | 1 | 26 | 30 (4)| 00:00:01 | | | | 5 | JOIN FILTER USE | :BF0000 | 27M| 418M| 58045 (1)| 00:00:03 | | | | 6 | PARTITION RANGE ALL | | 27M| 418M| 58045 (1)| 00:00:03 | 1 |1048575| |* 7 | TABLE ACCESS INMEMORY FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 27M| 418M| 58045 (1)| 00:00:03 | 1 |1048575| —————————————————————————————————————————— Predicate Information (identified by operation id): ————————————————— 2 – access(“LI”.”SKU_ITEM_KEY”=”P”.”SKU_ITEM_KEY”) 4 – inmemory(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’) filter(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’)
filter((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”))) 30 rows selected. Elapsed: 00:00:00.05 SQL> set echo off |
이번에는 조금 더 복잡한 조인/집계 쿼리의 In-Memory Coulumn Store 사용 여부에 따른 성능 차이를 확인해 보겠습니다.
| SQL> select p.sku_item_desc, sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwb_rtl_trx t, dwr_sku_item p, dwr_org_bsns_unit s where li. trx_nbr = t. trx_nbr and li.day_key = t.day_key and li.sku_item_key = p.sku_item_key and t. bsns_unit_key = s.org_bsns_unit_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’ and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’) group by p. sku_item_desc; Diet Pepsi Free Nr 130.2 Diet Pepsi Cf . 56.73 Diet Pepsi Nr 70.7 Diet Pepsi 74.31 Diet Pepsi Free 12pk 79.74 Diet Pepsi 24 Pk 14 Diet Pepsi Jr 12 Oz 6 Pk 82.16 Diet Pepsi Cola 6 Pack 103.2 Diet Pepsi Twist 102.34 Diet Pepsi Twist Fm 12z 12pk 150.15 Diet Pepsi Vanilla Cube 70.47 Diet Pepsi . 156 Diet Pepsi Wild Cherry 2 Lt 121.8 13 rows selected. Elapsed: 00:00:00.17 SQL> alter session set inmemory_query=disable; Session altered. Elapsed: 00:00:00.00 SQL> select p.sku_item_desc, sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwb_rtl_trx t, dwr_sku_item p, dwr_org_bsns_unit s where li. trx_nbr = t. trx_nbr and li.day_key = t.day_key and li.sku_item_key = p.sku_item_key and t. bsns_unit_key = s.org_bsns_unit_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’ and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’) group by p. sku_item_desc; Diet Pepsi Free Nr 130.2 Diet Pepsi Cf . 56.73 Diet Pepsi Nr 70.7 Diet Pepsi 74.31 Diet Pepsi Free 12pk 79.74 Diet Pepsi 24 Pk 14 Diet Pepsi Jr 12 Oz 6 Pk 82.16 Diet Pepsi Cola 6 Pack 103.2 Diet Pepsi Twist 102.34 Diet Pepsi Twist Fm 12z 12pk 150.15 Diet Pepsi Vanilla Cube 70.47 Diet Pepsi . 156 Diet Pepsi Wild Cherry 2 Lt 121.8 13 rows selected. Elapsed: 00:00:52.44 SQL> alter session set inmemory_query=enable; Session altered. Elapsed: 00:00:00.00 |
실행 계획을 확인해 보겠습니다. 앞서 살펴 보았던 단순 쿼리에서 뿐만 아니라 좀 더 복잡한 쿼리에서도 bloom filter 가 사용되는 것을 볼 수 있습니다. DWB_RTL_SLS_RETRN_LINE_ITEM 테이블 스캔 시, 그리고 DWB_RT_TRX 테이블을 스캔하면서 bloom filter가 사용되고 있습니다.
| SQL> select p.sku_item_desc, sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwb_rtl_trx t, dwr_sku_item p, dwr_org_bsns_unit s where li. trx_nbr = t. trx_nbr and li.day_key = t.day_key and li.sku_item_key = p.sku_item_key and t. bsns_unit_key = s.org_bsns_unit_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’ and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’) group by p. sku_item_desc; Diet Pepsi Free Nr 130.2 Diet Pepsi Cf . 56.73 Diet Pepsi Nr 70.7 Diet Pepsi 74.31 Diet Pepsi Free 12pk 79.74 Diet Pepsi 24 Pk 14 Diet Pepsi Jr 12 Oz 6 Pk 82.16 Diet Pepsi Cola 6 Pack 103.2 Diet Pepsi Twist 102.34 Diet Pepsi Twist Fm 12z 12pk 150.15 Diet Pepsi Vanilla Cube 70.47 Diet Pepsi . 156 Diet Pepsi Wild Cherry 2 Lt 121.8 13 rows selected. Elapsed: 00:00:00.12 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID 2zbw1j6qby9zh, child number 1 ————————————- select p.sku_item_desc, sum(li.extended_amt) from dwb_rtl_sls_retrn_line_item li, dwb_rtl_trx t, dwr_sku_item p, dwr_org_bsns_unit s where li. trx_nbr = t. trx_nbr and li.day_key = t.day_key and li.sku_item_key = p.sku_item_key and t. bsns_unit_key = s.org_bsns_unit_key and li.actn_cd = ‘Sale’ and p.sku_item_desc like ‘Diet Pepsi%’ and s.state in (‘AZ’, ‘CA’, ‘NM’, ‘TX’) group by p. sku_item_desc Plan hash value: 2395958102 ———————————————————————————————————————————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ———————————————————————————————————————————- | 0 | SELECT STATEMENT | | | | 61783 (100)| | | | | 1 | HASH GROUP BY | | 13 | 1534 | 61783 (1)| 00:00:03 | | | |* 2 | HASH JOIN | | 582 | 68676 | 61781 (1)| 00:00:03 | | | |* 3 | TABLE ACCESS INMEMORY FULL | DWR_ORG_BSNS_UNIT | 198 | 1980 | 4 (0)| 00:00:01 | | | |* 4 | HASH JOIN | | 3952 | 416K| 61777 (1)| 00:00:03 | | | | 5 | JOIN FILTER CREATE | :BF0001 | 11925 | 838K| 58144 (1)| 00:00:03 | | | | 6 | PART JOIN FILTER CREATE | :BF0000 | 11925 | 838K| 58144 (1)| 00:00:03 | | | |* 7 | HASH JOIN | | 11925 | 838K| 58144 (1)| 00:00:03 | | | | 8 | JOIN FILTER CREATE | :BF0002 | 24 | 624 | 30 (4)| 00:00:01 | | | |* 9 | TABLE ACCESS INMEMORY FULL | DWR_SKU_ITEM | 24 | 624 | 30 (4)| 00:00:01 | | | | 10 | JOIN FILTER USE | :BF0002 | 27M| 1204M| 58045 (1)| 00:00:03 | | | | 11 | PARTITION RANGE ALL | | 27M| 1204M| 58045 (1)| 00:00:03 | 1 |1048575| |* 12 | TABLE ACCESS INMEMORY FULL| DWB_RTL_SLS_RETRN_LINE_ITEM | 27M| 1204M| 58045 (1)| 00:00:03 | 1 |1048575| | 13 | JOIN FILTER USE | :BF0001 | 1834K| 62M| 3629 (1)| 00:00:01 | | | | 14 | PARTITION RANGE JOIN-FILTER | | 1834K| 62M| 3629 (1)| 00:00:01 |:BF0000|:BF0000| |* 15 | TABLE ACCESS INMEMORY FULL | DWB_RTL_TRX | 1834K| 62M| 3629 (1)| 00:00:01 |:BF0000|:BF0000| ———————————————————————————————————————————- Predicate Information (identified by operation id): ————————————————— 2 – access(“T”.”BSNS_UNIT_KEY”=”S”.”ORG_BSNS_UNIT_KEY”) 3 – inmemory((“S”.”STATE”=’AZ’ OR “S”.”STATE”=’CA’ OR “S”.”STATE”=’NM’ OR “S”.”STATE”=’TX’)) filter((“S”.”STATE”=’AZ’ OR “S”.”STATE”=’CA’ OR “S”.”STATE”=’NM’ OR “S”.”STATE”=’TX’)) 4 – access(“LI”.”DAY_KEY”=”T”.”DAY_KEY” AND “LI”.”TRX_NBR”=”T”.”TRX_NBR”) 7 – access(“LI”.”SKU_ITEM_KEY”=”P”.”SKU_ITEM_KEY”) 9 – inmemory(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’) filter(“P”.”SKU_ITEM_DESC” LIKE ‘Diet Pepsi%’) 12 – inmemory((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”))) filter((“LI”.”ACTN_CD”=’Sale’ AND SYS_OP_BLOOM_FILTER(:BF0000,”LI”.”SKU_ITEM_KEY”))) 15 – inmemory(SYS_OP_BLOOM_FILTER(:BF0000,”T”.”TRX_NBR”)) filter(SYS_OP_BLOOM_FILTER(:BF0000,”T”.”TRX_NBR”)) Note —– – dynamic statistics used: dynamic sampling (level=2) – 2 Sql Plan Directives used for this statement 53 rows selected. Elapsed: 00:00:00.05 |