GV$SQLAREA

select
inst_id,                 -- INST_ID NUMBER
kglnaobj,                -- SQL_TEXT VARCHAR2(1000)
sum                      -- SHARABLE_MEM NUMBER
  (kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6),
sum (kglobt08+kglobt11), -- PERSISTENT_MEM NUMBER
sum (kglobt10),          -- RUNTIME_MEM NUMBER
sum (kglobt01),          -- SORTS NUMBER
count (*) -1,            -- VERSION_COUNT NUMBER
sum                      -- LOADED_VERSIONS NUMBER
  (decode (kglobhs6, 0, 0, 1)),
decode                   -- OPEN_VERSIONS NUMBER
  (sum (decode (kglhdlmd, 0, 0, 1)), 0, 0, sum (decode (kglhdlmd, 0, 0, 1)) -1),
sum (kglhdlkc) /2,       -- USERS_OPENING NUMBER
sum (kglobt04),          -- FETCHES NUMBER
sum (kglobt05),          -- EXECUTIONS NUMBER
sum (kglobpc6),          -- USERS_EXECUTING NUMBER
sum (kglhdldc) -1,       -- LOADS NUMBER
substr                   -- FIRST_LOAD_TIME VARCHAR2(19)
  (to_char (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
sum (kglhdivc),          -- INVALIDATIONS NUMBER
sum (kglobt12),          -- PARSE_CALLS NUMBER
sum (kglobt13),          -- DISK_READS NUMBER
sum (kglobt14),          -- BUFFER_GETS NUMBER
sum (kglobt15),          -- ROWS_PROCESSED NUMBER
sum                      -- COMMAND_TYPE NUMBER
  (decode (kglobt09, 0, kglobt02, 0)),
decode                   -- OPTIMIZER_MODE VARCHAR2(25)
  (count (*) -1, 1, decode (sum (decode (kglobt09, 0, kglobt32, 0)), 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), 'MULTIPLE CHILDREN PRESENT'),
sum                      -- PARSING_USER_ID NUMBER
  (decode (kglobt09, 0, kglobt17, 0)),
sum                      -- PARSING_SCHEMA_ID NUMBER
  (decode (kglobt09, 0, kglobt18, 0)),
decode                   -- KEPT_VERSIONS NUMBER
  (sum (decode (kglhdkmk, 0, 0, 1)), 0, 0, sum (decode (kglhdkmk, 0, 0, 1)) -1),
kglhdpar,                -- ADDRESS RAW(4)
kglnahsh,                -- HASH_VALUE NUMBER
kglobts0,                -- MODULE VARCHAR2(64)
kglobt19,                -- MODULE_HASH NUMBER
kglobts1,                -- ACTION VARCHAR2(64)
kglobt20,                -- ACTION_HASH NUMBER
sum (kglobt21),          -- SERIALIZABLE_ABORTS NUMBER
sum (kglobt06),          -- CPU_TIME NUMBER
sum (kglobt07),          -- ELAPSED_TIME NUMBER
decode                   -- IS_OBSOLETE VARCHAR2(1)
  (kglobt33, 1, 'Y', 'N'),
kglhdclt                 -- CHILD_LATCH NUMBER
from
x$kglcursor 
group by inst_id,
kglnaobj,
kglhdpar,
kglnahsh,
kglnatim,
kglobts0,
kglobt19,
kglobts1,
kglobt20,
decode (kglobt33, 1, 'Y', 'N'),
kglhdclt 
having sum (decode (kglobt09, 0, kglobt02, 0)) != 0
home |  up  |  mailbox