Oracleのパフォーマンス統計をGraphiteMessageFormatで取得するSQL


Oracleのパフォーマンス統計情報をGrafanaで表示させる必要が出てきたんだけど、Webを探してもそれらしい方法が無いので [Graphite message format][] を出力するSQLを作ってみた。
Oracleを使う層ってあんまりSENSUとかGrafana使ったりしないのかな？

Sensu経由でGraphiteに送り込んでるんだけど、標準出力に吐き出してるだけなので [Mackerel][] とかでも使えるはず。

[Graphite message format]: http://graphite.wikidot.com/getting-your-data-into-graphite
[Mackerel]: https://mackerel.io/

単に [v$sysstat][] から複数回情報を集めて差分をとってるだけです。
取得内容については手持ちの本でおすすめされてるものだけを抜き出しているんで、他に「これ重要！！」って項目あったら教えて下さい！！

[v$sysstat]: http://docs.oracle.com/cd/E16338_01/server.112/b56311/stats002.htm

SENSUで利用する場合は `conf.d` に `Oracle.json` などを用意して、下記の感じに指定すれば60秒毎に情報を取得する。
SQLの中で待機時間のインターバルを58秒に指定してあるので、もし間隔を狭めたい場合はそちらも要調整。
SENSUはメトリック取得のコマンドを重複して実行しないようなので、起動させる間隔より気持ち少なめにする必要がある感じ。

```json:oracle.json
{
  "performance-metric": {
    "type": "metric",
    "command": "sqlplus -s ****/****@**** @oracle_performance.sql | findstr /r /e \"[0123456789]\"",
    "interval": 60,
    "handlers": ["graphite"],
    "subscribers": ["oracle"]
  }
}
 ```
 この例はWindows用なので、Linuxの場合は `findstr` を `grep` 等に変更してください。

```sql:oracle_performance.sql
SET SERVEROUTPUT ON
--
DECLARE
    TYPE NAME_ARRAY IS TABLE OF VARCHAR2(125);
    NA NAME_ARRAY;
    ITVL NUMBER := 58; -- INTERVAL
    HOSTNAME VARCHAR2(25) := UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'));
--
    FUNCTION UNIXTIME(D DATE)
        RETURN NUMBER
    AS
    BEGIN
        RETURN TRUNC((CAST(SYS_EXTRACT_UTC(D) AS DATE) - DATE '1970-01-01') * 24 * 60 * 60);
    END;
--
    FUNCTION GETPUTDATA(N VARCHAR, V NUMBER)
        RETURN VARCHAR
    AS
    BEGIN
        RETURN HOSTNAME || '.ORACLE.' || REGEXP_REPLACE(REGEXP_REPLACE(N, '\s', '_'), '[\(\)]', '') || ' '  || V || ' ' || UNIXTIME(SYSDATE);
    END;
--
    PROCEDURE EXEC(NA NAME_ARRAY)
    IS
        TYPE CURTYPE IS REF CURSOR;
        CUR CURTYPE;
        TYPE ARRAYOFNUMBERS IS TABLE OF NUMBER(20);
        TYPE ARRAYOFCHARS IS TABLE OF VARCHAR2(155);
        S ARRAYOFCHARS;
        V1 ARRAYOFNUMBERS;
        V2 ARRAYOFNUMBERS;
        IN_STR VARCHAR2(1000);
    BEGIN
        FOR I IN 1..NA.COUNT LOOP
            IF I != 1 THEN
                IN_STR := IN_STR || ',';
            END IF; 
            IN_STR := IN_STR || '''' || NA(I) || '''';
        END LOOP; 
        --
        OPEN CUR FOR 'SELECT VALUE, NAME FROM V$SYSSTAT VS WHERE VS.NAME IN (' || IN_STR || ') ORDER BY NAME';
        FETCH CUR BULK COLLECT INTO V1, S;
        CLOSE CUR;
        DBMS_LOCK.SLEEP(ITVL);
        OPEN CUR FOR 'SELECT VALUE FROM V$SYSSTAT VS WHERE VS.NAME IN (' || IN_STR || ') ORDER BY NAME';
        FETCH CUR BULK COLLECT INTO V2;
        CLOSE CUR;
        --
        FOR I IN 1..S.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(GETPUTDATA(S(I), ROUND((V2(I) - V1(I)) / ITVL, 2)));
        END LOOP; 
    END EXEC;
    --
BEGIN
    NA := NAME_ARRAY(
        -- Oracleインスタンスによって使用されたCPU時間(10msec)
        -- Oracleインスタンスがどの程度CPUを使っているかを判断するために確認
        -- サーバーのCPUコア数よりも少なければCPU負荷として問題なし
        'CPU used by this session',
        -- 読み取り一貫性ブロックが要求された回数
        -- ブロックの要求回数を表した統計地で読み込んだデータのサイズではない
        'consistent gets',
        -- UPDATE分の発行時などに読み取られるCURRENTブロックの読み取り要求数
        'db block gets',
        -- consistent getsとdb block getsの合計値
        -- PGAに読み込まれたすべての論理読み込み要求回数
        -- 論理読み込み回数を確認したい時に使用
        'session logical reads',
        -- SQLが実行された回数
        -- パースなどで内部的に自動実行されてる再帰SQLの回数もカウント
        'execute count',
        -- 現在ログインしているすべてのセッション数
        'logons current',
        -- ハードパースが実行された回数
        'parse count (hard)',
        -- ソフトパースも含めたパースの実行回数
        'parse count (total)',
        -- パースに要した合計時間
        'parse time elapsed',
        -- パースの際に使用したCPU時間の合計
        'parse time cpu',
        -- アプリケーションやユーザーが発行したSQLによる物理読み込み量の合計
        'physical read bytes',
        -- バックアップやリカバリなども含んだ物理読み込み量の合計
        'physical read total bytes',
        -- 物理読み込み要求数
        -- session logical readsと合わせると物理読み込み要求数と論理読み込み要求数を比較できる
        'physical read IO requests',
        -- アプリケーションやユーザーが発行したSQLによる書き込み量の合計
        'physical write bytes',
        -- バックアップやリカバリなども含んだ書き込み量の合計
        'physical write total bytes',
        -- 書き込みの要求回数
        'physical write IO requests',
        -- REDO情報の合計生成量
        -- 更新量を確認できる
        'redo size',
        -- テーブルフルアクセスを伴う大規模表のフルスキャン回数
        'table scans (long tables)',
        -- バッファキャッシュのキャッシュアップを伴う小さい表のフルスキャン回数
        'table scans (short tables)',
        -- ダイレクトパスReadによる読み込み回数
        'table scans (direct read)',
        -- UNDO情報の合計生成量
        'undo change vector size',
        -- コミットの実行回数
        'user commits',
        -- ロールバックの実行回数
        'user rollbacks'
    );
    EXEC(NA);
END;
/
EXIT
```

全部小文字にしてv$sysstatのクラス名を追加したほうがいい感じもするので、Graphiteのmetric_pathについては調整が必要かも。