Skip to content
Home » Oracle » Improve Query on Dictionary and Dynamic Views

Improve Query on Dictionary and Dynamic Views

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.


In this step, we gather the statistics of base tables of views, which belong to SYS.

SQL> show user
SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *