Oracle 12c의 In-Memory 기능 테스트


유니원아이앤씨 DB기술팀 의 박용석 수석 입니다.

오늘은 Oracle 12c의 In-Memory 기능 테스트를 해보겠습니다.

먼저 테스트 할 테이블 정보입니다.

=======================

====== TABLE SIZE =====

=======================

OWNER           SEGMENT_NAME              PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME      TSIZE

————— ————————- —————————— —————— ————— ———-

BILLING         USERINFO                                                 TABLE              BILLING               1.31

                *************************                                                                   ———-

                sum                                                                                               1.31

***************                                                                                             ———-

sum                                                                                                               1.31

INDEX_OWNER

——————————————————————————————————————————–

INDEX_NAME                                                                                                                       COLUMN_NAME          COLUMN_POSITION

——————————————————————————————————————————– ——————– —————

BILLING

IDX_USERINFO_EMAIL                                                                                                               EMAIL                              1

IDX_USERINFO_MOBILE                                                                                                              MOBILE                             1

IDX_USERINFO_NAME                                                                                                                USERNAME                           1

IDX_USERINFO_REGDATE                                                                                                             REGDATE                            1

IDX_USERINFO_USERID                                                                                                              USERID                             1

UDX_USERINFO_ACCOUNTNUM                                                                                                          ACCOUNTNUM                         1

UDX_USERINFO_DUPID                                                                                                               USERID                             1

                                                                                                                                 GROUPNUM                           2

                                                                                                                                 CP_FLAG                            3

9 rows selected.

Full Table Scan 하는 쿼리를 수행 합니다.

14:07:05 SQL>  alter system flush  BUFFER_CACHE ;                                     

System altered.

Elapsed: 00:00:00.17

14:07:09 SQL> alter system flush  BUFFER_POOL ALL ;

System altered.

Elapsed: 00:00:00.12

14:07:23 SQL>  select SEX,sms_send,count(1) from userinfo where regdate < to_date(‘2012/07/24 00:00:00′,’YYYY/MM/DD HH24:MI:SS’) group by sex,sms_send ;

S S   COUNT(1)

– – ———-

2 N    2064953

1 N    4654692

Elapsed: 00:00:05.98

수행 쿼리의 PLAN 과 STATISTIC 정보를 확인 합니다.

PLAN_TABLE_OUTPUT

——————————————————————————————————————————————————————————————–

SQL_ID  8u2yphb2spzbd, child number 0

————————————-

select SEX,sms_send,count(1) from userinfo where regdate <

to_date(‘2012/07/24 00:00:00′,’YYYY/MM/DD HH24:MI:SS’) group by

sex,sms_send

Plan hash value: 2500223777

—————————————————————————————————————————–

| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

—————————————————————————————————————————–

|   0 | SELECT STATEMENT   |          |      1 |        |      2 |00:00:05.86 |     165K|    165K|       |       |          |

|   1 |  HASH GROUP BY     |          |      1 |      3 |      2 |00:00:05.86 |     165K|    165K|  1520K|  1520K|  618K (0)|

|*  2 |   TABLE ACCESS FULL| USERINFO |      1 |   7644K|   6719K|00:00:05.01 |     165K|    165K|       |       |          |

—————————————————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   2 – filter(“REGDATE”<TO_DATE(‘ 2012-07-24 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

21 rows selected.

no rows selected

   elapsed       disk      query    current       rows

———- ———- ———- ———- ———-

  5.859295     165883     165890          0          2

      Row Row_Source_Operation

——— ————————————————————————————————————————

        2 SELECT STATEMENT   (cr=165890 pr=165883 pw=0 time=5859295)

        2  HASH GROUP BY  (cr=165890 pr=165883 pw=0 time=5859295)

  6719645   TABLE ACCESS FULL USERINFO (cr=165890 pr=165883 pw=0 time=5013518)

이번에는 IN_MEMORY 테스트를 위해 영역을 할당합니다.

[oracle@unioda1 ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 10:28:41 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter inmemory

NAME                                 TYPE        VALUE

———————————— ———– ——————————

inmemory_clause_default              string

inmemory_force                       string      DEFAULT

inmemory_max_populate_servers        integer     0

inmemory_query                       string      ENABLE

inmemory_size                        big integer 0

inmemory_trickle_repopulate_servers_ integer     1

percent

optimizer_inmemory_aware             boolean     TRUE

SQL> set lines 100

SQL> col name for a40

SQL> col value for 999999999999999

SQL> select name,value from v$sga;

NAME                                                VALUE

—————————————- —————-

Fixed Size                                        2944952

Variable Size                                  2315255880

Database Buffers                               6241124352

Redo Buffers                                     30609408

SQL> alter system set inmemory_size=2g scope=spfile sid=’*’ ;

System altered.

SQL> alter system set inmemory_clause_default = “memcompress for query high”

  2  scope=both sid=’*’ ;

System altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

DB 를 재기동 합니다.

[oracle@unioda1 ~]$ srvctl stop database -d uniodadb

[oracle@unioda1 ~]$ srvctl start database -d uniodadb

영역이 잡혔는지 확인 합니다.

[oracle@unioda1 ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 10:36:43 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter inmemory

NAME                                 TYPE        VALUE

———————————— ———– ——————————

inmemory_clause_default              string      memcompress for query high

inmemory_force                       string      DEFAULT

inmemory_max_populate_servers        integer     1

inmemory_query                       string      ENABLE

inmemory_size                        big integer 2G

inmemory_trickle_repopulate_servers_ integer     1

percent

optimizer_inmemory_aware             boolean     TRUE

SQL> set lines 100

SQL> col name for a40

SQL> col value for 999999999999999

SQL> select name,value from v$sga;

NAME                                                VALUE

—————————————- —————-

Fixed Size                                        2944952

Variable Size                                  1744830536

Database Buffers                               4664066048

Redo Buffers                                     30609408

In-Memory Area                                 2147483648

이제 대상 테스트 테이블을 In-Memory 영역으로 옮기는 작업을 합니다.

13:59:32 SQL>  alter system flush  BUFFER_CACHE ;                                     

System altered.

Elapsed: 00:00:00.44

14:00:04 SQL>  alter system flush  BUFFER_POOL ALL ;

System altered.

Elapsed: 00:00:00.11

14:00:11 SQL> alter table userinfo inmemory memcompress for query high priority high;

Table altered.

Elapsed: 00:00:00.40

14:00:24 SQL> conn /as sysdba

Connected.

14:00:28 SQL> execute dbms_inmemory.populate(‘BILLING’,’USERINFO’) ;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

In-Memory 영역으로 테스트 테이블이 잘 옮겨 졌는지 조회 합니다.

14:04:32 SQL> select owner, segment_name, bytes, inmemory_size, populate_status,

14:05:11   2  bytes_not_populated

14:05:11   3  from v$im_segments

14:05:11   4  where segment_name=’USERINFO’ ;

OWNER           SEGMENT_NAME                   BYTES INMEMORY_SIZE POPULATE_ BYTES_NOT_POPULATED

————— ————————- ———- ————- ——— ——————-

BILLING         USERINFO                  1409286144     624558080 COMPLETED           507396096

Elapsed: 00:00:00.11

14:05:36 SQL> select a.object_name,

14:05:43   2  b.inmemory_priority,

14:05:43   3  b.populate_status,

14:05:43   4  to_char(c.createtime,’mm/dd/yyyy hh24:mi:ss.ff2′) start_pop,

14:05:43   5  to_char(max(d.timestamp),’mm/dd/yyyy hh24:mi:ss.ff2′) finish_pop

14:05:43   6  from dba_objects a,

14:05:43   7  v$im_segments b,

14:05:43   8  v$im_segments_detail c,

14:05:43   9  v$im_header d

14:05:43  10  where object_name in (‘USERINFO’)

14:05:43  11  and a.object_name = b.segment_name

14:05:43  12  and a.object_type = ‘TABLE’

14:05:43  13  and a.object_id = c.baseobj

14:05:43  14  and c.dataobj = d.objd

14:05:43  15  group by a.object_name, b.inmemory_priority, b.populate_status,c.createtime

14:05:43  16   order by finish_pop;

OBJECT_NAME     INMEMORY POPULATE_ START_POP              FINISH_POP

————— ——– ——— ———————- ———————-

USERINFO        HIGH     COMPLETED 10/24/2017 14:04:39.24 10/24/2017 14:05:21.81

Elapsed: 00:00:00.01

14:05:44 SQL>

잘 옮겨 졌는지 확인 후 같은 쿼리를 다시 수행 합니다.

14:06:17 SQL>  select SEX,sms_send,count(1) from userinfo where regdate < to_date(‘2012/07/24 00:00:00′,’YYYY/MM/DD HH24:MI:SS’) group by sex,sms_send ;

S S   COUNT(1)

– – ———-

2 N    2064953

1 N    4654692

Elapsed: 00:00:05.65

PLAN_TABLE_OUTPUT

———————————————————————————————————————————————————————————————

SQL_ID  8u2yphb2spzbd, child number 0

————————————-

select SEX,sms_send,count(1) from userinfo where regdate <

to_date(‘2012/07/24 00:00:00′,’YYYY/MM/DD HH24:MI:SS’) group by

sex,sms_send

Plan hash value: 2500223777

————————————————————————————————————————————–

| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

————————————————————————————————————————————–

|   0 | SELECT STATEMENT            |          |      1 |        |      2 |00:00:05.58 |   63221 |  63218 |       |       |          |

|   1 |  HASH GROUP BY              |          |      1 |      3 |      2 |00:00:05.58 |   63221 |  63218 |  1520K|  1520K|  610K (0)|

|*  2 |   TABLE ACCESS INMEMORY FULL| USERINFO |      1 |   7644K|   6719K|00:00:04.66 |   63221 |  63218 |       |       |          |

————————————————————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   2 – inmemory(“REGDATE”<TO_DATE(‘ 2012-07-24 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

       filter(“REGDATE”<TO_DATE(‘ 2012-07-24 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

22 rows selected.

no rows selected

   elapsed       disk      query    current       rows

———- ———- ———- ———- ———-

  5.576106      63218      63221          0          2

      Row Row_Source_Operation

——— ————————————————————————————————————————

        2 SELECT STATEMENT   (cr=63221 pr=63218 pw=0 time=5576106)

        2  HASH GROUP BY  (cr=63221 pr=63218 pw=0 time=5576106)

  6719645   TABLE ACCESS
INMEMORY
FULL
USERINFO (cr=63221 pr=63218 pw=0 time=4661101)

SQL>

같은 쿼리의 플랜이 TABLE ACCESS FULL 에서 TABLE ACCESS INMEMORY FULL
으로 변경 되었고,

elapsed 수행 시간이 5.859295 에서 5.576106 으로 단축 되었습니다.

또 다른 테스트를 해보겠습니다. userinfo 테이블 대신 TRANSACTIONS 테이블을 조회해 보겠습니다.

17:18:21 SQL> select to_char(REGDATE,’YYYY’) as Year , sum(CASHAMOUNT) from TRANSACTIONS group by to_char(REGDATE,’YYYY’) ;

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:09.07

수행 후 플랜 정보 입니다.

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————————————————————–

SQL_ID  4mjqpbqxby921, child number 0

————————————-

select to_char(REGDATE,’YYYY’) as Year , sum(CASHAMOUNT) from

TRANSACTIONS group by to_char(REGDATE,’YYYY’)

Plan hash value: 1427863798

———————————————————————————————————————————

| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

———————————————————————————————————————————

|   0 | SELECT STATEMENT   |              |      1 |        |      7 |00:00:09.00 |     179K|    179K|       |       |          |

|   1 |  HASH GROUP BY     |              |      1 |     14M|      7 |00:00:09.00 |     179K|    179K|  1186K|  1186K|  982K (0)|

|   2 |   TABLE ACCESS FULL| TRANSACTIONS |      1 |     17M|     17M|00:00:03.68 |     179K|    179K|       |       |          |

———————————————————————————————————————————

15 rows selected.

no rows selected

   elapsed       disk      query    current       rows

———- ———- ———- ———- ———-

  9.001912     179503     179510          0          7

      Row Row_Source_Operation

——— ————————————————————————————————————————

        7 SELECT STATEMENT   (cr=179510 pr=179503 pw=0 time=9001912)

        7  HASH GROUP BY  (cr=179510 pr=179503 pw=0 time=9001912)

17389554   TABLE ACCESS FULL TRANSACTIONS (cr=179510 pr=179503 pw=0 time=3682351)

이전에 In-Memory 에 등록한 userinfo 테이블을 영역에서 제외합니다.

17:19:10 SQL> alter table userinfo no inmemory ;

Table altered.

Elapsed: 00:00:00.31

transactions 테이블을 In-memory 영역에 등록하고 Population(Load) 시킵니다.

Population 이란 ??

Populate는 데이터를 IM 열 저장소로 가져 오는 데 사용되는 용어입니다. 

load는 일반적으로 새로운 데이터를 데이터베이스에 삽입하는 것을 의미하기 때문에 “load”대신 “populate”라는 용어를 사용합니다.

Populate 데이터베이스에 새 데이터를 가져 오지 않으며 기존 데이터를 메모리로 가져 와서 최적화 된 열 형식으로 형식을 지정합니다.

17:20:06 SQL> alter table TRANSACTIONS inmemory priority high;

Table altered.

Elapsed: 00:00:00.08

17:21:03 SQL> alter table transactions inmemory memcompress for query high priority high;

Table altered.

Elapsed: 00:00:00.30

17:22:02 SQL> conn /as sysdba

Connected.

17:22:09 SQL> execute dbms_inmemory.populate(‘BILLING’,’TRANSACTIONS’) ;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

그리고 다시 수행해 봅니다… 이번에는 반복적으로 수행 합니다.

17:22:20 SQL> conn billing

Enter password:

Connected.

17:22:26 SQL>

17:22:26 SQL>

17:22:27 SQL> select to_char(REGDATE,’YYYY’) as Year , sum(CASHAMOUNT) from TRANSACTIONS group by to_char(REGDATE,’YYYY’) ;

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:16.00

17:22:54 SQL>  select to_char(REGDATE,’YYYY’) as Year , sum(CASHAMOUNT) from TRANSACTIONS group by to_char(REGDATE,’YYYY’) ;

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:07.13

17:23:49 SQL> /

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:06.87

17:24:13 SQL> /

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:06.79

17:24:23 SQL> /

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:06.77

17:28:54 SQL> /

YEAR SUM(CASHAMOUNT)

—- —————

2009      9820469306

2010      9457840955

2008      1.4209E+10

2007      9733116098

2011      1.0298E+10

2012      9119469224

2006       581415840

7 rows selected.

Elapsed: 00:00:06.95

17:29:06 SQL>

플랜을 확인해 보겠습니다.

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID  63x3yk3wsf5rt, child number 0

————————————-

select to_char(REGDATE,’YYYY’) as Year , sum(CASHAMOUNT) from

TRANSACTIONS group by to_char(REGDATE,’YYYY’)

Plan hash value: 1427863798

———————————————————————————————————————————

| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

———————————————————————————————————————————

|   0 | SELECT STATEMENT            |              |      1 |        |      7 |00:00:06.95 |     114K|       |       |          |

|   1 |  HASH GROUP BY              |              |      1 |     14M|      7 |00:00:06.95 |     114K|  1186K|  1186K| 1000K (0)|

|   2 |   TABLE ACCESS INMEMORY FULL| TRANSACTIONS |      1 |     17M|     17M|00:00:01.97 |     114K|       |       |          |

———————————————————————————————————————————

15 rows selected.

no rows selected

   elapsed       disk      query    current       rows

———- ———- ———- ———- ———-

  6.946008          0     114607          0          7

      Row Row_Source_Operation

——— ————————————————————————————————————————

        7 SELECT STATEMENT   (cr=114607 pr=0 pw=0 time=6946008)

        7  HASH GROUP BY  (cr=114607 pr=0 pw=0 time=6946008)

17389554   TABLE ACCESS INMEMORY FULL TRANSACTIONS (cr=114607 pr=0 pw=0 time=1974611)

플랜은 INMEMORY 로 변경되었으며, 9.001912 초 걸리던 쿼리가 6.946008 로 수행 시간이 단축 되었습니다.

Oracle 12c에 나온 In-Memory 테스트를 마치며

 Population 즉 Memory에 적재 (채우기) 가 끝나면 쿼리의 변경 없이 데이타의 검색 속도의 향상이 있음을 확인 하였습니다. 

 테스트 환경은 데이타는 적은 데이타이기 때문에 큰 차이를 보이기 어렵지만 수시간 수행 되는 배치의 경우 에는 더 큰 단축 시간 절감을 예상 할 수 있습니다.


Comments

comments

haisins

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

댓글 남기기

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