Oracle 運用術 : これだけでほぼ十分。運用監視スクリプト

5年ほど運用してきた Oracle 8i 時代に実運用で使用してきた運用スクリプト(で面倒なことにならないオープンソース的な部分)を公開します。当時は RAC という技術は存在しなかったので、キャッシュフュージョン等の状態をみるスクリプトは包括されていませんが、そこまで見る必要があるシステムなら、きっと運用に払えるお金もたくさんあると思うので対象外でもOKと考えています。
※実際、今の本業の Oracle 運用形態がそうなってるし。

僕が5年間ほど運用してきた感想として Oracle 8i のようなシングルインスタンスもしくは HA の場合は、

  • Oracleアラートログの監視スクリプトで異常監視 ※紹介済み
  • Oracleの生存監視 ※紹介済み
  • 独自の状態監視スクリプトで Oracle の基本情報の監視 ※今回紹介
  • STATSPACKを日時で差分レポートする独自スクリプトでパフォーマンスチューニング情報の監視 ※次回紹介
  • 負荷計測や瞬間的な Oracle の状態(セッションやロック等)の監視 ※いずれ紹介

で十分と感じています。OEM(Oracle Enterprize Manager) とかはいちいち画面見るのが面倒くさいし、微妙にわかりづらいし、なにより独自スクリプトでメールでサマリーを送りつけてくれる方がいろいろと都合が良かったりします。と言うわけで、今回紹介する運用スクリプトで Oracle がどのような状態で動作しているのか大まかに把握することができるので、事が起きる前に対処することが可能になります。対処方法に関しては本を片手に勉強するのが良いと思うので割愛します。

- スポンサーリンク -

Oracle 運用術 : これだけでほぼ十分。運用監視スクリプト

1. まずは各種スクリプトをダウンロード下さい。以下の3つで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='user@youredomain.co.jp'  ←ログ通知先
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) 空きブロックが10%を下回っているテーブルを抽出→ 自動でEXTENTSの事前把握
(14) 頻繁に使用するSQLの中でPARSINGが多いモノを抽出→ アプリ修正で SQL のチューニングを検討
(15) 物理読み込みが多く負荷が高いSQLを抽出→ index のチューニングを検討
(16) バッファ取得が多く負荷が高いSQLを抽出→ index のチューニングを検討
(17) 処理時間の長く負荷が高いSQLを抽出→ いろいろとチューニングが必要
(18) ライブラリ・キャッシュ内の大きなオブジェクトとそのロード回数の把握
(19) 表領域と各データファイルの使用量一覧表示(HWMより下の空きブロックも使用済みとしてカウント)
(20) 各テーブルのHWM情報を取得する
(21) 表領域と各データファイルのHWM配下も考慮した本当の空き容量

一度データベース設計をしてしまうと後で変更するのはなかなか難しいもので、チューニングは奥が深いのでチューニングに関するテクニックは書籍を見ながらが良いと思います。手軽にできるものとして、アプリ側の sql のチューニングと index あたりはこのレポートからすぐに手をつけるべき場所が特定できると思います。


僕が手元に置いている本で手放せない、もしくはおいておいて損はないと感じている書籍はこんなのがあります。

ORACLE9i ハイパフォーマンスチューニング―STATSPACK編
ドナルド・キース バールソン Donald Keith Burleson トップスタジオ 日本オラクル
翔泳社 (2002/09)
売り上げランキング: 503731
おら!オラ!オラクル
おら!オラ!オラクル
posted with amazlet on 07.06.12
木脇 高太郎
翔泳社 (2003/05/27)
売り上げランキング: 281428
絵で見てわかるOracleの仕組み
小田 圭二
翔泳社 (2006/06/21)
売り上げランキング: 14787
現場で使えるSQL―Oracle/SQL Server対応
小野 哲 藤本 亮
翔泳社 (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をチューニング
/*---------------------------------------------------------------------------*/



/*---------------------------------------------------------------------------*/
// 空きブロックが10%を下回っているテーブルを抽出→ 自動で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
- スポンサーリンク -