Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle transactions per second?

Oracle Consulting Tips by Burleson

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
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 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).

 

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:

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

   

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.