收获不止oracle，第一个优化案例

今天读到 梁敬彬同志写的 《收获不止oracle》，感觉相当受启发。希望多些这样的书。以下是书中第一部分的优化案例：

conn / as sysdba
grant alter system to scott;

conn scott/tiger
drop table t purge;
create table t (x int);

create or replace procedure proc1
as 
begin
	for i in 1.. 100000
	loop
		execute immediate 'insert into t values('|| i ||')';
		commit;
	end loop;
end;
/

alter system flush shared_pool;		--清空共享池

SQL> set timing on
SQL> exec proc1;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 05.75
SQL> select count(1) from t;

  COUNT(1)
----------
    10000

 select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where t.sql_text like '%insert into t values%';		--结果：解析次数，执行次数都为1
							-- 也就是每个 insert 语句解析了一次，执行了1次，总共解析了10000次，怪不得速度很慢。
 --------------------------------------------------------------------
优化：绑定变量，合并为一个写法，insert into t values(:x)

create or replace procedure proc2
as 
begin
	for i in 1.. 10000
	loop
		execute immediate 'insert into t values(:x)' using i;
		commit;
	end loop;
end;
/

drop table t purge;
create table t (x int);
alter system flush shared_pool;		--清空共享池

SQL> exec proc2;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.22		--速度提升明显

SQL> select count(1) from t;

  COUNT(1)
----------
     10000

 select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where t.sql_text like '%insert into t values%';		--一个hash值，只解析1次，执行了10000次，所以速度提升了很多

SQL_TEXT                                           SQL_ID        PARSE_CALLS EXECUTIONS
-------------------------------------------------- ------------- ----------- ----------
insert into t values(:x)                           ff53v38uhtzbz           0      10000


再次优化：静态改写，无需用动态sql

create or replace procedure proc3
as 
begin
	for i in 1.. 10000
	loop
		insert into t values(i);
		commit;
	end loop;
end;
/

drop table t purge;
create table t (x int);
alter system flush shared_pool;		--清空共享池

SQL> exec proc3;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.16		--又快了，为什么呢？

SQL> select count(1) from t;

  COUNT(1)
----------
     10000

SQL>  select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where lower(t.sql_text) like '%insert into t values%';

SQL_TEXT                                           	SQL_ID        	PARSE_CALLS EXECUTIONS
-------------------------------------------------- --------------------- ----------- ----------
INSERT INTO T VALUES(:B1 )                         9bay73nakuyw9           1      10000

	--静态sql 自动使用了绑定变量，而且，动态sql 是执行的时候才进行解析，而静态sql 是编译的过程中就解析好了。这点差别就是速度再度提升的原因。


第三次优化：批量提交

create or replace procedure proc4
as 
begin
	for i in 1.. 10000
	loop
		insert into t values(i);
	end loop;
   commit;
end;
/


drop table t purge;
create table t (x int);
alter system flush shared_pool;		--清空共享池

SQL> exec proc4;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.52		--速度又快了，这又为什么呢？
						--答案就是：commit 操作会出发 LGWR 去写redo buffer 到redolog ,批量提交就只触发一次。

SQL> select count(1) from t;

  COUNT(1)
----------
     10000

------------------------------------------
第四次优化：集合写法	 insert into t select rownum from dual connect by level <=10000;

drop table t purge;
create table t (x int);
alter system flush shared_pool;		--清空共享池

SQL> insert into t select rownum from dual connect by level <= 10000;

已创建10000行。

已用时间:  00: 00: 00.06		--速度更快了，因为原来的过程变成了sql,一条一条的插入语句变成了一个集合，一整批地写入data buffer ,
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL>

---------------------------------------------
第五次优化：直接路径 

drop table t purge;
create table t (x int);
alter system flush shared_pool;		--清空共享池

--因为原来数据量小，速度已经够快，这里扩大到100万
SQL>  insert into t select rownum from dual connect by level <= 1000000;

已创建1000000行。

已用时间:  00: 00: 01.55
SQL>commit;

--对比create as 直接路径
drop table t purge;
alter system flush shared_pool;		--清空共享池

SQL> create table t as select rownum x from dual connect by level <= 1000000;

表已创建。

已用时间:  00: 00: 01.40		--速度比上边又快了，为什么？

因为，insert into t select ....  的方式是先将数据写到data buffer 再刷到磁盘中。
而create table as ....却是跳过了 数据缓冲区，直接写到磁盘。又称为，直接路径读写。

直接路径读写方式的缺点在于，由于数据不经过数据缓存区，所以在数据缓存区中一定读不到这些数据，因此一定会有物理读。
但是在很多时候，尤其是海量数据需要迁移插入时，快速插入才是真正的第一目的，该表一般记录巨大，data buffer 甚至还装不下其十分之一、百分之一，这些共享的数据意义也不大，
这时，我们一般会选择直接路径读写的方式来完成海量数据的插入。

--------------------------------------------------------
第六次优化：并行设置 + nologging

--如果机器性能够好，可用并行。如这里，16cpu
SQL> create table t nologging parallel 16
     as select rownum x from dual connect by level <=1000000;

不过，并行最大特点就是占用大多数cpu资源，如果是有其他应用，影响会很严重，三思而行。