BenchmarkSQL 测试Oracle 12c TPC-C 性能

9 minute read

背景

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

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, 防止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截图,(持续更新)

pic

pic


特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。

优化中,期待Oracle更好的表现。


阶段2,

benchmarksql放到另一台主机,主机间万兆网同一交换机下互联。

参考

《BenchmarkSQL 支持多个 schema》


为了突破测试程序的极限,开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报告截图:

pic

pic

pic

pic

pic

pic

pic

pic

pic

pic

pic


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

Flag Counter

digoal’s 大量PostgreSQL文章入口