[20251221]建立完善mutexprofz.sql脚本.txt

[20251221]建立完善mutexprofz.sql脚本.txt --//以tpt写的mutexprof.sql为蓝本，做了许多修改。 --//首先作者采用 MAX(sleeps)应该不对，也许10g正确，11g以后不对，应该换成sum(sleeps). --//增加sql_id,以及增加更多字段hash，hash_value.mutex_addr地址。 --//另外生产系统sql语句带有chr(13)，会导致执行时显示混乱，过滤掉它。 $ cat mutexprofz.sql -------------------------------------------------------------------------------- -- -- File name:   mutexprof.sql ( Mutex sleep Profiler ) -- -- Purpose:     Display KGX mutex sleep history from v$mutex_sleep_history --              along library cache object names protected by these mutexes. --              Only top 20 rows are shown by default -- -- Author:      Tanel Poder -- Copyright:   (c) http://www.tanelpoder.com --               -- 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 -- -------------------------------------------------------------------------------- col msh_obj_name   head OBJECT_NAME for  a80 word_wrap col msh_mutex_type head MUTEX_TYPE for a15 truncate col loc   head GET_LOCATION for a33 truncate   col mutexprof_gets   head GETS_DIFF for 9999999999999 col mutexprof_sleeps head SLEEPS for 999999 col mutexprof_p2 head P2 for a16 wrap col mutexprof_p3 head P3 for a16 wrap 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=50 prompt prompt -- MutexProf by Tanel Poder (http://www.tanelpoder.com) prompt -- Showing profile of top &MSH_NUMROWS sleeps... prompt -- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp prompt --               req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr select * from (     select /*+ ORDERED USE_NL(o) */       -- TODO the sleep/get counting needs fixing!       --MAX(sleeps)               sleeps       sum(sleeps)               sum_sleeps       --count(*)                sleeps       --, decode(max(sleeps)-min(sleeps),0,to_number(null),max(sleeps)-min(sleeps)) mutexprof_sleeps -- may not be very accurate but give an idea       , decode(max(gets)-min(gets),0,to_number(null),max(gets)-min(gets)) mutexprof_gets -- may not be very accurate but give an idea       --  avg(sleeps)         sleeps       --, avg(gets)           gets       , mutex_type          msh_mutex_type       , &1       , sql_id       , 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       --, p1raw       --, CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END mutexprof_p2       --, CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END mutexprof_p3       --, CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END mutexprof_p4       --, p5 mutexprof_p5     from         (select             mutex_identifier   id           , mutex_identifier   idn           , to_char (mutex_identifier, 'fm0XXXXXXX') idnhex           , sleep_timestamp    ts           , mutex_type           , gets           , sleeps           , requesting_session req           , blocking_session   blk           , location           loc           , mutex_value        val           , mutex_addr         maddr           , p1           , p1raw           , p2           , p3           , p4           , p5          from x$mutex_sleep_history) m       , (select kglnahsh, kglnahsh hash_value, kglnahsh hash,                 kglhdpar, kglhdadr, kglnaown, kglnaobj, kglobt03 sql_id,kglhdbid kgl_bucket,                 decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) object_name,                 decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj) name          from x$kglob) o     where         m.id = o.kglnahsh (+)     and (o.kglhdadr = o.kglhdpar or (o.kglhdpar is null)) -- only parent KGL objects if still in cache     and &2     group by         mutex_type       , &1       , sql_id       , kglnaown       , kglnaobj       , p1raw       , CASE WHEN p2 < 536870912 THEN TO_CHAR(p2) ELSE TRIM(TO_CHAR(p2, 'XXXXXXXXXXXXXXXX')) END       , CASE WHEN p3 < 536870912 THEN TO_CHAR(p3) ELSE TRIM(TO_CHAR(p3, 'XXXXXXXXXXXXXXXX')) END       , CASE WHEN p4 < 536870912 THEN TO_CHAR(p4) ELSE TRIM(TO_CHAR(p4, 'XXXXXXXXXXXXXXXX')) END       --, p5     order by         1 desc        -- sleeps desc ) where rownum <= &MSH_NUMROWS /