通过session_cached_cursors 参数来减少软解析次数。
--装备测试的脚本
1.sql.sql
set echo off;
set feedback off;
set termout off;
select /*+session_cursor_test_11*/count(*) from scott.emp where EMPNO=11;
select /*+session_cursor_test_12*/count(*) from scott.emp where EMPNO=12;
select /*+session_cursor_test_13*/count(*) from scott.emp where EMPNO=13;
select /*+session_cursor_test_14*/count(*) from scott.emp where EMPNO=14;
select /*+session_cursor_test_15*/count(*) from scott.emp where EMPNO=15;
select /*+session_cursor_test_16*/count(*) from scott.emp where EMPNO=16;
select /*+session_cursor_test_17*/count(*) from scott.emp where EMPNO=17;
select /*+session_cursor_test_18*/count(*) from scott.emp where EMPNO=18;
select /*+session_cursor_test_19*/count(*) from scott.emp where EMPNO=19;
select /*+session_cursor_test_20*/count(*) from scott.emp where EMPNO=20;
set termout on;
2.cursor.sql
col name for a30
col value for 999999
SELECT sn.name, ss.value
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('session cursor cache hits', --缓存命中次数
'session cursor cache count',--缓存的SQL条数
'parse count (total)') --parse的总次数
AND ss.sid = (select sid from v$mystat where rownum<=1);
3.parse_count.sql
select substr(sql_text,-2) NO,users_opening,FETCHES,EXECUTIONS,PARSE_CALLS,LOADS from v$sql
where sql_text like '%+session_cursor_test_%'
and sql_text not like '%like%'
order by sql_text;
1.sql.sql
set echo off;
set feedback off;
set termout off;
select /*+session_cursor_test_11*/count(*) from scott.emp where EMPNO=11;
select /*+session_cursor_test_12*/count(*) from scott.emp where EMPNO=12;
select /*+session_cursor_test_13*/count(*) from scott.emp where EMPNO=13;
select /*+session_cursor_test_14*/count(*) from scott.emp where EMPNO=14;
select /*+session_cursor_test_15*/count(*) from scott.emp where EMPNO=15;
select /*+session_cursor_test_16*/count(*) from scott.emp where EMPNO=16;
select /*+session_cursor_test_17*/count(*) from scott.emp where EMPNO=17;
select /*+session_cursor_test_18*/count(*) from scott.emp where EMPNO=18;
select /*+session_cursor_test_19*/count(*) from scott.emp where EMPNO=19;
select /*+session_cursor_test_20*/count(*) from scott.emp where EMPNO=20;
set termout on;
2.cursor.sql
col name for a30
col value for 999999
SELECT sn.name, ss.value
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('session cursor cache hits', --缓存命中次数
'session cursor cache count',--缓存的SQL条数
'parse count (total)') --parse的总次数
AND ss.sid = (select sid from v$mystat where rownum<=1);
3.parse_count.sql
select substr(sql_text,-2) NO,users_opening,FETCHES,EXECUTIONS,PARSE_CALLS,LOADS from v$sql
where sql_text like '%+session_cursor_test_%'
and sql_text not like '%like%'
order by sql_text;
--查看一下session_cached_cursors的参数值。
SLQ>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SQL>@sql
SQL>@sql
SQL>@sql
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 3 3 1
12 3 3 1
13 3 3 1
14 3 3 1
15 3 3 1
16 3 3 1
17 3 3 1
18 3 3 1
19 3 3 1
20 3 3 1
--关注PARSE_CALLS 和USERS_OPENING 的值都是3次
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 0
session cursor cache count 0 --为0 表示没有缓存
parse count (total) 151
--接下来修 session_cached_cursors 的值为9 (小于要SQL脚本中的SQL的条数)
SQL>alter session set session_cached_cursors = 5;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 0 --没有命中的
session cursor cache count 5 --缓存了5个cursor
parse count (total) 150
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 6 6 1
12 6 6 1
13 6 6 1
14 6 6 1
15 6 6 1
16 6 6 1
17 6 6 1
18 6 6 1
19 6 6 1
20 6 6 1
--关注PARSE_CALLS 和USERS_OPENING 的值都是6次
--接下来修 session_cached_cursors 的值为15 (大于要SQL脚本中的SQL的条数)
SQL>alter session set session_cached_cursors = 15;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 24 --命中的24次
session cursor cache count 11 --缓存了11个cursor 因为还有其它SQL运行不一定等于sql文件里的sql个数
parse count (total) 183
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 7 9 1
12 7 9 1
13 7 9 1
14 7 9 1
15 7 9 1
16 7 9 1
17 6 9 1
18 6 9 1
19 6 9 1
20 6 9 1
--关注USERS_OPENING 的值小于PARSE_CALLS 的值证明缓存已经生效了。
--再运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
NAME VALUE
------------------------------ -------
session cursor cache hits 56
session cursor cache count 11
parse count (total) 215
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 7 12 1
12 7 12 1
13 7 12 1
14 7 12 1
15 7 12 1
16 7 12 1
17 6 12 1
18 6 12 1
19 6 12 1
20 6 12 1
--关注USERS_OPENING的值没有随着PARSE_CALLS 的增长而增长。
因为软解析是串行操作
所以可以通过将session_cached_cursors设置为一个适当的值
减少软解析的次数从而提高数据库的整体性能。
SLQ>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SQL>@sql
SQL>@sql
SQL>@sql
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 3 3 1
12 3 3 1
13 3 3 1
14 3 3 1
15 3 3 1
16 3 3 1
17 3 3 1
18 3 3 1
19 3 3 1
20 3 3 1
--关注PARSE_CALLS 和USERS_OPENING 的值都是3次
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 0
session cursor cache count 0 --为0 表示没有缓存
parse count (total) 151
--接下来修 session_cached_cursors 的值为9 (小于要SQL脚本中的SQL的条数)
SQL>alter session set session_cached_cursors = 5;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 0 --没有命中的
session cursor cache count 5 --缓存了5个cursor
parse count (total) 150
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 6 6 1
12 6 6 1
13 6 6 1
14 6 6 1
15 6 6 1
16 6 6 1
17 6 6 1
18 6 6 1
19 6 6 1
20 6 6 1
--关注PARSE_CALLS 和USERS_OPENING 的值都是6次
--接下来修 session_cached_cursors 的值为15 (大于要SQL脚本中的SQL的条数)
SQL>alter session set session_cached_cursors = 15;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
SQL>@cursor
NAME VALUE
------------------------------ -------
session cursor cache hits 24 --命中的24次
session cursor cache count 11 --缓存了11个cursor 因为还有其它SQL运行不一定等于sql文件里的sql个数
parse count (total) 183
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 7 9 1
12 7 9 1
13 7 9 1
14 7 9 1
15 7 9 1
16 7 9 1
17 6 9 1
18 6 9 1
19 6 9 1
20 6 9 1
--关注USERS_OPENING 的值小于PARSE_CALLS 的值证明缓存已经生效了。
--再运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
NAME VALUE
------------------------------ -------
session cursor cache hits 56
session cursor cache count 11
parse count (total) 215
--查看解析的情况
SQL>@parse_count
NO USERS_OPENING PARSE_CALLS LOADS
---- ------------- ----------- ----------
11 7 12 1
12 7 12 1
13 7 12 1
14 7 12 1
15 7 12 1
16 7 12 1
17 6 12 1
18 6 12 1
19 6 12 1
20 6 12 1
--关注USERS_OPENING的值没有随着PARSE_CALLS 的增长而增长。
因为软解析是串行操作
所以可以通过将session_cached_cursors设置为一个适当的值
减少软解析的次数从而提高数据库的整体性能。
如何设置选择session_cached_cursors的值呢!
我们可以通过查v$sesstat中每个session 的
session cursor cache hits
session cursor cache count
parse count (total)
这三个事件来设置 session_cached_cursors
1.如果 "session cursor cache count" 等于 "session_cached_cursors" 参数值 而且
"parse count (total)" 与 "session cursor cache hits" 相差很大的session 很多
就证明需要增加 session_cached_cursors 的取值。
2.在调整 session_cached_cursors的取值 时不应该大于 open_cursors 参数的值。
3.如果存在大量的session "session cursor cache count"小于 session_cached_cursors 的取值
"parse count (total)" 与 "session cursor cache hits" 相差又很大,说明是open_cursors参数
限制了缓存的cursor 的个数 应适当增大open_cursors的取值。
我们可以通过查v$sesstat中每个session 的
session cursor cache hits
session cursor cache count
parse count (total)
这三个事件来设置 session_cached_cursors
1.如果 "session cursor cache count" 等于 "session_cached_cursors" 参数值 而且
"parse count (total)" 与 "session cursor cache hits" 相差很大的session 很多
就证明需要增加 session_cached_cursors 的取值。
2.在调整 session_cached_cursors的取值 时不应该大于 open_cursors 参数的值。
3.如果存在大量的session "session cursor cache count"小于 session_cached_cursors 的取值
"parse count (total)" 与 "session cursor cache hits" 相差又很大,说明是open_cursors参数
限制了缓存的cursor 的个数 应适当增大open_cursors的取值。
最后提供一个好用的脚本
SELECT ss.SID,
sum(decode(sn.name,'session cursor cache hits',ss.value,0)) s_cursor_cache_hits,
sum(decode(sn.name,'session cursor cache count',ss.value,0)) s_cursor_cache_counts,
sum(decode(sn.name,'parse count (total)',ss.value,0)) parse_count
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('session cursor cache hits',
'session cursor cache count',
'parse count (total)')
group by ss.sid
order by 3
SELECT ss.SID,
sum(decode(sn.name,'session cursor cache hits',ss.value,0)) s_cursor_cache_hits,
sum(decode(sn.name,'session cursor cache count',ss.value,0)) s_cursor_cache_counts,
sum(decode(sn.name,'parse count (total)',ss.value,0)) parse_count
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('session cursor cache hits',
'session cursor cache count',
'parse count (total)')
group by ss.sid
order by 3
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26022000/viewspace-734320/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26022000/viewspace-734320/