"처음 사용자를 위한 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 테스트 – Single Instance DB
In-Memory 설정
CDB로 접속해서 현재 In-Memory 설정을 확인한다. 현재 SGA에 In-Memory 영역 설정이 되어 있지 않음을 확인할 수 있다. In-Memory 옵션 설정에 이용되는 “inmemory_size” 파라미터 값도 현재 0으로 되어 있다.
| SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> set pages 100 SQL> col name for a30 SQL> col value for 999999999999999 SQL> select name,value from v$sga; NAME VALUE ------------------------------ ---------------- Fixed Size 9702624 Variable Size 1778384896 Database Buffers 12683575296 Redo Buffers 23851008 SQL> show parameter inmemory NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_adg_enabled boolean TRUE inmemory_automatic_level string OFF inmemory_clause_default string inmemory_deep_vectorization boolean TRUE inmemory_expressions_usage string ENABLE inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_optimized_arithmetic string DISABLE inmemory_prefer_xmem_memcompress string inmemory_prefer_xmem_priority string inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent inmemory_virtual_columns string MANUAL inmemory_xmem_size big integer 0 optimizer_inmemory_aware boolean TRUE |
CDB에서alter system 명령으로 inmemory_size를 설정한다. In-Memory 설정 적용을 위해서는 데이터베이스 재기동이 필요하다. 앞서 In-Memory Advsior에서는 약 3GB 정도의 권고치가 나왔으나, 이번 테스트에는 SH 스키마의 전체 테이블 사이즈에 맞춰 11 GB로 inmemory_size를 설정했다. 실제 In-Memory 영역에 population 할때는 디폴트로 압축을 해서 올라가기 때문에 11GB를 모두 소모하지는 않을 것이다.
| SQL> alter system set inmemory_size=11g scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
데이터베이스가 startup 되면서 IM column store 영역도 할당된 것을 확인할 수 있다. Inmemory_area 파라미터에도 이제 앞서 할당한 11 GB가 조회된다.
| SQL> startup ORACLE instance started. Total System Global Area 1.4496E+10 bytes Fixed Size 9723416 bytes Variable Size 603979776 bytes Database Buffers 2046820352 bytes Redo Buffers 23830528 bytes In-Memory Area 1.1811E+10 bytes Database mounted. Database opened. SQL> show parameter inmemory NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_adg_enabled boolean TRUE inmemory_automatic_level string OFF inmemory_clause_default string inmemory_deep_vectorization boolean TRUE inmemory_expressions_usage string ENABLE inmemory_force string DEFAULT inmemory_max_populate_servers integer 2 inmemory_optimized_arithmetic string DISABLE inmemory_prefer_xmem_memcompress string inmemory_prefer_xmem_priority string inmemory_query string ENABLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_size big integer 11G inmemory_trickle_repopulate_servers_ integer 1 percent inmemory_virtual_columns string MANUAL inmemory_xmem_size big integer 0 optimizer_inmemory_aware boolean TRUE |
v$inmemory_area 딕셔너리 뷰를 조회해 보면, In-Memory Column Store 각 pool에 할당된 메모리, 그리고 사용 중인 메모리를 확인할 수 있다. 현재 Multitenant 환경이기 때문에 CDB, Seed PDB, PDB의 In-Memory pool 설정이 출력되고 있다.
| SQL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 8248098816 0 DONE 1 64KB POOL 3534487552 0 DONE 1 1MB POOL 8248098816 0 DONE 2 64KB POOL 3534487552 0 DONE 2 1MB POOL 8248098816 0 DONE 3 64KB POOL 3534487552 0 DONE 3 6 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 |
In-Memory population
INMEMORY 속성을 가진 오브젝트는 IM Column Store에 population이 될 후보가 된다. 기존 디스크상의 로우 포맷 데이터를 읽어서 칼럼 포맷으로 변환하여 IM Column Store에 저장하는 일련의 population 과정은 자동 또는 수동으로 별도로 이루어지는 작업이다.
그리고 population은 기존 데이터를 칼럼 포맷으로 변환하는 것에 반해, repopulation은 신규 데이터를 칼럼 포맷으로 변환하는 측면에서 서로 다른 작업이다. IMCU는 read-only 구조이기 때문에 오라클 데이터베이스는 로우가 변경될 때 population 하지 않는다. 대신 로우에 대한 변경이 발생하면 transactional journal에 기록하고, 이를 통해 새로운 IMCU를 repopulation 작업으로 수행한다. transactional journal은 IM Column Store의 트랜젝션의 일관성을 유지를 담당하는 Snapshot Metadata Unit (SMU)에 있는 메타데이터이다.
이러한 In-Memory Population 은 크게 자동으로 하는 방법, 수동으로 하는 방법이 있다.
먼저 자동으로 population 하는 방법이다.
- INMEMORY_AUTOMATIC_LEVEL는 “HIGH”로 설정하고, INMEMORY_FORCE는 “BASE_LEVEL”로 설정하지 않았다면 데이터베이스는 모든 오브젝트에 대해 자동으로 In-Memory Population을 한다. 이 경우 population과 evict가 필요에 따라 발생되며, DDL 명령에 IMMEORY 구문을 지정하지 않아도 된다.
수동으로 popluation 하는 방법은
- CREATE TABLE 또는 ALTER TABLE 명령에 INMEMORY 구문을 지정한 오브젝트에 대해 해당된다.
- On-demand population: 디폴트로 INMEMORY PRIORITY는 NONE으로 설정된다. 이 경우 Full table 스캔으로 엑세스된 오브젝트만 population된다. 엑세스되지 않거나 인덱스 스캔 또는 rowid로 fetch되는 경우 population은 일어나지 않는다. DBMS_INMEMORY.POPULATE 프로시저도 Full table 스캔과 마찬가지로 population을 발생시킨다.
- Priority 기반 population: INMEMORY PRIORITY가 NONE 이외의 값으로 설정된 경우, 오라클이 내부적으로 관리하는 priority queue를 이용해서 오브젝트를 자동으로 population 시킨다. INMEMORY PRIORITY는 CRITICAL, HIGH, MEDIUM, LOW, NONE 등의 다섯개 레벨로 나뉜다. Full table 스캔이 없어도 데이터베이스 인스턴스가 재시작되면 자동으로 이루어지며, 특정 Priority 레벨에 따라 오브젝트의 population을 queue에 놓고 수행한다.
Population 후 메모리에서 eviction은 해당 세그먼트가 drop, move, 또는 NO IMMEMORY 속성으로 변경되었을때 이루어진다. Eviction은 수동으로 하거나 ADO 정책으로 할 수 있다.
Swingbench 스키마 테이블들을 설정한 In-Memory Column Store에 population 한다. PDB에 접속해서 기존 SH 스키마 테이블들을 inmemory 속성으로 변경한다. 여기서는 테이블이 여러개 이므로 inmemroy 속성 변경 명령어들을 먼저 만들었다.
| [oracle@singledb scripts]$ sqlplus sys/Xxxxxxxxxxxxx@singledb:1521/db0621_pdb1.snpublic.cluster1.oraclevcn.com as sysdba ... SQL> show con_name CON_NAME ------------------------------ DB0621_PDB1 # 테이블의 inmemroy 속성 변경 스크립트 생성 SQL> select 'alter table sh.'||table_name||' inmemory;' from dba_tables where owner='SH'; 'ALTERTABLESH.'||TABLE_NAME||'INMEMORY;' ------------------------------------------------------------------------------------------------------------------------ alter table sh.CHANNELS inmemory; alter table sh.COUNTRIES inmemory; alter table sh.CUSTOMERS inmemory; alter table sh.PROMOTIONS inmemory; alter table sh.PRODUCTS inmemory; alter table sh.SUPPLEMENTARY_DEMOGRAPHICS inmemory; alter table sh.SALES inmemory; alter table sh.TIMES inmemory; 8 rows selected. |
inmemroy 속성 변경 alter table 명령을 수행한다.
| 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. |
그런데 v$im_segments를 확인해 보면 테이블의 속성은 inmemory로 변경이 되었지만 population은 즉각 일어나지 않은 것을 알 수 있다.
| SQL> select segment_name, bytes, inmemory_size, populate_status, bytes_not_populated from v$im_segments where owner='SH'; no rows selected |
테이블 속성 변경 후, In-Memory population을 발생시키기 위해서서는 해당 테이블을 full table 스캔으로 엑세스하거나, dbms_inmemory.populate 프로시저를 사용해서 할 수 있다. 여기서는 아래와 같이 dbms_inmemory.populate 프로시저로 수동으로 population 했다. 먼저 명령어들을 만든 후, 해당 명령을 수행했다.
| SQL> select 'execute dbms_inmemory.populate(''SH'','''||table_name||''');' from dba_tables where owner='SH'; 'EXECUTEDBMS_INMEMORY.POPULATE(''SH'','''||TABLE_NAME||''');' ------------------------------------------------------------------------------------------------------------------------ execute dbms_inmemory.populate('SH','CHANNELS'); execute dbms_inmemory.populate('SH','COUNTRIES'); execute dbms_inmemory.populate('SH','CUSTOMERS'); execute dbms_inmemory.populate('SH','PROMOTIONS'); execute dbms_inmemory.populate('SH','PRODUCTS'); execute dbms_inmemory.populate('SH','SUPPLEMENTARY_DEMOGRAPHICS'); execute dbms_inmemory.populate('SH','SALES'); execute dbms_inmemory.populate('SH','TIMES'); 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. |
In-Memory population 명령 수행 후, 별도 터미널에서 top 유틸리티로 확인해 보면 In-Memory population을 담당하는 worker process가 CPU 자원을 사용하면서 population 작업을 을 수행하고 있음을 유추할 수 있다.
Space Management Worker Processes (Wnnn) 최대 갯수를 INMEMORY_MAX_POPULATE_SERVERS 파라미터로 조정할 수 있다. 디폴트는 CPU 쓰레드 갯수의 절반값 또는 PGA_AGGREGATE_TARGET을 512MB로 나눈 값 중 작은 값을 디폴트로 한다.
INMEMORY_MAX_POPULATE_SERVERS을 0으로 설정한 경우, IM Column Store에 population이 발생하지 않는다.
In-Memory Column Store 관려 초기화 파라미터는 아래 링크를 참조한다.
In-Memory Initialization Parameters
| top - 05:43:00 up 5:25, 2 users, load average: 1.59, 0.68, 0.40 Tasks: 662 total, 4 running, 658 sleeping, 0 stopped, 0 zombie %Cpu(s): 45.0 us, 2.7 sy, 0.0 ni, 49.3 id, 2.2 wa, 0.0 hi, 0.9 si, 0.0 st KiB Mem : 30613012 total, 6481504 free, 19877872 used, 4253636 buff/cache KiB Swap: 16777212 total, 16777212 free, 0 used. 8055392 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 20410 oracle 20 0 14.7g 434616 104492 R 82.1 1.4 1:24.59 ora_w000_db0621 20422 oracle 20 0 14.7g 349816 101976 R 80.1 1.1 1:25.78 ora_w001_db0621 … |
잠시 후 v$im_segments 딕셔너리 뷰를 확인해 보면 inmemory population이 완료된 것을 알 수 있다. population 후, 전체 사이즈는 애초 In-Memory Advisor에서 추정한 3.159GB 보다 다소 많이 디폴트 압축 레벨에서는 공간을 차지하고 있다..
| SQL> select segment_name, bytes, inmemory_size, populate_status, bytes_not_populated from v$im_segments where owner='SH'; SEGMENT_NAME BYTES INMEMORY_SIZE POPULATE_STAT BYTES_NOT_POPULATED --------------------------------------------- ---------- ------------- ------------- ------------------- SALES 7578124288 2877816832 COMPLETED 0 PROMOTIONS 1015808 1310720 COMPLETED 0 CHANNELS 1015808 1310720 COMPLETED 0 SUPPLEMENTARY_DEMOGRAPHICS 1044717568 495124480 COMPLETED 0 TIMES 483328 1310720 COMPLETED 0 CUSTOMERS 2839076864 744226816 COMPLETED 0 PRODUCTS 1015808 1310720 COMPLETED 0 COUNTRIES 1015808 1310720 COMPLETED 0 SQL> select round(sum(inmemory_size)/1024/1024/1024,3) as gb from v$im_segments where owner = 'SH'; GB ---------- 3.829 |
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, v$im_segments b, v$im_segments_detail c, v$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 06:12:53.90 06:17:16.06 +000000000 00:04:22.157818 TIMES NONE COMPLETED 06:14:05.69 06:14:05.71 +000000000 00:00:00.025498 CUSTOMERS NONE COMPLETED 06:12:06.96 06:14:05.62 +000000000 00:01:58.663572 SUPPLEMENTARY_DEMOGRAPHICS NONE COMPLETED 06:11:56.06 06:12:46.45 +000000000 00:00:50.386750 PRODUCTS NONE COMPLETED 06:11:49.22 06:11:49.29 +000000000 00:00:00.074726 PROMOTIONS NONE COMPLETED 06:11:49.12 06:11:49.18 +000000000 00:00:00.059181 COUNTRIES NONE COMPLETED 06:11:48.92 06:11:48.99 +000000000 00:00:00.069640 CHANNELS NONE COMPLETED 06:11:48.88 06:11:48.95 +000000000 00:00:00.077245 8 rows selected. |
참고로 테이블 레벨의 Inmemory Compression은 아래와 같이 확인한다.
| SQL> col segment_name for a35 SQL> select segment_name, inmemory, inmemory_compression from dba_segments where owner ='SH' and segment_type='TABLE'; SEGMENT_NAME INMEMORY INMEMORY_COMPRESS ----------------------------------- -------- ----------------- TIMES ENABLED FOR QUERY LOW CHANNELS ENABLED FOR QUERY LOW COUNTRIES ENABLED FOR QUERY LOW CUSTOMERS ENABLED FOR QUERY LOW PROMOTIONS ENABLED FOR QUERY LOW PRODUCTS ENABLED FOR QUERY LOW SUPPLEMENTARY_DEMOGRAPHICS ENABLED FOR QUERY LOW SALES ENABLED FOR QUERY LOW 8 rows selected. |
칼럼 레벨의 Inmemory Compression 정보는 아래 뷰로 조회한다. 여기에서는 칼럼 레벨로 압축을 수행하지 않았기 때문에 모두 "DEFAULT"로 출력된다. 칼럼 레벨 압축은 칼럼별로 다른 압축으로 수행할 수 있으며, 압축을 한 경우, "FOR QUERY HIGH", "FOR CAPACITY LOW", "NO MEMCOMPRESS" 등과 같은 정보가 출력된다.
| SQL> col table_name for a20 SQL> col column_name for a35 SQL> select table_name, column_name, inmemory_compression from v$im_column_level where table_name = 'CHANNELS'; TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION -------------------- ----------------------------------- -------------------------- CHANNELS CHANNEL_ID DEFAULT CHANNELS CHANNEL_DESC DEFAULT CHANNELS CHANNEL_CLASS DEFAULT CHANNELS CHANNEL_CLASS_ID DEFAULT CHANNELS CHANNEL_TOTAL DEFAULT CHANNELS CHANNEL_TOTAL_ID DEFAULT 6 rows selected. |
<END>
