5.读书笔记收获不止Oracle之 插入表性能示例
1. 单车速度
一个案例：
#sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
单行插入如下：
sql> create or replace procedure proc1
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values ('||i||')';
commit;
end loop;
end;
/
然后开始执行：
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>exec proc1;
花费时间：Elapsed: 00:00:54.44
sql>select count(*) from t;
COUNT(*)
----------
100000
Elapsed: 00:00:00.01
在共享池中缓存下来的sql语句以及HASH出来的唯一值，都可以在v$sql中对应的sql_TEXT和sql_ID字段中查询到，而解析的次数和执行的次数分别可以从PARSE_CALL和EXECUTIONS字段中获取。
查看过程执行在共享池中执行的情况，如下：
sql> select t.sql_text,t.sql_id,t.parse_calls,t.executionsfrom v$sql t where sql_text like '%insert into t values%';
2. 绑定变量
create or replace procedure proc2
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values (:x)' using i;
commit;
end loop;
end;
/
然后执行如下：
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>exec proc2;
执行时间：Elapsed: 00:00:13.91
速度大幅提升了。
查看缓存的sql语句：
select t.sql_text,t.executions from v$sql twhere sql_text like '%insert into t values%';
只有一条了。
3. 再加速一次
将过程中的executeimmediate和双引号去掉。Execute immediate是一种动态sql的写法，用于表名字段名是变量、入参的情况，因为表名都不知道，所以不能直接写sql语句，要靠动态sql语句根据传入的表名参数，来拼成一条sql语句，由execute immediate调用执行。此处，不用多次一举。
create or replace procedure proc3
as
begin
for i in 1..100000
loop
insert into t values (i);
commit;
end loop;
end;
/
然后执行:
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>exec proc3;
速度好像没有提升多少：Elapsed: 00:00:17.49
4. 批量提交
create or replace procedure proc4
as
begin
for i in 1..100000
loop
insert into t values (i);
end loop;
commit;
end;
/
将commit移到loop外面，
然后执行如下：
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>exec proc4;
花费时间：Elapsed: 00:00:06.82
再看下使用动态执行，然后批量提交：
create or replace procedure proc5
as
begin
for i in 1..100000
loop
executeimmediate
'insert into t values (:x)' using i;
end loop;
commit;
end;
/
然后执行：
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>exec proc5;
花费时间：Elapsed: 00:00:08.80
发现，没有静态sql的快。
5. 集合写法
实现如下：
Insert into t select rownum from dual connect by level<=100000;
开始执行如下：
sqlplus / as sysdba
sql>drop table t purge;
sql>create table t ( x int);
sql>alter system flush shared_pool;
sql>set timing on;
sql>Insert into t select rownum from dual connect bylevel<=100000;
花费：Elapsed: 00:00:00.17
真是飞一样的速度。
查询确认：
sql> select count(*) from t;
COUNT(*)
----------
100000
Elapsed: 00:00:00.02
将插入的数据，整批的写到DATA BUFFER区里面。
6. 绕过DATA BUFFER
先执行集合写法，插入数据变成 1000000
sql>drop table t purge;
create table t ( x int);
alter system flush shared_pool;
set timing on;
Insert into t select rownum from dual connect by level<=1000000;
花费：
Elapsed: 00:00:01.17
终极写法如下：
sql>drop table t purge;
alter system flush shared_pool;
set timing on;
create table t as select rownum x from dual connect by level<=1000000;
花费：
Elapsed: 00:00:00.67
因为：insert into t select 方式是将数据先写到DATA BUFFER，然后刷到磁盘中。
而create table t 方式跳过了数据缓存区，直接写到磁盘中，叫做直接路径读写方式。
少了一个步骤，所以速度快了很多。
7. 并行设置
sql>drop table t purge;
alter system flush shared_pool;
set timing on;
create table t nologging parallel 2 as select rownum x from dualconnect by level<=1000000;
花费：Elapsed: 00:00:00.37
最后速度从：54秒提高到了0.37，而且0.37导入的量是54秒的10倍。
真是神奇！！！
