[Oracle] 12c In-Memory 기능 테스트

 

 

 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에 적재 (채우기) 가 끝나면 쿼리의 변경 없이 데이타의 검색 속도의 향상이 있음을 확인 하였습니다

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


TAGS.

Comments