1. Shared Pool查看
show parameter shared; 查看shared pool大小
alter system set shared_pool_size=600M; --修改shared pool大小
alter system set shared_pool_size=600M; --修改shared pool大小
相关参数查看：
select *
from v$sysstat;
select * from v$sysstat where name= 'parse count (hard)';
select * from v$sysstat where name= 'parse count (hard)';
select * from v$sysstat where name like '%hard%';
注意其中的parse count (hard)对应的value值
alter system
set cursor_sharing=
'similar';
alter system set cursor_sharing= 'exact';
alter system set cursor_sharing= 'exact';
select *
from emp
where empno = &empno;
重要实验：
准备表：
create
table m(x
int);
步骤1： 创建2个存储过程
create
or
replace
procedure proc1
as
begin
for i in 1..100000
loop
execute immediate
'insert into m values(:x)' using i;
end loop;
end;
/
as
begin
for i in 1..100000
loop
execute immediate
'insert into m values(:x)' using i;
end loop;
end;
/
create
or
replace
procedure proc2
as
begin
for i in 1..100000
loop
execute immediate
'insert into m values('||i|| ')';
end loop;
end;
/
as
begin
for i in 1..100000
loop
execute immediate
'insert into m values('||i|| ')';
end loop;
end;
/
步骤2：开启计时器
set timing
on
步骤3：执行第一个存储过程，查看执行时间
exec proc2;
步骤4：删除刚插入的语句
truncate
table m;
步骤5：执行第二个存储过程，查看执行时间
exec proc1;
在oracle9上面的评测结果：
@>
set timing
on
@> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.28
@> select count(*) from m;
COUNT(*)
----------
100000
Elapsed: 00:00:00.01
@> truncate table m;
Table truncated.
Elapsed: 00:00:00.39
@> exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.21
@> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.28
@> select count(*) from m;
COUNT(*)
----------
100000
Elapsed: 00:00:00.01
@> truncate table m;
Table truncated.
Elapsed: 00:00:00.39
@> exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.21
结论： 比较绑定变量与不绑定变量的差异， 发现不绑定变量比绑定变量消耗时间多3-5倍。
本文转自 tianya23 51CTO博客，原文链接：http://blog.51cto.com/tianya23/263847，如需转载请自行联系原作者
