67 lines
1.4 KiB
SQL
67 lines
1.4 KiB
SQL
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: sgastatx
|
|
-- Purpose: Show shared pool stats by sub-pool from X$KSMSS
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://www.tanelpoder.com
|
|
--
|
|
-- Usage: @sgastatx <statistic name>
|
|
-- @sgastatx "free memory"
|
|
-- @sgastatx cursor
|
|
--
|
|
-- Other: The other script for querying V$SGASTAT is called sgastat.sql
|
|
--
|
|
--
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
COL subpool HEAD SUBPOOL FOR a30
|
|
|
|
PROMPT
|
|
PROMPT -- All allocations:
|
|
|
|
SELECT
|
|
'shared pool ('||NVL(DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx), 'Total')||'):' subpool
|
|
, SUM(ksmsslen) bytes
|
|
, ROUND(SUM(ksmsslen)/1048576,2) MB
|
|
FROM
|
|
x$ksmss
|
|
WHERE
|
|
ksmsslen > 0
|
|
--AND ksmdsidx > 0
|
|
GROUP BY ROLLUP
|
|
( ksmdsidx )
|
|
ORDER BY
|
|
subpool ASC
|
|
/
|
|
|
|
BREAK ON subpool SKIP 1
|
|
PROMPT -- Allocations matching "&1":
|
|
|
|
SELECT
|
|
subpool
|
|
, name
|
|
, SUM(bytes)
|
|
, ROUND(SUM(bytes)/1048576,2) MB
|
|
FROM (
|
|
SELECT
|
|
'shared pool ('||DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx)||'):' subpool
|
|
, ksmssnam name
|
|
, ksmsslen bytes
|
|
FROM
|
|
x$ksmss
|
|
WHERE
|
|
ksmsslen > 0
|
|
AND LOWER(ksmssnam) LIKE LOWER('%&1%')
|
|
)
|
|
GROUP BY
|
|
subpool
|
|
, name
|
|
ORDER BY
|
|
subpool ASC
|
|
, SUM(bytes) DESC
|
|
/
|
|
|
|
BREAK ON subpool DUP
|