ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 11g 中 cursor_sharing 设置为SIMILAR 导致的问题

Oracle 11g 中 cursor_sharing 设置为SIMILAR 导致的问题

原创 Linux操作系统 作者:xz43 时间:2012-07-23 13:20:00 0 删除 编辑

由于是一个老项目,程序里面基本没有用绑定变量,所以,原来使用Oracle9i的时候,为了减少SQL的解析次数,特意把 cursor_sharing 参数设置为SIMILAR ,提高系统的性能。这次按客户要求将系统迁移,把原来的9i数据库直接升级为11g,对于这块也没细想,按照原来在9i上的参数设置方式对11g数据库参数进行设置。

最近,系统经常性的突然宕掉,很是奇怪。因为系统包括三台web服务器和一台数据库服务器,web服务器前面用的F5做负载均衡,按道理一下子三台web同时出问题的可能性不大。而且在系统不能登录的情况下,分别检测3web服务器的物理内存和CPU使用情况,发现资源利用率也不高,不应该是由于web服务器的物理资源问题造成的。分析3web的日志文件,也没有找到明细的导致问题的错误信息。

初步怀疑是Oracle数据库的问题,如是检测了警告日志和监听日志,一切正常,没有任何错误信息。如是手动创建一个快照后,重启数据库服务,重启完后,3web上的应用麻烦恢复正常,控制台也可以正常登录。查看awr报告,发现top event如下:

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

cursor: mutex S

25,509,723

106,490

4

59.49

Concurrency

library cache lock

4,432,424

50,705

11

28.33

Concurrency

DB CPU

 

19,996

 

11.17

 

library cache: mutex X

694,774

2,246

3

1.25

Concurrency

log file sync

9,120

7

1

0.00

Commit

可以看出,主要的Wait Class都是Concurrency,证明这个系统并发很高。各种Wait Class解释参考后面附录。

根据最占时间的Event cursor: mutex S去网上查找,发现遇到这个问题的还不少,基本都是在Oracle 11g使用上遇到的,解决办法就是把cursor_sharing 参数设置为FORCE,不需要重启数据库。如是按要求设置了该参数,经过了几天的考验,系统运行正常,不再像以前那样平均一天宕一次了。现在的Top Event如下:

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

DB CPU

 

1,326

 

99.34

 

log file sync

31,957

14

0

1.07

Commit

SQL*Net more data to client

88,957

3

0

0.21

Network

direct path read

265,631

2

0

0.15

User I/O

asynch descriptor resize

1,448,518

1

0

0.10

Other

Oracle Database 10g Enhanced wait model

Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.

Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).

Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.

Administrative: These are waits imposed by a privileged users by some action.

Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.

Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.

Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.

User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON

System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON

Scheduler: These are waits due to the resource manager

Cluster: waits which will occur only in RAC mode.

Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-736379/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-16

  • 博文量
    407
  • 访问量
    1721313