select rownum,v.* from v$version v where rownum < 3;
1 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 2 PL/SQL Release 11.2.0.1.0 - Production
select * from V$session s where event='cursor: mutex S' --sid=3791 --2668 --username = 'SIEBEL' and --schemaname = 'SIEBEL' and machine = 'appcn788' order by s.LOGON_TIME desc
select * from v$parameter p where p.NAME like '%cursor_sharing%'
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT 1 1491 cursor_sharing 2 EXACT EXACT TRUE
编码硬解析的改进方法
1.更改参数cursor_sharing
参数cursor_sharing决定了何种类型的SQL能够使用相同的SQL area
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
EXACT --只有当发布的SQL语句与缓存中的语句完全相同时才用已有的执行计划。
FORCE --如果SQL语句是字面量,则迫使Optimizer始终使用已有的执行计划,无论已有的执行计划是不是最佳的。
SIMILAR --如果SQL语句是字面量,则只有当已有的执行计划是最佳时才使用它,如果已有执行计划不是最佳则重新对这个SQL语句进行分析来制定最佳执行计划。
可以基于不同的级别来设定该参数,如ALTER SESSION, ALTER SYSTEM
sys@ASMDB> show parameter cursor_shar --查看参数cursor_sharing
更改cursor_sharing参数:
alter system set cursor_sharing=
去年在生产库碰到此问题,给折磨了好一阵后,把cursor_sharing改为force后解决。 /***************************************************************************** created by husthxd 2010-12-16 *****************************************************************************/
一、问题诊断 通过检查出问题时的statspack报告诊断数据库。 1、数据库实例状态 Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.95 Optimal W/A Exec %: 100.00 Library Hit %: 62.49 Soft Parse %: 96.42 Execute to Parse %: 15.47 Latch Hit %: 99.94 Parse CPU to Parse Elapsd %: 49.25 % Non-Parse CPU: 75.08
从以上信息可以看出: Library Hit偏低(通常应>95%); Execute to Parse明显偏低,每Execute 10次,需Parse 8.5次,Parse是耗CPU的操作,这也是导致CPU居高不下的原因。 从后续的系统统计视图也可以看出: Statistic Time (s) % DB time ----------------------------------- -------------------- --------- parse time elapsed 2,602.9 99.2
2、Top 5 Wait Events 从top 5等待事件可以看出与Library和Cursor相关的等待很高,合计占所有等待时间的98.8% Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ library cache lock 21,461 923 43 55.6 cursor: mutex S 5,220,286 717 0 43.2 CPU time 12 .7 db file sequential read 418 3 7 .2 log file sync 773 1 2 .1 -------------------------------------------------------------
3、检查Wait Events Wait Events (fg and bg) DB/Inst: SDMZLW/sdmzlw Snaps: 5-6 Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ library cache lock 21,461 0 923 43 32.7 55.6 cursor: mutex S 5,220,286 100 717 0 7,945.6 43.2 注意:等待时间cursor: mutex S的等待超时(Time Out)是100%,与出问题时数据库一直在执行简单的查询语句而没有返回的现象吻合。
二、解决方案 使用cursor: mutex S 关键字google,从找到的相关资料可以看出 mutex是10g以后出现的内存结构,在10g默认没有使用,在11g才开始启用,之所以出现问题有可能是因为数据库的bug引起的。 解决方法: 1/把数据库参数cursor_sharing设置为force(可在线修改)。 2/停止自动收集统计信息的job 3/停止AWR收集JOB 4/出现上述情况主要是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。因为SQL的并行太多,将该SQL复制成N个其它的SQL。 如: select /*SQL 1*/ object_name from vw_mzlw_cbxx_bl where <条件语句> select /*SQL 2*/ object_name from vw_mzlw_cbxx_bl where <条件语句> select /*SQL …*/ object_name from vw_mzlw_cbxx_bl where <条件语句> select /*SQL N*/ object_name from vw_mzlw_cbxx_bl where <条件语句> 这样就有了N个SQL Cursor,N个Mutex内存结构,就将争用分散开来,类似partition的作用了。
具体做法是在java拼装sql的时候,人为的加入区别sql的语句: 如:在select语句后加注释语句"/**/" SELECT /*SQL <随机产生一个1-500的值>*/ * from vw_x1 where <条件语句> SELECT /*SQL <随机产生一个1-500的值>*/ * from vw_x2 where <条件语句>