"처음 사용자를 위한 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 테스트
Auto DOP 적용 테스트 수행
RAC 환경에서 IM Column Store를 사용할 때 디스크나 버퍼 캐시 엑세스를 피하기 위해서는 적어도 하나의 parallel 서버 프로세스가 모든 active 데이터베이스 인스턴스에서 실행되어야 한다. 이를 위해서 RAC환경의 IM Column Store에서는 Automatic Degree of Parallelism (Auto DOP)가 권장된다.
Auto DOP를 사용하지 않는 경우에는 쿼리가 요청한 IMCU를 포함하는 IM Column Store 갯수와 같거나 더 큰 DOP가 보장되어야 한다.
Auto DOP는 옵티마이저가 비용 기반 산정에 따라 SQL 구문의 parallel degree를 결정하는 것으로 PARALLEL_DEGREE_POLICY 파라미터를 AUTO로 세팅함으로써 활성화된다. 옵티마이저가 SQL 구문을 파싱할 때 실행 시간을 추정한다.
- 이때 PARALLEL_MIN_TIME_THRESHOLD 값과 비교해서 추정값이 PARALLEL_MIN_TIME_THRESHOLD 보다 작으면 serial 하게 수행
- PARALLEL_MIN_TIME_THRESHOLD 값과 비교해서 추정값이 PARALLEL_MIN_TIME_THRESHOLD 보다 크면 serial 하게 수행한다.
Parallel degree는 자원 요구 상황에 따라 결정되며, PARALLEL_DEGREE_LIMIT 파라미터 값이나, Database Resource Manager설정으로 제한이 된다.
먼저 Auto DOP를 활성화 시킨다. Auto DOP를 활성화 시키는 parallel_degree_policy 파라미터는 Pluggable Database 레벨에서도 작동한다. 여기서는 1번 노드 PDB로 접속하여 parallel_degree_policy 파라미터를 AUTO로 변경했다.
| SQL> sqlplus sys/Xxxxxxxxxxxxx@10.0.30.18:1521/db0622_pdb1.snpublic.cluster1.oraclevcn.com as sysdba ... SQL> show con_name CON_NAME ------------------------------ DB0622_PDB1 SQL> alter system set parallel_degree_policy=auto scope=both sid='*'; System altered. SQL> show parameter parallel NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ awr_pdb_max_parallel_slaves integer 10 containers_parallel_degree integer 65535 fast_start_parallel_rollback string LOW max_datapump_parallel_per_job string 50 optimizer_ignore_parallel_hints boolean FALSE parallel_adaptive_multi_user boolean FALSE parallel_degree_limit string CPU parallel_degree_policy string AUTO parallel_execution_message_size integer 16384 parallel_force_local boolean FALSE parallel_instance_group string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_max_servers integer 160 parallel_min_degree string 1 parallel_min_percent integer 0 parallel_min_servers integer 16 parallel_min_time_threshold string AUTO parallel_servers_target integer 64 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 |
성능테스트를 수행하기 전에 Swingbench에서 수행한 쿼리 성능만 조회되도록하기 위해 RAC 양쪽 노드에서 테스트 대상 PDB에 sys 유저로 접속해서 shared_pool을 flush했다. 이후 성능 비교를 위해 테스트 수행 전후 AWR snapshot을 생성했다.
| SQL> select sys_context('userenv','server_host') from dual; SYS_CONTEXT('USERENV','SERVER_HOST') ------------------------------------------------------------------------------------------------------------------------ racdb1 SQL> show con_name CON_NAME ------------------------------ DB0622_PDB1 SQL> alter system flush shared_pool; System altered. SQL> connect sys/Xxxxxxxxxxxxx@10.0.30.246:1521/db0622_pdb1.snpublic.cluster1.oraclevcn.com as sysdba Connected. SQL> select sys_context('userenv','server_host') from dual; SYS_CONTEXT('USERENV','SERVER_HOST') ------------------------------------------------------------------------------------------------------------------------ racdb2 SQL> show con_name CON_NAME ------------------------------ DB0622_PDB1 SQL> alter system flush shared_pool; System altered. SQL> exit Disconnected from Oracle Database 21c EE Extreme Perf Release 21.0.0.0.0 - Production Version 21.1.0.0.0 |
Swingbench 디렉토리로 이동해서 “charbench” 유틸리티를 이용해서 8 유저로 10분간 수행한다.
| [root@inst-public scripts]# cd /home/opc/swingbench/bin/ [root@inst-public bin]# ./charbench -c /home/opc/swingbench/configs/shconfig.xml -cs //racdb-scan.snpublic.cluster1.oraclevcn.com:1521/db0622_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:43:37 [0/8] 0 0 ... 06:53:37 [8/8] 68 3 Saved results to results00004.xml 06:53:38 [5/8] 65 0 Completed Run. |
결과 확인은 “Saved results to results00004.xml”과 같이 명시된 대로 results00001.xml 파일을 참조한다. 전체 완료된 트랜젝션수는 In-Memory 적용 전과 비교하여, 기존 28대비 24배, 평균 초당 트랜젝션만 놓고 봤을 때 기존 0.05대비 22배 향상된 것을 볼 수 있다.
| [root@inst-public bin]# cat results00004.xml | grep TotalCompletedTransactions <TotalCompletedTransactions>664</TotalCompletedTransactions> [root@inst-public bin]# cat results00004.xml | grep AverageTransactionsPerSecond <AverageTransactionsPerSecond>1.11</AverageTransactionsPerSecond> |
테스트 대상 PDB에 sys 유저로 접속해서 In-Memory가 적용된 분석 쿼리 성능을 확인해 본다. 기존 약 330 – 430 초 대의 응답시간 대비, RAC node#1에서는 24-88초대, RAC node#1에서는 24-88초대, RAC node#2의 경우 모듈명 세팅하는 쿼리를 제외하고 업무 쿼리가 약 4초대 성능을 나타내고 있다.
| 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; # RAC node#1 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 ---------------------------------------------------------------------- 9w0c53r5um1xy 2 SH TopSalesWithinQ 20210628 065237 2 17.08311 87.83951 .00105 .00220 .00000 .02019 1650 0 16 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 416hz7t3mx918 2 SH TopSalesWithinQ 20210628 065313 1 24.71230 162.84473 .00104 .00320 .00000 .01957 3140 0 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 4ssk2q7txnuvh 2 SH TopSalesWithinQ 20210628 065039 2 10.57983 55.82566 .00388 .00114 .00000 .01252 1615 0 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu byaucpd91c4rg 2 SH TopSalesWithinQ 20210628 064938 2 7.84351 44.63608 .01806 .00711 .00070 .02534 1667 1 16 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 5j1naccdzaj3v 2 SH SalesCubeByMont 20210628 065312 1 11.27415 68.29830 .00070 .00148 .00000 .00000 1752 0 20 SELECT channel_desc, calendar_month_desc, countries.country_iso_code, 9b723qjgkmy9z 2 SH TopSalesWithinQ 20210628 065325 1 13.54646 63.38875 .00503 .00302 .00000 .02230 1682 0 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 954q4b1vkp8m4 2 SH TopSalesWithinQ 20210628 065034 1 9.98962 60.86897 .00255 .00358 .00000 .06264 1682 0 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 1dt3g8xuyg90w 2 SH SalesCubeByMont 20210628 065047 1 13.16279 52.48604 .00085 .00078 .00000 .00000 1670 0 20 SELECT channel_desc, calendar_month_desc, countries.country_iso_code, 03ghr9b5m3x43 2 SH TopSalesWithinQ 20210628 065206 1 7.59615 49.27225 .00145 .03370 .00000 .03140 1692 0 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 83wufms0kwjb0 2 SH TopSalesWithinQ 20210628 065013 2 6.12335 24.44577 .00138 .01496 .00000 .02233 2331 0 20 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 10 rows selected. # RAC node#2 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 ---------------------------------------------------------------------- 1yd1m17y1j3au 2 SH SalesMovingAver 20210628 065048 2 .59194 4.40252 .00155 .00338 .00000 .00000 1219 0 20 SELECT t.time_id, to_char(SUM(amount_sold), '9,999,999,999') AS sa d0gc0dn4frj1c 0 SH SalesByWeekCoun 20210628 065341 0 1.49574 3.98817 .00000 .00735 .01095 .00000 5673 1 0 SELECT SUM(amount_sold), t.calendar_year, t.calendar_week_number, c860hqput21a4 0 SH TopSalesWithinQ 20210628 065314 20 .01030 .03586 .00000 .00134 .00000 .00027 161 0 1 BEGIN dbms_application_info.set_module('TopSalesWithinQuarter', null); ahagwf5568kh6 0 SH TopSalesWithinQ 20210628 065333 19 .00421 .01326 .00000 .00271 .00000 .00116 31 0 1 BEGIN dbms_application_info.set_module('SalesCubeByMonth', null); END; fy8v6kg0spv9f 0 SH SalesByQuarterC 20210628 065321 34 .00534 .00699 .00000 .00051 .00000 .00053 83 0 1 BEGIN dbms_application_info.set_module('SalesByWeekCountry', null); EN 7p3m5a493ys3v 0 SH SalesRollupByWe 20210628 065332 28 .00259 .00537 .00000 .00060 .00005 .00024 32 0 1 BEGIN dbms_application_info.set_module('SalesRollupByMonth', null); EN 23bdv7p1xf88s 0 SH SalesByQuarterC 20210628 065336 36 .00183 .00300 .00000 .00015 .00002 .00036 17 0 1 BEGIN dbms_application_info.set_module('SalesMovingAverage', null); EN dxqdy7uch79qn 0 SH SalesByWeekCoun 20210628 065331 40 .00041 .00079 .00000 .00018 .00012 .00019 2 0 1 BEGIN dbms_application_info.set_module('SalesByQuarterCountry', null); bph8vd0ct58yz 0 SH SalesRollupByMo 20210628 065329 7 .00273 .00326 .00000 .00014 .00000 .00012 26 0 1 BEGIN dbms_application_info.set_module('TopSalesWithinWeek', null); EN 96uvdds9xf676 0 SH SalesByWeekCoun 20210628 065122 10 .00113 .00150 .00000 .00013 .00000 .00012 6 0 1 BEGIN dbms_application_info.set_module('SalesCubeByWeek', null); END; 10 rows selected. |
참고로 아래는 Audo DOP 적용 전후 AWR Report는 아래와 같이 Wait Event 상황이다. User I/O는 사라지고, Parallel 관련 Wait Event가 늘어난 것을 볼 수 있다.
[Auto DOP 적용 전]
[Auto DOP 적용 후]
<END>