[Oracle] uses open source tools to implement its own tpc-c

Source: Internet
Author: User
Tags benchmark command line config contains implement versions oracle database sqlplus
[Oracle] How to use the Open Source Tools to implement Your Own Tpc-c

Author: fenng
Date: Mar (v0.13)
Site: Http://www.dbanotes.net

Introduction to this article

Describes how to use open source tools to implement TPC-C in an Oracle database environment. It is valuable for DBAs, developers, and BI implementations.

Introduction description

Imagine a situation in which:

You are in charge of a company's OLTP type of database project, facing the problem of database server selection: How to select Hardware to meet the business requirements of the product environment? Is this something that can be decided by a "pat on the head"?

Undeniably, hardware selection is an art, choose the right hardware, can save a lot of money for the company, choose not to meet the business needs of the server products, not only spent the injustice, but also to the future business impact.

Perhaps everyone will first think of the reference server tpc-c information. Mention of Tpc-c, may be a lot of people feel both familiar and unfamiliar, because often hear people talk about, but the specific ask down, can say clearly not how many people, and, from everyone's mouth is not the same. So what is tpc/tpc-c and what does all this mean?

What is TPC?

TPC (Transaction processing performance Council, transaction Processing Performance Committee) is a non-profit organization whose official site is in http://www.tpc.org. Its purpose is to develop standard specifications for business application benchmark programs (Benchmark), provide performance and price metrics, and manage the release of test results. In the industry is extremely authoritative.

The standard specification for TPC's benchmark program (Standard specification) is open to the public and can be obtained at the official site, but TPC does not provide code for the benchmark program. Any software or hardware manufacturer or other tester can optimally build a system of its own to be tested according to the standard specifications. To ensure the objectivity of the test results, the tester (usually the manufacturer) must submit a complete set of reports to TPC (full disclosure), including the detailed configuration of the system under test, the classification price and the total price including five years maintenance cost. The report must be verified by the TPC authorized Auditor (TPC itself does not audit).

What is TPMC?

First of all, TPC-C,TPC-C is the benchmark program for online transaction processing (on-line transaction processing, OLTP). Tpc-c simulates a wholesaler's cargo management environment. Tpc-c uses three performance and price metrics, where performance is measured by tpc-c throughput, in units of TPMC. TPM is the abbreviation of transactions per minute; C refers to the C reference program in TPC. It is defined as the number of new orders processed by the system per minute.

With this in mind, you might think: Our company's PC server is completely saved by itself, how to tpc-c measure? Also refer to the information on the TPC official site? Do not forget that the test results published on the tpc.org are the results of the major manufacturers in accordance with their own system to fully optimize after the result. For example, TPC announced the value of the new Ferrari sports car after the careful inspection and maintenance of professional technicians, and then ran out of the special runway speed value, and our situation may be Ferrari (or even Xiali) to drive on the rural dirt road, comparable to a large discount.

Some friends will think: then we can according to their own situation, take a stopwatch in the "Country soil Road" on the test to see AH. The significance is also obvious, is not more reference value to our actual environment? Yes, that's the purpose of this article: let's use the open Source tool to implement our own Oracle database TPC-C.

Hammerora-the Open Source Oracle Load Test Tool

First of all, introduce Hammerora. As you can see from the creator's title description of the tool, Hammerora is an Oracle database load testing tool. This tool can be used on versions of Oracle 8i, 9i, and 10g.
It is currently available on Linux/unix and Windows. The preliminary implementation of the Cross-platform. Hammerora is completely written in TCL/TK and released in the form of a GPL copyright. The writer is Steve Shaw.
The homepage of the project is in: http://sourceforge.net/projects/hammerora/.

Figure 1:hammerora's logo

Hammerora's design is to parse Oracle's trace files and convert them into ORATCL programs, and then "Replay" the user's transactions concurrently with multiple users in an Oracle database. We know that Oracle's trace file can record the user's operations to the database. This hammerora can guarantee the real simulation of the user's environment.

Hammerora was originally used for learning purposes: the expectation is to simulate a certain number of users of the database. Such a small family based learning development system can be closer to the product environment. The tool has grown into a stress test for an Oracle database-from small databases to large, RAC-based clusters.

Tcl language has been used to provide high-performance and OCI integration of Oracle databases without recompiling the generated programs. Each generated program has strict TCL/ORATCL compatibility and can be run under the Hammerora command line shell. The power and flexibility of TCL means that the potential functionality is endless. Tcl threads to implement multiple concurrent connections to an Oracle database.

Hammerora contains Oracle stored procedures and TCL driver scripts to run TPC-C benchmark programs. However, we first build a database that conforms to the TPC-C specification. In this way, we need to use ORABM.


ORABM is Geoff Ingram (high-performance Oracle:proven Methods for
Achieving Optimum, author of performance and availability, builds a package that contains a set of SQL scripts and several command-line programs. The ORABM Data Loader (orabmload) builds a set of data that complies with the TPC-C standard for testing. The ORABM package and source code can be http://www.linxcel.co.uk/orabm/from its homepage.

The operating mechanism of ORABM

The operating mechanism of ORABM is clearly stated in Geoff Ingram, which is translated as follows: ORABM works by running the number of user-executed database transactions in each user-specified concurrent database session. The transaction passes ORABM in orabm_ mode Serverside_stress stored procedures run.
For each concurrent session, Orabm_serverside_stress runs the number of transactions specified under the ORABM command line and returns the TPS value of the session at the completion of the sampling interval. To ensure that all concurrent sessions handle transactions during the sampling interval, the TPS value includes only 80 of the middle of the transaction %, namely: the initial 10% and the last 10% will be ignored.

More detailed information can refer to the contents of http://www.linxcel.co.uk/orabm/.

Installation steps

1 Installing Oracle OCI Libraries

For Oracle 9i, if this component is not installed, you may want to rerun Runinstall and select Oracle Call interfaces in the Oracle 9i Client products tree.

Oracle OCI libraries should be installed by default for Oracle 10g.

2 Installing TCL


Rm-f Config.cache


Make clean


Make install

3 Installing TK


Rm-f Config.cache

./configure--with-tcl=. /.. /tcl8.4.5/unix--enable-threads--enable-shared

Make clean


Make install

4 Installing TCL Threads


Rm-f Config.cache

Sh.. /configure--enable-threads--enable-shared

Make clean


Make install

5 Installation ORATCL

For 10g, to edit the configuration file, add the following section to the Found_oracle=0 line near line 1728:

echo "Checking for Oracle 10.0 style toplevel (libclient10.a)" 1>&6
echo "configure:1730:checking for Oracle 10.0 style toplevel (libclient10.a)" >&5
For f in $oracle _directory $oratest 1 $oratest 2 $oratest 3 $oracle 4/opt/oracle/u/oracle/usr/oracle/usr/local/oracle; Todo
echo "$ac _t" "Looking for lib/libclient10.a in $f" 1>&6
If Test-r "$f/lib/libclient10.a"; Then
Ora= "$f/lib"
Oracle_home= $f
echo "$ac _t" "Setting ORA to $ORA" 1>&6

Export Oracle_home=your_oracle_home_directory

Cd/usr/local/src/oratcl-4-1-branch/unix rm-f Config.cache

Sh.. /configure--with-tcl=. /.. /tcl8.4.5/unix--with-oracle-directory= $ORACLE _home--with-oracle-version=9 (or OR or 8!)--enable-threads- Enable-shared
Note: The red section is for adjustments that need to be made for different database versions.

VI makefile– all the-rpath to do-wl,-rpath,

Make clean


Make install

Sqlplus orabm/orabm @delivery_sp. sql
Sqlplus orabm/orabm @new_order_sp. sql.
Sqlplus orabm/orabm @ostat_sp. sql
Sqlplus orabm/orabm @payment_sp. sql
Sqlplus orabm/orabm @slev_sp. sql

Step action
1 Create ORABM User (use tools to do default table space, temp table space is temporary table space)
Sqlplus system/pwd @orabm_user


Create a table

Sqlplus system/pwd @orabm_tab


Loading data

$orabmload Warehouses 1


Create an index

Sqlplus system/pwd @orabm_ind


Analysis Tables and indexes

Sqlplus system/pwd @orabm_analyze


Create a stress test pl/sql process

Sqlplus system/pwd @orabm_serverside_stress


Buffer the table and index data to the SGA

Sqlplus system/pwd @orabm_cache

Load the Tpc-c.tcl script into the Hammerora (remember to the "change" file filter from *.TRC to *.tcl to see TCL files), find the connect string in the TPC-C.TCL , adjust the connection information for your database user. and adjust the virtual user information.

Test run

Run Hammerora, create the number of users you need to simulate the TPC-C benchmark program (note that adjusting the environment variable to execute the Hammerora program is best done directly with an Oracle user). The following figure is the data from the author's notebook computer [a little bit:) machine performance]:

Figure 2:hammerora Test Results sample

Reference information

Http://hammerora.sourceforge.net/Hammerora's homepage. The current version of the software is 1.15.
Http://www.linxcel.co.uk/orabm/Geoff Ingram maintains a personal site. There is also a very interesting tool on the site orastress. Used for Oracle database stress testing.
Http://www.tpc.org TPC official site.
HTTP://WWW.CNOUG.ORG/VIEWTHREAD.PHP?TID=63 Technology master Rudolflu long ago, orabm This tool has been introduced.
http://www.dbanotes.net/Oracle/Install-Oracle10g-RHEL3.htm installing Oracle 10g on RHEL as 3 step-by-step

The author of this article
Fenng, a US-funded company DBA, spends his spare time in various database-related technical forums and enjoys it. The current focus is on how to effectively build enterprise applications using Oracle databases. There is a little research on Oracle tuning and troubleshooting. Personal technology site: http://www.dbanotes.net/. You can contact him via email dbanotes@gmail.com.

Original source


Back Page

All articles (by fenng) are licensed under a Creative Commons.
I would welcome any feedback. Please send questions, comments or corrections to dbanotes@gmail.com

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.