Oracle 運用術 ： これだけでほぼ十分。運用監視スクリプト
５年ほど運用してきた Oracle 8i 時代に実運用で使用してきた運用スクリプト（で面倒なことにならないオープンソース的な部分）を公開します。当時は RAC という技術は存在しなかったので、キャッシュフュージョン等の状態をみるスクリプトは包括されていませんが、そこまで見る必要があるシステムなら、きっと運用に払えるお金もたくさんあると思うので対象外でもＯＫと考えています。
※実際、今の本業の Oracle 運用形態がそうなってるし。
僕が５年間ほど運用してきた感想として Oracle 8i のようなシングルインスタンスもしくは HA の場合は、
- Oracleアラートログの監視スクリプトで異常監視 ※紹介済み
- Oracleの生存監視 ※紹介済み
- 独自の状態監視スクリプトで Oracle の基本情報の監視 ※今回紹介
- STATSPACKを日時で差分レポートする独自スクリプトでパフォーマンスチューニング情報の監視 ※次回紹介
- 負荷計測や瞬間的な Oracle の状態（セッションやロック等）の監視 ※いずれ紹介
で十分と感じています。OEM（Oracle Enterprize Manager） とかはいちいち画面見るのが面倒くさいし、微妙にわかりづらいし、なにより独自スクリプトでメールでサマリーを送りつけてくれる方がいろいろと都合が良かったりします。と言うわけで、今回紹介する運用スクリプトで Oracle がどのような状態で動作しているのか大まかに把握することができるので、事が起きる前に対処することが可能になります。対処方法に関しては本を片手に勉強するのが良いと思うので割愛します。
- スポンサーリンク -
Oracle 運用術 ： これだけでほぼ十分。運用監視スクリプト
1. まずは各種スクリプトをダウンロード下さい。以下の３つで１セットになっています。(1) ora_perf_report.sh ※スクリプト起動 shell です。
(2) ora_perf_report.sql ※運用監視 sql です。
(3) logfilter.pl ※spool したと具を整形するスクリプトです。.txt を rename 下さい。
これらのスクリプトを /tmp 配下に設置下さい。/tmp 以外に配置する際は、spool の設定変更が必要になります。
2. スクリプトを自分の環境に合わせて編集してください
編集が必要なファイルは ora_perf_report.sh のみです。以下をご自分の環境に合わせて設定下さい。
source /etc/bashrc ←Oracle の環境変数の設定 ## Required variable is defined. mailaddress='[email protected]' ←ログ通知先 MachineId=`hostname` ←マシン名 dbuser=sys ←変更不要 dbpw=dba ←oracle の sys ユーザのパスワード tnsname=@testdb ←oracle のサービス名。sqlplus sys/dba as sysdba で接続可能なら空文字で log_file=/tmp/ora_perf_report.lst ←sql-log の spool 先。/tmp 以外に配置するなら変更を script=/tmp/ora_perf_report.sql ←同上。/tmp 以外に配置するなら変更を
/tmp 以外にスクリプトを配置するなら、その他 /tmp になっている部分をおもいの場所に変更下さい。sql の spool 先も忘れず変更を。
3. ora_perl_report.sh を cron で夜中に実行する
4. 毎朝レポートを見る。このスクリプトで以下の項目がレポーティングされるようになります
(01) 最低限必要な shared_pool_sizeの測定
(02) データ・ディクショナリ・アクティビティについての統計情報
(03) 各種ディクショナリ・キャッシュのヒット率→ shared_pool_size のチューニングを検討
(04) NAMESPACE毎のライブラリ・キャッシュのヒット率→ shared_pool_size のチューニングを検討
(05) データベースバッファキャッシュのヒット率→ db_block_buffers のチューニング検討
(06) ソート処理のヒット率→ sort_area_size のチューニングを検討
(07) ラッチに対する詳細なミス率などを把握→ db_block_lru_latchesパラメータ値のチューニングを検討
(08) datafile毎のファイルの読込み/書込みの統計情報。I/Oの集中がないか監視する
(09) REDOログに対する書き込み待ち→ log_buffer のチューニングを検討
(10) REDOログに対するラッチミス率→ log_buffer のチューニングを検討
(11) rollback segment の待ちや収縮などの情報→ ロールバック・セグメントの追加のチューニングを検討
(12) エクステントの拡張がMAXEXTENTSの設定の70%に達したセグメント→ TABLEのEXTENTS のチューニングを検討
(13) 空きブロックが１０％を下回っているテーブルを抽出→ 自動でEXTENTSの事前把握
(14) 頻繁に使用するSQLの中でPARSINGが多いモノを抽出→ アプリ修正で SQL のチューニングを検討
(15) 物理読み込みが多く負荷が高いSQLを抽出→ index のチューニングを検討
(16) バッファ取得が多く負荷が高いSQLを抽出→ index のチューニングを検討
(17) 処理時間の長く負荷が高いSQLを抽出→ いろいろとチューニングが必要
(18) ライブラリ・キャッシュ内の大きなオブジェクトとそのロード回数の把握
(19) 表領域と各データファイルの使用量一覧表示(HWMより下の空きブロックも使用済みとしてカウント)
(20) 各テーブルのHWM情報を取得する
(21) 表領域と各データファイルのHWM配下も考慮した本当の空き容量
一度データベース設計をしてしまうと後で変更するのはなかなか難しいもので、チューニングは奥が深いのでチューニングに関するテクニックは書籍を見ながらが良いと思います。手軽にできるものとして、アプリ側の sql のチューニングと index あたりはこのレポートからすぐに手をつけるべき場所が特定できると思います。
僕が手元に置いている本で手放せない、もしくはおいておいて損はないと感じている書籍はこんなのがあります。
ORACLE9i ハイパフォーマンスチューニング―STATSPACK編
posted with amazlet on 07.06.12
ドナルド・キース バールソン Donald Keith Burleson トップスタジオ 日本オラクル
翔泳社 (2002/09)
売り上げランキング: 503731
翔泳社 (2002/09)
売り上げランキング: 503731
おら!オラ!オラクル
posted with amazlet on 07.06.12
木脇 高太郎
翔泳社 (2003/05/27)
売り上げランキング: 281428
翔泳社 (2003/05/27)
売り上げランキング: 281428
絵で見てわかるOracleの仕組み
posted with amazlet on 07.06.12
小田 圭二
翔泳社 (2006/06/21)
売り上げランキング: 14787
翔泳社 (2006/06/21)
売り上げランキング: 14787
現場で使えるSQL―Oracle/SQL Server対応
posted with amazlet on 07.06.12
小野 哲 藤本 亮
翔泳社 (2001/10)
売り上げランキング: 200546
翔泳社 (2001/10)
売り上げランキング: 200546
一応こんな感じのレポートがあがって来るというサンプルです。結果は仮想的な環境を想定したもので、内容はでたらめにしてあります。
===================
ora_perf_report.sh
2007/06/11
20:43:48
HostName: dev1
Database: testdb
===================
/*---------------------------------------------------------------------------*/
// 最低限必要な shared_pool_sizeの測定
/*---------------------------------------------------------------------------*/
SHARED_POOL_SIZE
----------------
29572111.4
/*---------------------------------------------------------------------------*/
// データ・ディクショナリ・アクティビティについての統計情報
/*---------------------------------------------------------------------------*/
SUM(gets) SUM(getmisses) MISS率(10%以上注意)
-------------------------------------------
35694134 2315312 6.09141212
/*---------------------------------------------------------------------------*/
// 各種ディクショナリ・キャッシュのヒット率→ shared_pool_size をチューニング
/*---------------------------------------------------------------------------*/
PARAMETER GETS GET MISS率(10%以上注意) SCANS SCAN MISS率(10%以上注意) count>usageが望ましい USAGE
----------------------------------------------------------------------------------------------------------------------------------
dc_constraints 6612 33.9987901 0 0 0 0
dc_files 980 82.0408163 0 0 4 4
dc_histogram_defs 7339183 16.5256678 0 0 1055 1055
dc_sequences 4898 47.7541854 0 0 2 2
dc_table_scns 365 100 0 0 0 0
dc_tablespace_quotas 4 25 0 0 0 0
outstanding_alerts 120837 13.1375324 0 0 10 10
/*---------------------------------------------------------------------------*/
// NAMESPACE毎のライブラリ・キャッシュのヒット率→ shared_pool_size をチューニング
/*---------------------------------------------------------------------------*/
NAMESPACE GETS MISS率(10%以上注意) PINS MISS率(10%以上注意) RELOADS(0が良い
--------------------------------------------------------------------------------------------------
SQL AREA 1861801 14.714086 20952448 5.24687616 758823
INDEX 38247 28.7839569 105843 35.4043253 22287
/*---------------------------------------------------------------------------*/
//データベースバッファキャッシュのヒット率→ db_block_buffers をチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
//ソート処理のヒット率→ sort_area_sizeをチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
// ラッチに対する詳細なミス率などを把握→ db_block_lru_latchesパラメータの値をチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
// datafile毎のファイルの読込み/書込みの統計情報。I/Oの集中がないか監視する
/*---------------------------------------------------------------------------*/
NAME PHYSICAL READ READ総時間 PHYSICAL WRITE WRITE総時間 待ちの回数 待ちの時間
---------------------------------------------------------------------------------------------------------------
/db1/oradata/testdb/undotbs101.dbf 7540 751 312921 26538 294 33
/db1/oradata/testdb/system01.dbf 377589 111678 139752 13708 84 91
/db1/oradata/testdb/users01.dbf 735793 477613 48527 16817 8 0
/db1/oradata/testdb/sysaux01.dbf 580474 360258 657008 81871 7 30
/*---------------------------------------------------------------------------*/
// REDOログに対する書き込み待ち→ log_bufferをチューニング
/*---------------------------------------------------------------------------*/
領域割り当て待ち率(1%以上注意)
------------------------------
2.17290148
/*---------------------------------------------------------------------------*/
// REDOログに対するラッチミス率→ log_bufferをチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
// rollback segment の待ちや収縮などの情報→ ロールバック・セグメントの追加のチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
// エクステントの拡張がMAXEXTENTSの設定の70%に達したセグメントに注意→ TABLEのEXTENTSをチューニング
/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*/
// 空きブロックが１０％を下回っているテーブルを抽出→ 自動でEXTENTSされる
/*---------------------------------------------------------------------------*/
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS 空きブロック率
---------------------------------------------------------------------------------
DBSNMP MGMT_BASELINE 5 0 0
DBSNMP MGMT_BASELINE_SQL 0 0 0
DBSNMP MGMT_BSLN_BASELINES 0 0 0
DBSNMP MGMT_BSLN_DATASOURCES 0 0 0
DBSNMP MGMT_BSLN_INTERVALS 0 0 0
DBSNMP MGMT_BSLN_METRICS 5 0 0
DBSNMP MGMT_BSLN_STATISTICS 0 0 0
DBSNMP MGMT_BSLN_THRESHOLD_PARMS 0 0 0
DBSNMP MGMT_CAPTURE 0 0 0
DBSNMP MGMT_CAPTURE_SQL 0 0 0
DBSNMP MGMT_HISTORY 0 0 0
DBSNMP MGMT_HISTORY_SQL 0 0 0
DBSNMP MGMT_LATEST 0 0 0
DBSNMP MGMT_LATEST_SQL 0 0 0
DBSNMP MGMT_RESPONSE_CONFIG 5 0 0
DBSNMP MGMT_SNAPSHOT 5 0 0
DBSNMP MGMT_SNAPSHOT_SQL 0 0 0
OUTLN OL$ 0 0 0
OUTLN OL$HINTS 0 0 0
OUTLN OL$NODES 0 0 0
TEST SAMPLE1 324950 2730 .833129883
TEST SAMPLE2 46 0 0
TEST SAMPLE3 46 0 0
TEST SESSIONS 10097 155 1.51190012
TEST STAT_TABLE 58 0 0
/*---------------------------------------------------------------------------*/
// 頻繁に使用するSQLの中でPARSINGが多いモノを抽出→ アプリ修正でSQLをチューニング
/*---------------------------------------------------------------------------*/
EXECUTES PARSED PARSED率 SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------
263 254 96.58 SELECT * FROM SAMPLE1 WHERE NAME=:p1
252 252 100.00 SELECT ID_SEQ.NEXTVAL FROM DUAL
252 252 100.00 UPDATE SAMPLE2 SET FLG=1 WHERE ID=:p1 AND NAME=:p2
/*---------------------------------------------------------------------------*/
// 物理読み込みが多く負荷が高いSQLを抽出→ index をチューニング
/*---------------------------------------------------------------------------*/
PHYSICAL READS EXECUTES BUFFER_GETS 物理READ/EXEC SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------
6 4 179 150 SELECT * FROM SAMPLE1 WHERE 1=0
4 0 38 0 select SYSDATE from DUAL /* ping */
/*---------------------------------------------------------------------------*/
// バッファ取得が多く負荷が高いSQLを抽出→ index をチューニング
/*---------------------------------------------------------------------------*/
PHYSICAL READS EXECUTES BUFFER_GETS 物理READ/EXEC SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------
364031 173 664 210422.543 select disk_reads "PHYSICAL READS" ,executions EXECUTES ,buffer_gets ,disk_reads*1
364031 174 666 209213.218 select disk_reads "PHYSICAL READS" ,executions EXECUTES ,buffer_gets ,disk_reads*1
/*---------------------------------------------------------------------------*/
// 処理時間の長く負荷が高いSQLを抽出→ いろいろとチューニングが必要
/*---------------------------------------------------------------------------*/
18674579 174 29429026 10732516.7 SELECT * FROM SAMPLE1 WHERE EXPIRE=TRUNC(SYSDATE) FOR UPDATE
3993663 173 157645255 2308475.72 begin DATA_list(); end;
/*---------------------------------------------------------------------------*/
// ライブラリ・キャッシュ内の大きなオブジェクトとそのロード回数の把握
/*---------------------------------------------------------------------------*/
OBJECT_NAME TYPE SHARABLE_MEM LOADS LOCKS
------------------------------------------------------------------------------------------------
DBSNMP.MGMT_BSLN_THRESHOLD_PARMS TABLE 584 2 0
DBSNMP.MGMT_BSLN_DATASOURCES TABLE 580 2 0
PUBLIC.DBMS_APPLICATION_INFO SYNONYM 580 27 0
TEST.idx_MAIN_DATA_FRIEND_ID INDEX 579 3 0
DBSNMP.MGMT_BSLN_STATISTICS TABLE 579 2 0
DBSNMP.BSLN_DATASOURCES_UK1 INDEX 579 2 0
DBSNMP.MGMT_RESPONSE_CONFIG TABLE 579 2 0
DBSNMP.MGMT_BSLN_BASELINES TABLE 578 2 0
DBSNMP.MGMT_BSLN_INTERVALS TABLE 578 2 0
DBSNMP.BSLN_DATASOURCES_PK INDEX 578 2 0
DBSNMP.BSLN_THRESHOLDS_PK INDEX 577 2 0
DBSNMP.BSLN_STATISTICS_PK INDEX 577 2 0
DBSNMP.BSLN_BASELINES_UK1 INDEX 577 2 0
DBSNMP.MGMT_BSLN_METRICS TABLE 576 2 0
DBSNMP.BSLN_BASELINES_PK INDEX 576 2 0
DBSNMP.MGMT_BASELINE_SQL TABLE 576 2 0
DBSNMP.MGMT_SNAPSHOT_SQL TABLE 576 2 0
/*---------------------------------------------------------------------------*/
// 表領域と各データファイルの使用量一覧表示(HWMより下の空きブロックも使用済みとしてカウント)
/*---------------------------------------------------------------------------*/
TABLESPACE_NAME FILE_NAME BYTES_MB USED_KB FREE_KB ts_used_% df_used_%
----------------------------------------------------------------------------------------------------------------------------------
SYSAUX (表領域) 500 338048 173952 66.03
SYSAUX /db1/oradata/testdb/sysaux01.dbf 500 338048 173952 66.03
SYSTEM (表領域) 3540 3578496 46464 98.72
SYSTEM /db1/oradata/testdb/system01.dbf 3540 3578496 46464 98.72
UNDOTBS1 (表領域) 600 7808 606592 1.27
UNDOTBS1 /db1/oradata/testdb/undotbs101.dbf 600 7808 606592 1.27
USERS (表領域) 13000 11347328 1964672 85.24
USERS /db1/oradata/testdb/users01.dbf 13000 11347328 1964672 85.24
/*---------------------------------------------------------------------------*/
// OWNER not like '%SYS%' の各テーブルのHWM情報を取得する
/*---------------------------------------------------------------------------*/
TABLE_NAME Tableの行数 HWM以下のブロック数 HWM以上のブロック数 HWM以下の平均空き容量数(KB) 行データの平均バイト数 チェーンブロックの数 PCT_FREE PCT_USED NUM_FREELIST_BLOCKS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SAMPLE1 11666917 40328 11000 819 23 0 10 0
SAMPLE2 9344656 74755 21245 836 56 0 10 0
SAMPLE3 7918960 324950 2730 1002 315 6031 10 0
/*---------------------------------------------------------------------------*/
// 表領域と各データファイルのHWM配下も考慮した本当の空き容量
/*---------------------------------------------------------------------------*/
オブジェクト名 テーブルスペース名 オブジェクト種別 割当-全Byte数 HWM使用Byte数 REAL使用Byte数 REAL空きByte数 再利ブロック数 Block内使用率(%) 使用率(%)
--------------------|--------------------|----------------|--------------|--------------|--------------|--------------|--------------|----------------|--------------
SAMPLE1 USERS TABLE 2,684,354,560 2,667,577,344 2,667,577,344 16,777,216 0 100.0 99.4
idx_SAMPLE1 USERS INDEX 788,529,152 411,041,792 411,041,792 377,487,360 0 100.0 52.1
SAMPLE2 USERS TABLE 786,432,000 615,514,112 615,514,112 170,917,888 0 100.0 78.3
SAMPLE3 USERS TABLE 420,478,976 332,398,592 332,398,592 88,080,384 0 100.0 79.1
idx_SAMPLE2 USERS INDEX 419,430,400 295,698,432 295,698,432 123,731,968 0 100.0 70.5
- スポンサーリンク -
コメントやシェアをお願いします！