Why Oracle Tablespace Queries Stall and How to Resolve Them Fast

1. Tablespace Information Query Slowness

A client reported that a custom query against DBA_FREE_SPACE stopped returning results, causing anxiety about unknown tablespace usage. The typical reasons are stale dictionary statistics and an excess of objects in the recycle bin.

Remediation steps:

exec dbms_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_dictionary_stats(degree => 8, cascade => true);

purge recyclebin;

After executing these commands, the query returned promptly.

The original SQL used by the client was:

SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
       ROUND(D.AVAILB_BYTES, 2) AS "表空间大小(G)",
       ROUND(D.MAX_BYTES, 2) AS "最终表空间大小(G)",
       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空间(G)",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100, 2), '999.99') AS "使用比",
       ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",
       F.MAX_BYTES AS "最大块(M)"
FROM   (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024*1024*1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024*1024*1024), 6) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024*1024*1024), 6) AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES,0,DD.BYTES,DD.MAXBYTES)) / (1024*1024*1024),6) MAX_BYTES
        FROM   SYS.DBA_DATA_FILES DD
        GROUP BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

Initially the script hung; after gathering fresh statistics and purging the recycle bin, the query completed in seconds.

2. ASH Data Collection Slowness

Another client observed that a monitoring SQL which collects Active Session History (ASH) data took up to four minutes on one database, while the same statement finished within a second on other instances.

Using an AWR monitor report, the wait events were identified: ~55% "read by other session" and ~36% GC‑related waits, indicating heavy I/O reads in a RAC environment.

The I/O originated mainly from the WRH$_ACTIVE_SESSION_HISTORY table (54% from one partition, 46% from another). The table should be partitioned and automatically cleaned, but in this case partitions accumulated data.

To verify partition information, the following PL/SQL block was run:

set serveroutput on
declare
  cursor cur_part is
    select partition_name from dba_tab_partitions
    where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
  query1 varchar2(200);
  query2 varchar2(200);
  type partrec is record (snapid number, dbid number);
  type partlist is table of partrec;
  outlist partlist;
begin
  dbms_output.put_line('PARTITION NAME  SNAP_ID  DBID');
  dbms_output.put_line('--------------------------- ------- ----------');
  for part in cur_part loop
    query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
    execute immediate query1 bulk collect into outlist;
    if outlist.count > 0 then
      for i in outlist.first..outlist.last loop
        dbms_output.put_line(part.partition_name||' Min '||outlist(i).snapid||' '||outlist(i).dbid);
      end loop;
    end if;
    query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
    execute immediate query2 bulk collect into outlist;
    if outlist.count > 0 then
      for i in outlist.first..outlist.last loop
        dbms_output.put_line(part.partition_name||' Max '||outlist(i).snapid||' '||outlist(i).dbid);
      end loop;
    end if;
    dbms_output.put_line('---');
  end loop;
end;
/

The output confirmed that partitions existed but were not being automatically created or cleaned.

Cleanup commands used were:

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 2810, high_snap_id => 18000);

alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

Because the shrink operation took long (several hours), the author also suggested truncating the large partition directly (with the risk of losing some performance data):

alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_xxxx_xxxx update global indexes;

After these actions, the ASH‑collecting SQL returned instantly. The author notes that on older Oracle versions (11.2.0.2) a hidden parameter _swrf_test_action = 72 may need to be set, and that patching to 12.1.0.1 or later can resolve automatic partition‑creation issues.