select
  inst_id,                                 -- INST_ID NUMBER
  '',                                      -- POOL VARCHAR2(12)
  ksmssnam,                                -- NAME VARCHAR2(26)
  ksmsslen                                 -- BYTES NUMBER
from
  x$ksmfs
where
  ksmsslen>1
union all
select
  inst_id,                                 -- INST_ID NUMBER
  'shared pool',                           -- POOL VARCHAR2(12)
  ksmssnam,                                -- NAME VARCHAR2(26)
  sum(ksmsslen)                            -- BYTES NUMBER
from
  x$ksmss
where
  ksmsslen>1
group by
  inst_id,
  'shared pool',
  ksmssnam
union all
select
  inst_id,                                 -- INST_ID NUMBER
  'large pool',                            -- POOL VARCHAR2(12)
  ksmssnam,                                -- NAME VARCHAR2(26)
  sum(ksmsslen)                            -- BYTES NUMBER
from
  x$ksmls
where
  ksmsslen>1
group by
  inst_id,
  'large pool',
  ksmssnam
union all
select
  inst_id,                                 -- INST_ID NUMBER
  'java pool',                             -- POOL VARCHAR2(12)
  ksmssnam,                                -- NAME VARCHAR2(26)
  sum(ksmsslen)                            -- BYTES NUMBER
from
  x$ksmjs
where
  ksmsslen>1
group by
  inst_id,
  'java pool',
  ksmssnam
union all
select
  inst_id,                                 -- INST_ID NUMBER
  'numa pool',                             -- POOL VARCHAR2(12)
  ksmnsnam,                                -- NAME VARCHAR2(26)
  sum(ksmnslen)                            -- BYTES NUMBER
from
  x$ksmns
where
  ksmnslen>1
group by
  inst_id,
  'numa pool',
  ksmnsnam
union all
select
  inst_id,                                 -- INST_ID NUMBER
  'streams pool',                          -- POOL VARCHAR2(12)
  ksmssnam,                                -- NAME VARCHAR2(26)
  sum(ksmsslen)                            -- BYTES NUMBER
from
  x$ksmstrs
where
  ksmsslen>1
group by
  inst_id,
  'streams pool',
  ksmssnam