cursor: mutex S 等待严重，请帮看一下awr报告
oracle 11.2.0.1 64位，Enterprise Linux Server 5.5。奇怪的是，堵塞时杀进程也解决不了，但是只要重启，就能正常地跑一天左右时间，反复多次都是这样。
等待的SQL就是正常的登录系统的SQL，每天要运行很多遍。 Event Waits Time(s) Avg wait (ms) % DB time Wait Class
cursor: mutex S 1,842,896 26,271 14 43.23 Concurrency
DB CPU 11,860 19.52
library cache: mutex X 10,544 4,815 457 7.92 Concurrency
db file sequential read 364,820 3,858 11 6.35 User I/O
log file sync 44,500 2,713 61 4.46 Commit
Parses: 296.9 17.7
Hard parses: 15.2 0.9
软解析算不上多 ，但是cursor: mutex S 是TOP 等待
但cursor: mutex S可能是 library cache: mutex X 的受害者
Mutex Sleep Summary
ordered by number of sleeps desc
Mutex Type Location Sleeps Wait Time (ms)
Library Cache kglhdgn2 106 5,085,665 914
hash table kkshGetNextChild 2,842,463 0
Library Cache kglIsOwnerVersionable 121 2,101,573 40
Library Cache kglpndl1 95 1,306,097 0
Library Cache kgllkdl1 85 1,071,303 628
Library Cache kglpin1 4 544,533 28
Library Cache kgllkc1 57 538,696 1
Library Cache kglUpgradeLock 119 337,472 -0
Library Cache kglhdgn1 62 328,908 100
Cursor Pin kkslce 251,236 1,765
Library Cache kglpnal1 90 163,062 0
Library Cache kglget2 2 153,151 958
Library Cache kgllkal3 82 82,645 0
Library Cache kgldtin1 42 11,119 -0
hash table kkscsSearchChildList 2,779 0
Cursor Stat kkocsStoreBindAwareStats 614 0
Cursor Pin kksLockDelete 457 0
Library Cache kglini1 32 53 -0
Cursor Pin kksfbc 40 0
Library Cache kglobld1 75 21 -0
Library Cache kglpur1 28 12 0
hash table kkshListChanged 7 0
Library Cache kglobpn1 71 5 -0
Library Cache kglrfcl1 79 1 -137
Cursor Parent kkscsAddChildNodeToNewChild [KKSPRTLOC33 1 0
Cursor Pin kksfbc 1 12
Cursor Pin kksfbc 1 0
Cursor Parent kksfbc 1 0
kglIsOwnerVersionable 、kglhdgn2 、kkshGetNextChild Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations
APP CONTEXT 1,920 0.00 1,920 0.00 0 0
BODY 12,334 0.01 406,248 0.00 0 0
CLUSTER 155 0.00 29 0.00 0 0
DBLINK 2,534 0.00 0 0 0
EDITION 1,361 0.00 2,707 0.00 0 0
INDEX 105 0.00 82 0.00 0 0
QUEUE 60 0.00 1,644 0.00 0 0
RULESET 2 0.00 2 0.00 0 0
SCHEMA 2,638 0.00 0 0 0
SQL AREA 386,575 36.21 24,951,188 0.59 54,839 53,674
SUBSCRIPTION 24 0.00 24 0.00 0 0
TABLE/PROCEDURE 451,862 0.00 1,325,036 0.00 0 0
TRIGGER 2,117 0.00 2,847 0.00 0 0
11.2.0.1 与 library cache: mutex X 相关的bug 较多：
NB Bug Fixed Description
16232803 11.2.0.4, 12.1.0.2, 12.2.0.0 Library cache lock / mutex contention with fix 8508078 enabled
14401731 11.2.0.3.BP20, 11.2.0.4, 12.1.0.1 Cursor leak using "SET TRANSACTION" within PLSQL
16864042 11.2.0.4, 12.2.0.0 Unnecessary library cache operations for DDLs on partitions in exadata
16400122 12.2.0.0 Spikes in library cache mutex contention for SQL using SQL Plan Baseline
15858022 11.2.0.4, 12.2.0.0 Frequent invalidation of tuning objects with VPD
13810393 11.2.0.2.10, 11.2.0.2.BP20, 11.2.0.3.6, 11.2.0.3.BP15, 11.2.0.4, 12.1.0.1 Deadlock waiting for 'library cache: mutex x' while producing an ORA-4031 diagnostic dump
13720753 11.2.0.4, 12.1.0.1 Diagnosability issue - mutex wait "idn" value truncated in session wait output in tracefile
13588248 11.2.0.2.BP17, 11.2.0.3.3, 11.2.0.3.BP06, 11.2.0.4, 12.1.0.1 "library cache: mutex X" contention for objects with very many library cache locks
12976376 11.2.0.2.6, 11.2.0.2.BP16, 11.2.0.3.3, 11.2.0.3.BP05, 12.1.0.1 High VERSION_COUNT for SQL with binds, including recursive dictionary SQL - superseded
12797420 11.2.0.2.11, 11.2.0.2.BP21, 11.2.0.3.3, 11.2.0.3.BP07, 11.2.0.4, 12.1.0.1 "library cache: mutex X" waits on DB instance handle with CURSOR_SHARING
11818335 11.1.0.7.9, 11.2.0.2.2, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.1 Additional support for bug 10411618 to allow dynamic Mutex wait scheme changes
10632113 11.2.0.3, 12.1.0.1 OLS calls cause mutex contention even with low number of concurrent users
10417716 11.2.0.2.5, 11.2.0.2.BP05, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1 Mutex X waits in 11G on an instance with heavy JAVA usage
10284845 11.2.0.3, 12.1.0.1 Need index on SID for X$KGLLK and X$KGLPN based views (V$OPEN_CURSOR etc..)
10284838 11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1 Cache line contention while waiting for mutexes
10204505 11.2.0.3, 12.1.0.1 SGA autotune can cause row cache misses, library cache reloads and parsing
10086843 11.2.0.3, 12.1.0.1 Recursive SQL cursors not reused - PMON crashes instance with ORA-600
9530750 11.2.0.2, 12.1.0.1 High waits for 'library cache: mutex X' for cursor Build lock
9239863 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1 Excessive "library cache:mutex X" contention on hot objects
8981059 11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1 High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking
8860198 11.2.0.2, 12.1.0.1 "library cache:mutex X" waits using XMLType
7352775 11.2.0.2, 12.1.0.1 Many child cursors when PARALLEL_INSTANCE_GROUP set wrong
12670165 11.2.0.2.4, 11.2.0.2.BP12, 11.2.0.3 Library cache mutex contention caused by lookup of triggers
12633340 11.2.0.2.6, 11.2.0.2.BP13, 11.2.0.3 Heavy "library cache lock" and "library cache: mutex X" contention for a "$BUILD$.xx" lock
8793492 10.2.0.5, 11.2.0.1.BP07, 11.2.0.2 Mutex Waits with Resource Manager
10145558 11.1.0.7.7, 11.2.0.1.BP12, 11.2.0.2 Selects on library cache V$/X$ views cause "library cache: mutex X" waits
9398685 11.2.0.2 High "library cache: mutex X" when using Application Context
9282521 11.2.0.2 Excessive "library cache:mutex X" contention on hot objects
9140262 11.2.0.2 ORA-600 followed by cpu spike/"library cache: mutex X" Waits
9003145 11.2.0.1.BP03, 11.2.0.2 Dump (kglIsOwnerVersionable) / "library cache: mutex X" waits
建议你至少升级到11.2.0.4 ， 或者考虑 开一些隐藏参数 本帖最后由 anbob 于 2014-1-7 19:32 编辑
Host Mem (MB): 32,158.8 32,158.8
SGA use (MB): 16,310.8 16,310.8
PGA use (MB): 3,882.6 6,740.2
% Host Mem used for SGA+PGA: 62.79 71.68
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 24050 07-Jan-14 09:00:46 439 5.1
End Snap: 24051 07-Jan-14 10:00:56 916 6.4
nstance Activity Stats - Absolute Values
Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
session pga memory max 14,818,616,448 20,047,630,808
session cursor cache count 999,731 1,104,960
session uga memory 12,601,180,608,328 12,946,951,806,720
opened cursors current 2,253 5,883
workarea memory allocated 1,744,644 2,262,316
logons current 439 916
session pga memory 10,380,637,088 13,062,129,848
session uga memory max 60,562,623,456 66,684,737,528
你的session数一小时增长了一倍，不知道是否正常？用hugepage了么？启用flashback database了吧？high version count sql一条，会不会与simulator 有关？加大seq cache.
ash，里是不是能发现点东西呢？？ 谢谢各位，准备升级数据库版本了。。
页:
[1]