一：参数SESSION_CACHED_CURSORS,就是说的是一个session可以缓存多少个cursor（软软解析），让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能，软解析同硬解析一样,比较消耗资源。
二：open_cursors设定每个session（会话）最多能同时打开多少个cursor（游标），是相对于SPA中的share pool下libarary cache而言。session_cached_cursor设定每个session（会话）最多可以缓存多少个关闭掉的cursor，是相对于PGA而言。
三：另外检查SESSION_CACHED_CURSORS参数是否设置的合理，可以从两个statistic来检查，即select name,value from v$sysstat where name like '%cursor%';和select name,value from v$sysstat where name like '%parse%';
１．创造环境：
SYS@ORA11GR2>create table t1 as select * from all_objects;
SYS@ORA11GR2>desc t1
SYS@ORA11GR2>select count(*) from t1;
COUNT(*)
----------
84999
２．当session_cached_cursors＝50进行操作
SYS@ORA11GR2>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
1）直接进行查询操作（无绑定变量）：
SYS@ORA11GR2>select count(*) from t1 where object_id=101;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=102;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=103;
COUNT(*)
----------
1
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
三次执行SQL都是硬解析,都是解析1次，执行一次；
2）用绑定变量执行sql查询语句：
SYS@ORA11GR2>var v_id number;
SYS@ORA11GR2>exec :v_id:=101;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=102;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=103;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=:v_id 3 1 3
绑定变量执行的后执行三次，硬解析1此，软解析2次，共解析3次，执行了3次，软解析发挥了作用；
SYS@ORA11GR2>exec :v_id:=104;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
再次验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=:v_id 4 1 4
3）绑定变量循环执行五次操作：
begin
for i in 1 .. 5 loop
execute immediate 'select count(*) from t1 where object_id=:1'
using i + 100;
end loop;
end;
/
SYS@ORA11GR2>begin
2 for i in 1..5 loop
3 execute immediate 'select count(*) from t1 where object_id=:1' using i+100;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=:1 1 1 5
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=:v_id 3 1 3
即总共解析了1次，而执行了五次，剩余四次的执行也没有经过解析就直接执行了，即软软解析；
软软解析，定义为同一sql连续执行了三次以上，则将sql的执行计划放到server process的pga中，下次再执行同一sql时跳过解析阶段直接执行。
３．当session_cached_cursors＝０时进行操作：
1)修改session_cached_cursor参数值为0；
SYS@ORA11GR2>show parameter session_cached_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
SYS@ORA11GR2>alter system set session_cached_cursors=0 scope=spfile;
System altered.
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>show parameter session_cached_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
2）直接进行查询操作（无绑定变量）：
SYS@ORA11GR2>select count(*) from t1 where object_id=101;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=102;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=103;
COUNT(*)
----------
1
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id= 103 1 1 1
三次硬解析
3）用绑定变量执行sql查询语句：
SYS@ORA11GR2>var v_id number;
SYS@ORA11GR2>exec :v_id:=101;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=102;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=103;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=：v_id 3 1 3
软解析，共3次解析，后2次软解析，执行3次；
4）绑定变量循环执行五次操作：
begin
for i in 1 .. 5 loop
execute immediate 'select count(*) from t1 where object_id=:1'
using i + 100;
end loop;
end;
/
SYS@ORA11GR2>begin
2 for i in 1..5 loop
3 execute immediate 'select count(*) from t1 where object_id=:1' using i+100;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
验证查看解析执行情况：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=：1 5 1 5
select count(*) from t1 where object_id=：v_id 3 1 3
此次没有参数软软解析，表明当session_cached_cursors值设为0时，不产生软软解析（不管同一语句执行了多少次）；
open_cursors与session_cached_cursor：
a、两个参数之间没有任何关系，相互也不会有任何影响。
b、两个参数有着相同的作用：让后续相同的sql语句不在打开游标，从而避免软解析过程来提供应用程序的效率。
C、pga中的cursor（软软解析）管理采用LRU原则，当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126425/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126425/
145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
