萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

精準定位 ORA-1652 臨時表空間不足的罪魁禍首 SQL

錯誤現場

ORA-1652: unable to extend temp segment by 128 in tablespace DBA_TEMP

四種定位手法優劣對比

方法優點缺點/限制
1. Event 1652 Trace自動生成 Trace 檔僅對**未來**錯誤有效;可能影響效能;無法追溯歷史
2. V$SQL 排序即時可查難知執行時間點;罪魁 SQL 可能已 Age Out
3. AWR/Statspack 報表歷史視角按總物理讀排序,低頻高耗 SQL 易被漏;不一定是 Temp I/O
4. AWR 歷史視圖查詢 ⭐ 推薦精準鎖定錯誤時間窗、直取 Direct Writes Top SQL需 10g+、Diagnostic Pack 授權

黃金查詢:AWR 歷史視圖直取 Top 10 Direct Writes SQL

SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text, 1, 4000))
FROM   sys.WRH$_SQLTEXT b
WHERE  b.sql_id IN (
  SELECT sql_id
  FROM  (
    SELECT a.sql_id
    FROM   sys.WRH$_SQLSTAT a
    WHERE  a.parsing_schema_name NOT IN ('SYS')
    AND    a.executions_total > 0
    AND    a.direct_writes_total > 0
    AND    a.snap_id IN (
      SELECT snap_id
      FROM   sys.WRM$_SNAPSHOT
      WHERE  TO_DATE('2008-08-20 17:20:08','YYYY-MM-DD HH24:MI:SS')
             BETWEEN begin_interval_time AND end_interval_time
    )
    ORDER BY a.direct_writes_total / a.executions_total DESC
  )
  WHERE rownum <= 10
);

結果判讀

  • 第 1 筆若**非** `INSERT /*+ APPEND */` 等合法大量寫入操作 → **極高機率即為罪魁禍首**。
  • 常見特徵:大排序 (`ORDER BY`/`GROUP BY`)、Hash Join 溢寫 Temp、大型 CTE/Materialize、`CREATE INDEX`/`REBUILD` 缺 `SORT_AREA_SIZE`。

9i/Statspack 環境對應查詢

SELECT sql_text
FROM   stats$sqltext
WHERE  hash_value IN (
  SELECT hash_value
  FROM  (SELECT hash_value
         FROM   stats$sql_summary
         WHERE  direct_writes > 0
         AND    snap_id IN (SELECT snap_id FROM stats$snapshot
                            WHERE snap_time BETWEEN ... )
         ORDER BY direct_writes/executions DESC)
  WHERE rownum <= 10
);

事後修復與預防

  1. **立即緩解**:`ALTER TABLESPACE temp ADD TEMPFILE ... SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;`
  2. **SQL 調優**:加 Hint `/*+ NO_MERGE */` 避免物化、調整 `PGA_AGGREGATE_TARGET`、建立適當索引消除排序。
  3. **監控腳本**:每 5 分鐘跑一次上述 AWR 查詢,寫入告警表,超過閾值發送通知。

結語

ORA-1652 看似空間不足,實則是 **「哪條 SQL 瘋狂吃 Temp」**。用 AWR 歷史視圖按 `DIRECT_WRITES/EXECUTIONS` 降序取 Top N,能在幾秒內鎖定元兇。建議將此查詢封裝為標準排查腳本,納入值班手冊。

copyright © 萬盛學電腦網 all rights reserved