Query on Views
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.
GATHER_FIXED_OBJECTS_STATS
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.
GATHER_DICTIONARY_STATS
Then we gather the statistics of dictionary views.
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
The query performs much better than before.