session_cached_cursors的研究

Oracle 10g後 預設將session_cached_cursors設定為20(之前為0) 表明了什麼？

Oracle建議在session中cache cursors.

當然前提是應用中是sharable SQL(充分繫結變數)

Why?

大量重複執行相同sql可以:

1 節省解析時間

2 降低cpu資源

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)

Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.

In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

用以下指令碼監控單個session的解析情況.我們可以看到"softer" soft parse 是怎麼產生的

--To show sql's parse infomations by a session when session_cached_cursors parameter has been set--
col name for a30
select a.value,b.name,s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid=a.sid
and b.name in ('opened cursors current','parse count (hard)','parse count (total)','session cursor cache count','session cursor cache hits')
and a.sid=&sid
/

解釋一下v$sesstat中相關取值的重要概念
1 > opened cursors current (當前單個session開啟的cursors數量)
2 > parse count (hard) (當前session硬解析次數)
3 > parse count (total) (當前session解析總次數)
4 > session cursor cache count (設定session_cached_cursors引數後,"相同"sql被解析3次後被cache在session cursor cache中的數量
5 > session cursor cache hits (軟軟解析的次數 a "softer" soft parse )
6 > 手工計算一下軟解析次數parse count (total) - session cursor cache hits - parse count (hard) = parse count(soft)

Ok 開始正式給出測試步驟

開啟兩個session
在Session B中監控session A每一步執行情況

SESSION A :

03:27:39 SQL> alter session set session_cached_cursors=100;

會話已更改。

03:28:04 SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ----------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 100
03:28:32 SQL> show user
USER 為 "LYON"

03:28:40 SQL> select distinct sid from v$mystat;

SID
----------
80

SESSION B:

在沒有執行sql前,SID為93的session相關sql解析資訊
03:27:17 SQL> --To show sql's parse infomations by a session when session_cached
_cursors parameter has been set--
03:31:35 SQL> col name for a30
03:31:35 SQL> select a.value,b.name,s.sid, s.serial#
03:31:35 2 from v$sesstat a, v$statname b, v$session s
03:31:35 3 where a.statistic# = b.statistic#
03:31:35 4 and s.sid=a.sid
03:31:35 5 and b.name in ('opened cursors current','parse count (hard)','pa
rse count (total)','session cursor cache count','session cursor cache hits')
03:31:35 6 and a.sid=&sid
03:31:35 7 /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
184 parse count (total) 80 3
24 parse count (hard) 80 3

SESSION A :
( test表由create table test as select * from dba_objects;在此試驗前已經建立 )

session A中執行一個簡單的sql語句
select count(*) from test where object_id=200;

03:30:01 SQL> select count(*) from test where object_id=200;

COUNT(*)
----------
0

SESSION B:
03:34:33 SQL> /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
186 parse count (total) 80 3
25 parse count (hard) 80 3

第一次執行select count(*) from test where object_id=50000;
Oracle執行一次hard parse .
相關資料字典檢視操作導致一次軟分析
parse count (total) 由184增加到186

第二次執行相同sql
SESSION A:

03:37:23 SQL> /

COUNT(*)
----------
1

SESSION B:
03:36:57 SQL> /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
187 parse count (total) 80 3
25 parse count (hard) 80 3

03:37:45 SQL>

Oracle內部執行一次軟分析

第三次執行相同sql
SESSION A
03:37:25 SQL> /

COUNT(*)
----------
1

03:39:48 SQL>

SESSION B
03:37:45 SQL> /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
159 session cursor cache hits 80 3
60 session cursor cache count 80 3
188 parse count (total) 80 3
25 parse count (hard) 80 3

03:40:04 SQL>

Oracle內部執行一次軟分析 同時由於相同sql被執行三次
所以session cursor cache count加1 (表示此sql已經被cache到了session的session cursor cache中)由於內部相關資料字典操作sql也被執行了3次所以session cursor cache count再加1

第四次執行相同sql
SESSION A
03:39:48 SQL> /

COUNT(*)
----------
1

03:40:36 SQL>

SESSION B
03:40:04 SQL> /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
160 session cursor cache hits 80 3
60 session cursor cache count 80 3
189 parse count (total) 80 3
25 parse count (hard) 80 3

03:40:53 SQL>

Oracle內部執行一次"softer" soft parse
我們看到session cursor cache hits加1

第五次執行相同sql
SESSION A

03:40:36 SQL> /

COUNT(*)
----------
1

03:41:12 SQL>

SESSION B
03:40:53 SQL> /
輸入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80

VALUE NAME SID SERIAL#
---------- ------------------------------ ---------- ----------
1 opened cursors current 80 3
161 session cursor cache hits 80 3
60 session cursor cache count 80 3
190 parse count (total) 80 3
25 parse count (hard) 80 3

03:41:44 SQL>

Oracle內部執行一次"softer" soft parse
至此我們可以看到Oracle使用"softer" soft parse代替soft parse.

轉載:http://foreverlee.itpub.net/index.php

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-160127/，如需轉載，請註明出處，否則將追究法律責任。