"처음 사용자를 위한 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 테스트
테스트 환경 준비
Single Instance Oracle Database 준비
아래와 같은 내용으로 OCI에서 Single Instance Oracle Database 서비스를 생성함.
- Shape: VM.Standard2.2
- Oracle Database Software Edition: Enterprise Edition Extreme Performance
- DB System Version: 21.1.0.0.0
데이터베이스 VM에서 작업. OCI에서 Oracle Database 서비스로 생성한 Oracle Linux VM 의 경우, 아래와 같이 eneble되어 있는 YUM repository없으며, firewalld, telnet 등의 유틸리티가 설치되어 있지 않음. 테스트 구성의 편의를 위해 아래 방법으로 YUM repository 다운로드와 설치를 함. (선택사항. Iptables 명령은 수행됨)
| [root@singledb opc]# hostname singledb [root@singledb opc]# whoami root [root@singledb opc]# yum install telnet -y Loaded plugins: versionlock There are no enabled repos. Run "yum repolist all" to see the repos you have. To enable custom repositories: yum-config-manager --enable <repo> |
YUM repository 다운로드 및 확인
| [root@singledb opc]# wget -nv https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/oci_dbaas_ol7repo -O /etc/yum.repos.d/ol7.repo 2021-06-21 08:37:01 URL:https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/oci_dbaas_ol7repo [957/957] -> "/etc/yum.repos.d/ol7.repo" [1] [root@singledb opc]# wget -nv https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/versionlock_ol7.list -O /etc/yum/pluginconf.d/versionlock.list 2021-06-21 08:37:13 URL:https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/versionlock_ol7.list [14660/14660] -> "/etc/yum/pluginconf.d/versionlock.list" [1] [root@singledb opc]# ls -l /etc/yum.repos.d/ total 4 -rw-r--r-- 1 root root 957 Sep 23 2019 ol7.repo [root@singledb opc]# yum repolist all Loaded plugins: versionlock ... Excluding 47 updates due to versionlock (use "yum versionlock status" to show them) repo id repo name status ol7_UEKR4/x86_64 Latest Unbreakable Enterprise Kernel Release 4 for Oracle Linux 7Server (x86_64) enabled: 198+18 ol7_UEKR4_archive/x86_64 Unbreakable Enterprise Kernel Release 4 for Oracle Linux 7Server (x86_64) - Archive enabled: 1259+163 ol7_latest/x86_64 Oracle Linux 7Server Latest (x86_64) enabled: 22766+960 ol7_latest_archive/x86_64 Oracle Linux 7Server Latest (x86_64) - Archive enabled: 24575+1211 repolist: 48798 |
telnet, firewalld 설치 및 startup, enable.
| [root@singledb opc]# yum install telnet -y Loaded plugins: versionlock [root@singledb opc]# yum install firewalld firewall-config -y Loaded plugins: versionlock ... Complete! [root@singledb opc]# systemctl start firewalld.service [root@singledb opc]# systemctl enable firewalld.service [root@singledb opc]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: active (running) since Mon 2021-06-21 11:08:15 UTC; 34s ago Docs: man:firewalld(1) Main PID: 9184 (firewalld) CGroup: /system.slice/firewalld.service └─9184 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid ... Hint: Some lines were ellipsized, use -l to show in full. |
스키마 생성 및 성능테스트 시, Swingbench VM에서 데이터베이스로의 연결에 사용될 1521 포트를 개방.
| [root@singledb opc]# firewall-cmd --permanent --zone=public --add-port=1521/tcp success [root@singledb opc]# firewall-cmd --reload success [root@singledb opc]# firewall-cmd --list-all public target: default icmp-block-inversion: no interfaces: sources: services: dhcpv6-client ssh ports: 1521/tcp protocols: masquerade: no forward-ports: source-ports: icmp-blocks: rich rules: |
데이터베이스 VM에서 oracle 유저로 PDB로 easy connect 연결이 되는 것을 확인. lsnrctl status 에서 출력되는 PDB의 서비스명으로 접속
| [oracle@singledb ~]$ whoami oracle [oracle@singledb ~]$ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 22-JUN-2021 09:29:57 … Services Summary... Service "+APX" has 1 instance(s). Instance "+APX1", status READY, has 1 handler(s) for this service... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_RECO" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "DB0621XDB.snpublic.cluster1.oraclevcn.com" has 1 instance(s). Instance "DB0621", status READY, has 1 handler(s) for this service... Service "DB0621_iad3k9.snpublic.cluster1.oraclevcn.com" has 1 instance(s). Instance "DB0621", status READY, has 2 handler(s) for this service... Service "c54478ae60122b1ce053031e000a0a07.snpublic.cluster1.oraclevcn.com" has 1 instance(s). Instance "DB0621", status READY, has 2 handler(s) for this service... Service "db0621_pdb1.snpublic.cluster1.oraclevcn.com" has 1 instance(s). Instance "DB0621", status READY, has 2 handler(s) for this service... The command completed successfully [oracle@singledb ~]$ sqlplus system/Xxxxxxxxxxxxx@singledb:1521/db0621_pdb1.snpublic.cluster1.oraclevcn.com ... SQL> show con_name CON_NAME ------------------------------ DB0621_PDB1 |
Swingbench 환경 구성
아래와 같은 내용으로 OCI에서 Swingbench 구성용 VM을 생성함.
- Shape: VM.Standard.E4.Flex
- OCPU Count: 2
- Network Bandwidth (Gbps): 2
- Memory (GB): 32
- Private IP Address: 10.0.30.2
Swingbench VM에서 작업. Swingbench를 설치하기 위해서는 먼저 JDK가 필요하다. 아래와 같이 1.8버전의 Open JDK를 설치했다.
| [root@inst-public opc]# hostname inst-public [root@inst-public opc]# whoami root [root@inst-public opc]# yum install java-1.8.0-openjdk -y Loaded plugins: langpacks, ulninfo ... Complete! [root@inst-public opc]# java -version openjdk version "1.8.0_292" OpenJDK Runtime Environment (build 1.8.0_292-b10) OpenJDK 64-Bit Server VM (build 25.292-b10, mixed mode) |
Swingbench 다운로드 및 압축해제
| [root@inst-public opc]# wget http://www.dominicgiles.com/swingbench/swingbenchlatest.zip -O swingbench.zip ... 2021-06-21 07:30:26 (10.7 MB/s) - ‘swingbench.zip’ saved [29278500/29278500] [root@inst-public opc]# unzip swingbench.zip Archive: swingbench.zip ... inflating: swingbench/sql/soedgcreateuser.sql |
이제 설치한 Swingbench 를 이용해서 앞서 생성한 오라클 데이터베이스에 분석업무 스키마 “SH”를 생성한다. 여기서는 8 scale로 생성했다. . PDB 서비스명으로 연결하는 것에 유의한다.
| [root@inst-public opc]# cd swingbench/bin [root@inst-public bin]# ./shwizard -create -scale 8 -cs //10.0.30.3:1521/db0621_pdb1.snpublic.cluster1.oraclevcn.com -dbap Xxxxxxxxxxxxx -ts SH -tc 4 -u sh -p Xxxxxxxxxxxxx -cl -df +DATA/DB0621_IAD3K9/C54478AE60122B1CE053031E000A0A07/DATAFILE/sh.dbf SwingBench Wizard Author : Dominic Giles Version : 2.6.0.1135 Running in Lights Out Mode using config file : ../wizardconfigs/shwizard.xml ============================================ | Datagenerator Run Stats | ============================================ Connection Time 0:00:00.005 Data Generation Time 0:11:30.082 DDL Creation Time 0:13:31.691 Total Run Time 0:25:01.780 Rows Inserted per sec 206,915 Data Generated (MB) per sec 16.8 Actual Rows Generated 142,784,056 Commits Completed 7,467 Batch Updates Completed 714,250 Post Creation Validation Report =============================== The schema appears to have been created successfully. Valid Objects ============= Valid Tables : 'COUNTRIES','CHANNELS','CUSTOMERS','PROMOTIONS','PRODUCTS','SUPPLEMENTARY_DEMOGRAPHICS','SALES','TIMES' Valid Indexes : 'CUSTOMERS_MARITAL_BIX','CUSTOMERS_YOB_BIX','CUSTOMERS_GENDER_BIX','PRODUCTS_PROD_STATUS_BIX','PRODUCTS_PROD_SUBCAT_IX','PRODUCTS_PROD_CAT_IX','SALES_PROD_BIX','SALES_CUST_BIX','SALES_CHANNEL_BIX','SALES_PROMO_BIX' Valid Views : Valid Sequences : Valid Code : Schema Created |
PDB로 접속해서 스키마가 생성되었는지 확인한다. 여기서는 통계정보도 새로 생성했다.
| SQL> show con_name CON_NAME ------------------------------ DB0621_PDB1 SQL> select nvl(segment_type,'TOTAL') as segment_type, round(sum(bytes)/1024/1024,0) as mb from dba_segments where owner = 'SH' group by segment_type; SEGMENT_TYPE MB ------------------------- ---------- TABLE 10958 INDEX 2039 SQL> exec dbms_stats.gather_schema_stats(ownname=>'SH',estimate_percent=>100,degree=>4); PL/SQL procedure successfully completed. SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss'; Session altered. SQL> select min(last_analyzed),max(last_analyzed) from dba_tables where owner='SH'; MIN(LAST_ANALYZED MAX(LAST_ANALYZED ----------------- ----------------- 20210623 02:49:16 20210623 02:59:02 |
Swingbench 분석업무 스키마 테스트 수행
이제 In-memory가 적용되기 전 성능을 확인하기 위한 테스트를 진행한다.
성능테스트를 수행하기 전에 Swingbench에서 수행한 쿼리 성능만 조회되도록하기 위해 테스트 대상 PDB에 sys 유저로 접속해서 shared_pool을 flush했다.
| SQL> alter system flush shared_pool; System altered. |
Swingbench VM으로 돌아와서 테스트는 “charbench” 유틸리티를 이용해서 8 유저로 10분간 수행한다. Charbench 실행을 위해서는 config 파일 생성이 필요하다. 기존 config 파일을 복사해서 이용한다.
| [root@inst-public bin]# cp /home/opc/swingbench/configs/Sales_History.xml /home/opc/swingbench/configs/shconfig.xml [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 04:06:01 [0/8] 0 0 ... Saved results to results.xml 04:16:02 [8/8] 1 0 Completed Run. |
결과 확인은 “Results will be written to results.xml”과 같이 명시된 대로 result.xml 파일을 참조한다. 여기서는 전체 완료된 트랜젝션수와 평균 초당 트랜젝션만 확인했다. 각각 26, 0.04 로 기록되었다.
| [root@inst-public bin]# cat results.xml | grep TotalCompletedTransactions <TotalCompletedTransactions>26</TotalCompletedTransactions> [root@inst-public bin]# cat results.xml | grep AverageTransactionsPerSecond <AverageTransactionsPerSecond>0.04</AverageTransactionsPerSecond> |
테스트 대상 PDB에 sys 유저로 접속해서 앞서 수행된 분석 쿼리 성능을 확인해 본다. 여기서는 elapsed time 기준 top 10 를 확인했다. 각 쿼리들은 대부분 리턴하는 로우 건수가 많지 않음에도 불구하고 약 170 – 790 초 대의 elapsed time을 나타내고 있다.
| 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 ---------------------------------------------------------------------- 09mgsjsatycjh 0 SH TopSalesWithinQ 20210623 031935 1 82.56067 787.00152 .00000 .00000 698.96449 .00000 929392 850092 0 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 1qr4ps69f36jy 0 SH TopSalesWithinQ 20210623 032108 1 76.19221 664.02605 .00000 .00000 584.14331 .00437 1272604 801550 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu cmkygw8kypnpb 0 SH TopSalesWithinQ 20210623 032120 1 73.73152 468.12872 .00000 .00000 389.92115 .00355 1272604 689052 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 334b1zwrkp669 0 SH SalesRollupByMo 20210623 031330 1 38.87605 406.26161 .00000 .00000 362.72027 .00000 2295265 420813 20 SELECT channels.channel_desc, calendar_month_desc, countries.c 56nj9ptzakm56 0 SH TopSalesWithinQ 20210623 032112 1 69.46539 395.28086 .00000 .00000 320.36599 .00459 1272604 699827 15 SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cu 3p5jgr90zjy38 0 SH SalesMovingAver 20210623 031237 1 65.93757 239.90865 .10583 .00000 168.46093 .00000 926933 925073 20 SELECT t.time_id, to_char(SUM(amount_sold), '9,999,999,999') AS sa 5pqzhudw3b4sw 0 SH SalesCubeByMont 20210623 031003 1 23.99316 214.91711 .00000 .00000 187.35547 .00000 916423 379590 20 SELECT channel_desc, calendar_month_desc, countries.country_iso_code, 2uja3rfs5nqx0 0 SH SalesByWeekCoun 20210623 031227 1 21.35365 212.67062 .00000 .00000 188.80149 .00537 553108 395633 1 SELECT SUM(amount_sold), t.calendar_year, t.calendar_week_number, 9232brt0szqjb 0 SH SalesMovingAver 20210623 031829 1 72.10090 179.47512 .07333 .00000 98.82772 .00000 926141 925073 20 SELECT t.time_id, to_char(SUM(amount_sold), '9,999,999,999') AS sa bqfchggauyjma 0 SH SalesByWeekCoun 20210623 031408 1 19.67675 171.44742 .00000 .00000 150.17089 .00396 547947 386721 1 SELECT SUM(amount_sold), t.calendar_year, t.calendar_week_number, 10 rows selected. |
<END>
