#!/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
See code depot for full script
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:
and include the URL for the page.
