[20211118]mutexprof指令碼使用簡介.txt

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