研发论坛 讨论主题
葛宏宾109649/user/zte_ltd 昨天 16:16 . 主题: . 用orabm测试oracle服务器的TPS值 . 分类: 测试
用orabm测试oracle服务器的TPS值
1、orabm简介 Orabm是一个开源的oracle性能测试工具,,包含了一套SQL脚本和几个命令行程序。
作者Geoff Ingram,是《High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability》一书的作者。
该工具可以从作者的主页(http://www.dbcool.com/)获得,具体下载地址是http://www.linxcel.co.uk/orabm/orabm.tar
2、安装orabm 下载的orabm是一个tar包,一般可以将该文件上传到oracle服务器上,方便直接运行SQL脚本。 本次测试的环境是:AIX 5.2 ,oracle 9.2.0.1,操作系统用户是oracle
使用orabm的用户必须具有oracle的相关环境变量,本次测试中用的是oracle用户。
(1)解包 $ tar xvf orabm.tar x orabm, 0 bytes, 0 tape blocks x orabm/src, 0 bytes, 0 tape blocks x orabm/src/orabmload.pc, 24200 bytes, 48 tape blocks x orabm/src/orabm.c, 9346 bytes, 19 tape blocks x orabm/src/init.ora, 1442 bytes, 3 tape blocks x orabm/src/BUILD_FROM_SOURCE.txt, 863 bytes, 2 tape blocks x orabm/install, 0 bytes, 0 tape blocks x orabm/install/orabm_analyze.sql, 1068 bytes, 3 tape blocks x orabm/install/orabm_cache.sql, 1116 bytes, 3 tape blocks x orabm/install/orabm_ind.sql, 1124 bytes, 3 tape blocks x orabm/install/orabm_query_cache.sql, 497 bytes, 1 tape blocks x orabm/install/orabm_serverside_stress.sql, 8528 bytes, 17 tape blocks x orabm/install/orabm_tab.sql, 2321 bytes, 5 tape blocks x orabm/install/orabm_tab_rm.sql, 197 bytes, 1 tape blocks x orabm/install/orabm_user.sql, 233 bytes, 1 tape blocks x orabm/bin, 0 bytes, 0 tape blocks x orabm/bin/orabm_tps.awk, 182 bytes, 1 tape blocks x orabm/bin/orabm_tps.sh, 70 bytes, 1 tape blocks x orabm/bin/solaris, 0 bytes, 0 tape blocks x orabm/bin/solaris/orabmload, 85940 bytes, 168 tape blocks x orabm/bin/solaris/orabm, 53140 bytes, 104 tape blocks ......
解包完成以后就可以开始安装了,其实主要就是运行SQL脚本建立测试环境。 安装进度表如下: # Operation Command 1 create the ORABM user (assumes TOOLS tablespace, TEMP temporary tablespace) sqlplus system/pwd @orabm_user 2 create the tables sqlplus system/pwd @orabm_tab 3 load the data $ orabmload Warehouses 1 4 create the indexes sqlplus system/pwd @orabm_ind 5 analyze the tables and indexes sqlplus system/pwd @orabm_analyze 6 create the stress-test PL/SQL procedures sqlplus system/pwd @orabm_serverside_stress 7 cache the table and index data in the SGA sqlplus system/pwd @orabm_cache
(2)建数据库用户和表 对应于安装进度表的第1、2两个步骤
注意:由于所建用户ORABM的缺省表空间是TOOLS,因此最好在安装之前(起码在装载数据之前)将TOOLS表空间扩大到150MB左右。虽然一般TOOLS表空间都设置为AUTOEXTENT,但是还是会影响装载数据的速度,后面的步骤会看到装载的数据是比较大的。
$ ls orabm_analyze.sql orabm_serverside_stress.sql orabm_cache.sql orabm_tab.sql orabm_ind.sql orabm_tab_rm.sql orabm_query_cache.sql orabm_user.sql
$ sqlplus "/ as sysdba" @orabm_user
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 26 14:32:48 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning and OLAP options JServer Release 9.2.0.1.0 - Production User created.
Grant succeeded.
User altered.
User altered.
Revoke succeeded.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 – Production
$ sqlplus "/ as sysdba" @orabm_tab
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 26 14:32:48 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning and OLAP options JServer Release 9.2.0.1.0 - Production
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production
(3)装载数据 对应于安装进度表中的第3个步骤
按照步骤描述,本来这个应该在oracle服务器上运行orabmload来完成,但是程序包中的orabmload程序只有linux、solaris、windows三个平台下的,没有AIX平台下的。 但orabm是开源的程序,在src目录下就是源程序 $ ls -l total 88 -rw-r--r-- 1 oracle dba 863 Nov 07 2002 BUILD_FROM_SOURCE.txt -rw-r--r-- 1 oracle dba 1442 Nov 07 2002 init.ora -rw-r--r-- 1 oracle dba 9346 Nov 07 2002 orabm.c -rw-r--r-- 1 oracle dba 24200 Nov 07 2002 orabmload.pc
其中 orabm.c、orabmload.c 是两个应用程序的源程序,BUILD_FROM_SOURCE.txt 是编译说明。我按照编译说明上的指导,make了一下,但是最后运行程序的时候出错,也许是还有什么编译选项不对吧。下面是运行错误: $ ./orabmload Warehouse 1 exec(): 0509-036 Cannot load program ./orabmload because of the following errors : 0509-150 Dependent module /home/oracle/OraHome1/lib32/libclntsh.a(shr. o) could not be loaded. 0509-124 The program is a discontinued 64-bit object file.
------------后记------------ 后面发现是程序链接的库文件错了,不应该链接 /home/oracle/OraHome1/lib32/ 目录,这是为32位程序准备的,应该链接 /home/oracle/OraHome1/lib/ ,缺省就是64位。 按此修改了一下主机用户oracle的.profile文件,然后重新登录,再编译一下,最后运行是没有问题的。 ------------后记结束------------
还好的是,orabm预先提供windows版本的数据装载程序,在 orabm/bin/windows 目录下就是。 将orabm拷贝或者FTP到windows平台的机器上,配置好oracle连接,即可装载数据了,如下:
E:\temp>set LOCAL=ccbver
E:\temp>orabmload Warehouse 1
Connected to ORACLE as user: ORABM using timestamp=20050126103630 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
成功装载数据,需要的时间可能有点长。
(4)建索引、分析表和索引、建压力测试存储过程、将数据装载到cache 对应于安装进度表中的第4、5、6、7步骤 这几个步骤都是执行SQL脚本,在oracle服务器上执行即可。 $ cd install $ ls orabm_analyze.sql orabm_ind.sql orabm_serverside_stress.sql orabm_tab_rm.sql orabm_cache.sql orabm_query_cache.sql orabm_tab.sql orabm_user.sql $ sqlplus "/ as sysdba" @orabm_ind SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 26 14:32:48 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning and OLAP options JServer Release 9.2.0.1.0 - Production ...... $ sqlplus "/ as sysdba" @ orabm_analyze ........... $ sqlplus "/ as sysdba" @ orabm_serverside_stress ...... $ sqlplus "/ as sysdba" @ orabm_cache ......
这后面执行的SQL脚本都不会有什么问题,记住步骤顺序即可。
至此,orabm的安装已经完成了。
3、测试TPS值 Orabm安装完成以后,就可以开始测试oracle服务器的TPS值了。 同样的,在本次测试中,由于应用程序没有AIX版本,所以只能在windows平台作为oracle客户端来执行测试程序了。
(1)orabm的原理 我们先通过作者主页上的一段话来了解一下orabm的原理。
Orabm works by running a user-specified number of database transactions in each of a user-specified number of concurrent database sessions. Orabm通过在用户指定的并发下运行指定数量的事务来 [1] [2] [3] 下一页 [网络安全]代理服务器的概念及功能介绍 [网页制作]在Dreamweaver中按F12浏览的为什么是远程服务器的… [网络技术]详细介绍通过远程桌面连接控制远程服务器的操作方… [网络技术]服务器的硬件由哪些构成 [系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [常用软件]PB7 连接 Oracle 的配置方法 [Web开发]oracle Export and Import 简介 [Web开发]ADO访问Oracle结果集的心得 [JAVA开发]JDBC+Hibernate将Blob数据写入Oracle [JAVA开发]J2EE应用中与Oracle数据库的连接
|