Oracle 常用指令 & SQL - STATSPACK 篇 (STATSPACK section)

Statspack 相關指令

Install statspack (安裝)

SQL> create tablespace perstat datafile size 100m autoextend on maxsize unlimited;
SQL> @?/rdbms/admin/spcreate.sql

SQL> Enter value for perfstat_password: perfstat
SQL> Enter value for default_tablespace: perstat
SQL> Enter value for temporary_tablespace: temp

修改預設statspack snap收集層級為7

SQL> select * from stats$level_description order by snap_level

SNAP_LEVEL DESCRIPTION
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	 0 This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
	 5 This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
	 6 This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
	 7 This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
	10 This level includes capturing Child Latch statistics, along with all data captured by lower levels


SQL> execute statspack.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter => 'true');

Manually create snap (手動建立 snap)

SQL> execute statspack.snap;

Create statspack auto snapshot by 1 hour (設定每小時自動 snap)

-- create schedule
BEGIN
   DBMS_SCHEDULER.CREATE_SCHEDULE(
   schedule_name   => 'SNAP_1hour',
   repeat_interval => 'FREQ=HOURLY;INTERVAL=1');
END;
/

-- create job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'sp_snapshot',
    job_type      => 'STORED_PROCEDURE',
    job_action    => 'statspack.snap',
    schedule_name => 'SNAP_1hour',
    auto_drop     =>  FALSE,
    comments      => 'Statspack collection');
END;
/

-- enable the job :
BEGIN DBMS_SCHEDULER.ENABLE('sp_snapshot');
END;
/ 

-- disable the job :
BEGIN DBMS_SCHEDULER.DISABLE('sp_snapshot');
END;
/

Create statspack auto purge snapshot older than 7 days (每天自動刪除7天前的 snap)

-- create schedule
BEGIN
   DBMS_SCHEDULER.CREATE_SCHEDULE(
   schedule_name   => 'SNAP_1day',
   repeat_interval => 'FREQ=DAILY;BYHOUR=0');
END;
/

-- create job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'sp_snapshot_purge',
    job_type      => 'PLSQL_BLOCK',
    job_action    => 'begin statspack.purge(i_num_days => 7); end;',
    schedule_name => 'SNAP_1day',
    auto_drop     =>  FALSE,
    comments      => 'Statspack purge');
END;
/

-- enable the job :
BEGIN DBMS_SCHEDULER.ENABLE('sp_snapshot_purge');
END;
/

-- disable the job :
BEGIN DBMS_SCHEDULER.DISABLE('sp_snapshot_purge');
END;
/ 

Create statspack report (建立 statspack 報表)

SQL> @?/rdbms/admin/spreport.sql

Manually statspack purge (手動刪除 snap)

SQL> exec statspack.purge(number_of_days);
SQL> @?/rdbms/admin/sppurge

Important Notice: The SQL Texts in the Statspack reports are truncated if they are longer than a specific length. If you want to be able to read the whole SQL statement, apply the below fix.

Modify the num_rows_per_hash parameter in $ORACLE_HOME\rdbms\admin\sprepins.sql

1. Open sprepins.sql
2. Search for the row define num_rows_per_hash=5;
3. Modify the value to a larger number define num_rows_per_hash=15;
4. Save the modifications in sprepins.sql