使用BenchmarkSQL 测试Oracle 12c TPC-C 性能

使用BenchmarkSQL测试一下Oracle 12c的TPC-C性能，同时对比一下PostgreSQL 9.5的性能。
测试机用的是FusionIO卡，24核的CPU，192G内存。

12c安装请参考
http://blog.163.com/digoal@126/blog/static/1638770402015112281556942/
http://blog.163.com/digoal@126/blog/static/163877040201511232138735/

下载benchmarksql
http://sourceforge.net/projects/benchmarksql/

下载安装 JDK7
http://www.oracle.com/technetwork/cn/java/javase/downloads/jdk7-downloads-1880260.html
wget http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.rpm
rpm -ivh jdk-7u79-linux-x64.rpm

检查包安装位置(使用rpm安装时也可以直接指定位置)
rpm -ql jdk
...
/usr/java/jdk1.7.0_79/bin/java
...

配置JAVA环境变量，将ORACLE jdbc驱动拷贝到benchmark/run目录
$  cp ~/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc6.jar   benchmarksql/run/
$  cp ~/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc7.jar   benchmarksql/run/

$  export JAVA_HOME=/usr/java/jdk1.7.0_79
$  export PATH=$JAVA_HOME/bin:$PATH
$  export CLASSPATH=./ojdbc6.jar:./ojdbc7.jar:$CLASSPATH

修改runSQL.sh，加上$CLASSPATH，否则会报oracle驱动CLASS不存在的错误。
$  vi runSQL.sh
myCP="../lib/postgresql-9.3-1101.jdbc41.jar"
myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"

myOPTS="-Dprop=$1"
myOPTS="$myOPTS -DcommandFile=$2"

java -cp .:$myCP:$CLASSPATH $myOPTS ExecJDBC

修改props.ora
$  vi props.ora
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:orcl12c
user=benchmarksql
password=benchmarksql

创建数据库用户
$  sqlplus "/ as sysdba"
SQL> create user benchmarksql identified by "benchmarksql";
SQL> grant dba,connect to benchmarksql;
SQL> alter user benchmarksql default tablespace users;

设置ORACLE 批量提交参数
SQL> alter system set commit_write='batch,nowait';

扩展表空间，防止空间不够。(1000个warehouse够用了）。
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users01.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users02.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users03.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users04.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users05.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users06.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users07.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users08.dbf' size 31G;
SQL>  alter tablespace users add datafile '/disk1/digoal/oradata/users09.dbf' size 31G;

修改建表SQL的ORACLE兼容性：
$  vi sqlTableCreates
create sequence benchmarksql.hist_id_seq;

create table benchmarksql.history (
  hist_id  integer default benchmarksql.hist_id_seq.nextval primary key,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
);

创建表
$  cd benchmarksql/run
$  ./runSQL.sh props.pg sqlTableCreates

导入数据
$  nohup ./runLoader.sh props.ora numWarehouses 1000 >./load.log 2>&1 &
单位为10万。1000将写入一亿记录。

创建索引
$  ./runSQL.sh props.ora sqlIndexCreates

benchmark 测试
修改runBenchmark.sh
$   vi runBenchmark.sh
java -cp .:$CLASSPATH:../lib/postgresql-9.3-1101.jdbc41.jar:../lib/log4j-1.2.17.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 jTPCC
修改props.ora，设置比例。
$ vi props.ora
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:orcl12c
user=benchmarksql
password=benchmarksql

warehouses=1000
terminals=20
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=300000
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=0

//The following five values must add up to 100
newOrderWeight=40
paymentWeight=36
orderStatusWeight=8
deliveryWeight=8
stockLevelWeight=8

$ ./runBenchmark.sh props.ora
测试结果后续给出。

[其他]
清数据
./runSQL.sh props.pg sqlTableTruncates
删表
./runSQL.sh props.pg sqlTableDrops