oracle数组接口性能测试

测试场景和结果:

向没有索引的表中插入20万条数据

PL/SQL测试结果:插入20万条记录,花费时间500ms左右

Jdbc批量操作(每50条一个batch)测试结果:插入20万条记录,花费时间1200ms左右。

表结构如下:

CREATE TABLE INSERT_TEST(
F1 NUMBER,
F2 VARCHAR2(10),
F3 VARCHAR2(10),
F4 VARCHAR2(10),
F5 VARCHAR2(10),
F6 NUMBER,
F7 NUMBER,
F8 NUMBER,
F9 NUMBER,
F10 NUMBER
)

测试环境:
AMD Athlon(tm) II X2 240 Processor 2G内存
单块7200rpm SATA硬盘
Linux 2.6.31-14-generic i686
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

SYS@orcl>show parameter log_buffer
NAME                     TYPE     VALUE
———————————— ———– ——————————
log_buffer                 integer     7057408

PL/SQL代码:

declare
    type numlist is table of number index by binary_integer;
    type charlist is table of varchar2(10) index by binary_integer;
    g_start number;
    n numlist;
    c charlist;
  begin
  for i in 1 .. 200000 loop
    c(i) := 'a';
    n(i) := 1;
  end loop;
  g_start := dbms_utility.get_time;
  forall i in 1 .. 200000
    insert into insert_test values(1,c(i),c(i),c(i),c(i),n(i),n(i),n(i),n(i),n(i));
  commit;
  dbms_output.put_line('total spend time:' || (10*(dbms_utility.get_time - g_start)) || 'ms');
end;
/

Java批量操作代码:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchInsert {
    public static void main(String[] args) throws SQLException,
            ClassNotFoundException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "system",
                    "manager");
            String sql = "insert into insert_test values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            connection.setAutoCommit(false);

            long t1 = System.currentTimeMillis();

            statement = connection.prepareStatement(sql);

            for (int i = 1; i <= 200000; i++)

            {
                statement.setInt(1, i);

                statement.setString(2, "a");
                statement.setString(3, "a");
                statement.setString(4, "a");
                statement.setString(5, "a");
                statement.setInt(6, 1);
                statement.setInt(7, 1);
                statement.setInt(8, 1);
                statement.setInt(9, 1);
                statement.setInt(10, 1);

                statement.addBatch();

                if ((i % 50) == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }

            connection.commit();

            long t2 = System.currentTimeMillis();
            System.out.println("total run time:" + (t2 - t1));

        } finally {
            statement.close();
            connection.close();
        }

    }
}

参考资料:

ORACLE数据库插入性能测试

Comments are closed.