一,测试准备
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,但是不显示查询输出