In-Memory on Oracle 12c (조인편)


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%’)


7 – 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”)))

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


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다