"처음 사용자를 위한 Oracle Database In-Memory 가이드" 시리즈를 통해 Swingbench의 분석 업무 스키마 ‘SH’ 데이터를 이용해서 오라클 21c Single 인스턴스 데이터베이스와 RAC 데이터베이스에서 간단한 In-memory 옵션 사용 방법을 살펴봅니다.
글 순서
#4. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 설정 & Population
#5. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 수행 확인
#6. 참고. 칼럼 레벨 In-Memory 설정 및 In-Memory hybrid scan
#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>