It is widely known that the Oracle server uses kslgetl – Kernel Service Lock Management Get Latch function to acquire the latch,oradebug call kslgetl/kslfre can be used to acquire the latch manually. This is very useful to simulate latch related hangs and contention.
Oracle 11g externalizes latch structures in x$kslltr_parent and x$kslltr_children fixed tables for parent and child latches respectively,view v$latch_parent and v$latch_children were created on thers tables, Versions 10g and before used x$ksllt table,since 11g used x$kslltr table , Fixed views v$latch these tables.
x$ksmfsv kernel service, memory fixed SGA variables detailing fixed SGA:
select a.ksmfsnam, a.ksmfstyp, a.ksmfssiz, b.ksmmmval
from x$ksmfsv a, x$ksmmem b
where a.ksmfsadr = b.addr and a.ksmfsnam like…
that they point to. X$KSMMEM has one row for every memory address in the SGA,. For a latch, get ksmfsnam by matching x$ksmfsv.ksmfadr (KSMFSADR in 11g)with x$kslld.kslldadr(v$latchname created by this table). You can see SGA parameters in ksmfsnam column and get their values with oradebug dumpvar varname or all values with oradebug dumpsga
The Redo Allocation Latch as Seen from X$KSMFSV
on 10g
select ksmfsnam, ksmfstyp, ksmfssiz, ksmfsadr from x$ksmfsv where ksmfsnam like 'kcrfal%'
on 11g
Now we holde the latch manually.For several years it was commonly supposed that kslgetl() has two parameters:[latch address] [wait].
To release the latch
Notice:
Never perform the function on your production environment,if to hold redo allocation latch possible hit the follow problem event
ORA-00600: internal error code, arguments: [504], [0x0F5519E28], [32], [1], [session idle bit]
ORA-07445: exception encountered: core dump [ksl_hierarchy_error()+560] [SIGSEGV] [ADDR:0xFB5D8A530] [PC:0x4860A22] [Address not mapped to object]
Now to hold all shared pool latches ,try to let DB instance hang.
Print/Dump memory of one shared pool latch with oradebug peek
oradebug peek [level]
oradebug poke
Tip:
oradebug poke function directly modify the memory not like oradebug call kslgetl
Create new session try to logon DB with sqlplus will be hang(eg. sqlplus anbob/pwd or sqlplus / as sysdba)
Trace system state dump
Tip:
The system dump trace file path will be written in alert log ,the following output:
Wed Aug 07 16:40:36 2013
System State dumped to trace file /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
To release all holded shared pool latches
oracle@db231 ~]$ sqlplus anbob/anbob
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 17:04:55 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
anbob@ANBOB>
Oracle 11g externalizes latch structures in x$kslltr_parent and x$kslltr_children fixed tables for parent and child latches respectively,view v$latch_parent and v$latch_children were created on thers tables, Versions 10g and before used x$ksllt table,since 11g used x$kslltr table , Fixed views v$latch these tables.
x$ksmfsv kernel service, memory fixed SGA variables detailing fixed SGA:
select a.ksmfsnam, a.ksmfstyp, a.ksmfssiz, b.ksmmmval
from x$ksmfsv a, x$ksmmem b
where a.ksmfsadr = b.addr and a.ksmfsnam like…
that they point to. X$KSMMEM has one row for every memory address in the SGA,. For a latch, get ksmfsnam by matching x$ksmfsv.ksmfadr (KSMFSADR in 11g)with x$kslld.kslldadr(v$latchname created by this table). You can see SGA parameters in ksmfsnam column and get their values with oradebug dumpvar varname or all values with oradebug dumpsga
The Redo Allocation Latch as Seen from X$KSMFSV
on 10g
select ksmfsnam, ksmfstyp, ksmfssiz, ksmfsadr from x$ksmfsv where ksmfsnam like 'kcrfal%'
on 11g
sys@ANBOB>select ksmfsnam, ksmfstyp, ksmfssiz, ksmfsadr from x$ksmfsv where ksmfsnam ='kcrfral_';
KSMFSNAM KSMFSTYP KSMFSSIZ KSMFSADR
-------------------- -------------------- -------------------- ----------------
kcrfral_ ksllt 160 0000000060022778
sys@ANBOB>select a.KSMFSADR,b.KSLLTNAM,b.ksmfsnam,b.KSMFSTYP from x$kslltr a, x$ksmfsv b
where b.KSMFSADR=a.KSLLTADDR and ksmfsnam ='kcrfral_';
KSMFSADR KSLLTNAM KSMFSNAM KSMFSTYP
---------------- ------------------------------ -------------------- --------------------
0000000060022778 redo allocation kcrfral_ ksllt
sys@ANBOB> select k.ksmfsadr, ksmfsnam, ksmfstyp, ksmfssiz, kslldnam, kslldlvl
2 from x$ksmfsv k, x$kslld a
3 where k.ksmfsadr = a.kslldadr
4 and ksmfsnam = 'kcrfral_'
5* order by ksmfsnam
KSMFSADR KSMFSNAM KSMFSTYP KSMFSSIZ KSLLDNAM
---------------- --------------- --------------- ------------- ------------------------
0000000060022778 kcrfral_ ksllt 160 redo allocation
Now we holde the latch manually.For several years it was commonly supposed that kslgetl() has two parameters:[latch address] [wait].
sys@ANBOB>oradebug call kslgetl 0x0000000060022778 1
Function returned 1
sys@ANBOB>select * from v$latchholder;
PID SID LADDR NAME GETS
-------------------- -------------------- ---------------- ------------------------------ --------------------
19 96 0000000060022778 redo allocation 391
sys@ANBOB>oradebug peek 0x0000000060022778 20
[060022778, 06002278C) = 00000016 00000000 00000187 000000BB 00000005
To release the latch
sys@ANBOB>oradebug call kslfre 0x0000000060022778 0
Function returned 0
sys@ANBOB>oradebug peek 0x0000000060022778 20
[060022778, 06002278C) = 00000000 00000000 00000187 000000BB 00000005
SQL> select * from v$latchholder;
no rows selected
Notice:
Never perform the function on your production environment,if to hold redo allocation latch possible hit the follow problem event
ORA-00600: internal error code, arguments: [504], [0x0F5519E28], [32], [1], [session idle bit]
ORA-07445: exception encountered: core dump [ksl_hierarchy_error()+560] [SIGSEGV] [ADDR:0xFB5D8A530] [PC:0x4860A22] [Address not mapped to object]
Now to hold all shared pool latches ,try to let DB instance hang.
Print/Dump memory of one shared pool latch with oradebug peek
oradebug peek
oradebug poke
sys@ANBOB>oradebug setmypid
Statement processed.
sys@ANBOB>oradebug peek 0x60107A78 4
[060107A78, 060107A7C) = 00000000
sys@ANBOB>select addr from v$latch_children where name='shared pool';
ADDR
----------------
0000000060107A78
00000000601079D8
0000000060107938
0000000060107898
00000000601077F8
0000000060107758
00000000601076B8
sys@ANBOB>select 'oradebug poke 0x'||addr||' 4 0x01' poke_sha from v$latch_children where name='shared pool';
sys@ANBOB>oradebug poke 0x0000000060107A78 4 0x01
BEFORE: [060107A78, 060107A7C) = 00000000
AFTER: [060107A78, 060107A7C) = 00000001
sys@ANBOB>oradebug poke 0x00000000601079D8 4 0x01
BEFORE: [0601079D8, 0601079DC) = 00000000
AFTER: [0601079D8, 0601079DC) = 00000001
sys@ANBOB>oradebug poke 0x0000000060107938 4 0x01
BEFORE: [060107938, 06010793C) = 00000000
AFTER: [060107938, 06010793C) = 00000001
sys@ANBOB>oradebug poke 0x0000000060107898 4 0x01
BEFORE: [060107898, 06010789C) = 00000000
AFTER: [060107898, 06010789C) = 00000001
sys@ANBOB>oradebug poke 0x00000000601077F8 4 0x01
BEFORE: [0601077F8, 0601077FC) = 00000000
AFTER: [0601077F8, 0601077FC) = 00000001
sys@ANBOB>oradebug poke 0x0000000060107758 4 0x01
BEFORE: [060107758, 06010775C) = 00000000
AFTER: [060107758, 06010775C) = 00000001
sys@ANBOB>oradebug poke 0x00000000601076B8 4 0x01
BEFORE: [0601076B8, 0601076BC) = 00000000
AFTER: [0601076B8, 0601076BC) = 00000001
Tip:
oradebug poke function directly modify the memory not like oradebug call kslgetl
sys@ANBOB>oradebug peek 0x60107A78 4
[060107A78, 060107A7C) = 00000001
Create new session try to logon DB with sqlplus will be hang(eg. sqlplus anbob/pwd or sqlplus / as sysdba)
Trace system state dump
[oracle@db231 ~]$ sqlplus -prelim
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 16:39:06 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / as sysdba
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
idle>oradebug setmypid
Statement processed.
idle>oradebug dump systemstate 266;
Statement processed.
idle>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
idle>exit
Tip:
The system dump trace file path will be written in alert log ,the following output:
Wed Aug 07 16:40:36 2013
System State dumped to trace file /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
[oracle@db231 ~]$ awk -f ass109.awk /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
Starting Systemstate 1
..............................
Ass.Awk Version 1.0.9 - Processing /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
System State 1
~~~~~~~~~~~~~~~~
1:
2: 0: waiting for 'pmon timer'
3: 0: waiting for 'rdbms ipc message'
4: 0: waiting for 'VKTM Logical Idle Wait'
5: 0: waiting for 'rdbms ipc message'
6: 0: waiting for 'DIAG idle wait'
7: 0: waiting for 'rdbms ipc message'
8: 0: waiting for 'DIAG idle wait'
9: 0: waiting for 'rdbms ipc message'
10: 0: waiting for 'rdbms ipc message'
11: 0: waiting for 'rdbms ipc message'
12: 0: waiting for 'rdbms ipc message'
13: 0: waiting for 'smon timer'
14: 0: waiting for 'latch: shared pool'[Latch 601076b8]
15: 0: waiting for 'latch: shared pool'[Latch 601076b8]
16: 0: waiting for 'rdbms ipc message'
17:
18:
19: 0: waiting for 'SQL*Net message from client'
20: 0: waiting for 'Streams AQ: qmn slave idle wait'
21: 0: waiting for 'latch: shared pool'[Latch 60107758]
22: 0: waiting for 'SQL*Net message from client'
23: 0: waiting for 'latch: shared pool'[Latch 60107758]
24: 0: waiting for 'Space Manager: slave idle wait'
25: [Latch 601076b8]
26: [Latch 60107758]
27: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
30: 0: waiting for 'latch: shared pool'[Latch 60107758]
40: 9: waited for 'Streams AQ: waiting for time management or cleanup tasks'
41: 0: waiting for 'rdbms ipc message'
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.
Resource Holder State
Latch 601076b8 ??? Blocker
Latch 60107758 ??? Blocker
Object Names
~~~~~~~~~~~~
Latch 601076b8 Child shared pool
Latch 60107758 Child shared pool
To release all holded shared pool latches
[oracle@db231 ~]$ sqlplus -prelim '/ as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 16:59:12 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
idle>oradebug setmypid
Statement processed.
idle>oradebug poke 0x0000000060107A78 4 0x00
BEFORE: [060107A78, 060107A7C) = 00000001
AFTER: [060107A78, 060107A7C) = 00000000
idle>oradebug poke 0x00000000601079D8 4 0x00
BEFORE: [0601079D8, 0601079DC) = 00000001
AFTER: [0601079D8, 0601079DC) = 00000000
idle>oradebug poke 0x0000000060107938 4 0x00
BEFORE: [060107938, 06010793C) = 00000001
AFTER: [060107938, 06010793C) = 00000000
idle>oradebug poke 0x0000000060107898 4 0x00
BEFORE: [060107898, 06010789C) = 00000001
AFTER: [060107898, 06010789C) = 00000000
idle>oradebug poke 0x00000000601077F8 4 0x00
BEFORE: [0601077F8, 0601077FC) = 00000001
AFTER: [0601077F8, 0601077FC) = 00000000
idle>oradebug poke 0x0000000060107758 4 0x00
BEFORE: [060107758, 06010775C) = 00000001
AFTER: [060107758, 06010775C) = 00000000
idle>oradebug poke 0x00000000601076B8 4 0x00
BEFORE: [0601076B8, 0601076BC) = 00000001
AFTER: [0601076B8, 0601076BC) = 00000000
sys@ANBOB>oradebug peek 0x60107A78 4
[060107A78, 060107A7C) = 00000000
oracle@db231 ~]$ sqlplus anbob/anbob
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 17:04:55 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
anbob@ANBOB>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
