유니원아이앤씨 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에 적재 (채우기) 가 끝나면 쿼리의 변경 없이 데이타의 검색 속도의 향상이 있음을 확인 하였습니다.
테스트 환경은 데이타는 적은 데이타이기 때문에 큰 차이를 보이기 어렵지만 수시간 수행 되는 배치의 경우 에는 더 큰 단축 시간 절감을 예상 할 수 있습니다.