在工作中我接触到的程序员，很多完全没有SQL优化这个概念，在他们看来，只要这条语句能用就可以，不管快慢如何。而且就算他觉得慢，大部分人都会说是数据库的原因，而不认为是自己写的SQL太烂，所以完全忽略SQL语句本身的性能问题。下面来看看一个简单的SQL性能提升，给你飞一般的感觉！
环境说明：oracle 11g R2 64位 memory_max_target =3200m sga_max_size=3G --- 机子很烂，不过效果会更明显，嘿嘿
set timing on --显示时间
create table t (x int); ---建表t
alter system flush shared_pool; ---清空共享池
这是一般程序员或者数据库开发人员写的，一个简单的存储过程，实现向t表超如100w条记录。
create or replace procedure p1
as
begin
for i in 1..1000000
loop
execute immediate
‘insert into t values (‘||i||‘)‘;
commit;
end loop;
end;
/
1、第一次，性能最差的时候
这里先执行一遍，是为了将存储过程建立起来。下次直接调用它。
重新建立t表。
SQL> create table t (x int);
表已创建。
SQL> alter system flush shared_pool;
系统已更改。
SQL> exec p1;
PL/SQL 过程已成功完成。
已用时间: 00: 07: 15.56
7分25秒，也就是平均每秒插入2293行，哎呀！算算不错哟！
由于上面的过程p1，因为没有绑定变量，于是每个语句必须得硬解析1次，执行1次，硬解析了100w次，
或许有人就会想：什么是硬解析？既然有硬解析，应该也有软解析吧？
查看共享池中的执行情况:
select s.sql_text,ssql_id,s.parse_calls,s.executions
from v$sql s
where sql_text like ‘%insert into t values%‘;
由于记录很多就不列出来，免得占地儿。
2、第一次提升：
接下来看看绑定变量后的情况：
当然为了公平。这里还是先删除t表，病清空共享池：
SQL> drop table t purge;
SQL> create table t (x int);
SQL> alter system flush shared_pool;
先创建存储过程p2
create or replace procedure p2
as
begin
for i in 1..1000000
loop
execute immediate
‘insert into t values(:x)‘ using i;
commit;
end loop;
end;
/
重复上面的步骤，后再次运行过程p2
结果：
SQL> exec p2;
PL/SQL 过程已成功完成。
已用时间: 00: 02: 11.41
看见没，2分9秒。足足快了三倍多，平均每秒7751条记录,快了吧！
这是因为绑定变量后hash成为一个唯一的hash值，解析一次，执行了100w次。语句执行硬解析1次，之后的999999次都是软解析。(软解析出来了吧，至于什么是硬解析、什么是软解析不是这里的重点，就不做说明）
查看共享池中的执行情况:
SQL> select s.SQL_TEXT,s.sql_id,s.parse_calls,s.executions
2 from v$sql s
3 where sql_text like ‘%insert into t values%‘;
SQL_TEXT SQL_ID PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into t values(:x) ff53v38uhtzbz 1 1000000
就一条记录。
来学习一点知识：
execute immediate是一种动态的SQL写法，常用于于表名字段名是变量，入参的情况，由于表名都不知道，所以当然不能直接写SQL语句了，所以要靠动态SQL语句根据传入表名参数，来拼成一条SQL语句，由 execute immediate调用执行，。但是当知道表名的时候就是多此一举了。一般来说，静态sql会知道使用绑定变量。动态SQL的特点是执行过程中再解析，而静态SQL的特点是编译的过程解析好了，这样又节省了时间。
3、开始我们的第二次提升：
create or replace procedure p3
as
begin
for i in 1..1000000
loop
insert into t values(i);
commit;
end loop;
end;
/
SQL> exec p3;
PL/SQL 过程已成功完成。
已用时间: 00: 02: 08.79
稍微快了一点点，由于是机器和数据量的问题，但是还有提升了。
再来学习一点知识：
commit 触发LGWR 将REDO BUFFER 写到redo buffer中，并且将回滚段的活动事务标记为不活动，同时让回滚段中记录对应前镜像记录的所在位置标记为可以重写，（commit 不是写数据的动作，将数据从databuffer刷出磁盘是有CKPT决定的，）所以commit做的事情开销并不大，单词提交可能需要0.001秒即可完成，不管多大批量操作后的提交，针对commit而言，也是做这三件事情，所花费的总时间不可能超过1秒钟加入1条记录执行完后commit的提交时间需要0.8秒，但是100w条记录，就远远不止0.8秒。
4、来体验一下3次飞跃：
create or replace procedure p4
as
begin
for i in 1..1000000
loop
insert into t values(i);
end loop;
commit;
end;
/
SQL> exec p4;
PL/SQL 过程已成功完成。
已用时间: 00: 01: 14.01
噢耶！又是一个档次的提升。
把过程变成了SQL一条一条插入变成了一个集合的概念，变成了一个整批的写进DATA ,这就相当于一瓢一瓢的向池子里面注水，变成了一桶一桶的注水。是不是就更快些呢!
5、第四次提升
好了，不废话，直接进入第4次巨大的飞跃：
insert into t select rownum from dual connect by level<=1000000;
SQL> insert into t select rownum from dual connect by level<=1000000;
已创建1000000行。
已用时间: 00: 00: 01.40
天啦！不到2秒钟...没看错吧！哈哈，神奇吧。
学习一下：
insert into t select ...的方式是将数据先写到data buffer 中，然后再刷到磁盘。
而create table t的方式确实跳过了数据缓存区，直接写进磁盘中，这种方式称之为直接路径读写方式。原本是先写到内存，再写到磁盘，改为直接写到磁盘，少了一个步骤。
6、第五次质变
create table t as select rownum x from dual connect by level <=1000000;
SQL> create table t as select rownum x from dual connect by level <=1000000;
表已创建。
已用时间: 00: 00: 00.97
神速啊！，一秒钟都不要。
让效果更明显一些，插入300w。
SQL> create table t as select rownum x from dual connect by level <=3000000;
表已创建。
已用时间: 00: 00: 02.50
直接路径读写方式的缺点在于由于数据不经过数据缓存区，所以在数据缓存区中一定读不到这些数据，因此一定会有物理读。但是实际很多情况下，尤其是海量数据需要迁移插入的时候，快速插入才是真正的第一目的。该表一般记录巨大，date buffer 甚至存放不下其十分之一，百分之一，那么这写共享的数据意义也就不大了。这是，我们一般会选择直接路径读写方式来完成海量数据的插入。
7、其他的方式加快性能提升
例如：
nologging方式，
并行方式，等等
由于本人的本本配置较差，这里开2个并行度。
create table t nologging parallel 16
as select rownum x from dual connect by level<=3000000;
SQL> create table t nologging parallel 2
2 as select rownum x from dual connect by level<=3000000;
表已创建。
已用时间: 00: 00: 02.02
还是有一定的提升！如果是更大的数据量，效率会更明显。如果把SQL运行在强劲的服务器上，16CPU，32CPU，64CPU，128CPU，会有不同程度的提升。
但是并且最大的特点就是占用了大多数的CPU资源，如果是一个并发环境，有很多应用在跑着，就可能开过多的并且而影响其他的应用，导致别的应用资源不足，所以在做这些类似的操作前，想清楚哟！
SQL优化提升性能 真实体验屌丝变土豪
原文：http://blog.itpub.net/27099995/viewspace-1350531/
