11gR2游标共享新特性带来的一些问题以及_cursor_features_enabled、_cursor_obsolete_threshold和106001 event
版本11gR2中引入cursor sharing游标共享和mutex互斥锁增强的一些特性,而这些特性也带来了一些问题(主要体现在版本11.2.0.1和11.2.0.2上,11.2.0.3上基本已经修复)。
Cursor Obsolescence游标废弃是一种SQL Cursor游标管理方面的增强特性,该特性启用后若parent cursor父游标名下的子游标child cursor总数超过一定的数目,则该父游标parent cursor将被废弃,同时一个新的父游标将被开始。 这样做有2点好处:
- 避免进程去扫描长长的子游标列表child cursor list以找到一个合适的子游标child cursor
- 废弃的游标将在一定时间内被age out,其占用的内存可以被重新利用
版本11.1.0.7 SQL> alter system set "_cursor_features_enabled"=18 scope=spfile; System altered. SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile; System altered. 并重启实例 版本11.2.0.1 SQL> alter system set "_cursor_features_enabled"=34 scope=spfile; System altered. SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile; System altered. 版本11.2.0.2 SQL> alter system set "_cursor_features_enabled"=1026 scope=spfile; System altered. SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile; System altered. 11gR2 Experience -> If using cursor_sharing = “FORCE” or “SIMILAR” 1) ORA-600 errors as workload increases [kkspsc0: basehd] or [kglLockOwnersListAppend-ovf] - applied patches to address 2) AWR showing -> cursor: mutex S and library cache lock 1. Download and apply the 11.2.0.2.3PSU Patch 11724916 2. Enable event 106001 to address Bug 10187168. To enable the fix "_cursor_features_enabled" needs to be set 3) Oracle 11.2.0.2.2 PSU (Patch Set Update) includes new parameters that you can tweak based on workload characteristics. Even more fixes have been added Note: 10411618 - Enhancement to add different "Mutex" wait schemes [ID 10411618.8] 4) 11.2.0.3 Has many Mutex enhancement’s 106001 level The level is used to specify the maximum number of child cursors that a parent can have before we obsolete the parent cursor and create a new parent. Doing the above can help reduce mutex waits, memory consumption and other side effects seen when we see many child cursors for a given parent.题外话是11.2.0.3中的Mutex增强了很多,不要再跟着初学者论坛的那帮家伙一起愚蠢地大喊:"虽然版本升级 8i=>9i=>10g=>11g=>12c,但是我觉得oracle里面基础、核心的东西一直都没变了"这种神话了, 你一直浮游在Oracle的表面怎么可能知道Kernel到底有多大的变化?!!
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(492) 评论(0) 编辑 收藏 举报