In this post I show you how easy it is to install a 3rd party application like Swingbench (credits to Dominic Giles) on a Compute Instance within the Oracle Cloud and connect to an Autonomous Transaction Processing Database.
From the Cloud Infrastructure Console menu select Compute > Instances
Press [Create Instance]
Press [Create Instance]
The instance will now get provisioned. After some minutes you will notice the status changes from Provisioning to Running
Connect to the newly created Compute Instance using the default user opc
Reference: http://www.dominicgiles.com/blog/files/category-swingbench.html
Robs-MacBook-Pro:.ssh rzoeteweij$ ssh opc@130.61.120.117 -i /Users/rzoeteweij/.ssh/id_rsa The authenticity of host '130.61.120.117 (130.61.120.117)' can't be established. ECDSA key fingerprint is SHA256:u4oqHV70YZoDV9CAih8AORkgxQ3CLvZZRTQroOPfc8k. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '130.61.120.117' (ECDSA) to the list of known hosts. [opc@swingbench-client ~]$ [opc@swingbench-client ~]$ sudo yum makecache fast
Now install Java JDK 8
[opc@swingbench-client ~]$ sudo yum install java-1.8.0-openjdk-headless.x86_64 [opc@swingbench-client ~]$ java -version openjdk version "1.8.0_191" OpenJDK Runtime Environment (build 1.8.0_191-b12) OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)
Next, we install the swingbench code
[opc@swingbench-client ~]$ curl http://www.dominicgiles.com/swingbench/swingbenchlatest.zip -o swingbench.zip [opc@swingbench-client ~]$ unzip swinbench.zip [opc@swingbench-client ~]$ ls -al total 23348 drwx------. 6 opc opc 4096 Nov 2 08:20 . drwxr-xr-x. 3 root root 17 Nov 1 14:35 .. -rw-r--r--. 1 opc opc 18 Jan 26 2018 .bash_logout -rw-r--r--. 1 opc opc 193 Jan 26 2018 .bash_profile -rw-r--r--. 1 opc opc 231 Jan 26 2018 .bashrc drwxrwxr-x. 3 opc opc 18 Nov 2 07:47 .cache drwxrwxr-x. 3 opc opc 18 Nov 2 07:47 .config drwx------. 2 opc opc 29 Nov 1 14:35 .ssh drwx------. 12 opc opc 4096 Sep 6 17:45 swingbench -rw-rw-r--. 1 opc opc 23885962 Nov 2 08:18 swingbench.zip
The wallet file we have created from database DBATPD01 needs to be copied to the swingbench_client server
Robs-MacBook-Pro:admin rzoeteweij$ scp Wallet_DBATPD01.zip opc@130.61.80.167:/home/opc/swingbench Wallet_DBATPD01.zip 100% 19KB 220.1KB/s 00:00 Robs-MacBook-Pro:admin rzoeteweij$
Install workload schema into DBATPD01
[opc@swingbench-client bin]$ ./oewizard -cf /home/opc/swingbench/Wallet_DBATPD01.zip \ > -cs dbatpd01_medium \ > -ts DATA \ > -dbap <DB Admin Password> \ > -dba admin \ > -u soe \ > -p <SOE Password to be created> \ > -async_off \ > -scale 5 \ > -hashpart \ > -create \ > -cl \ > -v SwingBench Wizard Author : Dominic Giles Version : 2.6.0.1090 Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml Connecting to : jdbc:oracle:thin:@dbatpd01_medium Connected Running script ../sql/soedgcreateuser.sql Script completed in 0 hour(s) 0 minute(s) 10 second(s) 813 millisecond(s) Starting run Starting script ../sql/soedgdrop2.sql Script completed in 0 hour(s) 0 minute(s) 8 second(s) 159 millisecond(s) Starting script ../sql/soedgcreatetableshash2.sql Script completed in 0 hour(s) 4 minute(s) 8 second(s) 376 millisecond(s) Starting script ../sql/soedgviews.sql Script completed in 0 hour(s) 0 minute(s) 5 second(s) 138 millisecond(s) Starting script ../sql/soedgsqlset.sql Script completed in 0 hour(s) 0 minute(s) 30 second(s) 979 millisecond(s) Inserting data into table ADDRESSES_5624999 ... Script completed in 0 hour(s) 0 minute(s) 1 second(s) 395 millisecond(s) ============================================ | Datagenerator Run Stats | ============================================ Connection Time 0:00:00.004 Data Generation Time 1:02:33.445 DDL Creation Time 0:21:37.988 Total Run Time 1:24:11.439 Rows Inserted per sec 16,122 Data Generated (MB) per sec 1.3 Actual Rows Generated 61,608,289 Commits Completed 3,116 Batch Updates Completed 308,078 Connecting to : jdbc:oracle:thin:@dbatpd01_medium Connected Post Creation Validation Report =============================== The schema appears to have been created successfully. Valid Objects ============= Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS' Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX' Valid Views : 'PRODUCTS','PRODUCT_PRICES' Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ' Valid Code : 'ORDERENTRY' Schema Created
While viewing the Database activity during the creation of the SOE schema we see the following:
Next, we are doing some checks on the created schema
[opc@swingbench-client bin]$ ./sbutil -soe -cf /home/opc/swingbench/Wallet_DBATPD01.zip -cs dbatpd01_medium -u soe -p <SOE Schema user password> -val The Order Entry Schema appears to be valid. -------------------------------------------------- |Object Type | Valid| Invalid| Missing| -------------------------------------------------- |Table | 10| 0| 0| |Index | 26| 0| 0| |Sequence | 5| 0| 0| |View | 2| 0| 0| |Code | 1| 0| 0| --------------------------------------------------
Collect statistics for SOE schema
[opc@swingbench-client bin]$ ./sbutil -soe -cf /home/opc/swingbench/Wallet_DBATPD01.zip -cs dbatpd01_medium -u soe -p <SOE Schema user password> -stats Collecting statistics for the schema Collected statistics in : 0:01:52.533
Run a workload
Adjust swingbench configuration to load the users on in a sensible fashion (i.e. to not exceed the login rate)
sed -i -e 's/<LogonGroupCount>1<\/LogonGroupCount>/<LogonGroupCount>5<\/LogonGroupCount>/' \ -e 's/<LogonDelay>0<\/LogonDelay>/<LogonDelay>300<\/LogonDelay>/' \ -e 's/<WaitTillAllLogon>true<\/WaitTillAllLogon>/<WaitTillAllLogon>false<\/WaitTillAllLogon>/' \ ../configs/SOE_Server_Side_V2.xml [opc@swingbench-client bin]$ ./charbench -c ../configs/SOE_Server_Side_V2.xml \ > -cf /home/opc/swingbench/Wallet_DBATPD01.zip \ > -cs dbatpd01_low \ > -u soe \ > -p <SOE Schema user password> \ > -v users,tpm,tps,vresp \ > -intermin 0 \ > -intermax 0 \ > -min 0 \ > -max 0 \ > -uc 20 \ > -di SQ,WQ,WA \ > -rt 0:0.30 Author : Dominic Giles Version : 2.6.0.1090 Results will be written to results.xml. Hit Return to Terminate Run... Time Users TPM TPS NCR UCD BP OP PO BO SQ WQ WA 11:29:45 [0/20] 0 0 0 0 0 0 0 0 0 0 0 11:29:47 [0/20] 0 0 0 0 0 0 0 0 0 0 0 11:29:48 [0/20] 0 0 0 0 0 0 0 0 0 0 0 11:29:49 [20/20] 0 0 0 0 0 0 0 0 0 0 0 11:29:50 [20/20] 0 0 0 0 1140 0 0 0 0 0 0 11:29:51 [20/20] 8 8 0 0 1921 1510 1696 0 0 0 0 11:29:52 [20/20] 27 19 859 154 658 1668 1696 0 0 0 0 11:29:53 [20/20] 40 13 1139 409 66 1737 842 1628 0 0 0 11:29:54 [20/20] 62 22 2154 381 982 2079 842 1628 0 0 0 11:29:55 [20/20] 78 16 2154 381 310 1315 752 937 0 0 0 11:29:56 [20/20] 96 18 2172 381 1308 2282 752 845 0 0 0 11:29:57 [20/20] 108 12 2225 381 828 1989 752 845 0 0 0 11:29:58 [20/20] 124 16 1064 1124 455 734 752 1433 0 0 0 11:29:59 [20/20] 143 19 2681 1124 88 2268 981 950 0 0 0 11:30:00 [20/20] 168 25 805 301 274 1539 805 1023 0 0 0 11:30:01 [20/20] 186 18 805 916 1229 1368 805 935 0 0 0 11:30:02 [20/20] 203 17 1941 428 453 701 1468 1253 0 0 0 11:30:03 [20/20] 228 25 1559 428 333 1275 1468 618 0 0 0 11:30:04 [20/20] 260 32 569 262 449 623 509 754 0 0 0 11:30:05 [20/20] 281 21 1678 197 153 1844 509 754 0 0 0 11:30:06 [20/20] 306 25 1587 648 991 960 509 757 0 0 0 11:30:07 [20/20] 329 23 1221 648 1273 2093 1110 445 0 0 0 11:30:08 [20/20] 344 15 1221 608 1341 1374 1110 608 0 0 0 11:30:09 [20/20] 348 4 2070 608 924 1374 1832 528 0 0 0 11:30:10 [20/20] 369 21 1359 608 785 1466 1007 1233 0 0 0 11:30:11 [20/20] 392 23 969 568 996 1674 1007 1227 0 0 0 11:30:12 [20/20] 411 19 683 440 401 1807 1007 1024 0 0 0 11:30:13 [20/20] 432 21 1126 388 185 274 475 323 0 0 0 11:30:14 [20/20] 460 28 974 311 741 1147 475 1217 0 0 0 11:30:15 [20/20] 476 16 1337 311 730 1390 475 1217 0 0 0 11:30:16 [20/20] 484 8 2069 813 378 2847 475 1217 0 0 0 11:30:17 [0/20] 499 15 2069 813 378 2847 475 1217 0 0 0 Completed Run.
Additional DB statistics using dbtimemonitoring
On my desktop I have downloaded the dbtimemonitoring from http://www.dominicgiles.com/swingbench/dbtimemonitor16Aug2018.zip
I have changed the xml in the dbtimemonitoring
Robs-MacBook-Pro:bin rzoeteweij$ cat databases.xml <?xml version = '1.0' encoding = 'UTF-8'?> <WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor"> <!-- You can add as many Monitored Databases as you like. --> <!-- They will connect to the server over the thin jdbc connection stack.--> <!-- Passwords will be encrypted from clear text after the file is read.--> <!-- To change/update a password simply change all the text between the <Password> tags</Password>--> <!-- --> <MonitoredDatabase> <ConnectString>adb.eu-frankfurt-1.oraclecloud.com:1522/aa8lbv43ewwd_dbatpd01_low.atp.oraclecloud.com</ConnectString> <Comment>Test ERP System</Comment> <Username>Admin</Username> <Password>Password</Password> </MonitoredDatabase> </WaitMonitor>
Run the dbtimemonitor tool
./dbtimemonitor -u admin -p Password -cs dbatpd01_medium -cf /Users/rzoeteweij/instantclient_12_2/network/admin/Wallet_DBATPD01.zip
Run swingbench from my laptop
./swingbench -c ../configs/SOE_Server_Side_V2.xml \ -cf /Users/rzoeteweij/instantclient_12_2/network/admin/Wallet_DBATPD01.zip \ -cs dbatpd01_low \ -u soe \ -p Password \ -intermin 0 \ -intermax 0 \ -min 0 \ -max 0 \ -uc 20 \ -di SQ,WQ,WA
Disclaimer
Please notice that all statements made by me and information provided on this blog are mine and not necessarily those of Oracle Corporation.
