因為 Oracle AWR 需要 DIAGNOSTIC+TUNING 授權才可以明確使用，但是在實務中沒有監控 DBA 只能當通靈大師 Tunning Database，因此轉而使用Oracle Statspack 作為替代方案。
以下是啟用方式
Statspack 啟用方式
- 進入 Sqlplus
- 檢查 job_queue_processes > 0
SQL> show parameter job_queue_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
- 規劃一個 tablespace 空間供 Statspack 存放資料
CREATE TABLESPACE PERFSTAT DATAFILE '/u01/app/oracle/oradata/DB/PERFSTAT01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;
SQL> select file_name from sys.dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB/system01.dbf
/u01/app/oracle/oradata/DB/sysaux01.dbf
/u01/app/oracle/oradata/DB/users01.dbf
/u01/app/oracle/oradata/DB/undotbs01.dbf
SQL> CREATE TABLESPACE PERFSTAT DATAFILE '/u01/app/oracle/oradata/DB/PERFSTAT01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;
Tablespace created.
- 執行建立腳本
-- define default_tablespace='perfstat'
-- define temporary_tablespace='temp'
@?/rdbms/admin/spcreate
SQL> @?/rdbms/admin/spcreate
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: ****
****
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
PERFSTAT PERMANENT
STATSPACK PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: PERFSTAT
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
...
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;
Session altered
- 建立排程: 快照排程
@?/rdbms/admin/spauto
SQL> select count(*) from stats$snapshot;
COUNT(*)
----------
0
1 row selected.
-- Create snapshot job using scheduler - every fifteen minutes
exec dbms_scheduler.create_program(program_name => 'SP_SNAP_PROG', program_type => 'STORED_PROCEDURE', program_action => 'PERFSTAT.statspack.snap', number_of_arguments => 0, enabled => FALSE);
exec dbms_scheduler.enable(name => 'SP_SNAP_PROG');
exec dbms_scheduler.create_schedule (schedule_name => 'SP_SNAP_SCHED', repeat_interval => 'freq=hourly; byminute=0,15,30,45; bysecond=0',end_date => null, comments => 'Schedule for Statspack snaps');
exec dbms_scheduler.create_job (job_name => 'SP_SNAP_JOB', program_name => 'SP_SNAP_PROG', schedule_name => 'SP_SNAP_SCHED', enabled => TRUE, auto_drop => FALSE, comments => 'Statspack Job for snaps');
- 建立排程: 資料保留排程
variable jobno number;
begin
dbms_job.submit(
job => :jobno,
what => 'statspack.purge(i_num_days => 14, i_extended_purge => TRUE);',
next_date => trunc(sysdate + 1) + 1/24, -- 明天凌晨一點開始跑
interval => 'trunc(sysdate + 1) + 1/24' -- 每天跑一次
);
commit;
end;
/
產生報表
檢查快照
SELECT SNAP_ID, SNAP_TIME FROM STATS$SNAPSHOT ORDER BY 1;
@?/rdbms/admin/spreport.sql
SNAP_ID|SNAP_TIME |
-------+-----------------------+
1|2026-04-14 17:00:00.000|
2|2026-04-14 18:00:00.000|
3|2026-04-14 19:00:00.000|
4|2026-04-14 20:00:00.000|
SQL> @?/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1994028977 DB 1 db151
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1994028977 1 DB db151 vm151-db-ora
cle-19c.home
.pollochang.
work
Using 1994028977 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
db151 DB 1 14 Apr 2026 17:00 5
2 14 Apr 2026 18:00 5
3 14 Apr 2026 19:00 5
4 14 Apr 2026 20:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name sp_1_2
其他使用方式
基準線保護(Baselining)
保存調教後的資料，例如: 壓力測試後調教結果
- 標記指令（例如標記快照 10 到 20）
EXEC statspack.make_baseline(i_begin_snap => 10, i_end_snap => 20);