5.读书笔记收获不止Oracle之 插入表性能示例

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

真是神奇！！！