GV$SQL_PLAN_STATISTICS_ALL
select inst_id, -- INST_ID NUMBER PHADD_QESRS, -- ADDRESS RAW(4) HASHV_QESRS, -- HASH_VALUE NUMBER SQLID_QESRS, -- SQL_ID VARCHAR2(13) PLHASH_QESRS, -- PLAN_HASH_VALUE NUMBER HADDR_QESRS, -- CHILD_ADDRESS RAW(4) CHILDNO_QESRS, -- CHILD_NUMBER NUMBER TIMESTAMP_QESRS, -- TIMESTAMP DATE substr -- OPERATION VARCHAR2(30) (oper_qesrs, 1, 30), substr -- OPTIONS VARCHAR2(30) (oopt_qesrs, 1, 30), substr -- OBJECT_NODE VARCHAR2(40) (tqid_qesrs, 1, 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 -- OBJECT_TYPE VARCHAR2(20) (objtype_qesrs, 1, 20), substr -- OPTIMIZER VARCHAR2(20) (opti_qesrs, 1, 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 -- OTHER_TAG VARCHAR2(35) (otag_qesrs, 1, 35), substr -- PARTITION_START VARCHAR2(5) (psta_qesrs, 1, 5), substr -- PARTITION_STOP VARCHAR2(5) (psto_qesrs, 1, 5), to_number -- PARTITION_ID NUMBER (decode (pnid_qesrs, 0, NULL, pnid_qesrs)), other_qesrs, -- OTHER VARCHAR2(4000) substr -- DISTRIBUTION VARCHAR2(20) (dist_qesrs, 1, 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

home |  up  |  mailbox