Information Technology | Databases » Christian Antognini - Free Load Testing Tools for Oracle Database

Source: http://www.doksinet Free Load Testing Tools for Oracle Database 16 Nov 2016, DOAG Konferenz, Nurnberg (D) Christian Antognini @ChrisAntognini BASEL BERN BRUGG DÜSSELDORF HAMBURG COPENHAGEN LAUSANNE FRANKFURT A.M FREIBURG IBR GENEVA MUNICH STUTTGART VIENNA ZURICH Source: http://www.doksinet Our company. Trivadis is a market leader in IT consulting, system integration, solution engineering and the provision of IT services focusing on and technologies in Switzerland, Germany, Austria and Denmark. We offer our services in the following strategic business fields: OPERATION 2 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet With over 600 specialists and IT experts in your region. COPENHAGEN 14 Trivadis branches and more than 600 employees HAMBURG 200 Service Level Agreements Over 4,000 training participants Research and development budget: CHF 5.0 million DÜSSELDORF FRANKFURT Financially self-supporting

and sustainably profitable STUTTGART FREIBURG BASEL BRUGG ZURICH BERN GENEVA 3 LAUSANNE 16/11/2016 MUNICH VIENNA Experience from more than 1,900 projects per year at over 800 customers Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Technology on its own wont help you. You need to know how to use it properly. 4 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet @ChrisAntognini Senior principal consultant, trainer and partner at Trivadis christian.antognini@trivadiscom http://antognini.ch Focus: get the most out of Oracle Database Logical and physical database design Query optimizer Application performance management Author of Troubleshooting Oracle Performance (Apress, 2008/14) OakTable Network, Oracle ACE Director 5 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Agenda 1. Introduction 2. Free Load

Testing Tools I Use 3. Summary 6 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Introduction 7 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Common Situations Leading to Database Load Generation Perform usability and functionality checks of tools or utilities that require a non-trivial load to be carried out Assess the performance of a (new) platform or storage subsystem Verify whether a set of SQL statements executed on a specific environment and/or configuration fulfils the expected performance requirements 8 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Golden Rule Use the right tool for the right job! 9 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Free Load Testing Tools in My Toolbox Swingbench The Silly Little

Oracle Benchmark (SLOB) Apache JMeter 10 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Swingbench 11 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What’s Swingbench? Load generator designed to stress test Oracle Database Includes four configurable synthetic benchmarks OrderEntry, SalesHistory, CallingCircle and StressTest Extensible with user-defined transactions (StressTest provides an example) Consists of several CLI and GUI utilities Developed by Dominic Giles Available at http://www.dominicgilescom/swingbenchhtml 12 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What Can Swingbench Do? Carry out, with minimal effort, all necessary operations to run one of the four provided benchmarks Create test schema (incl. data population) Generate load Store key performance indicators about

test run 13 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Swingbench Key Components – Setup Three wizards, that also expose a CLI, are available: OrderEntry, SalesHistory and CallingCircle XML file config oewizard shwizard ccwizard data StressTest doesn’t have a wizard because it requires no setup 14 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Swingbench Key Components – Load Generation load swingbench minibench charbench KPI XML file results2pdf config XML file 15 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? PDF file Source: http://www.doksinet Swingbench: Demo 16 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Swingbench: Example of Utilization (1) Purpose Benchmark Observe the impact of a vMotion started while a database

server is subject to a non-trivial load 17 16/11/2016 OrderEntry, scale 10 900 transactions/second Free Load Testing Tools for Oracle Database – Which One Do I Use? 18 16/11/2016 Time Free Load Testing Tools for Oracle Database – Which One Do I Use? 14:02:33 14:02:23 14:02:13 14:02:03 14:01:53 14:01:43 14:01:33 14:01:23 14:01:13 14:01:02 14:00:48 14:00:38 14:00:28 14:00:18 14:00:08 13:59:58 13:59:48 13:59:38 13:59:28 13:59:18 13:59:08 13:58:58 13:58:48 13:58:38 13:58:28 13:58:18 13:58:08 AAS Source: http://www.doksinet Swingbench: Example of Utilization (2) vMotion took 79 seconds 80 60 40 20 0 Source: http://www.doksinet The Silly Little Oracle Benchmark (SLOB) 19 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What’s SLOB? Platform performance and stability-testing framework that makes Oracle Database execute SQL statements Synthetic benchmark Consists of a few scripts

Developed by Kevin Closson Available at https://kevinclosson.net/slob/ 20 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What Can SLOB Do? Create one or several test schemas (incl. data population) Generate load Random PIO and/or LIO Read only or read write Lite or heavy redo generation Store DB as well as OS key performance indicators about test run 21 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet SLOB Key Components – Setup slob.conf 22 16/11/2016 config setup.sh data Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet SLOB Key Components – Load Generation load AWR reports runit.sh config iostat mpstat vmstat slob.conf 23 16/11/2016 awr info.sh Free Load Testing Tools for Oracle Database – Which One Do I Use? text file Source: http://www.doksinet SLOB: Demo 24 16/11/2016 Free

Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet SLOB: Example of Utilization (1) Purpose Benchmark Find out the maximum number of random IOPS (and their average latency) that a storage subsystem can sustain 25 16/11/2016 100GB Read-only PIO/LIO Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet SLOB: Example of Utilization (2) 103299 93546 db file sequential reads 78 72563 60 44 64 96 128 160 33 31 2.9 48 2.8 48 Sessions 26 16/11/2016 db file parallel reads 101258 Latency in Milliseconds Physical Reads per Second 103718 Free Load Testing Tools for Oracle Database – Which One Do I Use? 64 4.0 96 Sessions 5.2 128 6.3 160 Source: http://www.doksinet Apache JMeter 27 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What’s JMeter? Application designed to load test functional behavior and

measure performance No out-of-the box benchmark available  DIY Consists of several CLI and GUI utilities Open source project supported by a number of contributors Available at http://jmeter.apacheorg/ 28 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet What Can JMeter Do? Many things! For the intent of this presentation: Execute SQL statements through JDBC Verify whether the executions return the expected output and/or in the expected time But it’s “only” a framework it requires time to “code” a benchmark Use real schema with data Use the SQL statements to be verified – Bind variables can be either generated or provided as input Store key performance indicators 29 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet SLOB Key Components – Load Generation load KPI front end GUI/CLI config log test plan 30 16/11/2016 Free Load Testing Tools

for Oracle Database – Which One Do I Use? user defined output text file Source: http://www.doksinet JMeter: Demo 31 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet JMeter: Example of Utilization (1) Purpose Benchmark Verify that a set of queries performs acceptably under load Verify that the DB server supports the expected load 32 16/11/2016 33 distinct queries, each with each own set of bind variable values, randomly picked out 100 queries/minute Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet 4000 3500 3000 2500 2000 1500 1000 500 0 407 408 503 114 303 111 115 112 308 101 301 117 204 113 118 105 306 116 505 120 201 504 304 305 307 404 403 401 501 203 202 502 302 Average Elapsed Time [ms] JMeter: Example of Utilization (2) Query 33 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet JMeter:

Example of Utilization (3) AAS during a period of 13 minutes 34 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Summary 35 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Comparison Can generates load for Synthetic benchmark User-defined benchmark Effort to implement Swingbench SLOB JMeter Oracle Database Oracle Database any DB with JDBC support   minimal GUI  CLI for automation with scripts  Distributed load generation  36 16/11/2016  () Free Load Testing Tools for Oracle Database – Which One Do I Use? minimal low  high     Source: http://www.doksinet Tool of Choice for Common Situations Leading to Database Load Generation Perform usability and functionality checks of tools or utilities that require a non-trivial load to be carried out  Swingbench Assess the performance of a (new) platform

or storage subsystem  SLOB Verify whether a set of SQL statements executed on a specific environment and/or configuration fulfils the expected performance requirements  Apache JMeter 37 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Summary No single tool can cover all requirements If used correctly, Swingbench and SLOB are simple and effective tools To run a specific set of SQL statements don’t spend time build your own script/tool, use JMeter 38 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use? Source: http://www.doksinet Questions and Answers Christian Antognini Senior Principal Consultant christian.antognini@trivadiscom @ChrisAntognini 39 16/11/2016 Free Load Testing Tools for Oracle Database – Which One Do I Use?