GV$SHARED_POOL_RESERVED

select
p.inst_id,               -- INST_ID NUMBER
p.free_space,            -- FREE_SPACE NUMBER
p.avg_free_size,         -- AVG_FREE_SIZE NUMBER
p.free_count,            -- FREE_COUNT NUMBER
p.max_free_size,         -- MAX_FREE_SIZE NUMBER
p.used_size,             -- USED_SPACE NUMBER
p.avg_used_size,         -- AVG_USED_SIZE NUMBER
p.used_count,            -- USED_COUNT NUMBER
p.max_used_size,         -- MAX_USED_SIZE NUMBER
s.requests,              -- REQUESTS NUMBER
s.request_misses,        -- REQUEST_MISSES NUMBER
s.last_miss_size,        -- LAST_MISS_SIZE NUMBER
s.max_miss_size,         -- MAX_MISS_SIZE NUMBER
s.request_failures,      -- REQUEST_FAILURES NUMBER
s.last_failure_size,     -- LAST_FAILURE_SIZE NUMBER
s.aborted_request_threshold, -- ABORTED_REQUEST_THRESHOLD NUMBER
s.aborted_requests,      -- ABORTED_REQUESTS NUMBER
s.last_aborted_size      -- LAST_ABORTED_SIZE NUMBER
from
(
select
avg (x$ksmspr.inst_id) inst_id,
sum (decode (ksmchcls, 'R-free', ksmchsiz, 0)) free_space,
avg (decode (ksmchcls, 'R-free', ksmchsiz, 0)) avg_free_size,
sum (decode (ksmchcls, 'R-free', 1, 0)) free_count,
max (decode (ksmchcls, 'R-free', ksmchsiz, 0)) max_free_size,
sum (decode (ksmchcls, 'R-free', 0, ksmchsiz)) used_size,
avg (decode (ksmchcls, 'R-free', 0, ksmchsiz)) avg_used_size,
sum (decode (ksmchcls, 'R-free', 0, 1)) used_count,
max (decode (ksmchcls, 'R-free', 0, ksmchsiz)) max_used_size 
from
x$ksmspr 
where
ksmchcom not like '%reserved sto%') p,
(
select
sum (kghlurcn) requests,
sum (kghlurmi) request_misses,
max (kghlurmz) last_miss_size,
max (kghlurmx) max_miss_size,
sum (kghlunfu) request_failures,
max (kghlunfs) last_failure_size,
max (kghlumxa) aborted_request_threshold,
sum (kghlumer) aborted_requests,
max (kghlumes) last_aborted_size 
from
x$kghlu) s
home |  up  |  mailbox