70 lines
1.7 KiB
SQL
70 lines
1.7 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: lpstat.sql
|
|
-- Purpose: Show large pool stats by sub-pool from X$KSMLS
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) https://tanelpoder.com
|
|
--
|
|
-- Usage: @lpstat <statistic name>
|
|
-- @lpstat "free memory"
|
|
-- @lpstat cursor
|
|
--
|
|
-- Other: The other script for querying V$SGASTAT is called sgastat.sql
|
|
-- and shows many more pools of interest (but not break down by subpool)
|
|
--
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
COL lpstat_subpool HEAD SUBPOOL FOR a30
|
|
|
|
PROMPT
|
|
PROMPT -- Subpool sizes:
|
|
|
|
SELECT
|
|
'large pool ('||NVL(DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx), 'Total')||'):' lpstat_subpool
|
|
, SUM(ksmsslen) bytes
|
|
, ROUND(SUM(ksmsslen)/1048576,2) MB
|
|
FROM
|
|
x$ksmls
|
|
WHERE
|
|
ksmsslen > 0
|
|
--AND ksmdsidx > 0
|
|
GROUP BY ROLLUP
|
|
( ksmdsidx )
|
|
ORDER BY
|
|
lpstat_subpool ASC
|
|
/
|
|
|
|
BREAK ON lpstat_subpool SKIP 1
|
|
PROMPT -- Allocations matching "&1":
|
|
|
|
SELECT
|
|
subpool lpstat_subpool
|
|
, name
|
|
, SUM(bytes)
|
|
, ROUND(SUM(bytes)/1048576,2) MB
|
|
FROM (
|
|
SELECT
|
|
'large pool ('||DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx)||'):' subpool
|
|
, ksmssnam name
|
|
, ksmsslen bytes
|
|
FROM
|
|
x$ksmls
|
|
WHERE
|
|
ksmsslen > 0
|
|
AND LOWER(ksmssnam) LIKE LOWER('%&1%')
|
|
)
|
|
GROUP BY
|
|
subpool
|
|
, name
|
|
ORDER BY
|
|
subpool ASC
|
|
, SUM(bytes) DESC
|
|
/
|
|
|
|
BREAK ON lpstat_subpool DUP
|