|
If you were logged in you would be able to see more operations.
|
|
|
| Product Version: |
11.2.0.3.7
|
| Operating System: |
Solaris
|
| Host Name: |
.
|
| Database Name: |
.
|
The customer has upgraded from Oracle 10.2.0.5 to Oracle 11.2.0.3.7. They encountered library cache lock problem.
Some excerpt from the AWR:
|
|
Description
|
The customer has upgraded from Oracle 10.2.0.5 to Oracle 11.2.0.3.7. They encountered library cache lock problem.
Some excerpt from the AWR:
|
Show » |
|
Change by ubTools Support - 27/Jul/13 01:56 PM
|
| Field |
Original Value |
New Value |
|
Comment
|
[ The next step is to search Mutex 22c1df2d30 in SYSTEMSTATE trace to find the blocker.
]
|
|
|
Change by ubTools Support - 28/Jul/13 03:57 PM
|
|
Comment
|
[ An excerpt from ERRORSTACK LEVEL3 3 trace of SID#2413:
{noformat}
KGX Atomic Operation Log 220b3014f8
Mutex 22c1df2c18(2413, 0) idn d4d88873 oper EXCL
Cursor Parent uid 2413 efd 5 whr 1 slp 0
oper=OPERATION_DEFAULT pt1=0 pt2=0 pt3=0
pt4=0 u41=0 stt=0
KGX Atomic Operation Log 220b301548
Mutex 22c1df2d30(0, 1) idn d4d88873 oper GET_EXCL
hash table uid 2413 efd 5 whr 4 slp 36444
oper=OPERATION_DEFAULT pt1=22bcfe1bc8 pt2=22bcfe1f80 pt3=22bcfe1ba8
pt4=0 u41=0 stt=0
{noformat}
It is the same mutex 0x22c1df2d30(oper GET_EXCL) is requested in EXCL mode for IDN 0xd4d88873.
]
|
|
|
Change by ubTools Support - 28/Jul/13 03:57 PM
|
|
Comment
|
[ The customer reproduced the problem next day by running the original SQL.
An excerpt from HANGANALYZE LEVEL3 trace:
{noformat}
Oracle session identified by:
{
instance: 1 (opusdata.opusdata)
os id: 14155
process id: 527, oracle@allianzsun21
session id: 2413
session serial #: 18262
}
is waiting for 'cursor: mutex X' with wait info:
{
p1: 'idn'=0xd4d88873
p2: 'value'=0x1
p3: 'where'=0x400000000
time in wait: 0.000000 sec
heur. time in wait: 8 min 49 sec
timeout after: never
wait id: 4724362
blocking: 0 sessions
current sql: <originalSQLText>
short stack: ksedsts()+380<-ksdxfstk()+52<-ksdxcb()+3524<-sspuser()+140<-__sighndlr()+12<-
call_user_handler()+992<-sigacthandler()+104<-__pollsys()+8<-_pollsys()+260<-_pselect()+496<-_select()+160<-
skgpwwait()+664<-ksliwat()+1752<-kslwaitctx()+144<-kgxWait()+484<-kgxExclusive()+308<-kkshhcdel()+276<-
kksfbc()+6244<-kkspsc0()+1320<-kksParseCursor()+100<-opiosq0()+1904<-kpooprx()+212<-kpoal8()+536<-
opiodr()+1164<-ttcpip()+916<-opitsk()+1640<-opiino()+924<-opiodr()+1164<-opidrv()+1032<-sou2o()+88<-
opimai_real()+504<-ssthrdmain()+3
wait history:
* time between current wait and wait #1: 0.000007 sec
1. event: 'cursor: mutex X'
time waited: 0.000003 sec
wait id: 4724361 p1: 'idn'=0xd4d88873
p2: 'value'=0x1
p3: 'where'=0x400000000
* time between wait #1 and #2: 0.000008 sec
2. event: 'cursor: mutex X'
time waited: 0.000003 sec
wait id: 4724360 p1: 'idn'=0xd4d88873
p2: 'value'=0x1
p3: 'where'=0x400000000
* time between wait #2 and #3: 0.000006 sec
3. event: 'cursor: mutex X'
time waited: 0.000003 sec
wait id: 4724359 p1: 'idn'=0xd4d88873
p2: 'value'=0x1
p3: 'where'=0x400000000
}
Chain 12 Signature: 'cursor: mutex X'
Chain 12 Signature Hash: 0xad67b469
{noformat}
As seen above, the original SQL waited again although the original SQL text has been replaced with a new SQL text yesterday.
]
|
|
|
Change by ubTools Support - 28/Jul/13 04:05 PM
|
|
Comment
|
[ *Workaround:*
Change the SQL text of SQL waiting for _cursor: mutex X_. The SQL text is available in the ERRORSTACK trace.
]
|
|
|
Change by ubTools Support - 28/Jul/13 04:42 PM
|
|
Summary
|
Self lock on "cursor: mutex X"
|
"cursor: mutex X"
|
|
Description
|
The customer has upgraded from Oracle 10.2.0.5 to Oracle 11.2.0.3.7. The encountered _library cache lock_ problem.
Some excerpt from the AWR:
{noformat}
Elapsed: 15.01 (mins)
DB Time: 6,548.72 (mins)
.....
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
library cache lock 425 364,186 856909 92.69 Concurrency
enq: TX - row lock contention 288 11,500 39930 2.93 Application
TCP Socket (KGAS) 136,317 9,552 70 2.43 Network
DB CPU 4,167 1.06
db file sequential read 742,541 2,176 3 0.55 User I/O
{noformat}
|
The customer has upgraded from Oracle 10.2.0.5 to Oracle 11.2.0.3.7. They encountered _library cache lock_ problem.
Some excerpt from the AWR:
{noformat}
Elapsed: 15.01 (mins)
DB Time: 6,548.72 (mins)
.....
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
library cache lock 425 364,186 856909 92.69 Concurrency
enq: TX - row lock contention 288 11,500 39930 2.93 Application
TCP Socket (KGAS) 136,317 9,552 70 2.43 Network
DB CPU 4,167 1.06
db file sequential read 742,541 2,176 3 0.55 User I/O
{noformat}
|
|
Change by ubTools Support - 07/Mar/25 08:18 AM
|
|
Status
|
Open
[ 1
]
|
Closed
[ 6
]
|
|
Resolution
|
|
Answered
[ 10
]
|
|
Oracle - Database Tuning
Closed
Major
"cursor: mutex X"