In release 10g, the view dba_free_space was modified to also include objects in the recycle bin.
Large number of objects in the recyclebin can slow down queries on dba_free_space.
This is a normal behaviour. For release 11g, the view dba_free_space doesn’t contain a hint which in case when there is only few objects in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.”
SQL> SELECT count(*) from dba_recyclebin;
Purge the recycle bin and re-ran the check:
SQL> purge dba_recyclebin;
Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’
Oracle document says:
In release 10g, the view dba_free_space was modified to also include objects in the recycle bin.
Large number of objects in the recyclebin can slow down queries on dba_free_space.
This is a normal behaviour. For release 11g, the view dba_free_space doesn’t contain a hint which in case when there is only few objects in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.”
SQL> SELECT count(*) from dba_recyclebin;Purge the recycle bin and re-ran the check:
SQL> purge dba_recyclebin;Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.