用orabm进行CPU压力测试

用orabm测试oracle服务器的tps值
1、orabm介绍
   orabm是一个开源的oracle性能测试工具，包含了一套sql脚本和几个命令行程序
   下载地址：http://www.linxcel.co.uk/orabm/orabm.tar

2、oracle@linux-0ygf:~/orabm> ls
_vti_cnf   orabm.pdf
bin        orabm_files
install    src
orabm.htm
注意：缺省的orabm测试用户会建立tools表空间，如果不用该表空间，编辑orabm\install\orabm_user.sql.该表空间最好在150mb以上。

3、安装orabm
 a create the orabm user (assumes tools tablespace, temp temporary tablespace) sqlplus system/pwd @orabm_user
 b create the tables sqlplus system/pwd @orabm_tab
 c load the data $ orabmload warehouses 1
 d create the indexes sqlplus system/pwd @orabm_ind
 e analyze the tables and indexes sqlplus system/pwd @orabm_analyze
 f create the stress-test pl/sql procedures sqlplus system/pwd @orabm_serverside_stress
 g cache the table and index data in the sga sqlplus system/pwd @orabm_cache

a、create the orabm user
oracle@linux-0ygf:~/orabm/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 17:13:22 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start orabm_user.sql
grant succeeded.

user altered.

user altered.

revoke succeeded.

b、create the tables

oracle@linux-0ygf:~/orabm/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 17:15:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start orabm_tab.sql

table created.

table created.

table created.

table created.

table created.

table created.

table created.

table created.

table created.

c、load the data
在装载数据的时候出现下面的错误，查看是因为系统是64位导致，编辑/etc/profile.d/oracle.sh将oracle---lib设置为lib32即可

oracle@linux-0ygf:~/orabm/bin/linux> ./orabmload.10g.linux Warehouse 1
./orabmload.10g.linux: error while loading shared libraries: libclntsh.so.10.1: wrong ELF class: ELFCLASS64

oracle@linux-0ygf:~/orabm/bin/linux> ./orabmload.10g.linux warehouse 1

Connected to ORACLE as user: ORABM
using timestamp=20120320163204
TPCC Data Load Started...
Loading Item
.................... 20000
.................... 40000
.................... 60000
.................... 80000
.................... 100000
Item Done.
Loading Warehouse
Loading Stock Wid=1
.................... 20000
.................... 40000
.................... 60000
.................... 80000
.................... 100000
 Stock Done.
Loading District
Loading Customer for DID=1, WID=1
...Customer Done.
Loading Customer for DID=2, WID=1
...Customer Done.
Loading Customer for DID=3, WID=1
...Customer Done.
Loading Customer for DID=4, WID=1
...Customer Done.
Loading Customer for DID=5, WID=1
...Customer Done.
Loading Customer for DID=6, WID=1
...Customer Done.
Loading Customer for DID=7, WID=1
...Customer Done.
Loading Customer for DID=8, WID=1
...Customer Done.
Loading Customer for DID=9, WID=1
...Customer Done.
Loading Customer for DID=10, WID=1
...Customer Done.
Loading Orders for D=1, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=2, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=3, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=4, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=5, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=6, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=7, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=8, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=9, W= 1
. 1000
. 2000
. 3000
Orders Done.
Loading Orders for D=10, W= 1
. 1000
. 2000
. 3000
Orders Done.

...DATA LOADING COMPLETED SUCCESSFULLY.
oracle@linux-0ygf:~/orabm/bin/linux> cd
oracle@linux-0ygf:~> cd orabm/install/

e analyze the tables
此处没有建立索引，跟后面建立索引做对比
oracle@linux-0ygf:~/orabm/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 16:33:41 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start orabm_analyze.sql

Session altered.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

Table analyzed.

analyze index ORABM.CUSTOMER_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.CUSTOMER_I2 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.DISTRICT_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.ITEM_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.NEW_ORDER_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.ORDERS_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.ORDERS_I2 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.ORDER_LINE_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.STOCK_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

analyze index ORABM.WAREHOUSE_I1 compute statistics
                    *
ERROR at line 1:
ORA-01418: specified index does not exist

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

oracle@linux-0ygf:~/orabm/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 16:35:15 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start orabm_serverside_stress.sql

Procedure created.

Procedure created.

Procedure created.

Procedure created.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

oracle@linux-0ygf:~/orabm/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 20 16:36:00 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start orabm_cache.sql

Table altered.

  COUNT(*)
----------
     30000

Table altered.

  COUNT(*)
----------
        10

Table altered.

  COUNT(*)
----------
     30000

Table altered.

  COUNT(*)
----------
    299630

Table altered.

  COUNT(*)
----------
    100000

  COUNT(*)
----------
     30000

  COUNT(*)
----------
     30000

  COUNT(*)
----------
    100000

  COUNT(*)
----------
    299630

  COUNT(*)
----------
     30000

  COUNT(*)
----------
     30000

  COUNT(*)
----------
    100000

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@linux-0ygf:~/orabm/install>

orabm的原理使用:

How Orabm Works

Orabm works by running a user-specified number of database transactions in each of a user-specified number of concurrent database sessions.
Orabm通过在用户指定的并发下运行指定数量的事务来测试系统性能.
The transactions are executed by the ORABM_SERVERSIDE_STRESS stored procedure, under the schema ORABM.
事务通过执行orabm用户的ORABM_SERVERSIDE_STRESS过程来实现.

For each concurrent session, ORABM_SERVERSIDE_STRESS runs the number of transactions specified on the orabm command line, and returns the transactions per second (TPS) value for that session during the sampling interval on completion.
对于每个并发session,ORABM_SERVERSIDE_STRESS运行在orabm命令行指定数量的事务.
并返回在完成所有任务的采样间隔内每秒的事务数量.
To ensure that all concurrent sessions are processing transactions during the sampling interval, the TPS value only includes results from the middle 80% of transactions: the first 10% and last 10% are ignored.
为了保证准确性,TPS值只取中间的80%数据,头尾的10%都将被忽略.

The transactions are loosely based on the TPC-C Order-Status and Stock-Level transactions, using a predefined distribution of transactions.
事务是松散的,基于标准的TPC-C测试的订单、库存事务模型,使用预定义的事务分布.
The transaction split, which is based on data returned by the DBMS_RANDOM package, should be:
事务基于DBMS_RANDOM包的返回值进行分割,应该是:

Stock-Level:Order-by-Customer-Name:Order-by-Customer-Id
50% :30% :20%

The string returned by ORABM_SERVERSIDE_STRESS includes the transaction split during the test, to ensure that the transaction distribution is correct, subject to random fluctuations e.g.:
为了确保事务分布的正确性,ORABM_SERVERSIDE_STRESS的输出包含事务分割比例,输出结果具有随机波动,例如:

oracle@linux-0ygf:~/orabm/bin/linux> ./orabm.10g.linux 1,2,6,10 10000
---begin sess=1 txn=10000 ORACLE_SID=orcl Tue Mar 20 16:40:58 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=283 tps=28 sl=3950(49.4%) n=2430(30.4%) i=1619(20.2%) end=200312-16:46:52
---end - Tue Mar 20 16:46:52 2012
---begin sess=2 txn=10000 ORACLE_SID=orcl Tue Mar 20 16:46:52 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=298 tps=27 sl=3982(49.8%) n=2377(29.7%) i=1640(20.5%) end=200312-16:53:05
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=301 tps=27 sl=4020(50.3%) n=2386(29.8%) i=1593(19.9%) end=200312-16:53:07
---end - Tue Mar 20 16:53:07 2012
---begin sess=6 txn=10000 ORACLE_SID=orcl Tue Mar 20 16:53:07 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=494 tps=16 sl=3958(49.5%) n=2439(30.5%) i=1602(20%) end=200312-17:03:41
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=514 tps=16 sl=3934(49.2%) n=2502(31.3%) i=1563(19.5%) end=200312-17:03:57
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=508 tps=16 sl=4017(50.2%) n=2404(30.1%) i=1578(19.7%) end=200312-17:03:56
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=533 tps=15 sl=3943(49.3%) n=2420(30.3%) i=1636(20.5%) end=200312-17:04:02
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=540 tps=15 sl=3959(49.5%) n=2450(30.6%) i=1590(19.9%) end=200312-17:04:09
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=562 tps=14 sl=4032(50.4%) n=2384(29.8%) i=1583(19.8%) end=200312-17:04:14
---end - Tue Mar 20 17:04:14 2012
---begin sess=10 txn=10000 ORACLE_SID=orcl Tue Mar 20 17:04:14 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=776 tps=10 sl=4043(50.5%) n=2377(29.7%) i=1579(19.7%) end=200312-17:20:32
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=805 tps=10 sl=3962(49.5%) n=2422(30.3%) i=1615(20.2%) end=200312-17:20:50
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=822 tps=10 sl=3966(49.6%) n=2450(30.6%) i=1583(19.8%) end=200312-17:20:54
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=826 tps=10 sl=3955(49.4%) n=2424(30.3%) i=1620(20.3%) end=200312-17:21:10
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=845 tps=9 sl=3979(49.7%) n=2466(30.8%) i=1554(19.4%) end=200312-17:21:22
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=849 tps=9 sl=3969(49.6%) n=2428(30.4%) i=1602(20%) end=200312-17:21:27
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=838 tps=10 sl=4022(50.3%) n=2389(29.9%) i=1588(19.9%) end=200312-17:21:29
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=862 tps=9 sl=3989(49.9%) n=2402(30%) i=1608(20.1%) end=200312-17:21:31
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=846 tps=9 sl=4018(50.2%) n=2431(30.4%) i=1550(19.4%) end=200312-17:21:32
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=884 tps=9 sl=4014(50.2%) n=2385(29.8%) i=1600(20%) end=200312-17:21:46
---end - Tue Mar 20 17:21:46 2012
（没建立索引下的tps)
oracle@linux-0ygf:~/orabm/bin> ./orabm_tps.sh linux/orabm.orcl.log
ORACLE_SID=orcl sess=1 tps=28
ORACLE_SID=orcl sess=2 tps=54
ORACLE_SID=orcl sess=6 tps=92
ORACLE_SID=orcl sess=10 tps=95

建立索引：
SQL> !ls
_vti_cnf           orabm_ind.sql                orabm_tab.sql
orabm_analyze.sql  orabm_query_cache.sql        orabm_tab_rm.sql
orabm_cache.sql    orabm_serverside_stress.sql  orabm_user.sql

SQL> start orabm_ind.sql

Session altered.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

分析表和索引

oracle@linux-0ygf:~/orabm/bin/linux> ./orabm.10g.linux 1,2,6,10 10000
---begin sess=1 txn=10000 ORACLE_SID=orcl Tue Mar 20 17:34:11 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=5 tps=1600 sl=4073(50.9%) n=2386(29.8%) i=1540(19.3%) end=200312-17:34:18
---end - Tue Mar 20 17:34:18 2012
---begin sess=2 txn=10000 ORACLE_SID=orcl Tue Mar 20 17:34:18 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=5 tps=1600 sl=3922(49%) n=2490(31.1%) i=1587(19.8%) end=200312-17:34:24
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=5 tps=1600 sl=3931(49.1%) n=2429(30.4%) i=1639(20.5%) end=200312-17:34:24
---end - Tue Mar 20 17:34:24 2012
---begin sess=6 txn=10000 ORACLE_SID=orcl Tue Mar 20 17:34:24 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=6 tps=1333 sl=3995(49.9%) n=2360(29.5%) i=1644(20.6%) end=200312-17:34:31
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=7 tps=1143 sl=4013(50.2%) n=2406(30.1%) i=1580(19.8%) end=200312-17:34:33
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=7 tps=1143 sl=3999(50%) n=2382(29.8%) i=1618(20.2%) end=200312-17:34:33
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=7 tps=1143 sl=4000(50%) n=2450(30.6%) i=1549(19.4%) end=200312-17:34:33
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=9 tps=889 sl=4029(50.4%) n=2422(30.3%) i=1548(19.4%) end=200312-17:34:34
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=9 tps=889 sl=4064(50.8%) n=2350(29.4%) i=1585(19.8%) end=200312-17:34:37
---end - Tue Mar 20 17:34:37 2012
---begin sess=10 txn=10000 ORACLE_SID=orcl Tue Mar 20 17:34:37 2012
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=7 tps=1143 sl=3950(49.4%) n=2407(30.1%) i=1642(20.5%) end=200312-17:34:45
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=8 tps=1000 sl=4093(51.2%) n=2358(29.5%) i=1548(19.4%) end=200312-17:34:46
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=8 tps=1000 sl=3930(49.1%) n=2379(29.7%) i=1690(21.1%) end=200312-17:34:49
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=10 tps=800 sl=3995(49.9%) n=2412(30.2%) i=1592(19.9%) end=200312-17:34:50
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=11 tps=727 sl=4027(50.3%) n=2373(29.7%) i=1599(20%) end=200312-17:34:50
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=6 tps=1333 sl=4082(51%) n=2381(29.8%) i=1536(19.2%) end=200312-17:34:50
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=9 tps=889 sl=4028(50.4%) n=2380(29.8%) i=1591(19.9%) end=200312-17:34:50
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=8 tps=1000 sl=4010(50.1%) n=2408(30.1%) i=1581(19.8%) end=200312-17:34:51
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=15 tps=533 sl=4070(50.9%) n=2406(30.1%) i=1523(19%) end=200312-17:34:52
ORCL txn(all)=10000 xn(sam)=7999 t(sam)=10 tps=800 sl=3987(49.8%) n=2396(30%) i=1616(20.2%) end=200312-17:34:53
---end - Tue Mar 20 17:34:53 2012

oracle@linux-0ygf:~/orabm/bin/linux> ./orabm.10g.linux 1,2,6,8,10,15 200000
。。。。。。。。
oracle@linux-0ygf:~/orabm/bin> ./orabm_tps.sh  linux/orabm.orcl.log
此处是未建立索引的情况,后面三个为建立索引的情况
ORACLE_SID=orcl sess=1 tps=28
ORACLE_SID=orcl sess=2 tps=54
ORACLE_SID=orcl sess=6 tps=92
ORACLE_SID=orcl sess=10 tps=95

ORACLE_SID=orcl sess=1 tps=1600
ORACLE_SID=orcl sess=2 tps=3200
ORACLE_SID=orcl sess=6 tps=6540
ORACLE_SID=orcl sess=10 tps=9225

ORACLE_SID=orcl sess=1 tps=2000
ORACLE_SID=orcl sess=2 tps=3200
ORACLE_SID=orcl sess=6 tps=7109
ORACLE_SID=orcl sess=10 tps=7003

ORACLE_SID=orcl sess=1 tps=1798
ORACLE_SID=orcl sess=2 tps=3596
ORACLE_SID=orcl sess=6 tps=6471
ORACLE_SID=orcl sess=8 tps=6221
ORACLE_SID=orcl sess=10 tps=6806
ORACLE_SID=orcl sess=15 tps=7059

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779291/viewspace-719080/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779291/viewspace-719080/