shared pool是性能调优的重要对象。
Shared Pool中包含两大核心pool，分别是Data dictionary cache和Library cache。
Data dictionary : 数据字典多数为静态的、固定的。
Library cache：经过编译、解析的SQL语句放在Library cache中。随着用户执行SQL语句的增多，执行PL/SQL代码的增多，Library cache会有被装满的时候。有效的管理其空间大小就显得格外重要。Oracle采用LRU算法来维持那些语句保留在Library cache中，哪些语句被清除。最近最少使用的SQL就是被清除对象的首选。
SQL语句在Library cache保留的是该语句的源代码么？比如select * from employees;答案是否定的，它保存的是该SQL语句的哈希值。
对SQL语句的解析分为两种，一种是硬解析另一种是软解析。硬解析是遇到从未执行过的SQL第一次为其解析的过程。软解析是再次遇到该SQL时查找已经解析过的解析结果。硬解析代价大于软解析。对比两个SQL是否相同采取的办法是对比其哈希值。如果哈希值相同就认为SQL语句相同。
在SQL语句中的字母大小写和缩进、空格等细微的差别都会导致看起来相同的SQL语句其对应的哈希值不同。下面的3条SQL语句在Library cache中就会被判断成2个不同的SQL语句，不能被重用。每次执行都需要一次硬解析。
select * from snow.t1;
SELECT * FROM SNOW.T1;
Library cache中最可怕的性能杀手要数“高并发下的非绑定变量SQL”。如果一个简单的查询语句，只是在where条件中的参数不同，会被当做不同的SQL，每次执行前进行一次硬解析。比如去营业厅查询话费：
select * from t where phone_number=‘18611010001’;
select * from t where phone_number=‘18611019527’;
这两个SQL语句只有phone_number的值不同导致SQL的哈希不同。如果并发量高，查询次数多，library cache的缓存作用基本被闲置了，必然导致执行效率低下。
这里引用TOM的经典实验来验证绑定变量的重要性
实验步骤：
1，创建存储过程proc1，使用绑定变量的方式插入10万条记录
SYS@PROD2 > create or replace procedure proc1
2 as
3 begin
4 for i in 1..100000
5 loop
6 execute immediate
7 'insert into snow.t2 values(:i)' using i;
8 end loop;
9 end;
10 /
Procedure created.
2，创建存储过程proc2，不适用绑定变量的方式插入10万条记录
SYS@PROD2 > create or replace procedure proc2
2 as
3 begin
4 for i in 1..100000
5 loop
6 execute immediate
7 'insert into snow.t2 values('||i||')' ;
8 end loop;
9 end;
10 /
Procedure created.
3，设置计时器
SYS@PROD2 > set timing on
4，不使用绑定变量的存储过程执行时间为32秒84
SYS@PROD2 > exec proc2
PL/SQL procedure successfully completed.
Elapsed: 00:00:32.84
5，清空snow.t2表中的数据，truncate使用0秒19
SYS@PROD2 > truncate table snow.t2;
Table truncated.
Elapsed: 00:00:00.19
6，使用绑定变量的存储过程执行时间为2秒20
SYS@PROD2 > exec proc1
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.20
7，删除snow.t2表中的数据，delete使用0秒84
SYS@PROD2 > delete from snow.t2;
100000 rows deleted.
Elapsed: 00:00:00.84
对比一下执行效果，不使用绑定变量的存储过程执行时间32.84秒，使用绑定变量的存储过程的时间为2.30，差距巨大。TOM的试验中插入1万条数据，我的实验中插入了10万条数据。如果插入的数据更多，并发更多，这个执行效率会更加悬殊。
Oracle提供了一个解决方案就是修改cursor_sharing的参数。通过对该参数的修改可以改进其“辨识度”。如果是类似于上面的例子中where字句中的变量不一样，应用程序中又没有采取绑定变量的做法，修改cursor sharing参数为similar活着force都会使解析器放宽要求，将他们视为相同的语句，从而减少硬解析的次数。
SYS@PROD1 > show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
alter system set cursor_sharing=similar;
这种做法只有那些已经上线的系统，并且不能更改应用程序中的SQL语句时才建议使用。
Shared Pool中包含两大核心pool，分别是Data dictionary cache和Library cache。
Data dictionary : 数据字典多数为静态的、固定的。
Library cache：经过编译、解析的SQL语句放在Library cache中。随着用户执行SQL语句的增多，执行PL/SQL代码的增多，Library cache会有被装满的时候。有效的管理其空间大小就显得格外重要。Oracle采用LRU算法来维持那些语句保留在Library cache中，哪些语句被清除。最近最少使用的SQL就是被清除对象的首选。
SQL语句在Library cache保留的是该语句的源代码么？比如select * from employees;答案是否定的，它保存的是该SQL语句的哈希值。
对SQL语句的解析分为两种，一种是硬解析另一种是软解析。硬解析是遇到从未执行过的SQL第一次为其解析的过程。软解析是再次遇到该SQL时查找已经解析过的解析结果。硬解析代价大于软解析。对比两个SQL是否相同采取的办法是对比其哈希值。如果哈希值相同就认为SQL语句相同。
在SQL语句中的字母大小写和缩进、空格等细微的差别都会导致看起来相同的SQL语句其对应的哈希值不同。下面的3条SQL语句在Library cache中就会被判断成2个不同的SQL语句，不能被重用。每次执行都需要一次硬解析。
select * from snow.t1;
SELECT * FROM SNOW.T1;
Library cache中最可怕的性能杀手要数“高并发下的非绑定变量SQL”。如果一个简单的查询语句，只是在where条件中的参数不同，会被当做不同的SQL，每次执行前进行一次硬解析。比如去营业厅查询话费：
select * from t where phone_number=‘18611010001’;
select * from t where phone_number=‘18611019527’;
这两个SQL语句只有phone_number的值不同导致SQL的哈希不同。如果并发量高，查询次数多，library cache的缓存作用基本被闲置了，必然导致执行效率低下。
这里引用TOM的经典实验来验证绑定变量的重要性
实验步骤：
1，创建存储过程proc1，使用绑定变量的方式插入10万条记录
SYS@PROD2 > create or replace procedure proc1
2 as
3 begin
4 for i in 1..100000
5 loop
6 execute immediate
7 'insert into snow.t2 values(:i)' using i;
8 end loop;
9 end;
10 /
Procedure created.
2，创建存储过程proc2，不适用绑定变量的方式插入10万条记录
SYS@PROD2 > create or replace procedure proc2
2 as
3 begin
4 for i in 1..100000
5 loop
6 execute immediate
7 'insert into snow.t2 values('||i||')' ;
8 end loop;
9 end;
10 /
Procedure created.
3，设置计时器
SYS@PROD2 > set timing on
4，不使用绑定变量的存储过程执行时间为32秒84
SYS@PROD2 > exec proc2
PL/SQL procedure successfully completed.
Elapsed: 00:00:32.84
5，清空snow.t2表中的数据，truncate使用0秒19
SYS@PROD2 > truncate table snow.t2;
Table truncated.
Elapsed: 00:00:00.19
6，使用绑定变量的存储过程执行时间为2秒20
SYS@PROD2 > exec proc1
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.20
7，删除snow.t2表中的数据，delete使用0秒84
SYS@PROD2 > delete from snow.t2;
100000 rows deleted.
Elapsed: 00:00:00.84
对比一下执行效果，不使用绑定变量的存储过程执行时间32.84秒，使用绑定变量的存储过程的时间为2.30，差距巨大。TOM的试验中插入1万条数据，我的实验中插入了10万条数据。如果插入的数据更多，并发更多，这个执行效率会更加悬殊。
Oracle提供了一个解决方案就是修改cursor_sharing的参数。通过对该参数的修改可以改进其“辨识度”。如果是类似于上面的例子中where字句中的变量不一样，应用程序中又没有采取绑定变量的做法，修改cursor sharing参数为similar活着force都会使解析器放宽要求，将他们视为相同的语句，从而减少硬解析的次数。
SYS@PROD1 > show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
alter system set cursor_sharing=similar;
这种做法只有那些已经上线的系统，并且不能更改应用程序中的SQL语句时才建议使用。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29047826/viewspace-1334522/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
