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

posted @   lfree  阅读(4)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2024-02-19 [20240219]建立完善sql_idx.sh脚本.txt
2019-02-19 [20190219]那个更快(11g).txt
点击右上角即可分享
微信分享提示