103 lines
2.8 KiB
SQL
103 lines
2.8 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: fcha.sql (Find CHunk Address) v0.2
|
|
-- Purpose: Find in which heap (UGA, PGA or Shared Pool) a memory address resides
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://blog.tanelpoder.com | @tanelpoder
|
|
--
|
|
-- Usage: @fcha <addr_hex>
|
|
-- @fcha F6A14448
|
|
--
|
|
-- Other: This would only report an UGA/PGA chunk address if it belongs
|
|
-- to *your* process/session (x$ksmup and x$ksmpp do not see other
|
|
-- session/process memory)
|
|
--
|
|
-- History: Jan 20 2021 Frits Hoogland Added query for fixed sga (x$ksmfsv)
|
|
--------------------------------------------------------------------------------
|
|
|
|
prompt Find in which heap (UGA, PGA, fixed SGA or Shared Pool) the memory address &1 resides...
|
|
prompt
|
|
prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
|
|
prompt in systems under load and with large shared pool. This may even completely hang
|
|
prompt your instance until the query has finished! You probably do not want to run this in production!
|
|
prompt
|
|
pause Press ENTER to continue, CTRL+C to cancel...
|
|
|
|
|
|
select
|
|
'VSGA' LOC,
|
|
KSMCHPTR,
|
|
KSMCHIDX,
|
|
KSMCHDUR,
|
|
KSMCHCOM,
|
|
KSMCHSIZ,
|
|
KSMCHCLS,
|
|
KSMCHTYP,
|
|
KSMCHPAR
|
|
from
|
|
x$ksmsp
|
|
where
|
|
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
|
|
between
|
|
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
|
|
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
|
|
union all
|
|
select
|
|
'UGA',
|
|
KSMCHPTR,
|
|
null,
|
|
null,
|
|
KSMCHCOM,
|
|
KSMCHSIZ,
|
|
KSMCHCLS,
|
|
KSMCHTYP,
|
|
KSMCHPAR
|
|
from
|
|
x$ksmup
|
|
where
|
|
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
|
|
between
|
|
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
|
|
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
|
|
union all
|
|
select
|
|
'PGA',
|
|
KSMCHPTR,
|
|
null,
|
|
null,
|
|
KSMCHCOM,
|
|
KSMCHSIZ,
|
|
KSMCHCLS,
|
|
KSMCHTYP,
|
|
KSMCHPAR
|
|
from
|
|
x$ksmpp
|
|
where
|
|
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
|
|
between
|
|
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
|
|
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
|
|
union all
|
|
select
|
|
'FSGA',
|
|
ksmfsadr,
|
|
null,
|
|
null,
|
|
ksmfsnam||' '||ksmfstyp,
|
|
ksmfssiz,
|
|
null,
|
|
null,
|
|
null
|
|
from
|
|
x$ksmfsv
|
|
where
|
|
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
|
|
between
|
|
to_number(ksmfsadr,'XXXXXXXXXXXXXXXX')
|
|
and to_number(ksmfsadr,'XXXXXXXXXXXXXXXX') + ksmfssiz - 1
|
|
/
|