select
  inst_id,                                 -- INST_ID NUMBER
  PHADD_QESRS,                             -- ADDRESS RAW(8)
  HASHV_QESRS,                             -- HASH_VALUE NUMBER
  SQLID_QESRS,                             -- SQL_ID VARCHAR2(13)
  PLHASH_QESRS,                            -- PLAN_HASH_VALUE NUMBER
  HADDR_QESRS,                             -- CHILD_ADDRESS RAW(8)
  CHILDNO_QESRS,                           -- CHILD_NUMBER NUMBER
  TIMESTAMP_QESRS,                         -- TIMESTAMP DATE
  substr(oper_qesrs, 1, 30),               -- OPERATION VARCHAR2(30)
  substr(oopt_qesrs, 1, 30),               -- OPTIONS VARCHAR2(30)
  substr(tqid_qesrs, 1, 40),               -- OBJECT_NODE VARCHAR2(40)
  to_number(                               -- OBJECT# NUMBER
    decode(objn_qesrs, 0, NULL, objn_qesrs)),
  objowner_qesrs,                          -- OBJECT_OWNER VARCHAR2(30)
  objname_qesrs,                           -- OBJECT_NAME VARCHAR2(30)
  alias_qesrs,                             -- OBJECT_ALIAS VARCHAR2(65)
  substr(objtype_qesrs, 1, 20),            -- OBJECT_TYPE VARCHAR2(20)
  substr(opti_qesrs, 1, 20),               -- OPTIMIZER VARCHAR2(20)
  opid_qesrs,                              -- ID NUMBER
  to_number(                               -- PARENT_ID NUMBER
    decode(opid_qesrs, 0, NULL, paid_qesrs)),
  depth_qesrs,                             -- DEPTH NUMBER
  to_number(                               -- POSITION NUMBER
    decode(pos_qesrs, 0, decode(cost_qesrs, 4294967295, NULL, cost_qesrs), pos_qesrs)),
  scols_qesrs,                             -- SEARCH_COLUMNS NUMBER
  to_number(                               -- COST NUMBER
    decode(cost_qesrs, 4294967295, NULL, cost_qesrs)),
  to_number(                               -- CARDINALITY NUMBER
    decode(card_qesrs, 0, NULL, card_qesrs)),
  to_number(                               -- BYTES NUMBER
    decode(size_qesrs, 0, NULL, size_qesrs)),
  substr(otag_qesrs, 1, 35),               -- OTHER_TAG VARCHAR2(35)
  substr(psta_qesrs, 1, 64),               -- PARTITION_START VARCHAR2(64)
  substr(psto_qesrs, 1, 64),               -- PARTITION_STOP VARCHAR2(64)
  to_number(                               -- PARTITION_ID NUMBER
    decode(pnid_qesrs, 0, NULL, pnid_qesrs)),
  other_qesrs,                             -- OTHER VARCHAR2(4000)
  substr(dist_qesrs, 1, 20),               -- DISTRIBUTION VARCHAR2(20)
  to_number(                               -- CPU_COST NUMBER
    decode(cpuc_qesrs, 4294967295, NULL, cpuc_qesrs)),
  to_number(                               -- IO_COST NUMBER
    decode(ioct_qesrs, 4294967295, NULL, ioct_qesrs)),
  to_number(                               -- TEMP_SPACE NUMBER
    decode(temp_qesrs, 0, NULL, temp_qesrs)),
  KEYS_QESRS,                              -- ACCESS_PREDICATES VARCHAR2(4000)
  FILTER_QESRS,                            -- FILTER_PREDICATES VARCHAR2(4000)
  PROJ_QESRS,                              -- PROJECTION VARCHAR2(4000)
  to_number(                               -- TIME NUMBER
    decode(time_qesrs, 0, NULL, time_qesrs)),
  QBLOCK_QESRS,                            -- QBLOCK_NAME VARCHAR2(30)
  REMARK_QESRS,                            -- REMARKS VARCHAR2(4000)
  OTHER_XML_QESRS,                         -- OTHER_XML CLOB
  EXECS_QESRS,                             -- EXECUTIONS NUMBER
  to_number(                               -- LAST_STARTS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LSTARTS_QESRS)),
  to_number(                               -- STARTS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, STARTS_QESRS)),
  to_number(                               -- LAST_OUTPUT_ROWS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LOUTROWS_QESRS)),
  to_number(                               -- OUTPUT_ROWS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, OUTROWS_QESRS)),
  to_number(                               -- LAST_CR_BUFFER_GETS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LCRGETS_QESRS)),
  to_number(                               -- CR_BUFFER_GETS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, CRGETS_QESRS)),
  to_number(                               -- LAST_CU_BUFFER_GETS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LCUGETS_QESRS)),
  to_number(                               -- CU_BUFFER_GETS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, CUGETS_QESRS)),
  to_number(                               -- LAST_DISK_READS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LDREADS_QESRS)),
  to_number(                               -- DISK_READS NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, DREADS_QESRS)),
  to_number(                               -- LAST_DISK_WRITES NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LDWRITES_QESRS)),
  to_number(                               -- DISK_WRITES NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, DWRITES_QESRS)),
  to_number(                               -- LAST_ELAPSED_TIME NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, LELAPTIME_QESRS)),
  to_number(                               -- ELAPSED_TIME NUMBER
    decode(LSTARTS_QESRS, 4294967295, NULL, ELAPTIME_QESRS)),
  substr(                                  -- POLICY VARCHAR2(10)
    SIZEPOLICY_QESRS, 1, 10),
  to_number(                               -- ESTIMATED_OPTIMAL_SIZE NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, OPTIMAL_QESRS * 1024)),
  to_number(                               -- ESTIMATED_ONEPASS_SIZE NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, ONEPASS_QESRS * 1024)),
  to_number(                               -- LAST_MEMORY_USED NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, LASTMEM_QESRS * 1024)),
  decode(                                  -- LAST_EXECUTION VARCHAR2(10)
    OPTIMAL_QESRS, 0, NULL, substr(decode(LASTPASS_QESRS, 0, 'OPTIMAL', to_char(LASTPASS_QESRS) || ' PASS' || decode(LASTPASS_QESRS, 1, '', 'ES')), 1, 10)),
  to_number(                               -- LAST_DEGREE NUMBER
    decode(LASTDOP_QESRS, 0, NULL, LASTDOP_QESRS)),
  to_number(                               -- TOTAL_EXECUTIONS NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, (OPTACTS_QESRS + SPAACTS_QESRS + MPAACTS_QESRS))),
  to_number(                               -- OPTIMAL_EXECUTIONS NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, OPTACTS_QESRS)),
  to_number(                               -- ONEPASS_EXECUTIONS NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, SPAACTS_QESRS)),
  to_number(                               -- MULTIPASSES_EXECUTIONS NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, MPAACTS_QESRS)),
  to_number(                               -- ACTIVE_TIME NUMBER
    decode(OPTIMAL_QESRS, 0, NULL, ATIME_QESRS)),
  to_number(                               -- MAX_TEMPSEG_SIZE NUMBER
    decode(MAXTSEG_QESRS, 0, NULL, MAXTSEG_QESRS)),
  to_number(                               -- LAST_TEMPSEG_SIZE NUMBER
    decode(LASTTSEG_QESRS, 0, NULL, LASTTSEG_QESRS)) 
from
  X$QESRSTATALL p
where
  p.haddr_qesrs != p.phadd_qesrs