"처음 사용자를 위한 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에서 In-memory 적용 후 성능을 확인하기 위한 테스트를 진행한다. 여기서는RAC PDB에서 통계정보를 다시 생성했다. (선택 사항임)
| SQL> exec dbms_stats.gather_schema_stats(ownname=>'SH',estimate_percent=>100,degree=>4); PL/SQL procedure successfully completed. |
성능테스트를 수행하기 전에 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 05:09:46 [0/8] 0 0 ... 05:19:45 [8/8] 0 0 Saved results to results00003.xml 05:19:46 [8/8] 0 0 Completed Run. |
결과 확인은 “Saved results to results00003.xml”과 같이 명시된 대로 results00003.xml 파일을 참조한다. 전체 완료된 트랜젝션수는 10, 평균 초당 트랜젝션0.02로 나타나고 있다.
| [root@inst-public bin]# cat results00003.xml | grep TotalCompletedTransactions <TotalCompletedTransactions>10</TotalCompletedTransactions> [root@inst-public bin]# cat results00003.xml | grep AverageTransactionsPerSecond <AverageTransactionsPerSecond>0.02</AverageTransactionsPerSecond> |
테스트 대상 RAC PDB에 sys 유저로 접속해서 앞서 수행된 분석 쿼리 성능을 확인해 본다. 여기서는 elapsed time 기준 top 10 를 확인했다. 각 쿼리들은 대부분 리턴하는 로우 건수가 많지 않음에도 불구하고 약 RAC node#1의 경우, 330 – 430 초대의 elapsed time을 나타내고 있다. RAC node#2 의 경우에는 세션이 들어와서 쿼리가 실행되지 않았다. 1번 RAC 노드에서만 쿼리가 실행되었다.
| 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 ---------------------------------------------------------------------- 8kv5jpc6ptwgk 0 SH TopSalesWithinW 20210628 052125 1 71.32133 426.36851 .00069 8.21834 325.02100 .00606 694897 685299 20 SELECT * FROM (SELECT times.Calendar_Week_Number, customers.cus 3zhkn815ptjk3 0 SH SalesCubeByMont 20210628 051601 1 64.92932 372.63224 .00368 .00203 285.99314 .00000 693182 692647 20 SELECT channel_desc, calendar_month_desc, countries.country_iso_code, 7x571u8p0r5yz 0 SH SalesByWeekCoun 20210628 051557 1 61.38075 368.92125 .42136 .00000 287.21099 .00732 692949 692647 1 SELECT SUM(amount_sold), t.calendar_year, t.calendar_week_number, 6x8p90qbqt09h 0 SH SalesByQuarterC 20210628 051557 1 62.13524 368.50077 .00259 .00998 285.00126 .01931 693585 692649 5 SELECT SUM(amount_sold), t.calendar_month_desc, t.calendar_week_nu 1p5p7drt7atxd 0 SH TopSalesWithinQ 20210628 052206 1 74.76040 367.24461 .00154 8.32472 261.57869 .01231 692876 684118 16 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu d1sb24k5pmbs0 0 SH SalesByQuarterC 20210628 051555 1 61.85179 366.30989 .00422 .00000 284.53019 .00835 692947 692648 5 SELECT SUM(amount_sold), t.calendar_month_desc, t.calendar_week_nu 6rqp21ru3gwbu 0 SH ProductSalesRol 20210628 051551 1 55.36745 362.98676 .00378 .01699 289.09619 .00662 693274 692647 18 SELECT calendar_year, calendar_quarter_number, calendar_month_numb 5pryx6c45y2z1 0 SH SalesCubeByWeek 20210628 051549 1 53.77277 361.14114 .00326 .00347 290.63250 .00982 693083 692646 16 SELECT channel_desc, calendar_week_number, countries.country_iso_code, 0ruc7gc7fjatz 0 SH SalesCubeByMont 20210628 051549 1 52.97894 360.89444 .00327 .00000 290.36611 .00000 692870 692647 20 SELECT channel_desc, calendar_month_desc, countries.country_iso_code, cufzfqxamt3zc 0 SH SalesRollupByMo 20210628 052117 1 58.91010 326.47368 .00165 .00000 241.38857 .00000 692944 692646 20 SELECT channels.channel_desc, calendar_month_desc, countries.c 10 rows selected. # RAC node#2 no rows selected |
<END>
