BenchmarkSQL 测试Oracle 12c TPC-C 性能

使用BenchmarkSQL测试一下Oracle 12c的TPC-C性能，同时对比一下PostgreSQL 9.5的性能。

测试机：

3 * PCI-E SSD，逻辑卷条带，XFS，数据块对齐，16核开HT，256G内存。

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

$ 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 批量提交参数

SQL> alter system set commit_write='batch,nowait';

使用强制软解析

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 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>

压测

nohup ./ runBenchmark.sh props.ora >/dev/null 2>./errrun.log &

TPM，低于 PostgreSQL 9.5.0 。

主机信息，截取压测第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放到另一台主机，主机间万兆网同一交换机下互联。

参考

http://blog.163.com/digoal@126/blog/static/163877040201601021838221/

------------------ ------------------ ------------------ ------------------ ------------------ ------------------

为了突破测试程序的极限，开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 

$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log 

TPM， 低于 PostgreSQL 9.5.0 。

第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 

$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log 

TPM， 低于 PostgreSQL 9.5.0 。(已达到本例测试Oracle的最高水平)

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，

感谢Oracle圈的朋友提供的优化建议。

虽然性能没有提升，瓶颈转嫁了。但是玩的就是折腾，至少知道这么做不行。

------------------ ------------------ ------------------ ------------------ ------------------ ------------------

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','');
alter system set session_cached_cursors=8000 scope=spfile;
alter system set inmemory_size=80g scope=spfile;
conn test01/test01
alter table STOCK inmemory;
alter table CUSTOMER inmemory;
alter table WAREHOUSE inmemory;
alter table DISTRICT inmemory;
alter table NEW_ORDER inmemory;
conn test02/test02
alter table STOCK inmemory;
alter table CUSTOMER inmemory;
alter table WAREHOUSE inmemory;
alter table DISTRICT inmemory;
alter table NEW_ORDER inmemory;
conn test03/test03
alter table STOCK inmemory;
alter table CUSTOMER inmemory;
alter table WAREHOUSE inmemory;
alter table DISTRICT inmemory;
alter table NEW_ORDER inmemory;
conn test04/test04
alter table STOCK inmemory;
alter table CUSTOMER inmemory;
alter table WAREHOUSE inmemory;
alter table DISTRICT inmemory;
alter table NEW_ORDER inmemory;

select count(*) from STOCK;
set autot trace
SELECT * FROM STOCK;

重启数据库

 

测试结果

$ tail -n 5 benchmarksql-4.1.0_oracle01/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle02/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle03/run/log/benchmarksql.log 

$ tail -n 5 benchmarksql-4.1.0_oracle04/run/log/benchmarksql.log 

TPM， 低于 PostgreSQL 9.5.0 。(低于阶段2的数据)

TOP

top - 22:50:25 up 4 days, 13:43,  4 users,  load average: 72.04, 63.52, 44.66
Tasks: 790 total,  50 running, 740 sleeping,   0 stopped,   0 zombie
Cpu(s): 72.7%us, 17.6%sy,  0.0%ni,  3.5%id,  1.3%wa,  0.0%hi,  5.0%si,  0.0%st
Mem:  264643396k total, 259771132k used,  4872264k free,     6980k buffers
Swap: 18825200k total,   424680k used, 18400520k free, 203314556k cached

iostat

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          73.89    0.00   23.05    0.93    0.00    2.13
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 2027.40 13793.20 24020.80 135840.00    10.10     9.77    0.62   0.04  70.24
dfb               0.00     0.00 2046.20 13701.20 24318.40 134443.20    10.08    10.42    0.66   0.05  72.70
dfc               0.00     0.00 2057.80 13784.80 24260.80 135686.40    10.10    10.65    0.67   0.05  75.26
dm-0              0.00     0.00 6129.60 36029.60 72598.40 405996.80    11.35    25.83    0.61   0.02  98.00

AWR 后面再打包提供。

[其他]

清数据

./runSQL.sh props.ora sqlTableTruncates

删表

./runSQL.sh props.ora sqlTableDrops