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<<!
execute statspack.snap;
select
name,
snap_id,
to_char(snap_time,' dd Mon YYYY HH24:mi:ss')
from
stats\$snapshot,
v\$database
where
snap_id > (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 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).
 |
To learn more and
get working scripts, I highly
recommend the books "Oracle
Benchmarking" for more details on conducting and interpreting Oracle
performance benchmarks.
For more on understanding Oracle tuning, see my masterwork "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
|
Here are my related notes on transactions per second:
