[20211118]mutexprof脚本使用简介.txt

来源：这里教程网时间：2026-03-03 17:13:20 作者：

[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.

编辑推荐：

返回列表

[20211118]mutexprof脚本使用简介.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推