http://www.dba-oracle.com/m_transactions_per_second.htm
Oracle Tips by Burleson Consulting
Question: I need to measure the number of transactions per second on my Oracle database. Where can I find the Oracle transactions per second performance metric?
Answer: First, let’s define transactions per second (TPS), and see how it’s different for different server components. To Oracle, a transaction is a SQL statement:
Disk transactions per second – To a disk, the number of transactions per second is the number of I/O requests (usually a block). See my notes for using the iostat utility for details on measuring disk transactions per second.
OS transactions per second – To the Operating system, a transaction is the creation and destruction of a „process“ (in UNIX/Linux) or a „thread“ (in Windows). Note that a database transaction (a SQL statement) may have many associated OS processes (Oracle background processes).
Oracle transactions per second – The Oracle documentation has the „Transactions/Sec“ defined as the number of commits (successful SQL) and rollbacks (aborted SQL) per second. In sum, we measure SQL statements per second. You can find „transactions per second“ in the load profile section of any AWR or STATSPACK report. I devote a whole chapter to monitoring Oracle performance in my book „Oracle Tuning: The Definitive Reference“, and I have script to help gather monitoring metrics.
The simple way to estimate your average transactions per second is to run a STATSPACK or AWR elapsed-time report for exactly one hour (during peak activity) and extract the total transactions. To make the elapsed time exactly one-hour, write a script to sleep 3,600 seconds between snapshots, or use the crontab utility to take STATSPACK snapshots every hour.
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d‘:’`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d‘:’`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
echo „Please enter the number of seconds between snapshots.“
read elapsed
$ORACLE_HOME/bin/sqlplus perfstat/perfstat<<!
execute statspack.snap;
exit
!
sleep $elapsed
$ORACLE_HOME/bin/sqlplus perfstat/perfstat< (select max(snap_id)-2 from stats$snapshot)
;
See Code deport for full script
You will find „transactions per second“ in the load profile section of any AWR or STATSPACK report.
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
————— —————
Redo size: 2,189.01 2,376.67
Logical reads: 5,467.24 5,935.95
Block changes: 10.59 11.50
Physical reads: 1,953.94 2,121.45
Physical writes: 20.35 22.10
User calls: 131.08 142.32
Parses: 28.80 31.27
Hard parses: 0.30 0.33
Sorts: 3.98 4.32
Logons: 0.21 0.22
Executes: 28.60 31.05
Transactions: 0.92
To compute transactions per second, simply compute transactions_per_second = transactions_per_hour/60/60 for average transactions per second. I also recommend the statspackanalyzer tool for interpreting STATSPACK and AWR output, a great get-started guide for the beginner.
Note that a busy OLTP database will have these transaction per second volumes:
High transactions per second eBay, Amazon 1,000 -10,000 TPS
Medium transactions per second International web application 100 – 1,000 TPS
Low transactions per second Small internal OLTP 10 – 100 TPS
Transactions per second in Oracle tests
Here are some Oracle transaction per second benchmark notes. Note that by using the „divide and conquer“ approach of horizontal scaling (using RAC or Streams, and adding new servers, as needed), the number of transactions per second is only constrained by the processing power of the server:
Transactions per second OS CPU’s RAM Server Cost
25,000 trans per second Solaris 32 1,000 gigabytes $6-8 million
25,000 trans per second Windows 16 115 gigabytes $1m+
Transactions per second in benchmark testing
In benchmarking, transactions per second is a great load metric.
In this simple example there is a dramatic increase in response time as we pass 11 transactions per second. Once we have “broken the systems (by exceeding the maximum transaction rate), we then correlate the increase with various internal Oracle metrics (wait events from ASH, AWR reports) and external metrics (CPU, RAM, disk and network enqueues).
