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倍。

真是神奇！！！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!