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 CHILDNO_QESRS, -- CHILD_NUMBER NUMBER 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)), case -- OBJECT_OWNER VARCHAR2(30) when objname_qesrs is not null then 'SYS' else u.name end, nvl -- OBJECT_NAME VARCHAR2(31) (p.objname_qesrs, o.name), 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(31) REMARK_QESRS, -- REMARKS VARCHAR2(4000) EXECS_QESRS, -- EXECUTIONS NUMBER LSTARTS_QESRS, -- LAST_STARTS NUMBER STARTS_QESRS, -- STARTS NUMBER LOUTROWS_QESRS, -- LAST_OUTPUT_ROWS NUMBER OUTROWS_QESRS, -- OUTPUT_ROWS NUMBER LCRGETS_QESRS, -- LAST_CR_BUFFER_GETS NUMBER CRGETS_QESRS, -- CR_BUFFER_GETS NUMBER LCUGETS_QESRS, -- LAST_CU_BUFFER_GETS NUMBER CUGETS_QESRS, -- CU_BUFFER_GETS NUMBER LDREADS_QESRS, -- LAST_DISK_READS NUMBER DREADS_QESRS, -- DISK_READS NUMBER LDWRITES_QESRS, -- LAST_DISK_WRITES NUMBER DWRITES_QESRS, -- DISK_WRITES NUMBER LELAPTIME_QESRS, -- LAST_ELAPSED_TIME NUMBER ELAPTIME_QESRS, -- ELAPSED_TIME NUMBER substr -- POLICY VARCHAR2(10) (SIZEPOLICY_QESRS, 1, 10), OPTIMAL_QESRS * 1024, -- ESTIMATED_OPTIMAL_SIZE NUMBER ONEPASS_QESRS * 1024, -- ESTIMATED_ONEPASS_SIZE NUMBER LASTMEM_QESRS * 1024, -- LAST_MEMORY_USED NUMBER substr -- LAST_EXECUTION VARCHAR2(10) (decode (LASTPASS_QESRS, 0, 'OPTIMAL', to_char (LASTPASS_QESRS) || ' PASS' || decode (LASTPASS_QESRS, 1, '', 'ES')), 1, 10), LASTDOP_QESRS, -- LAST_DEGREE NUMBER (OPTACTS_QESRS -- TOTAL_EXECUTIONS NUMBER + SPAACTS_QESRS + MPAACTS_QESRS), OPTACTS_QESRS, -- OPTIMAL_EXECUTIONS NUMBER SPAACTS_QESRS, -- ONEPASS_EXECUTIONS NUMBER MPAACTS_QESRS, -- MULTIPASSES_EXECUTIONS NUMBER ATIME_QESRS, -- ACTIVE_TIME NUMBER 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, obj$ o, user$ u where p.haddr_qesrs != p.phadd_qesrs and p.objn_qesrs = o.obj# (+) and o.owner# = u.user# (+)

home |  up  |  mailbox