Oracle Statspack 完整部署與效能分析實戰指南

Statspack 是 Oracle 官方輕量級效能診斷工具，適合無 AWR 授權或 10g 以前版本。本文整理從安裝、快照收集到報告分析的完整流程。

部署前置檢查

-- 1. 確認 JOB_QUEUE_PROCESSES > 0（自動快照需求）
SHOW PARAMETER job_queue_processes;
-- 2. 確認 TIMED_STATISTICS = TRUE
SHOW PARAMETER timed_statistics;

安裝步驟

1. 建立專用表空間（建議 500M+）：
   CREATE TABLESPACE perfstat DATAFILE '/u01/oradata/perfstat.dbf' SIZE 500M AUTOEXTEND ON;
2. 執行安裝腳本（以 SYS 身分）：
   @$ORACLE_HOME/rdbms/admin/spcreate.sql
   依序輸入：perfstat 密碼、預設表空間、暫存表空間。
3. 驗證安裝：
   EXEC statspack.snap;（執行兩次產生兩個快照）
   @$ORACLE_HOME/rdbms/admin/spreport.sql → 輸入 begin/end snap_id、報告檔名。

自動化快照排程

-- 預設每小時一個快照
@$ORACLE_HOME/rdbms/admin/spauto.sql

查看/移除排程：

SELECT job, log_user, last_date, next_date, interval FROM user_jobs;
EXEC dbms_job.remove(41); -- 替換實際 job 編號

快照等級與門檻調優

修改預設等級：

EXEC statspack.snap(i_snap_level=>5, i_modify_parameter=>'true');

門檻參數（儲存於 stats$statspack_parameter）：

• executions_th (預設 100)
• disk_reads_th (預設 1,000)
• buffer_gets_th (預設 10,000)

調整範例：

EXEC statspack.modify_statspack_parameter(i_buffer_gets_th=>100000, i_disk_reads_th=>100000);

常用報告腳本

• spreport.sql：標準報告
• sprepsql.sql：指定 SQL Hash 值報告
• spuexp.par：匯出儲存庫資料

清理歷史資料

DELETE FROM stats$snapshot WHERE snap_id < 3; -- 級聯刪除相關表
-- 或執行 @$ORACLE_HOME/rdbms/admin/sptrunc.sql 截斷所有統計表

Statspack 雖輕量，善用等級與門檻調優，即可成為生產環境效能基線建設的利器。