[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
/
--//在做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
/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2024-02-19 [20240219]建立完善sql_idx.sh脚本.txt
2019-02-19 [20190219]那个更快(11g).txt