案例：Oracle表空間查詢慢一例

今天接到客戶反饋，一條監控SQL執行超時，讓分析原因。

拿到SQL後，是一條UNDO表空間監控的SQL，嘗試執行，發現要近10分鐘。首先懷疑回收站中是不是有太多對象，查詢發現有1萬3千多個，很多是好幾個月以前的，與客戶溝通後清理8天以前的，清理後只剩下400多。重新查詢發現還是很慢，那麼就不是（或不僅僅是）回收站的原因了。

繼續分析......

先跑sql monitor

SQL> select dbms_sqltune.report_sql_monitor('f094zzskw9gnz') from dual;

從sql monitor的結果可以看出，時間主要消耗在對X$KTFBUE這個Fixed Table的全表掃上，這裡預估的Rows是5000行。

查詢真實的記錄數，實際有138萬行，那麼問題就是統計信息不準了

SQL> select count(*) from X$KTFBUE';

 COUNT(*)
--------------
 1382383

查詢統計信息收集時間，為空

SQL> select owner, table_name, last_analyzed
 2 from dba_tab_statistics t
 3 where t.TABLE_NAME = 'X$KTFBUE';

OWNER TABLE_NAME LAST_ANALYZED
---------- --------------- -------------------
SYS X$KTFBUE

接下來收集統計信息

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KTFBUE',estimate_percent => 100);

PL/SQL procedure successfully completed. 

收集後繼續查詢，發現還是很慢，X$KTFBUE仍然是全表掃，預估還是5000行，那麼應該是SQL沒有重新解析，共享了上次執行的遊標，通過下面的方式清理SQL遊標，讓其重新解析

SQL> exec dbms_shared_pool.purge('000000441BE44690,633650847','C');

PL/SQL procedure successfully completed.

再次執行，只需要0.2秒，問題解決

SQL> SELECT case
 2 when va.co = 0 then
 3 'undo表空間監控正常'
 4 else
 5 'undo表空間監控' || va.ti
 6 end as TITLE,
 7 va.co as COUNTS
 8 FROM (select listagg(tablespace_name || ':' || pct_used || '%,剩餘' ||
 9 (total_mb - used_mb) || 'MB,') within group(order by pct_used) ti,
 10 count(tablespace_name) co
 11 from (SELECT ts.tablespace_name,
 12 ROUND(NVL(use_d.use_bytes, 0) / 1024 / 1024, 2) used_mb,
 13 total_bytes / 1024 / 1024 total_mb,
 14 round((NVL(use_d.use_bytes, 0) * 100) / total_bytes, 2) pct_used
 15 FROM (SELECT tablespace_name,
 16 SUM(decode(ddf.AUTOEXTENSIBLE,
 17 'YES',
 18 ddf.MAXBYTES,
 19 'NO',
 20 ddf.bytes)) total_bytes
 21 FROM dba_data_files ddf
 22 GROUP BY ddf.tablespace_name) total_d,
 23 (select TABLESPACE_NAME, SUM(bytes) use_bytes
 24 from dba_undo_extents
 25 where status <> 'EXPIRED'
 26 group by tablespace_name) use_d,
 27 dba_tablespaces ts
 28 WHERE ts.tablespace_name = total_d.tablespace_name(+)
 29 AND ts.tablespace_name = use_d.tablespace_name(+)
 30 AND ts.CONTENTS = 'UNDO'
 31 ORDER BY ts.tablespace_name)
 32 WHERE pct_used > 95) va;

TITLE COUNTS
------------------------- ----------
undo表空間監控正常 0

Elapsed: 00:00:00.20

再次查詢執行計劃，已經走了索引

參考資料：

《Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang (Doc ID 1292253.1)》

《Some Fixed Tables(X$) are Missing CBO Statistics (Doc ID 1637294.1)》