Generate load with Oracle

I am an Oracle DBA – it should not come as a surprise that I felt the need to generate load with the DB: plsql_loop.ksh script was born.
This method will allow us to gather metrics from the OS and the DB.
In this post I will show you a simplified version, just enough to hint to the direction we are going.

The script will report CPU used by the Oracle sessions and the number of transactions they did during execution.

# my oracle process ran for 10 seconds, spent 9.39 seconds of the time on CPU, utilization is 94%
plsql_loop.ksh 10
Run for [sec] 10, SESS_CPU 9.39, SESS_CPU_PER_SEC .94, tx_per_sec 138.3

Metrics collected:
– SESS_CPU, v$mystat ‘CPU used by this session’
– SESS_CPU_PER_SEC CPU, used by the DB each second
– tx_per_sec, the number of pl/sql loops executed while the hog ran

I am double checking CPU utilization numbers with mpstat.
My assumption is that the logical CPUs only used by my hogs. This way CPU used by the Oracle process is the same as CPU utilization from mpstat for the logical CPU.

# mpstat
08:14:15 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle
08:14:30 PM all 98.98 0.00 0.81 0.00 0.00 0.20 0.00 0.00 0.00

An even more accurate way would be to use ‘pprof’ to gather process level utilization; we will get to that later.

Summary for number of transactions per second as we execute more concurrent hogs:

hogs	tx_per_sec	delta	tx-s thread	delta	resp tx [ms]	delta
1	91  		1.00 	91  		1.00 	10.99  		1.00
2	120  		1.32 	60  		-0.34 	16.67  		1.52
4	200  		2.20 	50  		-0.45 	20.00  		1.82

More hogs produce more transactions. This is possible since more threads were active resulting in increased core throughput. At the same time each transaction took longer to finish, response time grew from 10.99 msec (single thread, SMT1) to 20 msec when we ran 4 threads (SMT4).

Who cares?
I do…it is possible that my CPU heavy sql is finishing 1.52 or even 1.82 times slower…depending on what SMT level the POWER7 processor was using at the time the sql was executing.

Next I will show you theoretical CPU utilization numbers for both core and thread.