- 23 Nov, 2023
- 0 Comments
- 43 Secs Read
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.
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
SQL> exec dbms_stats.gather_dictionary_stats; PL/SQL procedure successfully completed.