처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #5. In-Memory 테스트

"처음 사용자를 위한 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 수행 확인

이제 In-memory 적용 후 성능을 확인하기 위한 테스트를 진행한다. 여기서는PDB에서 통계정보를 다시 생성했다. (선택 사항임)

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SH',estimate_percent=>100,degree=>4);
PL/SQL procedure successfully completed.

성능테스트를 수행하기 전에 Swingbench에서 수행한 쿼리 성능만 조회되도록하기 위해 테스트 대상 PDB에 sys 유저로 접속해서 shared_pool을 flush했다.

SQL> alter system flush shared_pool;
System altered.

Swingbench VM으로 돌아와서 테스트는 “charbench” 유틸리티를 이용해서 8 유저로 10분간 수행한다.

[root@inst-public bin]# ./charbench -c /home/opc/swingbench/configs/shconfig.xml -cs //10.0.30.3:1521/db0621_pdb1.snpublic.cluster1.oraclevcn.com -u sh -p Xxxxxxxxxxxxx -uc 8 -rt 00:10 -min 0 -max 0 -a -v users,tpm,tps
Author :        Dominic Giles
Version :        2.6.0.1135

Results will be written to results.xml.

Time     Users       TPM      TPS
06:53:18 [0/8]       0        0
...
07:03:18 [8/8]       90       2
Saved results to results00001.xml
07:03:19 [6/8]       90       0

Completed Run.

결과 확인은 “Saved results to results00001.xml”과 같이 명시된 대로 results00001.xml 파일을 참조한다. 전체 완료된 트랜젝션수는 기존 26대비 45배, 평균 초당 트랜젝션만 놓고 봤을 때 기존 0.04대비 49배 향상된 것을 볼 수 있다.

[root@inst-public bin]# cat results00001.xml | grep TotalCompletedTransactions
<TotalCompletedTransactions>1177</TotalCompletedTransactions>
[root@inst-public bin]# cat results00001.xml | grep AverageTransactionsPerSecond
<AverageTransactionsPerSecond>1.96</AverageTransactionsPerSecond>

테스트 대상 PDB에 sys 유저로 접속해서 In-Memory가 적용된 분석 쿼리 성능을 확인해 본다. 기존 약 170 – 790 초 대의 응답시간 대비, “SalesByQuarterC” 모듈 쿼리를 제외하고는 대체로 7초 – 47초 elapsed time 쿼리가 top 10을 차지하고 있다.

SQL> set pagesize 999
SQL> set linesize 150
SQL> set verify off
SQL> col sql_id for a15
SQL> col child for 99999
SQL> col schema for a15
SQL> col module for a15 trunc
SQL> col sql_text format a70 trunc
SQL> alter session set nls_date_format = 'yyyymmdd hh24miss';
SQL> select * from (
select sql_id,
       child_number child,
        parsing_schema_name schema,
       module,
        last_active_time,
       executions,
        to_char(cpu_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg CPU Time",
        to_char(elapsed_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg Elapsed Time",
        to_char(application_wait_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg App Time",
       to_char(cluster_wait_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg Cluster Time",
       to_char(user_io_wait_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg IO Time",
        to_char(plsql_exec_time/1000000/decode(executions,null,1,0,1,executions),'999,999,999.99999') "Avg Plsql Time",
        round(buffer_gets/DECODE( executions, null, 1, 0, 1, executions)) "Buffer Gets Ratio",
        round(disk_reads/DECODE( executions, null, 1, 0, 1, executions)) "Disk Reads Ratio",
        round(rows_processed/DECODE( executions, null, 1, 0, 1, executions)) "Rows Processed Ratio",
        sql_text
from v$sql
where upper(parsing_schema_name) = 'SH'
order by elapsed_time desc)
where rownum <= 10;

SQL_ID           CHILD SCHEMA          MODULE          LAST_ACTIVE_TIM EXECUTIONS Avg CPU Time       Avg Elapsed Time   Avg App Time
--------------- ------ --------------- --------------- --------------- ---------- ------------------ ------------------ ------------------
Avg Cluster Time   Avg IO Time        Avg Plsql Time     Buffer Gets Ratio Disk Reads Ratio Rows Processed Ratio
------------------ ------------------ ------------------ ----------------- ---------------- --------------------
SQL_TEXT
----------------------------------------------------------------------
10n4fdghd4fnc        1 SH              SalesByQuarterC 20210623 070727          1          395.85383          669.42859             .00000
            .00000           57.52606             .00629             16165          1339982                    5
SELECT SUM(amount_sold),   t.calendar_month_desc,   t.calendar_week_nu
4a46mn0jsfzjm        1 SH              SalesByQuarterC 20210623 070959         1          354.18328          561.23679             .00000
            .00000           49.85879             .00680             17434          1291023                    6
SELECT SUM(amount_sold),   t.calendar_month_desc,   t.calendar_week_nu
709uggnu40a9a        0 SH              TopSalesWithinQ 20210623 070234          5           17.88951           36.24874             .00000
            .00000            1.93591             .00137               450            32893                   15
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
0bg8rg9srv8rm        0 SH              TopSalesWithinQ 20210623 070311          1           15.78952           47.43003             .00000
            .00000            2.16796             .00668              1227            25380                   16
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
5vbwrcz3vw3s0        0 SH              TopSalesWithinQ 20210623 070235          1           14.80278           34.96904             .00000
            .00000            1.82751             .00646               527            21660                   16
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
dfdg44tjr89uf        0 SH              TopSalesWithinQ 20210623 070049          3            5.88614            9.65869             .00000
            .00000             .00000             .00585                53                0                   16
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
gpxukn3ft9qgw        0 SH              TopSalesWithinQ 20210623 070119          1           12.05189           22.47298             .00000
            .00000             .92283             .01336                58            14865                   15
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
cyp8nrzv5k9g9        0 SH              SalesCubeByMont 20210623 070115          1            8.22712           14.13086             .00000
            .00000             .00000             .00000                64                0                   20
SELECT channel_desc, calendar_month_desc, countries.country_iso_code,
cutxgd5zp1vfv        0 SH              TopSalesWithinQ 20210623 065813          2            4.12420            6.85969             .00000
            .00000             .00000             .00557                54                0                   15
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu
6u54j4s3wf1ym        0 SH              TopSalesWithinQ 20210623 065729          2            3.52660            6.50657             .00000
            .00000             .00000             .00323                54                0                   15
SELECT * FROM   (SELECT times.calendar_quarter_desc,      customers.cu

10 rows selected.

참고로, top 10 중, 1,2 위인 “SalesByQuarterC” 쿼리(10n4fdghd4fnc, 4a46mn0jsfzjm) 의 경우, 각각 두개의 cursor로 실행되었는데, 아래 “10n4fdghd4fnc" 쿼리 예와 같이 각 cusrsor에서 수행된 쿼리의 응답시간과 실행계획이 다르다. cursor 1의 쿼리의 경우, cursor 0 쿼리와 다르게 In-Memory에서의 쿼리 성능 향상을 가져오는 Bloom Filter나 Vector Group By 등이 실행되지 않았다. 이번 테스트에서는 cursor 간 실행 계획상의 변경을 가져온 원인을 분석하지는 않았다. 다만, In-Memory 적용 시, 대체로 분석 쿼리 성능이 향상되지만 일부는 기대한 대로 실행계획이 수행되지 않기 때문에 이는 확인이 필요한 사항이다.

Enter value for sql_id: 10n4fdghd4fnc

SQL_ID           CHILD SCHEMA          MODULE          LAST_ACTIVE_TIM EXECUTIONS Avg Elapsed Time   Avg App Time       Avg Cluster Time
--------------- ------ --------------- --------------- --------------- ---------- ------------------ ------------------ ------------------
Avg IO Time        Avg Plsql Time     Total Buffer Gets Rows Processed Ratio SQL_TEXT
------------------ ------------------ ----------------- -------------------- ----------------------------------------------------------------------
10n4fdghd4fnc        1 SH              SalesByQuarterC 20210623 070727          1          669.42859             .00000             .00000
          57.52606             .00629             16165                    5 SELECT SUM(amount_sold),   t.calendar_month_desc,   t.calendar_week_nu

10n4fdghd4fnc        0 SH              SalesByQuarterC 20210623 065556          1            1.06284             .00000             .00000
            .00000             .00655                93                    5 SELECT SUM(amount_sold),   t.calendar_month_desc,   t.calendar_week_nu

…
SQL_ID 10n4fdghd4fnc, child number 1
-------------------------------------
…
Plan hash value: 414154094
----------------------------------------------------------------------------------------------
| Id | Operation                        | Name      | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |       |       | 10564 (100)|          |
|   1 | HASH GROUP BY                   |           |     1 |    75 | 10564 (12)| 00:00:01 |
|* 2 |   HASH JOIN                      |           |     1 |    75 | 10563 (12)| 00:00:01 |
|* 3 |    HASH JOIN                     |           |     1 |    56 | 10562 (12)| 00:00:01 |
|* 4 |     HASH JOIN                    |           |     5 |   190 | 10561 (12)| 00:00:01 |
|   5 |      VIEW                        | VW_GBC_16 |     5 |   135 | 10555 (12)| 00:00:01 |
|   6 |       HASH GROUP BY              |           |     5 |    85 | 10555 (12)| 00:00:01 |
|   7 |        TABLE ACCESS INMEMORY FULL| SALES     |   117M| 1910M| 10554 (12)| 00:00:01 |
|   8 |      TABLE ACCESS INMEMORY FULL | CUSTOMERS |     1 |    11 |     2   (0)| 00:00:01 |
|* 9 |     TABLE ACCESS INMEMORY FULL   | COUNTRIES |     1 |    18 |     2   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS INMEMORY FULL    | TIMES     |     1 |    19 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

SQL_ID 10n4fdghd4fnc, child number 0
-------------------------------------
…
Plan hash value: 1328482768
----------------------------------------------------------------------------------------------------------------------
| Id | Operation                                | Name                      | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |       | 14209 (100)|          |
|   1 | TEMP TABLE TRANSFORMATION               |                           |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67C1_33BF49 |       |       |            |          |
|   3 |    HASH GROUP BY                         |                           |    24 |   552 |     2 (50)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED           | :KV0000                   |    24 |   552 |     1   (0)| 00:00:01 |
|* 5 |      TABLE ACCESS INMEMORY FULL          | TIMES                     |    31 |   589 |     1   (0)| 00:00:01 |
|   6 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67C2_33BF49 |       |       |            |          |
|   7 |    HASH GROUP BY                         |                           |     1 |    33 | 3648   (5)| 00:00:01 |
|   8 |     KEY VECTOR CREATE BUFFERED           | :KV0001                   |     1 |    33 | 3632   (5)| 00:00:01 |
|* 9 |      HASH JOIN                           |                           |   591K|    16M| 3631   (5)| 00:00:01 |
| 10 |       JOIN FILTER CREATE                 | :BF0000                   |     1 |    18 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS INMEMORY FULL        | COUNTRIES                 |     1 |    18 |     2   (0)| 00:00:01 |
| 12 |       JOIN FILTER USE                    | :BF0000                   |    13M|   136M| 3593   (4)| 00:00:01 |
|* 13 |        TABLE ACCESS INMEMORY FULL        | CUSTOMERS                 |    13M|   136M| 3593   (4)| 00:00:01 |
| 14 |   HASH GROUP BY                          |                           |    17 | 1020 | 10559 (12)| 00:00:01 |
|* 15 |    HASH JOIN                             |                           |    17 | 1020 | 10558 (12)| 00:00:01 |
| 16 |     MERGE JOIN CARTESIAN                 |                           |    24 |   936 |     4   (0)| 00:00:01 |
| 17 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D67C2_33BF49 |     1 |    23 |     2   (0)| 00:00:01 |
| 18 |      BUFFER SORT                         |                           |    24 |   384 |     2   (0)| 00:00:01 |
| 19 |       TABLE ACCESS FULL                  | SYS_TEMP_0FD9D67C1_33BF49 |    24 |   384 |     2   (0)| 00:00:01 |
| 20 |     VIEW                                 | VW_VT_0737CF93            |    17 |   357 | 10554 (12)| 00:00:01 |
| 21 |      VECTOR GROUP BY                     |                           |    17 |   425 | 10554 (12)| 00:00:01 |
| 22 |       HASH GROUP BY                      |                           |    17 |   425 | 10554 (12)| 00:00:01 |
| 23 |        KEY VECTOR USE                    | :KV0001                   | 72600 | 1772K| 10554 (12)| 00:00:01 |
| 24 |         KEY VECTOR USE                   | :KV0000                   |   527K|    10M| 10554 (12)| 00:00:01 |
|* 25 |          TABLE ACCESS INMEMORY FULL      | SALES                     |   117M| 1910M| 10554 (12)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Bloom filter는 오라클 데이터베이스 10g에서 해쉬 조인 성능 향상을 위해 처음 소개된 테이블 간 조인 기법으로 오라클 데이터베이스 In-Memory에 국한된 기능은 아니다. 다만 칼럼 포맷 데이터와 SIMD vector 프로세싱에서 대단히 효율적으로 작동하기 때문에 In-Memory 에서 조인 성능 향상에 도움이 된다.
In-Memory에서 해쉬 조인 시, 보통 작은 테이블이 먼저 스캔되고 WHERE 조건에 만족하는 로우가 PGA에 In-Memory 해쉬 테이블로 생성이 된다. 해쉬 테이블이 생성될 때 동시에 bit vector 또는 Bloom filter가 조인되는 칼럼 기반으로 만들어진다. Bit vector는 조인 대상 큰 테이블 스캔 시 추가 조건으로 보내진다. 조인 대상 테이블에 WHERE 조건 절이 적용되고 나면, 그 결과로 나온 로우들의 조인 칼럼은 해쉬 값을 갖게 되고 Bit vector의 값들과 비교된다. Bit vector와 일치하는 로우는 해쉬 조인에 참여를 하게 되고, 그렇지 않은 로우는 버려지게 된다.

TopSalesWithinQ 모듈 쿼리 중 하나를 살펴보면 블룸필터가 사용되었음을 알 수 있다. 아래의 경우, 쿼리 수행의 가장 부담이 되는 부분은 SALES 테이블에 엑세스할때 Bloom filter가 사용되었다. TIMES 테이블 스캔이 되고 Bloom filter “:BF0001”이 만들어 졌으며, 이는 SALES 테이블에 대한 INMEMORY FULL SCAN의 한 부분으로 적용되었다.

SQL> select * from table(dbms_xplan.display_cursor('709uggnu40a9a','0','advanced'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 709uggnu40a9a, child number 0
-------------------------------------
…
Plan hash value: 3712434380
-------------------------------------------------------------------------------------------------------
| Id | Operation                         | Name      | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |       |       |       | 79063 (100)|          |
|* 1 | VIEW                             |           | 1566K|   116M|       | 79063   (3)| 00:00:04 |
|* 2 |   WINDOW SORT PUSHED RANK         |           | 1566K|    79M|    96M| 79063   (3)| 00:00:04 |
|   3 |    HASH GROUP BY                  |           | 1566K|    79M|    96M| 79063   (3)| 00:00:04 |
|* 4 |     HASH JOIN                     |           | 1566K|    79M|    65M| 38146   (5)| 00:00:02 |
|   5 |      JOIN FILTER CREATE           | :BF0000   | 1566K|    47M|       | 10885 (14)| 00:00:01 |
|* 6 |       HASH JOIN                   |           | 1566K|    47M|       | 10885 (14)| 00:00:01 |
|   7 |        JOIN FILTER CREATE         | :BF0001   |    92 | 1380 |       |     1   (0)| 00:00:01 |
|* 8 |         TABLE ACCESS INMEMORY FULL| TIMES     |    92 | 1380 |       |     1   (0)| 00:00:01 |
|   9 |        JOIN FILTER USE            | :BF0001   |   117M| 1910M|       | 10554 (12)| 00:00:01 |
|* 10 |         TABLE ACCESS INMEMORY FULL| SALES     |   117M| 1910M|       | 10554 (12)| 00:00:01 |
| 11 |      JOIN FILTER USE              | :BF0000   |    13M|   260M|       | 3630   (5)| 00:00:01 |
|* 12 |       TABLE ACCESS INMEMORY FULL | CUSTOMERS |    13M|   260M|       | 3630   (5)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RANK_WITHIN_QUARTER"<16)
   2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY
              SUM("AMOUNT_SOLD") DESC )<16)
   4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")
   6 - access("TIMES"."TIME_ID"="SALES"."TIME_ID")
   8 - inmemory("TIMES"."CALENDAR_QUARTER_DESC"='2012-4')
       filter("TIMES"."CALENDAR_QUARTER_DESC"='2012-4')
10 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"SALES"."TIME_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"SALES"."TIME_ID"))
12 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"CUSTOMERS"."CUST_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"CUSTOMERS"."CUST_ID"))
…

elaspsed time 기준 top10 쿼리에는 없지만 "SalesByWeekCoun" 모듈 쿼리의 경우, Vector Group By 가 사용되고 있음. 작은 dimension 테이블을 스캔할 때 Key vector라는 구조를 만들고, 이를 조인 시 추가 필터 조건으로 적용하여 SALES 테이블 스캔과 동시에 집계가 되도록 하는 기법이다.

SQL> select * from table(dbms_xplan.display_cursor('8r0uv54d3bvsr','0','advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8r0uv54d3bvsr, child number 0
-------------------------------------
…

Plan hash value: 1328482768
----------------------------------------------------------------------------------------------------------------------
| Id | Operation                                | Name                      | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |       | 14209 (100)|          |
|   1 | TEMP TABLE TRANSFORMATION               |                           |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D68C2_33BF49 |       |       |            |          |
|   3 |    HASH GROUP BY                         |                           |     1 |    20 |     2 (50)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED           | :KV0000                   |     1 |    20 |     1   (0)| 00:00:01 |
|* 5 |      TABLE ACCESS INMEMORY FULL          | TIMES                     |     7 |   112 |     1   (0)| 00:00:01 |
|   6 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D68C3_33BF49 |       |       |            |          |
|   7 |    HASH GROUP BY                         |                           |     1 |    33 | 3648   (5)| 00:00:01 |
|   8 |     KEY VECTOR CREATE BUFFERED           | :KV0001                   |     1 |    33 | 3632   (5)| 00:00:01 |
|* 9 |      HASH JOIN                           |                           |   591K|    16M| 3631   (5)| 00:00:01 |
| 10 |       JOIN FILTER CREATE                 | :BF0000                   |     1 |    18 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS INMEMORY FULL        | COUNTRIES                 |     1 |    18 |     2   (0)| 00:00:01 |
| 12 |       JOIN FILTER USE                    | :BF0000                   |    13M|   136M| 3593   (4)| 00:00:01 |
|* 13 |        TABLE ACCESS INMEMORY FULL        | CUSTOMERS                 |    13M|   136M| 3593   (4)| 00:00:01 |
| 14 | HASH GROUP BY                          |                           |     1 |    64 | 10559 (12)| 00:00:01 |
|* 15 |    HASH JOIN                             |                           |     1 |    64 | 10558 (12)| 00:00:01 |
| 16 |     MERGE JOIN CARTESIAN                 |                           |     1 |    43 |     4   (0)| 00:00:01 |
| 17 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D68C2_33BF49 |     1 |    20 |     2   (0)| 00:00:01 |
| 18 |      BUFFER SORT                         |                           |     1 |    23 |     2   (0)| 00:00:01 |
| 19 |       TABLE ACCESS FULL                  | SYS_TEMP_0FD9D68C3_33BF49 |     1 |    23 |     2   (0)| 00:00:01 |
| 20 |     VIEW                                 | VW_VT_0737CF93            |     1 |    21 | 10554 (12)| 00:00:01 |
| 21 |      VECTOR GROUP BY                     |                           |     1 |    25 | 10554 (12)| 00:00:01 |
| 22 |       HASH GROUP BY                      |                           |    1 |    25 | 10554 (12)| 00:00:01 |
| 23 |        KEY VECTOR USE                    | :KV0001                   | 16394 |   400K| 10554 (12)| 00:00:01 |
| 24 |         KEY VECTOR USE                   | :KV0000                   |   119K| 2444K| 10554 (12)| 00:00:01 |
|* 25 |          TABLE ACCESS INMEMORY FULL      | SALES                     |   117M| 1910M| 10554 (12)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - inmemory(("T"."CALENDAR_WEEK_NUMBER"='36' AND "T"."CALENDAR_YEAR"='2005'))
       filter(("T"."CALENDAR_WEEK_NUMBER"='36' AND "T"."CALENDAR_YEAR"='2005'))
   9 - access("CU"."COUNTRY_ID"="C"."COUNTRY_ID")
11 - inmemory("C"."COUNTRY_ISO_CODE"='JP')
       filter("C"."COUNTRY_ISO_CODE"='JP')
13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"CU"."COUNTRY_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"CU"."COUNTRY_ID"))
15 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_7"=INTERNAL_FUNCTION("C0"))
25 - inmemory((SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0000) AND
              SYS_OP_KEY_VECTOR_FILTER("S"."CUST_ID",:KV0001)))
       filter((SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0000) AND
              SYS_OP_KEY_VECTOR_FILTER("S"."CUST_ID",:KV0001)))
…
Note
-----
   - vector transformation used for this statement
…

IM Column Store가 사용되었는 지 여부를 확인할 수 있는 방법은 앞서 살펴본 실행계획을 통한 확인 외에도 시스템 또는 세션 레벨의 통계값을 확인하는 것이다. V$SYSSTAT V$MYSTAT, V$STATNAME 등의 뷰를 통해 조회한다. 먼저 In-Memory Option이 소개되면서 나온 IM 으로 시작하는 IM Column Store 통계정보들을 어떤 것들이 있는 지 조회해 본다.

SQL> set lines 120
SQL> col display_name for a70
SQL> col value for 99999999999999999
SQL> select display_name from v$statname where display_name like 'IM %';

DISPLAY_NAME
----------------------------------------------------------------------
IM transactions
IM rac CUs invalid
IM ADG corrupt undo blocks seen
IM ADG inv pdb due to corrupt undo
IM ADG inv all due to corrupt undo
…
IM repopulate CUs req cleandelta
IM CUs hwm expanded
IM CUs hwm mismatch drop
IM CUs hwm dropped
IM (HPK4SQL) hash joins attempted
IM (HPK4SQL) hash joins completed

543 rows selected.

In-Memory 관련 각 통계정보의 내용은 아래 레퍼런스를 참조한다.
Oracle Database 21c Statistics Descriptions

자주 나오는 In-Memory 통계 정보의 내용은 다음과 같다.

IM scan bytes in-memory : 압축된 형태로 인메모리에서 스캔한 CU(Compression Unit). 바이트 단위 . pruning 등의 최적화 이전 수치
IM scan bytes uncompressed : 압축해제된 형태로 인메모리에서 스캔한 CU(Compression Unit). 바이트 단위. pruning 등의 최적화 이전 수치
IM scan CUs columns accessed : 인메모리 스캔으로 엑세스된 CU 갯수
IM scan CUs columns theoretical max : 인메모리 스캔 가능한 최대 CU 수
IM scan segments minmax eligible : minmax pruning 대상 CU 갯수
IM scan CUs pruned : minmax pruning CU 갯수
IM scan rows: 최적화가 안되었다면 스캔했을 로우 수
IM scan rows valid: 최적화를 통해 스캔된 실제 스캔된 로우 수
IM scan rows optimized: IM scan rows - IM scan rows valid

현재 PDB의 시스템 레벨의 In-Memory Column Store 관련 통계정보는 아래와 같이 조회할 수 있다.

SQL> select display_name, value
from v$sysstat m, v$statname n
where m.statistic# = n.statistic#
and n.display_name like 'IM%'
and value > 0
order by value desc;

DISPLAY_NAME                                                                        VALUE
---------------------------------------------------------------------- ------------------
IM scan bytes uncompressed                                                  8226646562291
IM scan bytes in-memory                                                     3517759036674
IM scan rows                                                                 135813696592
IM scan rows valid                                                            80910114479
IM scan rows optimized                                                        53961381842
IM populate bytes from storage                                                11466465280
IM populate bytes uncompressed data                                            8963230054
…
IMU ktichg flush                                                                       81
IMU CR rollbacks                                                                       49
IM space segments allocated                                                             8
IM populate segments requested                                                         8
IM populate segments                                                                    8
IM scan CUs optimized read                                                              4
IMU recursive-transaction flush                                                         1

93 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

아래와 같이 세션 레벨의 통계값을 통해 수행한 쿼리가 인메모리 스캔을 했는 지, 그리고 얼마나 읽어들였는지 확인할 수도 있다.

[oracle@singledb scripts]$ sqlplus system/Xxxxxxxxxxxxx@singledb:1521/db0621_pdb1.snpublic.cluster1.oraclevcn.com
…

SQL> select tax_country,sum(quantity_sold) from sh.sales group by tax_country;

TAX SUM(QUANTITY_SOLD)
--- ------------------
ag             9027913
ao             8486135
bc             8734382
…
zv             8518575
zx             8492863

676 rows selected.

SQL> set lines 120
SQL> col display_name for a70
SQL> col value for 99999999999999999
SQL> select display_name, value
from v$mystat m, v$statname n
where m.statistic# = n.statistic#
and n.display_name like 'IM%'
and value > 0
order by value desc;

DISPLAY_NAME                                                                        VALUE
---------------------------------------------------------------------- ------------------
IM scan rows                                                                    119479730
IM scan rows valid                                                              117830072
IM scan rows pcode aggregated                                                   117830072
IM scan rows projected                                                             154804
IM scan CUs columns accessed                                                          458
IM scan CUs pcode aggregation pushdown                                                229
IM scan CUs memcompress for query low                                                 229

7 rows selected.

<END>

저작자표시 비영리 변경금지

'Database > Oracle Database' 카테고리의 다른 글

처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #7. 참고. IM FastStart 수행 확인 (0)	2022.06.08
처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #6. 참고. 칼럼 레벨 In-Memory 설정 및 In-Memory hybrid scan (0)	2022.06.08
처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #4. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 설정 & Population (0)	2022.06.08
처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #3. In-Memory Advisor (0)	2022.06.08
처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #2. 테스트 환경 준비 (0)	2022.06.08

소소한 IT 기술 정리

처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #5. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 수행 확인

글 순서

In-Memory 수행 확인

'Database > Oracle Database' 카테고리의 다른 글

티스토리툴바

처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #5. In-Memory 테스트 – Single 인스턴스 DB: In-Memory 수행 확인

글 순서

In-Memory 수행 확인

'Database > Oracle Database' 카테고리의 다른 글

'Database/Oracle Database' Related Articles

티스토리툴바