Why my tablespace report is slow on one of my databases ?

Application team started complain about getting table space report is too slow.When I checked it, It took 20 min to get report. But I can get the same report in a sec. from other databases.

I executed SQL script and get the SQLHC report.

——————————————————————————————————————
| Id  | Operation                         | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
——————————————————————————————————————
|   0 | SELECT STATEMENT                  |                      |       |       |       |  1628 (100)|          |
|   1 |  SORT ORDER BY                    |                      |    17 |  1163 |       |  1627  (37)| 00:00:02 |
|   2 |   UNION-ALL                       |                      |       |       |       |            |          |
|   3 |    HASH JOIN OUTER                |                      |    16 |  1008 |       |  1621  (37)| 00:00:02 |
|   4 |     VIEW                          |                      |    16 |   528 |       |     9  (23)| 00:00:01 |
|   5 |      HASH GROUP BY                |                      |    16 |   416 |       |     9  (23)| 00:00:01 |
|   6 |       VIEW                        | DBA_DATA_FILES       |    42 |  1092 |       |     8  (13)| 00:00:01 |
|   7 |        UNION-ALL                  |                      |       |       |       |            |          |
|   8 |         HASH JOIN                 |                      |     1 |    89 |       |     3   (0)| 00:00:01 |
|   9 |          NESTED LOOPS             |                      |     1 |    41 |       |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS            |                      |     1 |    24 |       |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS FULL      | FILE$                |     1 |    21 |       |     2   (0)| 00:00:01 |
|  12 |            FIXED TABLE FIXED INDEX| X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  13 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  14 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  15 |          FIXED TABLE FULL         | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  16 |         NESTED LOOPS              |                      |    41 |  3813 |       |     5  (20)| 00:00:01 |
|  17 |          HASH JOIN                |                      |    41 |  3690 |       |     5  (20)| 00:00:01 |
|  18 |           NESTED LOOPS            |                      |    41 |  2993 |       |     2   (0)| 00:00:01 |
|  19 |            HASH JOIN              |                      |    41 |  2378 |       |     2   (0)| 00:00:01 |
|  20 |             FIXED TABLE FULL      | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  21 |             TABLE ACCESS FULL     | FILE$                |    42 |   420 |       |     2   (0)| 00:00:01 |
|  22 |            FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1)     |     1 |    15 |       |     0   (0)|          |
|  23 |           TABLE ACCESS FULL       | TS$                  |    16 |   272 |       |     2   (0)| 00:00:01 |
|  24 |          FIXED TABLE FIXED INDEX  | X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  25 |     VIEW                          |                      |     7 |   210 |       |  1612  (37)| 00:00:02 |
|  26 |      HASH GROUP BY                |                      |     7 |   161 |       |  1612  (37)| 00:00:02 |
|  27 |       VIEW                        | DBA_FREE_SPACE       | 46412 |  1042K|       |  1597  (36)| 00:00:02 |
|  28 |        UNION-ALL                  |                      |       |       |       |            |          |
|  29 |         NESTED LOOPS              |                      |     1 |    66 |       |     2   (0)| 00:00:01 |
|  30 |          NESTED LOOPS             |                      |     1 |    60 |       |     2   (0)| 00:00:01 |
|  31 |           TABLE ACCESS FULL       | FET$                 |     1 |    39 |       |     2   (0)| 00:00:01 |
|  32 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    21 |       |     0   (0)|          |
|  33 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  34 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  35 |         NESTED LOOPS              |                      |  2715 |   114K|       |     8  (75)| 00:00:01 |
|  36 |          HASH JOIN                |                      |  2715 |    98K|       |     7  (72)| 00:00:01 |
|  37 |           TABLE ACCESS FULL       | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  38 |           FIXED TABLE FULL        | X$KTFBFE             |  2959 | 29590 |       |     4 (100)| 00:00:01 |
|  39 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  40 |         HASH JOIN                 |                      | 43695 |  4693K|       |  1108  (38)| 00:00:02 |
|  41 |          TABLE ACCESS FULL        | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  42 |          HASH JOIN                |                      | 47619 |  3859K|       |  1104  (38)| 00:00:02 |
|  43 |           INDEX FULL SCAN         | I_FILE2              |    42 |   252 |       |     1   (0)| 00:00:01 |
|  44 |           HASH JOIN               |                      |   100K|  7519K|  7520K|  1098  (37)| 00:00:02 |
|  45 |            FIXED TABLE FULL       | X$KTFBUE             |   100K|  6347K|       |   205 (100)| 00:00:01 |
|  46 |            TABLE ACCESS FULL      | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  47 |         HASH JOIN                 |                      |     1 |    91 |       |   480  (33)| 00:00:01 |
|  48 |          NESTED LOOPS             |                      |     1 |    79 |       |     4   (0)| 00:00:01 |
|  49 |           NESTED LOOPS            |                      |     1 |    73 |       |     4   (0)| 00:00:01 |
|  50 |            TABLE ACCESS FULL      | TS$                  |     1 |    21 |       |     2   (0)| 00:00:01 |
|  51 |            TABLE ACCESS CLUSTER   | UET$                 |     1 |    52 |       |     2   (0)| 00:00:01 |
|  52 |             INDEX RANGE SCAN      | I_FILE#_BLOCK#       |     1 |       |       |     2   (0)| 00:00:01 |
|  53 |           INDEX UNIQUE SCAN       | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  54 |          TABLE ACCESS FULL        | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  55 |    HASH GROUP BY                  |                      |     1 |   155 |       |     6  (17)| 00:00:01 |
|  56 |     NESTED LOOPS OUTER            |                      |     1 |   155 |       |     5   (0)| 00:00:01 |
|  57 |      NESTED LOOPS                 |                      |     1 |   127 |       |     4   (0)| 00:00:01 |
|  58 |       HASH JOIN                   |                      |     2 |   220 |       |     2   (0)| 00:00:01 |
|  59 |        NESTED LOOPS               |                      |     5 |   355 |       |     0   (0)|          |
|  60 |         NESTED LOOPS              |                      |     5 |   285 |       |     0   (0)|          |
|  61 |          FIXED TABLE FULL         | X$KCCTF              |     6 |    72 |       |     0   (0)|          |
|  62 |          FIXED TABLE FIXED INDEX  | X$KCCFN (ind:1)      |     1 |    45 |       |     0   (0)|          |
|  63 |         FIXED TABLE FIXED INDEX   | X$KTFTHC (ind:1)     |     1 |    14 |       |     0   (0)|          |
|  64 |        VIEW                       | V_$TEMP_SPACE_HEADER |     3 |   117 |       |     2   (0)| 00:00:01 |
|  65 |         NESTED LOOPS              |                      |     3 |   141 |       |     2   (0)| 00:00:01 |
|  66 |          TABLE ACCESS FULL        | TS$                  |     1 |    27 |       |     2   (0)| 00:00:01 |
|  67 |          FIXED TABLE FIXED INDEX  | X$KTFTHC (ind:2)     |     3 |    60 |       |     0   (0)|          |
|  68 |       TABLE ACCESS BY INDEX ROWID | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  69 |        INDEX UNIQUE SCAN          | I_TS1                |     1 |       |       |     0   (0)|          |
|  70 |      VIEW PUSHED PREDICATE        | V_$TEMP_EXTENT_POOL  |     1 |    28 |       |     1   (0)| 00:00:01 |
|  71 |       NESTED LOOPS                |                      |     1 |    79 |       |     1   (0)| 00:00:01 |
|  72 |        TABLE ACCESS BY INDEX ROWID| TS$                  |     1 |    27 |       |     1   (0)| 00:00:01 |
|  73 |         INDEX UNIQUE SCAN         | I_TS1                |     1 |       |       |     0   (0)|          |
|  74 |        FIXED TABLE FIXED INDEX    | X$KTSTFC (ind:1)     |     1 |    52 |       |     0   (0)|          |
——————————————————————————————————————
I purged the recycle bin.

purge dba_recyclebin;

select count(*) from dba_recyclebin; –> should return 0. it may take time to purge.

and gather the fixed object statistic again and also  dictionary statistics.

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
exec DBMS_STATS.GATHER_DICTIONARY_STATS ; –>More important

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SYS’, cascade => TRUE, estimate_percent => 100);

It solved my problem.My query came in a second.

After that I executed SQLHC again and see no full table scan on RECYLEBIN

SELECT STATEMENT                      |                      |      1 |        |       |    70 (100)|          |     12 |00:00:00.19 |    4841 |      2 |       |       |          |
|   1 |  SORT ORDER BY                        |                      |      1 |     17 |  1163 |    69  (51)| 00:00:01 |     12 |00:00:00.19 |    4841 |      2 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                           |                      |      1 |        |       |            |          |     12 |00:00:00.17 |    4841 |      2 |       |       |          |
|*  3 |    HASH JOIN OUTER                    |                      |      1 |     16 |  1008 |    63  (54)| 00:00:01 |      9 |00:00:00.17 |    4789 |      2 |  1368K|  1368K|  921K (0)|
|   4 |     VIEW                              |                      |      1 |     16 |   528 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |       |       |          |
|   5 |      HASH GROUP BY                    |                      |      1 |     16 |   416 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |  1048K|  1048K|  993K (0)|
|   6 |       VIEW                            | DBA_DATA_FILES       |      1 |     42 |  1092 |     8  (13)| 00:00:01 |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|   7 |        UNION-ALL                      |                      |      1 |        |       |            |          |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|*  8 |         HASH JOIN                     |                      |      1 |      1 |    89 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |   982K|   982K|  162K (0)|
|   9 |          NESTED LOOPS                 |                      |      1 |      1 |    41 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|  10 |           NESTED LOOPS                |                      |      1 |      1 |    24 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 11 |            TABLE ACCESS FULL          | FILE$                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 12 |            FIXED TABLE FIXED INDEX    | X$KCCFE (ind:1)      |      0 |      1 |     3 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  13 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    17 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 14 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 15 |          FIXED TABLE FULL             | X$KCCFN              |      0 |     41 |  1968 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  16 |         NESTED LOOPS                  |                      |      1 |     41 |  3813 |     5  (20)| 00:00:01 |     42 |00:00:00.12 |      66 |      0 |       |       |          |
|* 17 |          HASH JOIN                    |                      |      1 |     41 |  3690 |     5  (20)| 00:00:01 |     42 |00:00:00.01 |      66 |      0 |  1421K|  1421K| 1132K (0)|
|  18 |           NESTED LOOPS                |                      |      1 |     41 |  2993 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |      44 |      0 |       |       |          |
|* 19 |            HASH JOIN                  |                      |      1 |     41 |  2378 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |  2440K|  2440K| 1279K (0)|
|* 20 |             FIXED TABLE FULL          | X$KCCFN              |      1 |     41 |  1968 |     0   (0)|          |     42 |00:00:00.01 |       0 |      0 |       |       |          |
|* 21 |             TABLE ACCESS FULL         | FILE$                |      1 |     42 |   420 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |       |       |          |
|* 22 |            FIXED TABLE FIXED INDEX    | X$KTFBHC (ind:1)     |     42 |      1 |    15 |     0   (0)|          |     42 |00:00:00.01 |      42 |      0 |       |       |          |
|  23 |           TABLE ACCESS FULL           | TS$                  |      1 |     16 |   272 |     2   (0)| 00:00:01 |     16 |00:00:00.01 |      22 |      0 |       |       |          |
|* 24 |          FIXED TABLE FIXED INDEX      | X$KCCFE (ind:1)      |     42 |      1 |     3 |     0   (0)|          |     42 |00:00:00.11 |       0 |      0 |       |       |          |
|  25 |     VIEW                              |                      |      1 |     11 |   330 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |       |       |          |
|  26 |      HASH GROUP BY                    |                      |      1 |     11 |   253 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |  1088K|  1088K|  993K (0)|
|  27 |       VIEW                            | DBA_FREE_SPACE       |      1 |  25367 |   569K|    45  (52)| 00:00:01 |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  28 |        UNION-ALL                      |                      |      1 |        |       |            |          |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  29 |         NESTED LOOPS                  |                      |      1 |      1 |    66 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  30 |          NESTED LOOPS                 |                      |      1 |      1 |    60 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  31 |           TABLE ACCESS FULL           | FET$                 |      1 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 32 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    21 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 34 |          INDEX UNIQUE SCAN            | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  35 |         NESTED LOOPS                  |                      |      1 |   2971 |   124K|     8  (75)| 00:00:01 |   3236 |00:00:00.03 |     642 |      0 |       |       |          |
|* 36 |          HASH JOIN                    |                      |      1 |   2971 |   107K|     7  (72)| 00:00:01 |   3236 |00:00:00.03 |     638 |      0 |  1421K|  1421K| 1135K (0)|
|* 37 |           TABLE ACCESS FULL           | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|  38 |           FIXED TABLE FULL            | X$KTFBFE             |      1 |   3238 | 32380 |     5 (100)| 00:00:01 |   3236 |00:00:00.02 |     616 |      0 |       |       |          |
|* 39 |          INDEX UNIQUE SCAN            | I_FILE2              |   3236 |      1 |     6 |     0   (0)|          |   3236 |00:00:00.01 |       4 |      0 |       |       |          |
|* 40 |         HASH JOIN                     |                      |      1 |  22394 |  2405K|    26  (66)| 00:00:01 |     34 |00:00:00.01 |    4035 |      2 |  2061K|  2061K| 1490K (0)|
|  41 |          INDEX FULL SCAN              | I_FILE2              |      1 |     42 |   252 |     1   (0)| 00:00:01 |     42 |00:00:00.01 |       1 |      0 |       |       |          |
|  42 |          NESTED LOOPS                 |                      |      1 |  45879 |  4659K|    23  (66)| 00:00:01 |     34 |00:00:00.01 |    4034 |      2 |       |       |          |
|  43 |           MERGE JOIN                  |                      |      1 |      2 |    78 |     9  (12)| 00:00:01 |      2 |00:00:00.01 |    4022 |      0 |       |       |          |
  |  44 |            TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$          |      1 |      2 |    24 |     6   (0)| 00:00:01 |      2 |00:00:00.01 |    4000 |      0 |       |       |          |
                      |  45 |             INDEX FULL SCAN           | RECYCLEBIN$_TS       |      1 |      2 |       |     4   (0)| 00:00:01 |      2 |00:00:00.01 |    3998 |      0 |       |       |          |
|* 46 |            SORT JOIN                  |                      |      2 |     10 |   270 |     3  (34)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |  2048 |  2048 | 2048  (0)|
|* 47 |             TABLE ACCESS FULL         | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|* 48 |           FIXED TABLE FIXED INDEX     | X$KTFBUE (ind:1)     |      2 |  25000 |  1586K|     7 (100)| 00:00:01 |     34 |00:00:00.01 |      12 |      2 |       |       |          |
|  49 |         NESTED LOOPS                  |                      |      1 |      1 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  50 |          NESTED LOOPS                 |                      |      1 |      2 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  51 |           NESTED LOOPS                |                      |      1 |      1 |    79 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  52 |            NESTED LOOPS               |                      |      1 |      1 |    73 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 53 |             TABLE ACCESS FULL         | TS$                  |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  54 |             TABLE ACCESS CLUSTER      | UET$                 |      0 |      1 |    52 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 55 |              INDEX RANGE SCAN         | I_FILE#_BLOCK#       |      0 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 56 |            INDEX UNIQUE SCAN          | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 57 |           INDEX RANGE SCAN            | RECYCLEBIN$_TS       |      0 |      2 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
                      |* 58 |          TABLE ACCESS BY INDEX ROWID  | RECYCLEBIN$          |      0 |      1 |    12 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  59 |    HASH GROUP BY                      |                      |      1 |      1 |   155 |     6  (17)| 00:00:01 |      3 |00:00:00.02 |      52 |      0 |   809K|   809K|  748K (0)|
|  60 |     NESTED LOOPS OUTER                |                      |      1 |      1 |   155 |     5   (0)| 00:00:01 |      6 |00:00:00.02 |      52 |      0 |       |       |          |
|  61 |      NESTED LOOPS                     |                      |      1 |      1 |   127 |     4   (0)| 00:00:01 |      6 |00:00:00.02 |      43 |      0 |       |       |          |
|* 62 |       HASH JOIN                       |                      |      1 |      2 |   220 |     2   (0)| 00:00:01 |      6 |00:00:00.02 |      34 |      0 |  1483K|  1483K|  915K (0)|
|  63 |        NESTED LOOPS                   |                      |      1 |      5 |   355 |     0   (0)|          |      6 |00:00:00.02 |       6 |      0 |       |       |          |
|  64 |         NESTED LOOPS                  |                      |      1 |      5 |   285 |     0   (0)|          |      6 |00:00:00.02 |       0 |      0 |       |       |          |
|* 65 |          FIXED TABLE FULL             | X$KCCTF              |      1 |      6 |    72 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 66 |          FIXED TABLE FIXED INDEX      | X$KCCFN (ind:1)      |      6 |      1 |    45 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 67 |         FIXED TABLE FIXED INDEX       | X$KTFTHC (ind:1)     |      6 |      1 |    14 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|  68 |        VIEW                           | V_$TEMP_SPACE_HEADER |      1 |      3 |   117 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|  69 |         NESTED LOOPS                  |                      |      1 |      3 |   141 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|* 70 |          TABLE ACCESS FULL            | TS$                  |      1 |      1 |    27 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |       |       |          |
|* 71 |          FIXED TABLE FIXED INDEX      | X$KTFTHC (ind:2)     |      2 |      3 |    60 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|* 72 |       TABLE ACCESS BY INDEX ROWID     | TS$                  |      6 |      1 |    17 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 73 |        INDEX UNIQUE SCAN              | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 74 |      VIEW PUSHED PREDICATE            | V_$TEMP_EXTENT_POOL  |      6 |      1 |    28 |     1   (0)| 00:00:01 |      5 |00:00:00.01 |       9 |      0 |       |       |          |
|  75 |       NESTED LOOPS                    |                      |      6 |      1 |    79 |     1   (0)| 00:00:01 |     25 |00:00:00.01 |       9 |      0 |       |       |          |
|* 76 |        TABLE ACCESS BY INDEX ROWID    | TS$                  |      6 |      1 |    27 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 77 |         INDEX UNIQUE SCAN             | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 78 |        FIXED TABLE FIXED INDEX        | X$KTSTFC (ind:1)     |      6 |      1 |    52 |     0   (0)|          |     25 |00:00:00.01 |       0 |      0 |       |       |          |
——————————————————————————————————————————————————————————————–

Here is the query

select /*+ monitor gather_plan_statistics */
a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc – nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 – round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
a.autoext
FROM
(
select f.tablespace_name,
sum(f.bytes) bytes_alloc,
max(f.autoextensible) autoext
from dba_data_files f
group by tablespace_name
) a,
(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION ALL
SELECT
h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
100 – round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
max(f.autoextensible) autoext
FROM
sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
WHERE
p.file_id(+) = h.file_id
AND
p.tablespace_name(+) = h.tablespace_name
AND
f.file_id = h.file_id
AND
f.tablespace_name = h.tablespace_name
GROUP BY
h.tablespace_name, f.maxbytes
ORDER BY 1

and check plan with command

select * from table(dbms_xplan.display_cursor);

Advertisements

About Ugurcan

Exadata Engineer
This entry was posted in Oracle Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s