[20211118]mutexprof脚本使用简介.txt
--//mutexprof是 Tanel Poder编写的脚本，用于定位有关mutex的性能问题。抽空学习一下。
--//实际上刚一使用就遇到一个小问题。开发写的sql语句使用\r chr(13)换行，导致我执行sqlplus的显示界面是乱的。
--//我只能自己修改：
, nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)') msh_obj_name
--//替换为如下
, replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') msh_obj_name
--, nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)') msh_obj_name
--//一般很少生产系统出现mutex作为主要的等待事件的情况,这样也就很少关注，除非遇到某个bug之类的情况。
--//实际上该命令包是自解析的，看看前面介绍基本知道如何使用。
-- Usage: @mutexprof <grouping columns> <filter condition>
--
-- The main grouping (and filtering) columns are:
--
-- id - mutex ID (which is the object hash value for library
-- cache object mutexes)
-- ts - timestamp of mutex sleep beginning
-- loc - code location where the waiter slept for the mutex
-- val - mutex value (shows whether mutex was held in exclusive or
-- shared mode)
-- req - requesting session SID
-- blk - blocking session SID
--
-- The filter condition allows filtering mutex sleep rows based on certain
-- criteria, such:
--
-- 1=1 - show all mutex sleeps (which are still in memory)
-- blk=123 - show only these mutex sleeps where blocking sid was 123
-- hash=2741853041 - show only these sleeps where mutex ID (KGL object hash value)
-- was 2741853041
--
--
-- Its also possible to have multiple "AND" filter conditions, as long as you keep
-- them in double quotes so that sqlplus would recognize them as one parameter
--
-- For example: "name like '%DUAL%' and blk in (115,98)"
--
-- Examples:
--
-- @mutexprof loc 1=1
-- @mutexprof id,loc,req,blk "lower(name) like 'select%from dual%'"
-- @mutexprof loc,val blk=98
-- @mutexprof id,loc,req,blk "blk in (select sid from v$session where username = 'SYS')"
--
-- Other: When the relevant object is aged out you will see (name not found)
-- as object_name.
--
-- On 10.2.0.1 the V$mutex_sleep_history does not have mutex_identifier
-- column externalized. In this case use X$mutex_sleep_history instead
ywdb> @mutexprof id,hash,loc ts>sysdate-15/1440
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
SLEEPS SLEEPS MUTEX_TYPE ID HASH GET_LOCATION OBJECT_NAME
---------- ------- --------------- ---------- ---------- --------------------------------- -------------------
6702 Library Cache 2536268875 2536268875 kglpsl1 38 MC$LOGFP2
5685 Library Cache 25675 kglhdgn1 62 (name not found)
4731 Library Cache 2536268875 2536268875 kgllkdl1 85 MC$LOGFP2
--//不要被前面第1列的SLEEPS迷惑，实际上这里记录的是最大值，第2列是最大减去最小的sleep，仅仅作为一个参考。
--//V$mutex_sleep_history来源gV$mutex_sleep_history,gV$mutex_sleep_history来源x$MUTEX_SLEEP_HISTORY,
--//x$MUTEX_SLEEP_HISTORY里面有一个重要字段MUTEX_ADDR,通过它可以定位具体对象。当然通过hash_value(MUTEX_IDENTIFIER)值也可
--//以，但是它仅仅是大概的范围。
--//我自己始终对loc的缩略写指向的具体位置不了解。
XXXX2> @mutexprof id,hash,loc ts>sysdate-5/1440
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
SLEEPS SLEEPS MUTEX_TYPE ID HASH GET_LOCATION OBJECT_NAME
---------- ------- --------------- ---------- ---------- --------------------------------- -----------------
739 597 Library Cache 2810066130 2810066130 kglpnal2 91 HZMCASSET.TAUDIT
4 Library Cache 3231142607 3231142607 kglpin1 4 SYS.DBMS_STANDARD
XXXX1> @mutexprof id,hash,loc ts>sysdate-1/1440
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
SLEEPS SLEEPS MUTEX_TYPE ID HASH GET_LOCATION OBJECT_NAME
---------- ------- --------------- ---------- ---------- --------------------------------- --------------------------------------------------------------------------------
30223 28932 Library Cache 3771887287 3771887287 kglget1 1 select count ( 0 ) from BSOFT_TEST_CONNECT
20833 19437 Library Cache 3771887287 3771887287 kgllkdl1 85 select count ( 0 ) from BSOFT_TEST_CONNECT
2187 Library Cache 1879237386 1879237386 kglpnal2 91 SYS.SQL_TXT
2033 744 Cursor Pin 2589686205 2589686205 kkslce [KKSCHLPIN2] SELECT PD_LSDL.DLID, PD_LSDL.RDID, PD_LSDL.RDSJ,
PD_LSDL.PDHM, PD_LSDL.PDCY, PD_LSDL.PDZT,
PD_LSDL.KSSJ, PD_LSDL.JSSJ, PD_LSDL.DDSJ,
PD_LSDL.WCSJ, PD_LSDL.JLSJ, PD_LSDL.SLGH,
PD_LSDL.SLRM, PD_LSDL.KSID, PD_LSDL.KSMC,
PD_LSDL.YSID, PD_LSDL.YSXM, PD_LSDL.QTID,
PD_LSDL.QTMC, PD_LSDL.YWLB, PD_LSDL.YWID,
PD_LSDL.BRID FROM PD_LSDL WHERE PD_LSDL.WCSJ >= :adt_Begin AND
PD_LSDL.WCSJ < :adt_End AND PD_LSDL.SLGH = :as_slgh
--//前面2条是我们生产系统登录必须执行的sql语句，而且几乎每个schema下都有这样一个表。调用很频繁。我没有把一些程序设置为
--//cursor_sharing=force.
--// SELECT PD_LSDL.DLID .. 有时候真心无语，我已经提交，这条语句无法执行的，没有PD_LSDL.DLID字段。
XXXX1> @ sharepool/shp4 0 2589686205
TEXT KGLHDADR KGLHDPAR KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000130AEEDE08 0000001302AB8D68 0 0 6506874 00 00 0 0 6724 6724 6724 2589686205 b0u9avfd5qydx 0
child handle address 000000130A6A84A8 0000001302AB8D68 0 0 3467 00 00 0 0 6724 6724 6724 2589686205 b0u9avfd5qydx 1
child handle address 0000001306CCEA98 0000001302AB8D68 0 0 3 00 00 0 0 6724 6724 6724 2589686205 b0u9avfd5qydx 2
child handle address 0000001306CCE928 0000001302AB8D68 0 0 4 00 00 0 0 6724 6724 6724 2589686205 b0u9avfd5qydx 3
child handle address 000000008C0859D8 0000001302AB8D68 0 0 1 00 00 0 0 6724 6724 6724 2589686205 b0u9avfd5qydx 4
parent handle address 0000001302AB8D68 0000001302AB8D68 1 0 6510349 0000001308A69CF0 00 9464 0 0 9464 9464 2589686205 b0u9avfd5qydx 65535
6 rows selected.
--//子光标没有堆0，堆6信息。
--//10g早期版本V$mutex_sleep_history没有mutex_identifier字段，建议使用X$mutex_sleep_history代替。
--//我建立新的mutexprofx.sql,加入mutex_addr字段。
$ diff -Nur mutexprof.sql mutexprofx.sql
--- mutexprof.sql 2021-11-18 10:10:18.000000000 +0800
+++ mutexprofx.sql 2021-11-19 08:51:49.000000000 +0800
@@ -63,6 +63,8 @@
col mutexprof_p4 head P4 for a16 wrap
col mutexprof_p5 head P5 for a20 wrap
+col maddr head mutex_addr for a20 wrap
+
def MSH_NUMROWS=20
prompt
@@ -98,13 +100,14 @@
, blocking_session blk
, location loc
, mutex_value val
+ , mutex_addr maddr
, p1
, p1raw
, p2
, p3
, p4
, p5
- from v$mutex_sleep_history) m
+ from x$mutex_sleep_history) m
, (select kglnahsh, kglnahsh hash_value, kglnahsh hash,
kglhdpar, kglhdadr, kglnaown, kglnaobj,
decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) object_name,
XXXX1> @mutexprofx id,hash,loc,maddr ts>sysdate-1/1440
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
SLEEPS SLEEPS MUTEX_TYPE ID HASH GET_LOCATION mutex_addr OBJECT_NAME
---------- ------- --------------- ---------- ---------- --------------------------------- -------------------- ------------------------------------------
237618 236174 Library Cache 3771887287 3771887287 kglget1 1 000000130B9B3560 select count ( 0 ) from BSOFT_TEST_CONNECT
2109 700 Library Cache 3771887287 3771887287 kgllkdl1 85 000000130B9B3560 select count ( 0 ) from BSOFT_TEST_CONNECT
--//这样可以获取mutex_addr.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2843009/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
