ORACLE数据库插入性能测试

祝湉湉

　　
ORACLE数据库插入性能测试

MKing
2010-3-8

测试环境基本信息：
OS：Windows XP sp3
DB：Oracle 9.2.0.1 未启用归档
DB重做日志文件大小：100MB
硬盘型号：SAMSUNG HD161GJ（SATA-300,160G,7200rpm,8M cache）
CPU：Intel Core2 E8400（3.0G）
内存：2G

通过HD Tune得到的硬盘基本测试信息，
IOPS：66
读取：90MB/s
写入：82MB/s

测试表脚本：

create table T_EMPLOYEE
(
ID NUMBER(10) not null,
NAME VARCHAR2(20) not null,
CREATE_DATE DATE default sysdate not null,
BIRTHDAY DATE,
ADDRESS VARCHAR2(200),
EMAIL VARCHAR2(200),
MOBILEPHONE VARCHAR2(11),
TELEPHONE VARCHAR2(20),
IDENTITY_CARD VARCHAR2(18),
WEIGHT NUMBER,
HEIGHT NUMBER
)


插入10万条记录，采用Java JDBC方式的测试结果如下(单位：秒)：

执行方式	OCI TCP/IP	OCI IPC	thin AutoCommit=true	thin AutoCommit=false
Statement(test1)	69.84	67.03	66.96	42.81
PreparedStatement(test2)	40	37.18	39.21	12.66
PreparedStatement Batch(test3)	51.72	50.78	2.81	2.81

从测试结果可以看出，采用thin连接方式 batch插入的性能最好，而采用oci的batch插入性能未得到提高，也可能是bug。
记得以前在ORACLE文档里说采用oci模式的性能最好，9i中经过测试完全不成立，我想可能是以前java本身性能的问题，现在java语言的性能已经非常好了，thin连接方式不管是从管理还性能方面来说都是首选了。

注：采用oci的batch插入性能未得到提高确实是BUG，后来把ORACLE客户端升级到9.2.0.8，花的时间只要6s，但还是比thin的方式差。

以下是测试程序源码：
import java.sql.*;
import java.util.Calendar;

public class inserttest {
public static void test1(Connection iConn) throws SQLException{
Statement statement = iConn.createStatement();
String str_i;
for (int i=1;i<10000;i++) {
str_i=new Integer(i).toString();
String vSQL = "insert into t_employee(id,name,birthday,address,email,mobilephone,telephone,identity_card,weight,height)/n"
+ "values(seq_t_employee_id.nextval,'张三"+str_i+ "',sysdate - "+str_i+","
+ "'上海市南京东路11号203室"+str_i+"',"
+ "'abcd"+str_i+"@gmail.com',"
+ "'138'|| trim(to_char("+str_i+", '00000000')),"
+ "'021-'|| trim(to_char("+str_i+", '00000000')),"
+ "'3504561980' || trim(to_char("+str_i+", '00000000')),"
+ "64,1.72)";
//System.out.println(vSQL);
statement.executeUpdate(vSQL);
}
}

public static void test2(Connection iConn) throws SQLException{
String vSQL = "insert into t_employee(id,name,birthday,address,email,mobilephone,telephone,identity_card,weight,height)/n"
+ "values(seq_t_employee_id.nextval,'张三'||?,sysdate - ?,"
+ "'上海市南京东路11号203室'||?,"
+ "'abcd'||?||'@gmail.com',"
+ "'138'|| trim(to_char(?, '00000000')),"
+ "'021-'|| trim(to_char(?, '00000000')),"
+ "'3504561980' || trim(to_char(?, '00000000')),"
+ "64,1.72)";
PreparedStatement ps = iConn.prepareStatement(vSQL);
String str_i;
for (int i=1;i<10000;i++) {
str_i=new Integer(i).toString();
ps.setString(1, str_i);
ps.setString(2, str_i);
ps.setString(3, str_i);
ps.setString(4, str_i);
ps.setInt(5, i);
ps.setInt(6, i);
ps.setInt(7, i);
ps.executeUpdate();
}
}

public static void test3(Connection iConn) throws SQLException{
String vSQL = "insert into t_employee(id,name,birthday,address,email,mobilephone,telephone,identity_card,weight,height)/n"
+ "values(seq_t_employee_id.nextval,'张三'||?,sysdate - ?,"
+ "'上海市南京东路11号203室'||?,"
+ "'abcd'||?||'@gmail.com',"
+ "'138'|| trim(to_char(?, '00000000')),"
+ "'021-'|| trim(to_char(?, '00000000')),"
+ "'3504561980' || trim(to_char(?, '00000000')),"
+ "64,1.72)";
PreparedStatement ps = iConn.prepareStatement(vSQL);
String str_i;
for (int i=1;i<10000;i++) {
str_i=new Integer(i).toString();
ps.setString(1, str_i);
ps.setString(2, str_i);
ps.setString(3, str_i);
ps.setString(4, str_i);
ps.setInt(5, i);
ps.setInt(6, i);
ps.setInt(7, i);
ps.addBatch();
//ps.executeUpdate();
}
ps.executeBatch();
}

public static void main(String[] args) throws ClassNotFoundException,SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:mydb", "yzs", "yzs");
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@mydb", "yzs", "yzs");
java.util.Date d1=Calendar.getInstance().getTime();
conn.setAutoCommit(false);
test2(conn);
java.util.Date d2= Calendar.getInstance().getTime();
System.out.println("es:"+(d2.getTime()-d1.getTime())+"ms");
conn.commit();
conn.close();
}
}

采用服务器PL/SQL 方式插入10万条记录的测试结果如下：
注：t_e1与t_employee同样的表结构

执行方式	说明	运行时间 （单位：秒）
pl/sql insert（脚本1）	普通insert	3.203
pl/sql forall insert（脚本2）	从一个表BULK COLLECT INTO到目标表	0.578
insert into select *（脚本3）	使用insert into select方式插入	0.156
insert /*+ append*/ into select *（脚本4）	加append hint的插入	0.234

从测试结果分析，采用insert into select 的方式最快，只要0.156s，根据数据量统计，平均每行大小为134字节，总共插入数据量为134*100000=12.78MB，可得每秒约插入81MB的数据，基本上达到了硬盘的上限。
而采用append hint插入反而更慢，从同事讨论结果得到，采用append的insert会采用direct-path插入，因此数据会直接写入数据文件，所以消耗的时间更多。

--------------------------------------------脚本1--------------
declare
i integer;
begin
for i in 1 .. 100000 loop
insert into t_employee
(id,
name,
birthday,
address,
email,
mobilephone,
telephone,
identity_card,
weight,
height)
values
(
seq_t_employee_id.nextval,
'张三' || i,
sysdate - i,
'上海市南京东路11号203室' || i,
'abcd' || i || '@gmail.com',
'138' || trim(to_char(i, '00000000')),
'021-' || trim(to_char(i, '00000000')),
'3504561980' || trim(to_char(i, '00000000')),
64,
1.72);
end loop;
commit;
end;
--------------------------------------------脚本1--------------

--------------------------------------------脚本2--------------
DECLARE
TYPE table_t_employee IS TABLE OF t_employee%ROWTYPE;
v_table table_t_employee;
BEGIN
SELECT * BULK COLLECT INTO v_table FROM t_employee;
FORALL idx IN 1 .. v_table.COUNT
INSERT INTO t_e1 VALUES v_table (idx);
COMMIT;
END;
--------------------------------------------脚本2--------------


--------------------------------------------脚本3--------------
insert into t_e1 select * from t_employee
--------------------------------------------脚本3--------------


--------------------------------------------脚本4--------------
insert /*+ append*/ into t_e1 select * from t_employee
--------------------------------------------脚本4-------------