BenchmarkSQL 测试Oracle 12c TPC-C 性能 (含个人测试结果)
背景
使用BenchmarkSQL测试一下Oracle 12c的TPC-C性能，同时对比一下PostgreSQL 9.5的性能。
测试机：
3 * PCI-E SSD，逻辑卷条带，XFS，数据块对齐，16核开HT，256G内存。
12c安装，配置请参考
《Linux RHEL6 x64 命令行静默安装 Oracle 12c - 1》
《Linux RHEL6 x64 命令行静默安装 Oracle 12c - 2》
下载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
$ vi runLoader.sh
java -cp .:$CLASSPATH:../lib/postgresql-9.3-1101.jdbc41.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 LoadData $2 $3 $4 $5
修改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 异步刷redolog buffer提交参数(类似提交时指定 commit [write [wait|nowait immediate|batch]])
SQL> alter system set commit_write='nowait,immediate';
使用强制软解析
SQL> alter system set cursor_sharing=force;
使用O_DIRECT
SQL> alter system set filesystemio_options=directio scope=spfile;
SQL> alter system set disk_asynch_io=false scope=spfile;
(以上参数 lunar 建议后改为如下)
SQL> alter system set filesystemio_options=setall scope=spfile;
SQL> alter system reset disk_asynch_io scope=spfile;
修改最大连接数，打开游标数。
SQL> alter system set processes=1000 scope=spfile;
SQL> ALTER SYSTEM SET open_cursors=900 SCOPE=BOTH;
alter system set session_cached_cursors=0 scope=spfile;
(以上参数 lunar 建议后改为如下)
SQL> alter system reset session_cached_cursors scope=spfile;
重启数据库.
防止NUMA影响(需重启服务器)：
在grub.conf中加入
numa=off
扩展表空间，防止空间不够。(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;
alter tablespace UNDOTBS1 add datafile '/disk1/digoal/oradata/undotbs1_01.dbf' size 31G;
alter tablespace UNDOTBS1 add datafile '/disk1/digoal/oradata/undotbs1_02.dbf' size 31G;
alter tablespace SYSTEM add datafile '/disk1/digoal/oradata/system_01.dbf' size 31G;
alter tablespace SYSAUX add datafile '/disk1/digoal/oradata/sysaux_01.dbf' size 31G;
alter tablespace TEMP add tempfile '/disk1/digoal/oradata/temp_01.dbf' size 31G;
alter tablespace TEMP add tempfile '/disk1/digoal/oradata/temp_02.dbf' size 31G;
新增redo log file，默认只有3个50MB的redo log，产生大量log file switch (checkpoint incomplete)和log file switch completion等待事件。
select group#,sequence#,bytes,members,status from v$log;
select member from v$logfile;
alter database add logfile group 4 ('/disk1/digoal/oradata/orcl12c/redo04.log') size 2g;
alter database add logfile group 5 ('/disk1/digoal/oradata/orcl12c/redo05.log') size 2g;
alter database add logfile group 6 ('/disk1/digoal/oradata/orcl12c/redo06.log') size 2g;
alter database add logfile group 7 ('/disk1/digoal/oradata/orcl12c/redo07.log') size 2g;
alter database add logfile group 8 ('/disk1/digoal/oradata/orcl12c/redo08.log') size 2g;
alter database add logfile group 9 ('/disk1/digoal/oradata/orcl12c/redo09.log') size 2g;
alter database add logfile group 10 ('/disk1/digoal/oradata/orcl12c/redo10.log') size 2g;
alter database add logfile group 11 ('/disk1/digoal/oradata/orcl12c/redo11.log') size 2g;
alter database add logfile group 12 ('/disk1/digoal/oradata/orcl12c/redo12.log') size 2g;
alter database add logfile group 13 ('/disk1/digoal/oradata/orcl12c/redo13.log') size 2g;
alter database add logfile group 14 ('/disk1/digoal/oradata/orcl12c/redo14.log') size 2g;
alter database add logfile group 15 ('/disk1/digoal/oradata/orcl12c/redo15.log') size 2g;
alter database add logfile group 16 ('/disk1/digoal/oradata/orcl12c/redo16.log') size 2g;
alter database add logfile group 17 ('/disk1/digoal/oradata/orcl12c/redo17.log') size 2g;
alter database add logfile group 18 ('/disk1/digoal/oradata/orcl12c/redo18.log') size 2g;
alter database add logfile group 19 ('/disk1/digoal/oradata/orcl12c/redo19.log') size 2g;
alter database add logfile group 20 ('/disk1/digoal/oradata/orcl12c/redo20.log') size 2g;
alter database add logfile group 21 ('/disk1/digoal/oradata/orcl12c/redo21.log') size 2g;
alter database add logfile group 22 ('/disk1/digoal/oradata/orcl12c/redo22.log') size 2g;
alter database add logfile group 23 ('/disk1/digoal/oradata/orcl12c/redo23.log') size 2g;
alter database add logfile group 24 ('/disk1/digoal/oradata/orcl12c/redo24.log') size 2g;
alter database add logfile group 25 ('/disk1/digoal/oradata/orcl12c/redo25.log') size 2g;
alter database add logfile group 26 ('/disk1/digoal/oradata/orcl12c/redo26.log') size 2g;
alter database add logfile group 27 ('/disk1/digoal/oradata/orcl12c/redo27.log') size 2g;
alter database add logfile group 28 ('/disk1/digoal/oradata/orcl12c/redo28.log') size 2g;
alter database add logfile group 29 ('/disk1/digoal/oradata/orcl12c/redo29.log') size 2g;
alter database add logfile group 30 ('/disk1/digoal/oradata/orcl12c/redo30.log') size 2g;
alter database add logfile group 31 ('/disk1/digoal/oradata/orcl12c/redo31.log') size 2g;
alter database add logfile group 32 ('/disk1/digoal/oradata/orcl12c/redo32.log') size 2g;
alter database add logfile group 33 ('/disk1/digoal/oradata/orcl12c/redo33.log') size 2g;
alter database add logfile group 34 ('/disk1/digoal/oradata/orcl12c/redo34.log') size 2g;
alter database add logfile group 35 ('/disk1/digoal/oradata/orcl12c/redo35.log') size 2g;
alter database add logfile group 36 ('/disk1/digoal/oradata/orcl12c/redo36.log') size 2g;
alter database add logfile group 37 ('/disk1/digoal/oradata/orcl12c/redo37.log') size 2g;
alter database add logfile group 38 ('/disk1/digoal/oradata/orcl12c/redo38.log') size 2g;
alter database add logfile group 39 ('/disk1/digoal/oradata/orcl12c/redo39.log') size 2g;
alter database add logfile group 40 ('/disk1/digoal/oradata/orcl12c/redo40.log') size 2g;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
rm -f /disk1/digoal/oradata/orcl12c/redo01.log
rm -f /disk1/digoal/oradata/orcl12c/redo02.log
rm -f /disk1/digoal/oradata/orcl12c/redo03.log
alter database add logfile group 1 ('/disk1/digoal/oradata/orcl12c/redo01.log') size 2g;
alter database add logfile group 2 ('/disk1/digoal/oradata/orcl12c/redo02.log') size 2g;
alter database add logfile group 3 ('/disk1/digoal/oradata/orcl12c/redo03.log') size 2g;
修改benchmarksql建表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.ora sqlTableCreates
导入数据
$ nohup ./runLoader.sh props.ora numWarehouses 1000 >./load.log 2>&1 &
单位为50万。1000将写入5亿记录。
......
------------- LoadJDBC Statistics --------------------
Start Time = Sat Jan 09 01:41:18 CST 2016
End Time = Sat Jan 09 05:35:35 CST 2016
Run Time = 14057 Seconds
Rows Loaded = 499139685 Rows
Rows Per Second = 35508 Rows/Sec
------------------------------------------------------
修改创建索引的脚本ORACLE兼容性。
删除以下无效SQL
-- select setval('hist_id_seq', (select max(hist_id) + 1 from benchmarksql.history), false);
-- vacuum analyze;
增加
exec dbms_stats.gather_schema_stats( -
ownname => 'BENCHMARKSQL', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 34 -
);
创建索引
$ ./runSQL.sh props.ora sqlIndexCreates
手工修改序列值
SQL> select max(hist_id) + 1 from benchmarksql.history;
MAX(HIST_ID)+1
--------------
30000001
SQL> select benchmarksql.hist_id_seq.nextval from dual;
NEXTVAL
----------
1
SQL> alter sequence benchmarksql.hist_id_seq increment by 30000001;
Sequence altered.
SQL> select hist_id_seq.nextval from dual;
NEXTVAL
----------
30000002
SQL> alter sequence benchmarksql.hist_id_seq increment by 1;
Sequence altered.
SQL> select benchmarksql.hist_id_seq.nextval from dual;
NEXTVAL
----------
30000003
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=96
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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
修改log4j，减少日志打印量。priority改成info，只输出最终结果，不输出产生订单的日志。
$ vi log4j.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>
<appender name="console" class="org.apache.log4j.ConsoleAppender">
<param name="Threshold" value="info"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %5p - %m%n"/>
</layout>
</appender>
<appender name="R" class="org.apache.log4j.rolling.RollingFileAppender">
<param name="Append" value="True" />
<rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy">
<param name="FileNamePattern" value="log/archive/benchmarksql.%d{yyyyMMddHHmm}.log"/>
<param name="ActiveFileName" value="log/benchmarksql.log"/>
</rollingPolicy>
<triggeringPolicy class="org.apache.log4j.rolling.SizeBasedTriggeringPolicy">
<param name="MaxFileSize" value="1000"/>
</triggeringPolicy>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/>
</layout>
<filter class="org.apache.log4j.filter.StringMatchFilter">
<param name="StringToMatch" value ="\n" />
<param name="AcceptOnMatch" value="false" />
</filter>
</appender>
<appender name="E" class="org.apache.log4j.rolling.RollingFileAppender">
<param name="Append" value="True" />
<param name="Threshold" value="warn"/>
<rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy">
<param name="FileNamePattern" value="log/BenchmarkSQLError.%d.log"/>
<param name="ActiveFileName" value="log/BenchmarkSQLError.log"/>
</rollingPolicy>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/>
</layout>
</appender>
<root>
<priority value="info"/>
<appender-ref ref="R"/>
<appender-ref ref="E"/>
</root>
</log4j:configuration>
压测
阶段1
TPM：
$ nohup ./runBenchmark.sh props.ora > ./dev/null 2>./errrun.log &
INFO [2016-01-10 10:35:04.390] Thread-27 Term-00, Measured tpmC (NewOrders) = 92239.6
INFO [2016-01-10 10:35:04.390] Thread-27 Term-00, Measured tpmTOTAL = 230538.34
INFO [2016-01-10 10:35:04.390] Thread-27 Term-00, Session Start = 2016-01-10 10:25:03
INFO [2016-01-10 10:35:04.390] Thread-27 Term-00, Session End = 2016-01-10 10:35:04
INFO [2016-01-10 10:35:04.390] Thread-27 Term-00, Transaction Count = 2306977
主机信息，截取压测第9分钟的数据。
TOP
top - 10:34:25 up 4 days, 1:27, 3 users, load average: 19.52, 18.53, 13.60
Tasks: 775 total, 13 running, 762 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.7%us, 12.1%sy, 0.0%ni, 50.5%id, 0.2%wa, 0.0%hi, 2.4%si, 0.0%st
Mem: 264643396k total, 161935616k used, 102707780k free, 52096k buffers
Swap: 18825200k total, 0k used, 18825200k free, 101708384k cached
iostat -x
avg-cpu: %user %nice %system %iowait %steal %idle
35.27 0.00 15.13 0.26 0.00 49.34
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 406.60 5993.40 4667.20 54110.60 9.18 2.49 0.39 0.04 22.78
dfb 0.00 0.00 401.60 5990.20 4561.60 54016.00 9.16 2.06 0.32 0.03 19.82
dfc 0.00 0.00 406.40 5961.40 4624.00 53787.40 9.17 2.52 0.39 0.03 21.78
dm-0 0.00 0.00 1212.60 14448.40 13849.60 161914.00 11.22 5.66 0.36 0.02 31.28
测试Oracle时，遇到大量(约13万)超出最大打开游标数错误。
ERROR [2016-01-10 10:35:00.349] Thread-20 delivGetCustId() not found! O_ID=3235 O_D_ID=2 O_W_ID=184
ERROR [2016-01-10 10:35:00.349] Thread-20 Term-20, TERMINAL=Term-20 TYPE=DELIVERY COUNT=23819
ERROR [2016-01-10 10:35:00.351] Thread-5 Term-05, TERMINAL=Term-05 TYPE=ORDER-STATUS COUNT=23820
ERROR [2016-01-09 10:14:31.185] Thread-3 Term-03, TERMINAL=Term-03 TYPE=ORDER-STATUS COUNT=2946
ERROR [2016-01-09 10:14:31.190] Thread-3 java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
at jTPCCTerminal.orderStatusTransaction(Unknown Source)
at jTPCCTerminal.executeTransaction(Unknown Source)
at jTPCCTerminal.executeTransactions(Unknown Source)
at jTPCCTerminal.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
数据库没有做过多优化，测试数据仅供参考。
并且使用benchmarksql测试，系统还有大量空闲CPU，IO资源，所以性能应该不止于此。
有兴趣的童鞋可以使用load runner或者sysbench或其他工具再测试一下。
生成AWR：
压测开始前
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
压测开始 到 结束。
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
生成报告：
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
取压测两端的snapshot。
最新AWR截图，（持续更新）
特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。
优化中，期待Oracle更好的表现。
阶段2
benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。
参考
为了突破测试程序的极限，开4个schema，每个schema负责1000个仓库，数据量总共20亿左右。
每个测试程序对付一个schema。
终端数保持一致，每个测试程序开32个终端，一共128个终端。
$ ll
drwxr-xr-x 7 digoal users 4.0K Jan 10 13:24 benchmarksql-4.1.0_oracle01
drwxr-xr-x 7 digoal users 4.0K Jan 10 13:11 benchmarksql-4.1.0_oracle02
drwxr-xr-x 7 digoal users 4.0K Jan 10 13:24 benchmarksql-4.1.0_oracle03
drwxr-xr-x 7 digoal users 4.0K Jan 10 13:24 benchmarksql-4.1.0_oracle04
测试
cd benchmarksql-4.1.0_oracle01/run
nohup ./runBenchmark.sh props.ora >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_oracle02/run
nohup ./runBenchmark.sh props.ora >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_oracle03/run
nohup ./runBenchmark.sh props.ora >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_oracle04/run
nohup ./runBenchmark.sh props.ora >/dev/null 2>./errrun.log &
cd ../..
测试结果
$ tail -n 5 benchmarksql-4.1.0_oracle01/run/log/benchmarksql.log
INFO [2016-01-10 19:02:51.902] Thread-3 Term-00, Measured tpmC (NewOrders) = 40127.78
INFO [2016-01-10 19:02:51.902] Thread-3 Term-00, Measured tpmTOTAL = 100363.11
INFO [2016-01-10 19:02:51.902] Thread-3 Term-00, Session Start = 2016-01-10 18:52:51
INFO [2016-01-10 19:02:51.902] Thread-3 Term-00, Session End = 2016-01-10 19:02:51
INFO [2016-01-10 19:02:51.902] Thread-3 Term-00, Transaction Count = 1004781
$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log
INFO [2016-01-10 19:02:51.917] Thread-4 Term-00, Measured tpmC (NewOrders) = 39416.45
INFO [2016-01-10 19:02:51.917] Thread-4 Term-00, Measured tpmTOTAL = 98618.7
INFO [2016-01-10 19:02:51.917] Thread-4 Term-00, Session Start = 2016-01-10 18:52:51
INFO [2016-01-10 19:02:51.917] Thread-4 Term-00, Session End = 2016-01-10 19:02:51
INFO [2016-01-10 19:02:51.917] Thread-4 Term-00, Transaction Count = 987353
$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log
INFO [2016-01-10 19:02:51.986] Thread-19 Term-00, Measured tpmC (NewOrders) = 39903.81
INFO [2016-01-10 19:02:51.986] Thread-19 Term-00, Measured tpmTOTAL = 99838.49
INFO [2016-01-10 19:02:51.986] Thread-19 Term-00, Session Start = 2016-01-10 18:52:51
INFO [2016-01-10 19:02:51.986] Thread-19 Term-00, Session End = 2016-01-10 19:02:51
INFO [2016-01-10 19:02:51.986] Thread-19 Term-00, Transaction Count = 999617
$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log
INFO [2016-01-10 19:02:51.950] Thread-2 Term-00, Measured tpmC (NewOrders) = 40873.63
INFO [2016-01-10 19:02:51.950] Thread-2 Term-00, Measured tpmTOTAL = 102276.93
INFO [2016-01-10 19:02:51.950] Thread-2 Term-00, Session Start = 2016-01-10 18:52:51
INFO [2016-01-10 19:02:51.950] Thread-2 Term-00, Session End = 2016-01-10 19:02:51
INFO [2016-01-10 19:02:51.950] Thread-2 Term-00, Transaction Count = 1024011
TPM ：
100363.11 + 98618.7 + 99838.49 + 102276.93 = 401097.23
第9分钟操作系统统计信息
TOP
top - 19:00:11 up 4 days, 9:53, 4 users, load average: 81.90, 65.33, 38.22
Tasks: 865 total, 70 running, 795 sleeping, 0 stopped, 0 zombie
Cpu(s): 71.7%us, 18.5%sy, 0.0%ni, 1.8%id, 2.5%wa, 0.0%hi, 5.6%si, 0.0%st
Mem: 264643396k total, 253849120k used, 10794276k free, 52464k buffers
Swap: 18825200k total, 0k used, 18825200k free, 202431316k cached
iostat -x
avg-cpu: %user %nice %system %iowait %steal %idle
71.43 0.00 24.09 2.71 0.00 1.77
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 6228.00 13424.80 74062.40 131863.00 10.48 22.67 1.15 0.04 88.04
dfb 0.00 0.00 6304.00 13426.20 75308.80 132180.80 10.52 20.56 1.04 0.04 88.06
dfc 0.00 0.00 6193.20 13436.80 73580.80 132116.80 10.48 24.18 1.23 0.05 89.12
dm-0 0.00 0.00 18721.40 34599.40 222953.60 396163.80 11.61 59.40 1.11 0.02 99.10
测试过程AWR报告截图：
阶段3
阶段2的测试，读IO等待比较多，所以使用如下优化手段。
使用O_DIRECT，把sga加到196GB，测10分钟把活跃数据尽量弄到内存。
再测10分钟，取第二次10分钟的测试数据。
SQL> alter system set sga_max_size=196G scope=spfile;
SQL> alter system set sga_target=196G scope=spfile;
SQL> alter system set filesystemio_options=directio scope=spfile;
SQL> alter system set disk_asynch_io=false scope=spfile;
重启数据库
show parameter pga;
pga_aggregate_limit big integer 40000M
pga_aggregate_target big integer 20000M
测试结果：
$ tail -n 5 benchmarksql-4.1.0_oracle01/run/log/benchmarksql.log
INFO [2016-01-10 21:19:13.351] Thread-17 Term-00, Measured tpmC (NewOrders) = 42310.97
INFO [2016-01-10 21:19:13.351] Thread-17 Term-00, Measured tpmTOTAL = 105901.9
INFO [2016-01-10 21:19:13.351] Thread-17 Term-00, Session Start = 2016-01-10 21:09:12
INFO [2016-01-10 21:19:13.351] Thread-17 Term-00, Session End = 2016-01-10 21:19:13
INFO [2016-01-10 21:19:13.351] Thread-17 Term-00, Transaction Count = 1060513
$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log
INFO [2016-01-10 21:19:13.269] Thread-16 Term-00, Measured tpmC (NewOrders) = 41991.81
INFO [2016-01-10 21:19:13.269] Thread-16 Term-00, Measured tpmTOTAL = 104960.25
INFO [2016-01-10 21:19:13.269] Thread-16 Term-00, Session Start = 2016-01-10 21:09:12
INFO [2016-01-10 21:19:13.269] Thread-16 Term-00, Session End = 2016-01-10 21:19:13
INFO [2016-01-10 21:19:13.269] Thread-16 Term-00, Transaction Count = 1050966
$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log
INFO [2016-01-10 21:19:13.359] Thread-23 Term-00, Measured tpmC (NewOrders) = 41933.05
INFO [2016-01-10 21:19:13.359] Thread-23 Term-00, Measured tpmTOTAL = 104937.98
INFO [2016-01-10 21:19:13.359] Thread-23 Term-00, Session Start = 2016-01-10 21:09:12
INFO [2016-01-10 21:19:13.359] Thread-23 Term-00, Session End = 2016-01-10 21:19:13
INFO [2016-01-10 21:19:13.359] Thread-23 Term-00, Transaction Count = 1050834
$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log
INFO [2016-01-10 21:19:13.355] Thread-9 Term-00, Measured tpmC (NewOrders) = 42229.93
INFO [2016-01-10 21:19:13.355] Thread-9 Term-00, Measured tpmTOTAL = 105468.32
INFO [2016-01-10 21:19:13.355] Thread-9 Term-00, Session Start = 2016-01-10 21:09:12
INFO [2016-01-10 21:19:13.355] Thread-9 Term-00, Session End = 2016-01-10 21:19:13
INFO [2016-01-10 21:19:13.355] Thread-9 Term-00, Transaction Count = 1056150
TPM：
105901.9 + 104960.25 + 104937.98 + 105468.32 = 421268.45
TOP
top - 21:14:38 up 4 days, 12:08, 4 users, load average: 83.15, 79.45, 58.33
Tasks: 785 total, 89 running, 696 sleeping, 0 stopped, 0 zombie
Cpu(s): 73.5%us, 19.8%sy, 0.0%ni, 0.4%id, 0.2%wa, 0.0%hi, 6.1%si, 0.0%st
Mem: 264643396k total, 260419476k used, 4223920k free, 1480k buffers
Swap: 18825200k total, 30972k used, 18794228k free, 202044588k cached
iostat -x
avg-cpu: %user %nice %system %iowait %steal %idle
72.09 0.00 26.41 0.32 0.00 1.18
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 1253.20 15725.40 14926.40 151338.40 9.79 9.80 0.58 0.04 65.46
dfb 0.00 0.00 1257.00 15687.40 14905.60 151028.80 9.79 9.37 0.55 0.04 64.84
dfc 0.00 0.00 1243.60 15726.80 14753.60 151422.40 9.79 10.36 0.61 0.04 69.50
dm-0 0.00 0.00 3752.80 40830.60 44582.40 453809.40 11.18 24.55 0.55 0.02 93.10
AWR 到时候打包上来
阶段4
关闭资源，使用一个控制文件，使用大页。调整异步 redolog buffer flush策略
sysctl -w vm.nr_hugepages=102352
alter system set audit_trail=none scope=spfile;
alter system set control_files='/data01/digoal/oradata/orcl12c/control01.ctl' scope=spfile;
alter system set recyclebin='OFF' scope=spfile;
alter system set resource_manager_plan='';
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
-- execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
SQL> show parameter use_large_pages;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
use_large_pages string
TRUE
重启数据库
确认已使用大页
cat /proc/meminfo
HugePages_Total: 102352
HugePages_Free: 1999
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
SQL> alter system set COMMIT_WRITE='nowait,immediate' scope=both;
测试结果
$ tail -n 5 benchmarksql-4.1.0_oracle01/run/log/benchmarksql.log
INFO [2016-01-12 02:40:56.974] Thread-8 Term-00, Measured tpmC (NewOrders) = 56362.41
INFO [2016-01-12 02:40:56.974] Thread-8 Term-00, Measured tpmTOTAL = 140809.3
INFO [2016-01-12 02:40:56.974] Thread-8 Term-00, Session Start = 2016-01-12 02:30:56
INFO [2016-01-12 02:40:56.974] Thread-8 Term-00, Session End = 2016-01-12 02:40:56
INFO [2016-01-12 02:40:56.974] Thread-8 Term-00, Transaction Count = 1408951
$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log
INFO [2016-01-12 02:40:57.024] Thread-9 Term-00, Measured tpmC (NewOrders) = 57162.67
INFO [2016-01-12 02:40:57.024] Thread-9 Term-00, Measured tpmTOTAL = 142557.02
INFO [2016-01-12 02:40:57.024] Thread-9 Term-00, Session Start = 2016-01-12 02:30:56
INFO [2016-01-12 02:40:57.024] Thread-9 Term-00, Session End = 2016-01-12 02:40:57
INFO [2016-01-12 02:40:57.024] Thread-9 Term-00, Transaction Count = 1426560
$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log
INFO [2016-01-12 02:40:57.083] Thread-9 Term-00, Measured tpmC (NewOrders) = 57396.07
INFO [2016-01-12 02:40:57.083] Thread-9 Term-00, Measured tpmTOTAL = 143534.39
INFO [2016-01-12 02:40:57.083] Thread-9 Term-00, Session Start = 2016-01-12 02:30:56
INFO [2016-01-12 02:40:57.083] Thread-9 Term-00, Session End = 2016-01-12 02:40:57
INFO [2016-01-12 02:40:57.083] Thread-9 Term-00, Transaction Count = 1436374
$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log
INFO [2016-01-12 02:40:57.130] Thread-19 Term-00, Measured tpmC (NewOrders) = 57297.04
INFO [2016-01-12 02:40:57.130] Thread-19 Term-00, Measured tpmTOTAL = 143200.3
INFO [2016-01-12 02:40:57.130] Thread-19 Term-00, Session Start = 2016-01-12 02:30:56
INFO [2016-01-12 02:40:57.130] Thread-19 Term-00, Session End = 2016-01-12 02:40:57
INFO [2016-01-12 02:40:57.130] Thread-19 Term-00, Transaction Count = 1433088
TPM：
140809.3 + 142557.02 + 143534.39 + 143200.3 = 570101.01
TOP
top - 02:00:26 up 16:42, 2 users, load average: 76.44, 63.95, 49.59
Tasks: 635 total, 67 running, 568 sleeping, 0 stopped, 0 zombie
Cpu(s): 74.0%us, 16.3%sy, 0.0%ni, 2.5%id, 0.7%wa, 0.0%hi, 6.5%si, 0.0%st
Mem: 264643396k total, 255406652k used, 9236744k free, 84332k buffers
Swap: 6291444k total, 0k used, 6291444k free, 11687700k cached
iostat
avg-cpu: %user %nice %system %iowait %steal %idle
71.16 0.00 23.65 0.95 0.00 4.24
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 766.40 17304.80 9120.00 166542.40 9.72 16.17 0.89 0.04 71.42
dfb 0.00 0.00 738.40 17309.80 8662.40 166842.20 9.72 15.43 0.85 0.04 72.06
dfc 0.00 0.00 762.60 17276.40 9049.60 166425.60 9.73 17.55 0.97 0.04 76.46
dm-0 0.00 0.00 2265.00 44134.00 26832.00 499805.40 11.35 39.87 0.86 0.02 98.42
AWR 后面再打包提供。
其他
清数据
./runSQL.sh props.ora sqlTableTruncates
删表
./runSQL.sh props.ora sqlTableDrops
参考
1. http://www.orafaq.com/node/93