"처음 사용자를 위한 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 Hybrid Scan
디폴트로 INMEMORY 속성 테이블의 모든 칼럼은 IM Column Store에 저장되지만, 메모리 크기 제약 등의 이유 등으로 테이블 내 개별 칼럼 레벨에서 INMEMORY 구문을 사용할 수도 있다. 각 칼럼 별로 다른 In-Memory압축 레벨을 사용할 수도 있다. External 테이블은 칼럼 레벨 In-Memory 설정을 지원하지 않는다.
In-Memory hybrid scan은 모든 칼럼이 population 되어 있지 않은 IM Column Store 테이블에 엑세스할 때 사용되는 옵티마이저의 기법이다. 오라클 데이터베이스21c 이전에는 NO INMEMORY 설정이 있는 칼럼을 참조하는 경우, 해당 쿼리는 로우 스토어에서 모든 데이터를 엑세스했었다. 따라서 칼럼 포맷, Predicate pushdown과 같은 In-Memory 기능의 잇점을 얻지 못했다. 오라클 데이터베이스21c 부터는 INMEMORY와 NO INMEMORY 칼럼 참조하는 쿼리도 아래 조건에 만족하는 경우, 칼럼 포맷 데이터에 엑세스할 수 있게 되었다.
- Predicate에는 INMEMORY 칼럼만 포함
- SELECT 절에는 INMEMORY, NO INMEMORY 둘 다 포함 가능
In-Memory hybrid scan은 수행 여부는 실행 계획을 통해 알 수 있다.
In-Memory hybrid scan을 확인하기 위해 우선 테스트 테이블을 만들고, 테이블은 INMEMORY 속성으로, 소속 칼럼 중 일부는 IMMEMORY MEMCOMPRESS FOR QUERY 옵션으로, 일부는 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH 옵션으로 압축했다. 그리고 나머지 칼럼은 NO INMEMORY 옵션을 부여했다.
| SQL> show con_name CON_NAME ------------------------------ DB0621_PDB1 SQL> create table sh.sales_colim as select * from sh.sales; Table created. SQL> alter table sh.sales_colim inmemory; Table altered. SQL> alter table sh.sales_colim inmemory memcompress for query ( prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH ( seller, fulfillment_center, courier_org, amount_sold) no inmemory ( tax_country, tax_region); Table altered. SQL> col segment_name for a30 SQL> select segment_name, inmemory, inmemory_compression from dba_segments where owner ='SH' and segment_name='SALES_COLIM'; SEGMENT_NAME INMEMORY INMEMORY_COMPRESS ------------------------------ -------- ----------------- SALES_COLIM ENABLED FOR QUERY LOW SQL> set pages 100 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 = 'SALES_COLIM'; TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION -------------------- ----------------------------------- -------------------------- SALES_COLIM PROD_ID FOR QUERY LOW SALES_COLIM CUST_ID FOR QUERY LOW SALES_COLIM TIME_ID FOR QUERY LOW SALES_COLIM CHANNEL_ID FOR QUERY LOW SALES_COLIM PROMO_ID FOR QUERY LOW SALES_COLIM QUANTITY_SOLD FOR QUERY LOW SALES_COLIM SELLER FOR CAPACITY HIGH SALES_COLIM FULFILLMENT_CENTER FOR CAPACITY HIGH SALES_COLIM COURIER_ORG FOR CAPACITY HIGH SALES_COLIM TAX_COUNTRY NO INMEMORY SALES_COLIM TAX_REGION NO INMEMORY SALES_COLIM AMOUNT_SOLD FOR CAPACITY HIGH 12 rows selected. |
테스트 테이블의 In-Memory Population을 수행한다.
| SQL> execute dbms_inmemory.populate('SH','SALES_COLIM'); PL/SQL procedure successfully completed. SQL> select segment_name, bytes, inmemory_size, populate_status, bytes_not_populated from v$im_segments where owner='SH' and segment_name='SALES_COLIM'; SEGMENT_NAME BYTES INMEMORY_SIZE POPULATE_STAT BYTES_NOT_POPULATED ------------------------------ ---------- ------------- ------------- ------------------- SALES_COLIM 7288676352 2549088256 COMPLETED 0 |
먼저 압축 레벨은 서로 다른 In-Memory 칼럼으로 구성된 쿼리를 수행해 본다. INMEMORY FULL 스캔을 수행했다.
| SQL> set pages 0 SQL> set lines 200 SQL> set timing on SQL> select min(QUANTITY_SOLD), max(AMOUNT_SOLD) from sh.SALES_COLIM where PROD_ID > 100; 1 98 Elapsed: 00:00:00.08 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID asmgp994rfgkn, child number 0 ------------------------------------- select min(QUANTITY_SOLD), max(AMOUNT_SOLD) from sh.SALES_COLIM where PROD_ID > 100 Plan hash value: 4277465842 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 10494 (100)| | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | TABLE ACCESS INMEMORY FULL| SALES_COLIM | 41M| 394M| 10494 (15)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - inmemory("PROD_ID">100) filter("PROD_ID">100) 21 rows selected. |
아래와 같이 SELECT 절에 INMEMORY와 NOINMEMORY 속성의 칼럼이 들어가 있고, Predicate에 INMEMORY 속성의 칼럼이 들어간 쿼리의 경우, 옵티마이저가 In-Memory hybrid scan을 선택하는 것을 알 수 있다.
| SQL> select max(PROD_ID), max(TAX_REGION) from sh.SALES_COLIM where PROD_ID > 1; 147 zz Elapsed: 00:01:38.35 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID a04rb6r1j8zy0, child number 0 ------------------------------------- select max(PROD_ID), max(TAX_REGION) from sh.SALES_COLIM where PROD_ID > 1 Plan hash value: 4277465842 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 242K(100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS INMEMORY FULL (HYBRID)| SALES_COLIM | 117M| 786M| 242K (1)| 00:00:10 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("PROD_ID">1) 20 rows selected. |
아래와 같이 SELECT 절에는 INMEMORY와 NOINMEMORY 속성의 칼럼이 들어가 있고, Predicate에 NO INMEMORY 속성이 들어간 쿼리의 경우에도, 옵티마이저가 In-Memory hybrid scan을 선택하는 것을 알 수 있다.
| SQL> select TAX_COUNTRY, min(QUANTITY_SOLD), max(AMOUNT_SOLD) from sh.SALES_COLIM where PROD_ID > 100 group by TAX_COUNTRY; dr 1 98 nb 1 98 ff 1 98 hs 1 98 ... qf 1 98 id 1 98 676 rows selected. Elapsed: 00:10:02.85 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID 5a70g7mk19pgk, child number 0 ------------------------------------- select TAX_COUNTRY, min(QUANTITY_SOLD), max(AMOUNT_SOLD) from sh.SALES_COLIM where PROD_ID > 100 group by TAX_COUNTRY Plan hash value: 2450388630 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 243K(100)| | | 1 | HASH GROUP BY | | 676 | 8788 | 243K (1)| 00:00:10 | |* 2 | TABLE ACCESS INMEMORY FULL (HYBRID)| SALES_COLIM | 41M| 512M| 242K (1)| 00:00:10 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("PROD_ID">100) 20 rows selected. |
NOINMEMORY 칼럼으로 구성된 쿼리의 경우, 테이블은 INMEMORY 속성이라도, 로우 스토어에서 데이터를 읽는다.
| SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:00.10 SQL> select max(TAX_COUNTRY), max(TAX_REGION) from sh.SALES_COLIM where TAX_COUNTRY > 'qq'; zz zz Elapsed: 00:01:17.12 SQL> select * from table(dbms_xplan.display_cursor()); SQL_ID 8axggudpr9t3x, child number 0 ------------------------------------- select max(TAX_COUNTRY), max(TAX_REGION) from sh.SALES_COLIM where TAX_COUNTRY > 'qq' Plan hash value: 4277465842 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 242K(100)| | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL| SALES_COLIM | 42M| 242M| 242K (1)| 00:00:10 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TAX_COUNTRY">'qq') 20 rows selected. |
<END>