[20250206]21c library cache mutex的深入探究使用的sql脚本.txt

[20250206]21c library cache mutex的深入探究使用的sql脚本.txt --//在做21c library cache mutex的深入探究使用一些sql脚本，附在每天文章后面太繁琐，单独写一篇汇总。 --//其中有一些来自tpt的脚本,比如fchaz.sql(我仅仅注解prompt信息,并做小量修改),ti.sql,不再贴出。 $ cat hidez.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_value format a22 select    a.indx + 1 num,    to_char(a.indx + 1, 'XXXX') n_hex,    a.ksppinm  name,    a.ksppdesc DESCRIPTION,    b.ksppstdf DEFAULT_VALUE,    b.ksppstvl SESSION_VALUE,    c.ksppstvl SYSTEM_VALUE,    DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,    DECODE        (           BITAND (a.ksppiflg / 65536, 3)          ,1, 'IMMEDIATE'          ,2, 'DEFERRED'          ,3, 'IMMEDIATE'          ,'FALSE'        ) ISSYS_MODIFIABLE from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx  and a.indx = c.indx -- and lower(a.ksppinm) like lower('%&1%') --escape '\' --and regexp_like (lower(a.ksppinm)||' '||lower(a.ksppdesc) ,lower('&1') --and regexp_like (lower(a.ksppdesc)||' '||lower(a.ksppinm) ,lower('&1') and regexp_like (lower(a.ksppinm) ,lower('&1') ) order by 1; $ cat opeek.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- Name:        opeek.sql -- Purpose:     oradebug peek -- Author:      lfree -- Usage: --     @ opeek <addr> <length> <1|0> -- argv3 : 1 -- write tracename and display 0 -- display -- touch zero.sql -------------------------------------------------------------------------------- set term off col 1 new_value 1 select replace(lower('&&1'),'x','') "1" from dual; column tt new_value var select decode(&&3,1,'ti','zero') tt  from dual; oradebug setmypid set term on @ &var. --@ ti oradebug peek 0x&1 &2 &3 prompt $ cat sharepool/shp4z.sql column N0_6_16 format 99999999 column fcura_addrlen new_value _fcura_addrlen  format 999 column handle_type format a22 set termout off select vsize(addr)*2 fcura_addrlen from x$dual; set termout on select * from (SELECT DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')        handle_type,        kglhdadr,        kglhdpar,        --//substr(kglnaobj,1,40) c40,            substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,            KGLHDLMD,            KGLHDPMD,            kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,            kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,            kglnahsh,            kglobt03,            kglobt09   FROM x$kglob  WHERE     KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0') or  KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0') or  KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0') --or  KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0') --or  KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0') --or  KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0') --or  KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0') --or  KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0') or  KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0') or  KGLOBT03 = lower('&1') or  KGLNAHSH= &2 ) where kglhdadr=kglhdpar; --//mutexprofz.sql根据tpt mutexprof.sql做了修改，采用sum(slppes)，而不是取max(sleeps)：  $ 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=20 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       , 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           , 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,                 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       , 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 /