不必纠结于session_cached_cursors的内存损耗

session cursor cache 特性旨在减少软解析soft parse的性能损耗。通过将session_cached_cursor参数设置为一个大于0 的值启动会话session缓存游标的机制。

重复的对统一语句的解析调用经过3次以后将该语句的游标缓存移入到session cursor cache中,后续的解析调用将在session 缓存中找到对应的游标并不再需要重复打开该游标(askmac.cn)。

注意仅仅是那些重复引发三次解析调用的语句将被缓存,该机制避免了随机游标和硬绑定的游标浪费缓存。

主要UGA中的KKSUG结构定位缓存中的游标状态。kksug.kksugscc存放了session cached cursor信息数据。kksug.kksugscc.kkssclru 代表游标链表,kksug.kksugscc.kkssccnt代表session cursor cache中的游标数量,kksug.kksugscc.kksschit代表cursor cache的命中次数,即游标在session cursor cache中(askmac.cn)正好被找到的次数。

 

一般不需要特别去关心session_cached_cursors参数的内存损耗,一来这些session cursor cache中的栏位slot是可以被重用的,而且每一个slot也不会占用太多内存,一般在几十个字节左右。

同时如上所述KKSUG是位于UGA中的,所以在dedicated server mode独立服务器模式下session cursor cache在PGA=>UGA中, 在共享服务器模式下shared server mode下载SGA=>UGA中。

 

 

@+-> structkkssc @{
/* Session cached cursors */
 /* open_cursors */ 
/* List of open curdef's */ 
/* Number of cursors open */ 
/* List of closed curdef's */ 
/* Cached frames and binds list */
kkssc kksugscc; ub4 kksugmoc; 
kgglk kksugocl; ub4 kksugopc; 
kgglk kksugccl; 
kgglk kksugcfb;
@ /* front is the MRU side the tail is the LRU side */
@ kgglk kkssclru; 
/* linked list of cursors in session cursor cache */
@ kksccht *kksschst;
 /* hash table of cursors in the session cache */
@ ub4 kksscmcc; 
/* max number of cursors allowed in cache */
@ ub4 kkssccnt; 
/* count of cursors in session cursor cache */
@ ub4 kksschit; 
/* session cache cursor hits */
@ };

Comments

  1. Session_Cached_Cursors8483739 has some very interesting notes on this so I suggest that you review the bug for more information. Here are some highlights: Say you have 100 sessions through connection pooling. Each one can cache 500 cursors in them. Each session has only a ‘working set’ of an application module say 50 unique cursors (i.e SQL statements). Then if you set session_cached_cursors to 500 then you are over configuring session_cached_cursors by 450. When you over configure it, then say if one of the SQL statements produces many versions (too many version_counts) then 450 slots can be used to unnecessarily cache different versions of the same SQL statement. Worse part is if we never hit the 500 limit, that is the version count for one the SQL statement is worst case 200, then 249 session cached cursors will be cached and nothing would LRU out until we hit total of 500. 4031’s typically are tyraised because of over sizing of caches like session_cached_cursors or caching everything using cursor_space_for_time. Chances of this error happening is very high on upgrade boundaries because our cursor sharable memory goes by 20% release over release. So what used to take 1M would now 1.2M which would mean over caching make it the hit the celing very easily because these caches would make a large portion of the memory behave like ‘permanent memory’. . Sizing anything for database needs a reasonable understanding of the application. All caches have an ROI (return on investment), they come at a space time for cost. Caching some version of a SQL statement for 24 hours when it would never be used or will be used once in 24 hours is very bad. At the same time too little caching where every session cached cursor ages out every minute is also bad too. You need to size it reasonably right suing ‘working set’ numbers, hence it is an art. more than science.

  2. windtalker says

    session cursor cache hits 比parse count (total)大是合理的吗?
    http://www.itpub.net/forum.php?mod=viewthread&tid=2049312&extra=

  3. windtalker says

    session cursor cache hits 比parse count (total)大是合理的吗?
    http://www.itpub.net/forum.php?mod=viewthread&tid=2049312&extra=

windtalker进行回复 取消回复

*

沪ICP备14014813号-2

沪公网安备 31010802001379号