본문 바로가기

Database/Oracle Database

처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #8. In-Memory 테스트 – RAC DB: In-Memory 설정 & Population

"처음 사용자를 위한 Oracle Database In-Memory 가이드" 시리즈를 통해 Swingbench의 분석 업무 스키마 ‘SH’ 데이터를 이용해서 오라클 21c Single 인스턴스 데이터베이스와 RAC 데이터베이스에서 간단한 In-memory 옵션 사용 방법을 살펴봅니다.

 

글 순서

#1. Introduction

#2. 테스트 환경 준비

#3. In-Memory Advisor

#4. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 설정 & Population

#5. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 수행 확인

#6. 참고. 칼럼 레벨 In-Memory 설정 및 In-Memory hybrid scan

#7. 참고. IM FastStart 수행 확인

#8. In-Memory 테스트 – RAC DB: In-Memory 설정 & Population

#9. In-Memory 테스트 – RAC DB: In-Memory 수행 확인

#10. In-Memory 테스트 – RAC DB: Auto DOP 적용 테스트

#11. In-Memory 테스트 – RAC DB: Distribute For Service 테스트

 

In-Memory 테스트 – RAC DB

참고 자료

Deploying IM Column Stores in Oracle RAC

Oracle Database In-Memory on RAC - Part I

Oracle Database In-Memory on RAC - Part 2

How to control where objects are populated into memory on RAC

 

In-Memory 설정 및 population

RAC CDB 접속해서 alter system 명령으로 inmemory_size 설정한다. 앞서 In-Memory Advsior에서는 3GB 정도의 권고치가 나왔으나, 이번 테스트에는 앞선 Single Instance DB 테스트에서 설정한 내용과 동일하게 11 GB RAC 양쪽 노드에 inmemory_size 설정했다. 실제 In-Memory 영역에 population 할때는 디폴트로 압축을 해서 올라가기 때문에 11GB 모두 소모하지는 않을 것이다. 특히 RAC에서는 distribution해서 population하기 때문에 소모되는 사이즈는 더욱 작을 것이다.

[root@inst-public scripts]# sqlplus sys/Xxxxxxxxxxxxx@racdb-scan.snpublic.cluster1.oraclevcn.com:1521/DB0622_iad3qc.snpublic.cluster1.oraclevcn.com as sysdba

 
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
 
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME    STATUS
---------------- ------------
DB06221          OPEN
DB06222          OPEN
 
SQL> alter system set inmemory_size=11g scope=spfile sid='*';
System altered.

 

In-Memory 설정 적용을 위해서는 데이터베이스 재기동이 필요하다. 여기서는 RAC 1 노드에 접속해서 srvctl 명령으로 재기동했다.

[oracle@racdb1 ~]$ hostname
racdb1
[oracle@racdb1 ~]$ whoami
oracle
[oracle@racdb1 ~]$ srvctl stop database -db DB0622_iad3qc
[oracle@racdb1 ~]$ srvctl start database -db DB0622_iad3qc
[oracle@racdb1 ~]$ srvctl status database -db DB0622_iad3qc
Instance DB06221 is running on node racdb1
Instance DB06222 is running on node racdb2

 

RAC CDB 접속해서 확인해 보면 Inmemory_area 파라미터에도 이제 앞서 할당한 11 GB 조회된다.

SQL> set lines 120
SQL> set pages 100
SQL> col name for a60
SQL> col value for a30
SQL> select inst_id,name,value from gv$parameter where name like 'inmemory%' order by 2;
 
   INST_ID NAME                                                         VALUE
---------- ------------------------------------------------------------ ------------------------------
         1 inmemory_adg_enabled                                         TRUE
         2 inmemory_adg_enabled                                         TRUE
         2 inmemory_automatic_level                                     OFF
         1 inmemory_automatic_level                                     OFF
         2 inmemory_clause_default
         1 inmemory_clause_default
         2 inmemory_deep_vectorization                                  TRUE
         1 inmemory_deep_vectorization                                  TRUE
         2 inmemory_expressions_usage                                   ENABLE
         1 inmemory_expressions_usage                                   ENABLE
         1 inmemory_force                                               DEFAULT
         2 inmemory_force                                               DEFAULT
         1 inmemory_max_populate_servers                                2
         2 inmemory_max_populate_servers                                2
         1 inmemory_optimized_arithmetic                                DISABLE
         2 inmemory_optimized_arithmetic                                DISABLE
         1 inmemory_prefer_xmem_memcompress
         2 inmemory_prefer_xmem_memcompress
         1 inmemory_prefer_xmem_priority
         2 inmemory_prefer_xmem_priority
         1 inmemory_query                                               ENABLE
         2 inmemory_query                                               ENABLE
         2 inmemory_size                                                11811160064
         1 inmemory_size                                                11811160064
         2 inmemory_trickle_repopulate_servers_percent                  1
         1 inmemory_trickle_repopulate_servers_percent                  1
         1 inmemory_virtual_columns                                     MANUAL
         2 inmemory_virtual_columns                                     MANUAL
         2 inmemory_xmem_size                                           0
         1 inmemory_xmem_size                                           0
 
30 rows selected.

 

gv$inmemory_area 딕셔너리 뷰를 조회해 보면, In-Memory Column Store pool 할당된 메모리, 그리고 사용 중인 메모리를 확인할 있다. 현재 RAC Multitenant 환경이기 때문에 양쪽 노드의 CDB, Seed PDB, PDB In-Memory pool 설정이 출력되고 있다.

SQL> select * from gv$inmemory_area order by inst_id,con_id;
 
   INST_ID POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
---------- -------------------------- ----------- ---------- -------------------------- ----------
         1 1MB POOL                    8248098816          0 DONE                                1
         1 64KB POOL                   3534487552          0 DONE                                1
         1 1MB POOL                    8248098816          0 DONE                                2
         1 64KB POOL                   3534487552          0 DONE                                2
         1 1MB POOL                    8248098816          0 DONE                                3
         1 64KB POOL                   3534487552          0 DONE                                3
         2 64KB POOL                   3534487552          0 DONE                                1
         2 1MB POOL                    8248098816          0 DONE                                1
         2 1MB POOL                    8248098816          0 DONE                                2
         2 64KB POOL                   3534487552          0 DONE                                2
         2 1MB POOL                    8248098816          0 DONE                                3
         2 64KB POOL                   3534487552          0 DONE                                3
 
12 rows selected.
 
SQL> exit
Disconnected from Oracle Database 21c EE Extreme Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

 

Swingbench 스키마 테이블들을 설정한 In-Memory Column Store population 한다. RAC PDB 접속해서 기존 SH 스키마 테이블들을 inmemory 속성으로 변경한다.

[oracle@singledb scripts]$ sqlplus sys/Xxxxxxxxxxxxx@racdb-scan.snpublic.cluster1.oraclevcn.com:1521/db0622_pdb1.snpublic.cluster1.oraclevcn.com as sysdba
...
 
SQL> show con_name
CON_NAME
------------------------------
DB0622_PDB1
 
SQL> alter table sh.CHANNELS inmemory;
Table altered.
 
SQL> alter table sh.COUNTRIES inmemory;
Table altered.
 
SQL> alter table sh.CUSTOMERS inmemory;
Table altered.
 
SQL> alter table sh.PROMOTIONS inmemory;
Table altered.
 
SQL> alter table sh.PRODUCTS inmemory;
Table altered.
 
SQL> alter table sh.SUPPLEMENTARY_DEMOGRAPHICS inmemory;
Table altered.
 
SQL> alter table sh.SALES inmemory;
Table altered.
 
SQL> alter table sh.TIMES inmemory;
Table altered.

 

SH 스키마 테이블들이 IM Column Store enable되어 있는 것을 있다. Population priority “NONE”이며 디폴트인 “FOR QUERY LOW” 레벨로 압축되어 있다.

SQL> set lines 120
SQL> col table_name for a30
SQL> select table_name, inmemory, inmemory_priority, inmemory_compression, inmemory_distribute, inmemory_duplicate from dba_tables where owner = 'SH';
 
TABLE_NAME                     INMEMORY INMEMORY INMEMORY_COMPRESS INMEMORY_DISTRI INMEMORY_DUPL
------------------------------ -------- -------- ----------------- --------------- -------------
CHANNELS                       ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
COUNTRIES                      ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
CUSTOMERS                      ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
PROMOTIONS                     ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
PRODUCTS                       ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
SUPPLEMENTARY_DEMOGRAPHICS     ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
SALES                          ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
TIMES                          ENABLED  NONE     FOR QUERY LOW     AUTO            NO DUPLICATE
 
8 rows selected.

 

테이블 속성 변경 , 여기서는 아래와 같이 dbms_inmemory.populate 프로시저로 수동으로 population 했다.

SQL> execute dbms_inmemory.populate('SH','CHANNELS');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','COUNTRIES');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','CUSTOMERS');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','PROMOTIONS');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','PRODUCTS');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','SUPPLEMENTARY_DEMOGRAPHICS');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','SALES');
PL/SQL procedure successfully completed.
 
SQL> execute dbms_inmemory.populate('SH','TIMES');
PL/SQL procedure successfully completed.

 

잠시 gv$im_segments 딕셔너리 뷰를 확인해 보면 inmemory population 완료된 것을 있다.

단, RAC 환경에서 gv$im_segments 뷰의 BYTES_NOT_POPULATED 칼럼 값은 해당 인스턴스의 값만을 나타내기 때문에 여러개 인스턴스에 걸쳐 distribution해서 population된 오브젝트의 경우, 오브젝트가 완전히 population 됐음에도 불구하고 BYTES_NOT_POPULATED 값이 0이 아닌 값으로 나오게 되는 것은 유의한다.

아래에서는 비교적 작은 테이블들인 CHANNELS, COUNTRIES, PRODUCTS, PROMOTIONS, TIMES distribution되지 않았고, 사이즈가 CUSTOMERS, SALES, SUPPLEMENTARY_DEMOGRAPHICS 테이블들은 두개 노드에 걸쳐 , distribution되어 있다. Distribution “AUTO” 되어 있다.

population , 전체 사이즈는 애초 In-Memory Advisor에서 추정한 3.159GB 보다 다소 많이 디폴트 압축 레벨에서는 공간을 차지하고 있다..

SQL> set lines 120
SQL> set pages 100
SQL> col segment_name for a30
SQL> select
             inst_id,
             decode(partition_name,null,segment_name,partition_name) as segment_name,
             bytes,
            (bytes - bytes_not_populated) as bytes_populated,
            bytes_not_populated,
            inmemory_size,
            populate_status
from gv$im_segments
order by segment_name, inst_id;
   INST_ID SEGMENT_NAME                        BYTES BYTES_POPULATED BYTES_NOT_POPULATED INMEMORY_SIZE POPULATE_STAT
---------- ------------------------------ ---------- --------------- ------------------- ------------- -------------
         1 CHANNELS                          1015808         1015808                   0       1310720 COMPLETED
         1 COUNTRIES                         1015808         1015808                   0       1310720 COMPLETED
         1 CUSTOMERS                      2839076864      1281949696          1557127168     336723968 COMPLETED
         2 CUSTOMERS                      2839076864      1557127168          1281949696     408616960 COMPLETED
         1 PRODUCTS                          1015808         1015808                   0       1310720 COMPLETED
         1 PROMOTIONS                        1015808         1015808                   0       1310720 COMPLETED
         1 SALES                          7578124288      3461095424          4117028864    1313210368 COMPLETED
         2 SALES                          7578124288      4117028864          3461095424    1541537792 COMPLETED
         1 SUPPLEMENTARY_DEMOGRAPHICS     1044717568       392724480           651993088     169410560 COMPLETED
         2 SUPPLEMENTARY_DEMOGRAPHICS     1044717568       651993088           392724480     297271296 COMPLETED
         2 TIMES                              483328          483328                   0       1310720 COMPLETED
 
11 rows selected.
 
 
SQL> select table_name,inmemory_distribute from dba_tables where owner='SH';
 
TABLE_NAME                     INMEMORY_DISTRI
------------------------------ ---------------
CHANNELS                       AUTO
COUNTRIES                      AUTO
CUSTOMERS                      AUTO
PROMOTIONS                     AUTO
PRODUCTS                       AUTO
SUPPLEMENTARY_DEMOGRAPHICS     AUTO
SALES                          AUTO
TIMES                          AUTO
 
SQL> select inst_id, round(sum(inmemory_size)/1024/1024/1024,3) as gb from gv$im_segments where owner = 'SH' group by inst_id;
 
   INST_ID         GB
---------- ----------
         1      1.699
         2      2.094

 

In-Memory Column Store population 소요된 시간은 아래와 같이 확인한다.

SQL> col object_name for a30
SQL> col diff for a30
SQL> select a.object_name,
                   b.inmemory_priority,
                   b.populate_status,
                   to_char(c.createtime,'hh24:mi:ss.ff2') start_pop,
                   to_char(max(d.timestamp), 'hh24:mi:ss.ff2') finish_pop,
                   max(d.timestamp) - c.createtime diff
from dba_objects a,
gv$im_segments b,
gv$im_segments_detail c,
gv$im_header d
where a.owner = 'SH'
and object_type = 'TABLE'
and a.object_name = b.segment_name
and a.object_type = 'TABLE'
and a.object_id = c.baseobj
and c.dataobj = d.objd
group by a.object_name, b.inmemory_priority, b.populate_status,c.createtime;
 
OBJECT_NAME                    INMEMORY POPULATE_STAT START_POP   FINISH_POP  DIFF
------------------------------ -------- ------------- ----------- ----------- ------------------------------
SALES                          NONE     COMPLETED     02:24:45.98 02:52:14.52 +000000000 00:27:28.545614
SALES                          NONE     COMPLETED     02:25:21.90 02:52:14.52 +000000000 00:26:52.629525
SUPPLEMENTARY_DEMOGRAPHICS     NONE     COMPLETED     02:24:15.71 02:52:12.89 +000000000 00:27:57.186239
SUPPLEMENTARY_DEMOGRAPHICS     NONE     COMPLETED     02:24:15.76 02:52:12.89 +000000000 00:27:57.129454
CUSTOMERS                      NONE     COMPLETED     02:24:29.55 02:52:10.69 +000000000 00:27:41.133917
CUSTOMERS                      NONE     COMPLETED     02:24:34.41 02:52:10.69 +000000000 00:27:36.275106
TIMES                          NONE     COMPLETED     02:27:44.37 02:27:44.40 +000000000 00:00:00.028218
PRODUCTS                       NONE     COMPLETED     02:24:08.86 02:24:08.95 +000000000 00:00:00.096789
PROMOTIONS                     NONE     COMPLETED     02:24:08.69 02:24:08.76 +000000000 00:00:00.068441
COUNTRIES                      NONE     COMPLETED     02:24:07.94 02:24:08.06 +000000000 00:00:00.115714
CHANNELS                       NONE     COMPLETED     02:24:07.94 02:24:08.04 +000000000 00:00:00.106786
 
11 rows selected.

 

<END>