STATSPACKを使った自動情報収集
SQL*Plus でレポートを出力させる処理を自動化
変数を定義してから呼び出してやるとプロンプトで入力を求められずに済む。下記を auto.sql とかにして直接実行すればスナップショット410と411の比較レポートが 410-411.txt として出力される。
define begin_snap=410 define end_snap=411 define report_name=410-411.txt @?/rdbms/admin/spreport
スナップショットの削除処理を自動化
- 10g(10.1) から sppurge の機能が statspack パッケージに統合されたようだ。これでデータベース内のジョブから取得もハウスキープも制御しやすくなる。
- i_extended_purge を TRUE にすることで、どのスナップショットからも参照されなくなったSQL情報(SQL本文や実行計画)を削除するようになる。むしろこれを標準でやってほしいが、『参照されていない』という判断にやはり時間が多少かかる感じ。
- 削除対象の指定は下記の通り柔軟にできる。
- スナップショットのIDを指定する場合、存在していないとエラーになる。自動化上は日付や保存日数による指定が使いやすいだろう。
- 後述するベースライン指定したスナップショットは範囲内に入っても削除対象外になる。
-- スナップショットIDの範囲を指定して削除 execute statspack.purge(i_begin_snap=>410, i_end_snap=>411, i_extended_purge=>TRUE) --スナップショットの取得日時範囲を指定して削除 execute statspack.purge(i_begin_date=>to_date(・・・), i_end_date=>to_date(・・・), i_extended_purge=>TRUE) --特定日時以前に取得したスナップショットを削除 execute statspack.purge(i_purge_before_date=>to_date(・・・), i_extended_purge=>TRUE) --指定日数分のスナップショットを残して削除 execute statspack.purge(i_num_days=>31, i_extended_purge=>TRUE)
比較基準に使うスナップショットを削除対象外にする(ベースライン指定する)
- 比較に使用するスナップショットを削除対象外にしたい場合は statspack.make_baseline および statspack.clear_baseline を使用してスナップショットをベースライン指定する。
- ベースライン指定したスナップショットは statspack.purge の範囲指定に含まれても削除されない。
- sptrunc はベースライン指定に関係なく全削除になる(TRUNCATEだから無理だろうし)。
- ベースラインになっているかどうかは STATS$SNAPSHOT 表の SNAPSHOT 列が Y か NULL かで判断できる。
- ベースラインの指定はスナップショットIDの他に日時範囲指定でも可能。
--スナップショットID 100 から 110 までをベースラインとしてマークする execute statspack.make_baseline(i_begin_snap=>100, i_end_snap=>110); --スナップショットID 106 から 110 まではベースラインのマークを解除する execute statspack.clear_baseline(i_begin_snap=>106, i_end_snap=>110); --スナップショットID 90 から 120 までを削除する execute statspack.purge(i_begin_snap=>90, i_end_snap=>120, i_extended_purge=>TRUE); --スナップショットID 100 から 105 までは削除されずに残る
長期傾向のためにスナップショットを取得する場合の注意事項
- スナップショットを比較する場合、それらのスナップショット取得の間にインスタンス再起動が入っていてはならない。
- 再起動が間にあったスナップショット同士を比較すると下記の ORA エラーとなる(411 と 421の間に再起動した場合)。
ORA-20200: The instance was shutdown between snapshots 411 and 421
- STATS$xxx 表に直接アクセスして情報を取得するのがメインであればよいが、spreport を使うのであれば、各再起動の間に2回はスナップショットをとっておかなければならない。
- スナップショット同士で再起動が間になかったかは STATS$SNAPSHOT 表の STARTUP_TIME (インスタンス起動日時)を比較すればわかる。
DBMS_SCHEDULER で取得を自動化する
DBMS_SCHEDULER を使う動機
cron と SQL*Plus の組み合わせもありだけど、下記を考えるとDBMS_SCHEDULERを使いたい。
- 外部からの接続処理を行わないといけないので、エラー発生要因が増える。
- PERFSTATユーザのパスワードを外に保存することになる。
- DB起動中でないとエラーになる(何が何でも取らないといけない、という情報でもないのでDB起動中に取れれば十分だと考える)。
- cron に登録するOS以外の設定はどこの設計書に記載するべきか結構迷う。DBに登録すればDBの設計書に書くのが自明(SIer事情)。
準備作業(SYSDBAで行う)
- ジョブ実行するプロセスが起動できることの確認。
- JOB_QUEUE_PROCESSES の値が1以上であることを確認。0であれば初期化パラメータを変更する。
SQL> SHOW PARAMETERS JOB_QUEUE_PROCESSES NAME TYPE VALUE --------------------- --------- ------- job_queue_processes integer 1000
- PERFSTATにジョブ・プログラム・スケジュールの作成権限をつける(スケジュール変更の都度SYSDBAで作業するのはどうかと思うので)。
GRANT CREATE JOB TO PERFSTAT; --GRANTが正常に実行されました。 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'PERFSTAT'; --GRANTEE PRIVILEGE ADMIN_OPTION -------------------------------- ---------------------------------------- ------------ --PERFSTAT CREATE TABLE NO --PERFSTAT CREATE JOB NO --PERFSTAT CREATE VIEW NO --PERFSTAT CREATE SESSION NO --PERFSTAT CREATE PROCEDURE NO --PERFSTAT CREATE SEQUENCE NO --PERFSTAT CREATE PUBLIC SYNONYM NO --PERFSTAT DROP PUBLIC SYNONYM NO --PERFSTAT ALTER SESSION NO --9行選択されました
実装
- DBMS_SCHEDULER を使う上で設定するべきものは3つ。
- プログラム: 実際に実行する処理を定義する。PL/SQL無名ブロック・ストアドプロシージャ・外部プログラムなどをプログラムにできる。
- スケジュール: 実行する日時や繰り返し頻度を定義する。ただしスケジュール自体は実行という概念はない。ジョブがスケジュールで定義された日時をトリガーとして扱う。
- ジョブ: プログラムとスケジュールを結びつけるもの。関連づけられたスケジュールで定義された日時に関連づけられたプログラムを起動する。
- 仕様
- 毎時0分0秒に statspack.snap を呼び出す。
- 取得レベルは 7 にする。
- 日曜日の朝6時30分0秒に statspack.purge を呼び出す。
- 保存期間は3ヶ月+1週間。削除が週次なので最大93+7日+7日分存在するタイミングがある。
- 毎時0分0秒に statspack.snap を呼び出す。
- 作成用SQL(PERFSTATユーザで実行)
--スナップショットの取得用 CREATE OR REPLACE PROCEDURE STATS_TAKE_SNAPSHOT AS BEGIN statspack.snap( i_snap_level => 7, i_ucomment => 'taken with STATS_TAKE_SNAPSHOT' ); END; / BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'PRG_STATS_TAKE_SNAPSHOT', program_type => 'STORED_PROCEDURE', program_action => 'STATS_TAKE_SNAPSHOT', enabled => TRUE, comments => 'Run STATS_TAKE_SNAPSHOT procedure' ); DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'SCH_STATS_TAKE_SNAPSHOT', start_date => NULL, --開始日設定なし (ジョブが有効になって最初のくり返しから即実行) end_date => NULL, --終了日設定なし repeat_interval => 'FREQ=HOURLY; BYMINUTE=0; BYSECOND=0', -- 毎時0分0秒に実行 comments => 'Run every hour (**:00:00)' ); DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_STATS_TAKE_SNAPSHOT', program_name => 'PRG_STATS_TAKE_SNAPSHOT', schedule_name => 'SCH_STATS_TAKE_SNAPSHOT', enabled => TRUE, auto_drop => FALSE, comments => 'Run PRG_STATS_TAKE_SNAPSHOT procedure based on SCH_STATS_TAKE_SNAPSHOT' ); END; / --スナップショットのハウスキープ用 CREATE OR REPLACE PROCEDURE STATS_PURGE_SNAPSHOT AS BEGIN statspack.purge( i_num_days => 31*3+7, --3ヶ月+1週間は残す i_extended_purge => TRUE ); END; / BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'PRG_STATS_PURGE_SNAPSHOT', program_type => 'STORED_PROCEDURE', program_action => 'STATS_PURGE_SNAPSHOT', enabled => TRUE, comments => 'Run STATS_PURGE_SNAPSHOT procedure' ); DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'SCH_STATS_PURGE_SNAPSHOT', start_date => NULL, --開始日設定なし (ジョブが有効になって最初のくり返しから即実行) end_date => NULL, --終了日設定なし repeat_interval => 'FREQ=DAILY; BYDAY=SUN; BYHOUR=6; BYMINUTE=30; BYSECOND=0', -- 毎週日曜日の 06:30:00 comments => 'Run at 06:30:00 Sunday' ); DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_STATS_PURGE_SNAPSHOT', program_name => 'PRG_STATS_PURGE_SNAPSHOT', schedule_name => 'SCH_STATS_PURGE_SNAPSHOT', enabled => TRUE, auto_drop => FALSE, comments => 'Run PRG_STATS_PURGE_SNAPSHOT procedure based on SCH_STATS_PURGE_SNAPSHOT' ); END; /
- 削除用SQL(PERFSTATユーザで実行)
--スナップショットの取得用 EXECUTE DBMS_SCHEDULER.DROP_JOB('JOB_STATS_TAKE_SNAPSHOT'); EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('SCH_STATS_TAKE_SNAPSHOT'); EXECUTE DBMS_SCHEDULER.DROP_PROGRAM('PRG_STATS_TAKE_SNAPSHOT'); DROP PROCEDURE STATS_TAKE_SNAPSHOT; --スナップショットのハウスキープ用 EXECUTE DBMS_SCHEDULER.DROP_JOB('JOB_STATS_PURGE_SNAPSHOT'); EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('SCH_STATS_PURGE_SNAPSHOT'); EXECUTE DBMS_SCHEDULER.DROP_PROGRAM('PRG_STATS_PURGE_SNAPSHOT'); DROP PROCEDURE STATS_PURGE_SNAPSHOT;
- 設定後のスケジュール状況をOEMで確認
- 実行履歴などもOEMで確認してみる
