GV$SQL_PLAN_MONITOR definition in Oracle Database 11.2
GV$SQL_PLAN_MONITOR

select
  inst_id,                                 -- INST_ID NUMBER
  key_keswxmonp,                           -- KEY NUMBER
  case                                     -- STATUS VARCHAR2(19) status_keswxmonp 
     when 1 
     then 'EXECUTING' 
     when 2 
     then 'DONE (ERROR)' 
     when 3 
     then 'DONE (FIRST N ROWS)' 
     when 4 
     then 'DONE (ALL ROWS)' 
     when 5 
     then 'DONE' 
     when 6 
     then 'FREED' 
     when 7 
     then 'QUEUED' 
     else 'UNKNOWN' end,
  startmon_keswxmonp,                      -- FIRST_REFRESH_TIME DATE
  lastpub_keswxmonp,                       -- LAST_REFRESH_TIME DATE
  firstchg_keswxmonp,                      -- FIRST_CHANGE_TIME DATE
  lastchg_keswxmonp,                       -- LAST_CHANGE_TIME DATE
  pubcount_keswxmonp,                      -- REFRESH_COUNT NUMBER
  sid_keswxmonp,                           -- SID NUMBER
  procname_keswxmonp,                      -- PROCESS_NAME VARCHAR2(5)
  sqlid_keswxmonp,                         -- SQL_ID VARCHAR2(13)
  execstart_keswxmonp,                     -- SQL_EXEC_START DATE
  execid_keswxmonp,                        -- SQL_EXEC_ID NUMBER
  planhash_keswxmonp,                      -- SQL_PLAN_HASH_VALUE NUMBER
  childaddr_keswxmonp,                     -- SQL_CHILD_ADDRESS RAW(8)
  to_number(                               -- PLAN_PARENT_ID NUMBER
    decode(opid_keswxmonp, 0, NULL, parentid_keswxmonp)),
  lineid_keswxmonp,                        -- PLAN_LINE_ID NUMBER
  lineopnam_keswxmonp,                     -- PLAN_OPERATION VARCHAR2(30)
  lineopopt_keswxmonp,                     -- PLAN_OPTIONS VARCHAR2(30)
  objowner_keswxmonp,                      -- PLAN_OBJECT_OWNER VARCHAR2(30)
  objname_keswxmonp,                       -- PLAN_OBJECT_NAME VARCHAR2(30)
  substr(                                  -- PLAN_OBJECT_TYPE VARCHAR2(20)
    objtype_keswxmonp, 1, 20),
  decode(                                  -- PLAN_DEPTH NUMBER
    depth_keswxmonp, 65535, to_number(NULL), depth_keswxmonp),
  to_number(                               -- PLAN_POSITION NUMBER
    decode(pos_keswxmonp, 0, decode(cost_keswxmonp, 4294967295, NULL, cost_keswxmonp), pos_keswxmonp)),
  decode(                                  -- PLAN_COST NUMBER
    cost_keswxmonp, 0, to_number(NULL), 4294967295, to_number(NULL), cost_keswxmonp),
  decode(                                  -- PLAN_CARDINALITY NUMBER
    cardinality_keswxmonp, 0, to_number(NULL), cardinality_keswxmonp),
  decode(                                  -- PLAN_BYTES NUMBER
    size_keswxmonp, 0, to_number(NULL), size_keswxmonp),
  to_number(                               -- PLAN_TIME NUMBER
    decode(time_keswxmonp, 0, NULL, time_keswxmonp)),
  substr(psta_keswxmonp, 1, 64),           -- PLAN_PARTITION_START VARCHAR2(64)
  substr(psto_keswxmonp, 1, 64),           -- PLAN_PARTITION_STOP VARCHAR2(64)
  to_number(                               -- PLAN_CPU_COST NUMBER
    decode(cpucost_keswxmonp, 4294967295, NULL, cpucost_keswxmonp)),
  to_number(                               -- PLAN_IO_COST NUMBER
    decode(iocost_keswxmonp, 4294967295, NULL, iocost_keswxmonp)),
  to_number(                               -- PLAN_TEMP_SPACE NUMBER
    decode(tempspc_keswxmonp, 0, NULL, tempspc_keswxmonp)),
  nsta_keswxmonp,                          -- STARTS NUMBER
  nrows_keswxmonp,                         -- OUTPUT_ROWS NUMBER
  io_inter_bytes,                          -- IO_INTERCONNECT_BYTES NUMBER
  pread_reqs,                              -- PHYSICAL_READ_REQUESTS NUMBER
  pread_bytes,                             -- PHYSICAL_READ_BYTES NUMBER
  pwrite_reqs,                             -- PHYSICAL_WRITE_REQUESTS NUMBER
  pwrite_bytes,                            -- PHYSICAL_WRITE_BYTES NUMBER
  case                                     -- WORKAREA_MEM NUMBER wasta_keswxmonp 
     when 1 
     then mem_keswxmonp * 1024 
     else null end,
  case                                     -- WORKAREA_MAX_MEM NUMBER wasta_keswxmonp 
     when 0 
     then null 
     else maxmem_keswxmonp * 1024 end,
  case                                     -- WORKAREA_TEMPSEG NUMBER 
     when wasta_keswxmonp = 1 and tmp_keswxmonp != 0 
     then tmp_keswxmonp * 1024 
     else null end,
  case                                     -- WORKAREA_MAX_TEMPSEG NUMBER 
     when wasta_keswxmonp != 0 and maxtmp_keswxmonp != 0 
     then maxtmp_keswxmonp * 1024 
     else null end 
from
  X$KESWXMON_PLAN

home |  up