ITPub博客

首页 > 数据库 > Oracle > [20211118]mutexprof脚本使用简介.txt

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

原创 Oracle 作者:lfree 时间:2021-11-19 09:18:05 1 删除 编辑

[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 ()
-- 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 ()
-- 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 ()
-- 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 ()
-- 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博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2843009/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3081
  • 访问量
    6806380