SELECT * FROM table(DBMS_XPLAN.display_cursor('2c4s3xc4fuujy', NULL, 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2c4s3xc4fuujy, child number 0 ------------------------------------- SELECT * FROM (SELECT rownum AS rnum, r.* FROM ( SELECT f.file_id, f.file_name, f.tablespace_name, round(f.bytes/1024/1024, 2) filesize_mb, f.blocks, round((f.bytes-nvl(s.bytes, 0))/1024/1024, 2) used_mb, CASE f.autoextensible WHEN 'NO' THEN round((f.bytes-nvl(s.bytes, 0))/f.bytes, 4)*100 ELSE round((f.bytes-nvl(s.bytes, 0))/f.maxbytes, 4)*100 END AS used_ratio, CASE f.autoextensible WHEN 'NO' THEN round(f.bytes/1024/1024, 2) ELSE round(f.maxbytes/1024/1024, 2) END AS maxsize_mb, CASE f.autoextensible WHEN 'NO' THEN f.blocks ELSE f.maxblocks END AS maxblocks, f.autoextensible, f.online_status, 'NORMAL' AS types FROM dba_data_files f, dba_tablespaces t, (SELECT file_id, SUM(bytes) bytes FROM dba_free_space GROUP BY file_id) s WHERE Plan hash value: 3170555723 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 30 |00:17:20.77 | 41M| 505 | | | | |* 1 | VIEW | | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | | | | |* 2 | COUNT STOPKEY | | 1 | | 30 |00:17:20.77 | 41M| 505 | | | | | 3 | VIEW | | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | | | | |* 4 | SORT ORDER BY STOPKEY | | 1 | 4 | 30 |00:17:20.77 | 41M| 505 | 6144 | 6144 | 6144 (0)| | 5 | NESTED LOOPS | | 1 | 4 | 292 |00:17:20.77 | 41M| 505 | | | | |* 6 | HASH JOIN | | 1 | 1 | 4072 |00:00:04.71 |8324 | 0 | 1856K| 1856K| 1333K (0)| | 7 | NESTED LOOPS OUTER | | 1 | 1 | 32 |00:00:00.01 | 94 | 0 | | | | | 8 | NESTED LOOPS | | 1 | 1 | 32 |00:00:00.01 | 93 | 0 | | | | | 9 | FIXED TABLE FULL | X$KCFISTSA | 1 | 6 | 35 |00:00:00.01 | 54 | 0 | | | | |* 10 | TABLE ACCESS CLUSTER | TS$ | 35 | 1 | 32 |00:00:00.01 | 39 | 0 | | | | |* 11 | INDEX UNIQUE SCAN | I_TS# | 35 | 1 | 35 |00:00:00.01 | 4 | 0 | | | | |* 12 | INDEX RANGE SCAN | I_IMSVCTS1 | 32 | 1 | 0 |00:00:00.01 | 1 | 0 | | | | | 13 | VIEW | DBA_DATA_FILES | 1 | 11 | 4082 |00:00:04.70 |8230 | 0 | | | | | 14 | UNION-ALL | | 1 | | 4082 |00:00:04.69 |8230 | 0 | | | | | 15 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | | | | | 16 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | | | | |* 17 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | 1162K| 1162K| 514K (0)| |* 18 | TABLE ACCESS FULL | FILE$ | 1 | 1 | 0 |00:00:00.01 | 31 | 0 | | | | |* 19 | FIXED TABLE FULL | X$KCCFN | 0 | 14 | 0 |00:00:00.01 | 0 | 0 | | | | |* 20 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 21 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 22 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 23 | NESTED LOOPS | | 1 | 10 | 4082 |00:00:04.66 |8199 | 0 | | | | | 24 | NESTED LOOPS | | 1 | 10 | 4082 |00:00:04.61 |4113 | 0 | | | | | 25 | NESTED LOOPS | | 1 | 10 | 4082 |00:00:04.36 | 31 | 0 | | | | |* 26 | HASH JOIN | | 1 | 14 | 4082 |00:00:00.03 | 31 | 0 | 1185K| 1185K| 1492K (0)| |* 27 | FIXED TABLE FULL | X$KCCFN | 1 | 14 | 4082 |00:00:00.01 | 0 | 0 | | | | |* 28 | TABLE ACCESS FULL | FILE$ | 1 | 3693 | 4082 |00:00:00.01 | 31 | 0 | | | | |* 29 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 4082 | 1 | 4082 |00:00:04.33 | 0 | 0 | | | | |* 30 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 4082 | 1 | 4082 |00:00:00.24 |4082 | 0 | | | | | 31 | TABLE ACCESS CLUSTER | TS$ | 4082 | 1 | 4082 |00:00:00.05 |4086 | 0 | | | | |* 32 | INDEX UNIQUE SCAN | I_TS# | 4082 | 1 | 4082 |00:00:00.02 | 4 | 0 | | | | | 33 | VIEW PUSHED PREDICATE | | 4072 | 1 | 292 |00:17:16.05 | 41M| 505 | | | | |* 34 | FILTER | | 4072 | | 292 |00:17:16.05 | 41M| 505 | | | | | 35 | SORT AGGREGATE | | 4072 | 1 | 4072 |00:17:16.04 | 41M| 505 | | | | | 36 | VIEW | DBA_FREE_SPACE | 4072 | 5 | 8368 |00:17:16.01 | 41M| 505 | | | | | 37 | UNION-ALL | | 4072 | | 8368 |00:17:16.01 | 41M| 505 | | | | | 38 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.06 | 16335 | 0 | | | | | 39 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.06 | 16335 | 0 | | | | | 40 | TABLE ACCESS BY INDEX ROWID | FILE$ | 4072 | 1 | 4072 |00:00:00.03 |8191 | 0 | | | | |* 41 | INDEX UNIQUE SCAN | I_FILE1 | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | | | | |* 42 | TABLE ACCESS CLUSTER | FET$ | 4072 | 1 | 0 |00:00:00.02 |8144 | 0 | | | | |* 43 | INDEX UNIQUE SCAN | I_TS# | 4072 | 1 | 4072 |00:00:00.01 |4072 | 0 | | | | |* 44 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 45 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 46 | NESTED LOOPS | | 4072 | 1 | 8368 |00:17:15.54 | 41M| 497 | | | | | 47 | NESTED LOOPS | | 4072 | 1 | 8368 |00:17:15.52 | 41M| 497 | | | | | 48 | TABLE ACCESS BY INDEX ROWID | FILE$ | 4072 | 1 | 4072 |00:00:00.02 |8191 | 0 | | | | |* 49 | INDEX UNIQUE SCAN | I_FILE1 | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | | | | |* 50 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 4072 | 1 | 8368 |00:17:15.49 | 41M| 497 | | | | |* 51 | TABLE ACCESS CLUSTER | TS$ | 8368 | 1 | 8368 |00:00:00.02 |8663 | 0 | | | | |* 52 | INDEX UNIQUE SCAN | I_TS# | 8368 | 1 | 8368 |00:00:00.01 | 295 | 0 | | | | | 53 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.08 | 12263 | 0 | | | | | 54 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.07 | 12263 | 0 | | | | | 55 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.07 | 12263 | 0 | | | | | 56 | TABLE ACCESS BY INDEX ROWID | FILE$ | 4072 | 1 | 4072 |00:00:00.05 |8191 | 0 | | | | |* 57 | INDEX UNIQUE SCAN | I_FILE1 | 4072 | 1 | 4072 |00:00:00.03 |4119 | 0 | | | | | 58 | TABLE ACCESS BY INDEX ROWID BATCHED| RECYCLEBIN$ | 4072 | 1 | 0 |00:00:00.02 |4072 | 0 | | | | |* 59 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 4072 | 1 | 0 |00:00:00.01 |4072 | 0 | | | | |* 60 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 61 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 62 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 63 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | | | | | 64 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | | | | | 65 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.17 | 157K| 8 | | | | | 66 | TABLE ACCESS BY INDEX ROWID | FILE$ | 4072 | 1 | 4072 |00:00:00.01 |8191 | 0 | | | | |* 67 | INDEX UNIQUE SCAN | I_FILE1 | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | | | | | 68 | TABLE ACCESS CLUSTER | UET$ | 4072 | 1 | 0 |00:00:00.15 | 149K| 8 | | | | |* 69 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 4072 | 1 | 139K|00:00:00.04 |9745 | 0 | | | | |* 70 | TABLE ACCESS BY INDEX ROWID BATCHED | RECYCLEBIN$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 71 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 72 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 73 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 74 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.10 | 20407 | 0 | | | | | 75 | NESTED LOOPS | | 4072 | 1 | 0 |00:00:00.09 | 20407 | 0 | | | | | 76 | TABLE ACCESS BY INDEX ROWID | FILE$ | 4072 | 1 | 4072 |00:00:00.01 |8191 | 0 | | | | |* 77 | INDEX UNIQUE SCAN | I_FILE1 | 4072 | 1 | 4072 |00:00:00.01 |4119 | 0 | | | | |* 78 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$ | 4072 | 1 | 0 |00:00:00.08 | 12216 | 0 | | | | |* 79 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 80 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RNUM">=1) 2 - filter(ROWNUM<=30) 4 - filter(ROWNUM<=30) 6 - access("F"."TABLESPACE_NAME"="TS"."NAME") 10 - filter(("TS"."ONLINE$"<>3 AND DECODE("TS"."CONTENTS$",0,DECODE(BITAND("TS"."FLAGS",4503599627370512),16,'UNDO',4503599627370496,'LOST WRITE PROTECTION','PERMANENT'),1,'TEMPORARY')='PERMANENT' AND BITAND("TS"."FLAGS",2048)<>2048 AND BITAND("TS"."FLAGS",16777216)<>16777216)) 11 - access("TS"."TS#"="TSATTR"."TSID") 12 - access("TS"."TS#"="SVC"."TS#") 17 - access("FNFNO"="F"."FILE#") 18 - filter("F"."SPARE1" IS NULL) 19 - filter(("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND BITAND("FNFLG",4)<>4)) 20 - filter(("X$KCCFE"."FENUM"="F"."FILE#" AND ("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))) 22 - access("F"."TS#"="TS"."TS#") 26 - access("FNFNO"="F"."FILE#") 27 - filter(("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND BITAND("FNFLG",4)<>4)) 28 - filter("F"."SPARE1" IS NOT NULL) 29 - filter(("X$KCCFE"."FENUM"="F"."FILE#" AND ("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))) 30 - filter("FNFNO"="HC"."KTFBHCAFNO") 32 - access("HC"."KTFBHCTSN"="TS"."TS#") 34 - filter(COUNT(*)>0) 41 - access("FI"."FILE#"="F"."FILE_ID") 42 - filter("F"."FILE#"="FI"."RELFILE#") 43 - access("F"."TS#"="FI"."TS#") 44 - filter("TS"."BITMAPPED"=0) 45 - access("TS"."TS#"="F"."TS#") 49 - access("FI"."FILE#"="F"."FILE_ID") 50 - filter(("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")) 51 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496)) 52 - access("TS"."TS#"="F"."KTFBFETSN") 57 - access("FI"."FILE#"="F"."FILE_ID") 59 - access("RB"."TS#"="FI"."TS#") 60 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496)) 61 - access("TS"."TS#"="RB"."TS#") 62 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")) 67 - access("FI"."FILE#"="F"."FILE_ID") 69 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 70 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")) 71 - access("U"."TS#"="RB"."TS#") 72 - filter("TS"."BITMAPPED"=0) 73 - access("TS"."TS#"="U"."TS#") 77 - access("FI"."FILE#"="F"."FILE_ID") 78 - filter("F"."EXTENT_DATAFILE_TSID"="FI"."TS#") 79 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)) 80 - access("TS"."TS#"="F"."EXTENT_DATAFILE_TSID") 155 rows selected.