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.sql_id,t.parse_calls,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倍。
真是神奇!!!