DB version: 11.2.0.4
Platform: Oracle Linux 6.5
I have a production DB which is 4 Terabytes in physical size and nearly 3 Terabyes in Logical size. The 'auto optimizer stats collection' job is running daily without any issues . ie. DBA_AUTOTASK_JOB_HISTORY.JOB_STATUS= SUCCEEDED
But, queries agains DBA_ views (and sometimes V$views) are slow. For example, the below query in red used to complete in 1 minute. But, now it is taking more than 5 minutes for the below query to execute.
SQL> select sum(bytes/power(1024,4)) from dba_Data_Files;
SUM(BYTES/POWER(1024,4))
------------------------
4.26480961
Elapsed: 00:00:00.26
SQL>
SQL> select sum(bytes/power(1024,4)) from dba_free_space; ----- > This query took only 1 minute earlier. Now taking more than 5 minutes
SUM(BYTES/POWER(1024,4))
------------------------
1.32211578
Elapsed: 00:05:28.15
SQL>
SQL> -- getting the count of extents
SQL>
SQL> select count(*) From dba_free_space;
COUNT(*)
----------
77468
SQL> select sum(bytes/power(1024,4)) from dba_extents;
SUM(BYTES/POWER(1024,4))
------------------------
2.94262528
Elapsed: 00:04:25.35
Is there a way to enhance the performance of queries agains DBA_ and V$ views ?