1、一个存储过程：向表里插入100000条数据
create or replace procedure proc1
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values('||i||')';
commit;
end loop;
end;
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values('||i||')';
commit;
end loop;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
很慢：
查询解析多少次：select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like '%insert into t values%'
发现解析很多次，
.png)
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;
as
begin
for i in 1..100000
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;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
.png)
.png)
select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like '%insert into t values%'
只解析一次：
.png)
3、静态改写：
create or replace procedure proc3
as
begin
for i in 1..100000
loop
execute immediate
insert into t values(i);
commit;
end loop;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
.png)
4、批量提交：
create or replace procedure proc4
as
begin
for i in 1..100000
loop
insert into t values(i);
end loop;
commit;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
.png)
5、集合写法：
insert into t select rownum from dual connect by level<=100000;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
.png)
6、直接路径：
.png)
7、并行设置（自己测试电脑没有多个cpu，不能测试）
create table t nologging parallel 64 as select rownum x from dual connect by level<=100000;
.png)
总结：该sql对深入了解oracle的体系结构有一定的帮助。