GV$SHARED_POOL_RESERVED

select
avg (x$ksmspr.inst_id),  -- INST_ID NUMBER
sum                      -- FREE_SPACE NUMBER
  (decode (ksmchcls, 'R-free', ksmchsiz, 0)),
avg                      -- AVG_FREE_SIZE NUMBER
  (decode (ksmchcls, 'R-free', ksmchsiz, 0)),
sum                      -- FREE_COUNT NUMBER
  (decode (ksmchcls, 'R-free', 1, 0)),
max                      -- MAX_FREE_SIZE NUMBER
  (decode (ksmchcls, 'R-free', ksmchsiz, 0)),
sum                      -- USED_SPACE NUMBER
  (decode (ksmchcls, 'R-free', 0, ksmchsiz)),
avg                      -- AVG_USED_SIZE NUMBER
  (decode (ksmchcls, 'R-free', 0, ksmchsiz)),
sum                      -- USED_COUNT NUMBER
  (decode (ksmchcls, 'R-free', 0, 1)),
max                      -- MAX_USED_SIZE NUMBER
  (decode (ksmchcls, 'R-free', 0, ksmchsiz)),
sum (requests) requests, -- REQUESTS NUMBER
sum                      -- REQUEST_MISSES NUMBER
  (request_misses) request_misses,
sum                      -- LAST_MISS_SIZE NUMBER
  (last_miss_size) last_miss_size,
sum                      -- MAX_MISS_SIZE NUMBER
  (max_miss_size) max_miss_size,
sum                      -- REQUEST_FAILURES NUMBER
  (request_failures) request_failures,
sum                      -- LAST_FAILURE_SIZE NUMBER
  (last_failure_size) last_failure_size,
sum                      -- ABORTED_REQUEST_THRESHOLD NUMBER
  (aborted_request_threshold) aborted_request_threshold,
sum                      -- ABORTED_REQUESTS NUMBER
  (aborted_requests) aborted_requests,
sum                      -- LAST_ABORTED_SIZE NUMBER
  (last_aborted_size) last_aborted_size 
from
x$ksmspr,
(
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) 
where
ksmchcom not like '%reserved sto%'
home |  up  |  mailbox