At times, I found that the query to check tablespace usage was running slowly. In the query, we retrieve some data from data dictionary view DBA_FREE_SPACE and DATA_FILES. To speed up queries on those data dictionary and dynamic views, we can analyze fixed objects and dictionary by gathering their statistics. To do so, we should use SYS or any other user who has ANALYZE ANY DICTIONARY privilege to collect the following 2 statistics.

1
2
3
4
5
6
7
8
9
10

In this step, we gather the statistics of base tables of views, which belong to SYS.
SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.