一个简单的Oracle存储过程测试数据插入效率

一，测试准备

1 创建表数据空间

SQL> create tablespace cat_data nologging

datafile '/home/oracle/app/oracle/oradata/catdb/cat_data.dbf' size 4096M

extent management local autoallocate;

 2，创建表索引空间

SQL> create tablespace cat_index nologging

datafile '/home/oracle/app/oracle/oradata/catdb/cat_index.dbf' size 4096M

extent management local autoallocate;

3，创建用户

SQL> create user cat identified by cat default tablespace cat_data account unlock;

SQL> grant connect, resource to cat;

SQL> grant select on v_$instance to cat;

SQL> grant select on v_$session to cat;

SQL> grant select any table to cat;

SQL> grant alter any sequence to cat;

SQL> grant create any trigger to cat;

SQL> grant create any directory to cat;

SQL> grant create any procedure to cat;

SQL> grant create any table to cat;

SQL> grant dba to cat;

 4，创建数据表

[oracle@cat admin]$ sqlplus cat/cat

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 14 08:31:16 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - Production

 

create table test_users(

userid varchar2(16) primary key,

username varchar2(32) not null,

passwd varchar2(16),

phone varchar2(32),

email varchar2(64))

tablespace cat_data;

 5，创建测试存储过程

$ vi test_proceduce.sql

set serveroutput on

create or replace procedure init_test_users
as
    currentId number(16) := 0;
    maxNum number(16) := 5000000;
    l_userid varchar2(16);
    l_username varchar2(32);
    sqltext varchar2(256);
begin
    dbms_output.put_line('begin ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

    --sqltext := 'insert into test_users(userid, username, passwd, phone, email) values(:userid, :username,' || '''111111'',' || '''13800000000'',' || '''xx''' || ')';

    sqltext := 'insert into test_users(userid, username, passwd, phone, email) values(:userid, :username,' || '111111,' || '13800000000,' || '''xx''' || ')';
    --这里需注意xx前后由'''包起来，而前面的111111和13800000000可以前后只用'包起来，因为他们像数字，Oracle可以自动进行转义，而xx就不行，被这个问题曾经折腾了好多天 ：（

    loop
        currentId:=currentId + 1;
        l_userid:=to_char(currentId);
        l_username:=sys_guid();

        execute immediate sqltext using l_userid, l_username;
        exit when currentId >= maxNum;
    end loop;

    dbms_output.put_line('before commit ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

    commit;

    dbms_output.put_line('end commit ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

end init_test_users;

/

$ chmod +x test_proceduce.sql 

$ sqlplus cat/cat

SQL> @test_proceduce.sql

Procedure created.

 

二，测试执行及结果

插入500W数据，执行的结果

SQL> exec init_test_users;
begin ...20:08:17.405825000
before commit ...20:25:36.913050000
end commit ...20:25:36.919653000

PL/SQL procedure successfully completed.

SQL> exec init_test_users;
begin ...21:22:47.472097000
before commit ...21:39:17.694782000
end commit ...21:39:17.701508000

PL/SQL procedure successfully completed.

三，查看存储过程的执行计划

SQL>set autotrace on

SQL>set timing on

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告 
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息 
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出