More than 5 years have passed since last update.

PythonでOracle StatspackのTOP5待機イベントを見える化する

Last updated at 2019-05-14Posted at 2018-12-13

はじめに　

本投稿は Oracle Database or GoldenGate Advent Calendar 2018　の Day 13 の記事になります。　
※昨日は、kurouuuron さんのインスタンス化SCNを使ってお手軽初期移行　の投稿でした。

本投稿では、PythonでStatspackのTOP5待機イベントを見える化について書きます。

Statspackレポート　待機イベントとは

StatspackはOracleの性能分析をするためのツールで、
性能分析を実施するにはtxt形式のレポートを出力します。

Statspackは、DBのエディションが「Standard Edition」または、「Enterprise Edition」で [Diagnostics Pack]ライセンスを購入されていない場合にStatspackを設定するDBが多いと思います。

※Statspackの説明は以下のリンクを参照ください
http://www.oracle.com/technetwork/jp/articles/index-349908-ja.html

Statspackレポートを出力してわかることはいろいろあります
・DBでどんな待機イベントが発生しているか
・DBでどんな処理を実行しているか(時間モデル統計)
・ワークロード(redo生成量、秒間SQL実行回数、秒間トランザクション数)
・高負荷SQLの情報
など

DB性能分析、性能劣化の調査を行う場合、
STATSPACKを出力して一番に確認するのは、「待機イベント」を確認して、DBの状態を確認します。

■Statspackのイメージ

今回やりたいこと

各期間のスナップショットの待機イベントを見える化します。
　
通常は各Statspackの待機イベントの各項目を抽出して、Excelでグラフで見える化します。
■通常

■今回のPythonスクリプトでやりたいこと
Statspackレポートを抽出することなく、スナップショット指定期間の「待機時間が長い上位５位の待機イベント」の傾向を見えるします。

top5events.pyを実行すると、スナップショット期間の「待機時間が長い上位５位の待機イベント」をグラフ化した画面を表示します。

事前準備

※今回の投稿記事の実行環境は、Windows環境になります。

以下の準備が必要です。
1.Pythonインストール
2.PythonのOracleDB接続設定

1.Pythonインストール

　Anacondaこちらからインストール
　　 ( https://www.anaconda.com/download/ )

　⇒Pythonの最新バージョンは、3ですので、3.xをインストール

2.PythonのOracleDB接続設定

　cx_Oracleのインストール、Oracle Clientインストール、Windows環境変数を設定します

　詳細手順は以下の投稿で纏めています。
　　Windows環境のPythonからOracleに接続してみる

Pythonスクリプト (top5events.py)

top5events.pyで実行しているSQLは、
STATSPACKレポート作成でコールしている(spreport.sql からコールされる sprepins.sql)で実行しているSQL文を流用しております。

※Pythonスクリプトは折りたたみしてます

Pythonスクリプト(top5events.py)はこちら

top5events.py


import sys
import cx_Oracle
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import datetime
import locale
import matplotlib.gridspec as gridspec

#DB接続処理
def Oracle_Connect(Svr,Port,SVS,USR,PWD):

    try:

        tns = cx_Oracle.makedsn(Svr, Port, SVS)
        conn = cx_Oracle.connect(USR, PWD, tns)

        return conn

    except Exception as e:
        print(e)


#sysstat統計取得(引数にイベント名)
def getSYSSTAT(conn,dbid,inst_id,snap_f,snap_e,ename):

    cur = conn.cursor()

    tcpu_f = 0
    tcpu_e = 0

    #ループ1回目は 開始SNAP 2回目は終了SNAP
    for i in range(1,3,1):

        if i == 1:
            snap = snap_f
        else:
            snap = snap_e

        sql=" select value  \
                  from stats$sysstat \
              where snap_id         = " + str(snap) + " \
                 and dbid            = " + str(dbid) + " \
                 and instance_number = " + str(inst_id) + " \
                 and name            = '" + str(ename) + "' "

        cur.execute(sql)
        row = cur.fetchall()

        for r in row:
            if i == 1:
                tcpu_f = int(r[0])     
            else:
                tcpu_e = int(r[0])     

    return (tcpu_e - tcpu_f)

#待機イベント情報取得
def getTop5SQL(conn,dbid,inst_id,snap_f,snap_e,tcpu,snaptime):

    df = pd.read_sql_query(
         " select '" + snaptime + "' as sdate ,event,time \
           from (select event, time  \
           from (select e.event                              event  \
                     , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000  time  \
                 from stats$system_event b  \
                    , stats$system_event e  \
                where b.snap_id(+)          = " + str(snap_f) + " \
                  and e.snap_id             = " + str(snap_e) + " \
                  and b.dbid(+)             = " + str(dbid)   + " \
                  and e.dbid                = " + str(dbid)    + " \
                  and b.instance_number(+)  = " + str(inst_id) + " \
                  and e.instance_number     = " + str(inst_id) + " \
                  and b.event(+)            = e.event  \
                  and e.total_waits         > nvl(b.total_waits,0) \
                  and e.event not in (select event from stats$idle_event) \
               union all \
               select 'CPU time'                              event \
                    , " + str(tcpu) + "/100                   time  \
                 from dual  \
                where " + str(tcpu) + " > 0  \
               )  \
         order by time desc  \
       )  \
     where rownum <= 5 ",conn)

    return df

#待機イベント取得処理
def getTop5data(conn,dbid,inst_id,snap_f,snap_e,snaptime):

    #CPU TIMEの取得
    tcpu = getSYSSTAT(conn,dbid,inst_id,snap_f,snap_e,'CPU used by this session')

    #待機イベントの取得
    df = getTop5SQL(conn,dbid,inst_id,snap_f,snap_e,tcpu,snaptime)

    return df

def top5event(conn,Snap_from,Snam_End):

    try:

        #stats$snapshotから Snap_from - Snam_End のsnap_id を取得
        cur = conn.cursor()

        #DBID取得
        sql="SELECT DBID FROM V$DATABASE "
        cur.execute(sql)
        row = cur.fetchall()

        for r in row:
            dbid =(r[0])    

        #Instance Number取得
        sql="SELECT INSTANCE_NUMBER FROM V$INSTANCE "
        cur.execute(sql)
        row = cur.fetchall()

        for r in row:
            inst_id =(r[0])

        #指定された時間で再起動があるかを確認。再起動をはさむ場合エラー
        sql="SELECT DISTINCT TO_CHAR(STARTUP_TIME,'yyyy/mm/dd hh24miss') STARTUPTIME FROM STATS$SNAPSHOT WHERE SNAP_TIME BETWEEN TO_DATE('"  + str(Snap_from) + "','YYYY/MM/DD HH24MISS') \
             AND TO_DATE('" + str(Snam_End)  + "','YYYY/MM/DD HH24MISS') AND INSTANCE_NUMBER = " + str(inst_id) + "  "

        cur.execute(sql)
        strow = cur.fetchall()

        #DB再起動をはさんでいる場合、エラーとして処理終了
        if len(strow) > 1:

            print('Include Some StartUp TIME (DB reboot) ')

            for r in strow:
                startuptime =(r[0])
                print('startuptime : ' + startuptime)

            print('Confirm SNAPTIME. ')

            return

        #SNAP_ID取得
        sql="SELECT SNAP_ID,TO_CHAR(SNAP_TIME,'yyyy/mm/dd hh24:mi:ss') SNAPTIME FROM STATS$SNAPSHOT WHERE SNAP_TIME BETWEEN TO_DATE('"  + str(Snap_from) + "','YYYY/MM/DD HH24MISS') \
             AND TO_DATE('" + str(Snam_End)  + "','YYYY/MM/DD HH24MISS') AND INSTANCE_NUMBER = " + str(inst_id) + " ORDER BY SNAP_ID"

        cur.execute(sql)

        rows = cur.fetchall()

        #SNAPが2つ以上ない場合はレポート作成できないのでexit
        if len(rows) < 2:
            print('NO STATSPACK DATA CHECK BEGIN/END SNAPSHOT')
            return

        #SNAP_IDを配列に格納
        snap_f=0
        snap_e=0
        snaptime =""

        #待機イベントdataframe空枠作成
        df=pd.DataFrame(columns=['SDATE','EVENT','TIME'])

        for r in rows:

            if snap_f == 0:
                snap_f = r[0]
                snaptime= r[1]
            else:
                snap_e = r[0]

            if snap_e > 0:

                print("SNAP_TIME : " + str(snaptime) )

                #待機イベントデータ取得
                dftop5ret = getTop5data(conn,dbid,inst_id,snap_f,snap_e,snaptime)

                #dfに追加
                df = df.append(dftop5ret)

                #SNAP_IDの移動
                snap_f = snap_e
                snaptime= r[1]
                snap_e = 0

        #DB切断
        conn.close()        

        #日付形式に変換
        #グラフ表示時に日付形式にしないとうまく表示されない
        df['SDATE'] = pd.to_datetime(df['SDATE'])

        #ピボットグラフ作成
        pv=df.pivot_table( values ='TIME',index = ['EVENT'], columns = ['SDATE'],aggfunc = 'sum', fill_value = 0 ,margins=True,margins_name='TOTAL')
        pv.sort_values(by='TOTAL',ascending=False,inplace=True)

        #行列入れ替え
        dftop5=pv[:6].T

        #[TOTAL列を削除]
        drop_idx = ['TOTAL']
        dftop5.drop(drop_idx, inplace=True)
        drop_col = ['TOTAL']
        dftop5.drop(drop_col, axis=1, inplace=True)

        #描画設定
        fig = plt.figure(figsize=(12, 6))
        plt.subplots_adjust(left = 0.1, right = 0.7)

        plt.style.use('ggplot')
        ax1 = fig.add_subplot(1,1,1)
        dftop5.plot(ax =ax1 ,kind='area')
        plt.title("TOP5 Wait Events", fontsize=14)
        plt.xlabel("", fontsize=12)
        plt.ylabel("Wait Time(second)", fontsize=12)
        plt.legend(bbox_to_anchor=(1.02, 1), loc='upper left', borderaxespad=0, fontsize=12)
        plt.tick_params(labelsize=12)

        ax1.xaxis.set_major_formatter(mdates.DateFormatter('%m/%d %H:%M'))

        plt.show()
        plt.close()

        return 

    except Exception as e:
        print('err')
        print(e)

#入力チェック
def inputcheck(Svr,Port,Svs,USR,PWD,Snap_from,Snam_End):

    try:
        #サーバ名チェック
        if len(Svr) == 0 :
            print('NO SERVER NAME or IP ADDRESS')

            return 1

        #ポート番号チェック
        if Port.isdecimal() == False:
            print('PORT IS NOT NUMERIC')

            return 1

        #サービス名チェック
        if len(Svs) == 0 :
            print('NO SID ')

            return 1

        #ユーザ名チェック
        if len(USR) == 0 :
            print('NO USERNAME ')

            return 1

        #パスワードチェック
        if len(PWD) == 0 :
            print('NO USER PASSWORD ')

            return 1

        #スナップ開始日付チェック
        try:
            newDate=datetime.datetime.strptime(Snap_from,"%Y/%m/%d %H%M%S")
        except:
            print('BEGIN SNAPTIME IS INCORRECT')
            return 1

        #スナップ終了日付チェック        
        try:
            newDate=datetime.datetime.strptime(Snam_End,"%Y/%m/%d %H%M%S")
        except:
            print('END SNAPTIME IS INCORRECT')
            return 1

    except Exception as e:
        print('err')
        print(e)


if __name__ == '__main__':


    #接続文字列取得
    print ('====== ServerName or IP Address =====')
    Svr = input('>  ')

    print ('')
    print ('===== PORT Number =====')
    inPort = input('>  ')

    print ('')
    print ('===== SID =====')
    Svs = input('>  ')

    print ('')
    print ('===== DBUSER =====')
    USR = input('>  ')

    print ('')
    print ('===== DBUSER PASSWORD =====')
    PWD = input('>  ')

    print ('')
    print ('===== BEGIN SNAPTIME =====')
    print ('===== yyyy/mm/dd hh24miss  ex) 2018/12/01 000000 =====')

    Snap_from = input('>  ')

    print ('')
    print ('===== END SNAPTIME =====')
    print ('===== yyyy/mm/dd hh24miss  ex) 2018/12/01 120000 =====')

    Snam_End  = input('>  ')

    print ('=====')
    print ('=====')
    print ('=====')

    #入力チェック
    if inputcheck(Svr,inPort,Svs,USR,PWD,Snap_from,Snam_End) == 1:

        print('YOU ENTERED AN INCORRECT. EXIT PROGRAM')
        sys.exit()

    else:
        Port = int(inPort)

    print ('===== STSRT STATSPACK MAKE GRAPH =====')


    try:
        #DB接続
        conn = Oracle_Connect(Svr,Port,Svs,USR,PWD)

        if isinstance(conn, type(None)) == False :

            #待機イベントグラフ処理
            top5event(conn,Snap_from,Snam_End)

        else:
            print('CONNECT ERROR CHECK INPUT')

    except Exception as e:
        print(e)
    finally:
        print ('===== END STATSPACK MAKE GRAPH =====')
        sys.exit()

SQLでもピボットができますが、今回はsprepins.sqlで実行しているSQLを流用してますので、
Python側のDataframeでピボットしております。

※Dataframeでピボットする考え方は、以下の投稿を参照ください
　　Python Pandas DataFrameでピボットグラフを作成して棒グラフ表示

　　Python DataFrame ピボットグラフの上位5位(Top 5)のみ抽出して棒グラフに表示

Pythonスクリプトの実行

Windows環境でPythonスクリプト(.py)の実行方法　でも記載してますが、
本投稿ではコマンドプロンプトからPythonスクリプト実行手順を書いています。

1.コマンドプロンプトを起動

コマンドプロンプト画面が起動

2.環境変数 PATHに Anaconda のインストールしたパスを設定

  set PATH=%PATH%;C:\Users\＜ユーザ名＞\Anaconda3

※Anacondaのインストールパスのデフォルトは
c:\User\ <ユーザ名>\Anaconda3 です

また、事前にコントロールパネルでPATHの設定をしておけば
毎回、環境変数 PATHを設定する必要はありません

3.Pythonスクリプトを実行

  python Pythonスクリプト(.py)を指定

Pythonスクリプトは、相対パス or 絶対パスのどちらかを指定

相対パスの場合、コマンドプロンプトのcd でpythonスクリプトの配置しているディレクトリに事前に移動しておく必要があります。

絶対パスの場合、パスから入力する必要があります。
※絶対パスは、以下に説明するpythonスクリプトをドラッグするのみですので簡単に実行できます

[Enter]キーを押下

4.DB接続情報とグラフ作成期間を入力

Pythonスクリプトを実行すると、以下の条件入力を行います。
※各値を入力したら[Enter]キーを押下します

実行時に入力する条件	説明
ServerName or IP Address	DBサーバ名　or ipアドレスを入力します
PORT Number	リスナーのポート番号を入力します
SID	SERVICE名でなく SID名を入力します
DBUSER	STATS$表にアクセスできるユーザ名を入力します
DBUSER PASSWORD	STATS$表にアクセスできるユーザ名のパスワードを入力します
BEGIN SNAPTIME	グラフ化する開始スナップショット時間を yyyy/mm/dd hh24miss 形式で入力します例)2018/12/10 010000
END SNAPTIME	グラフ化する終了スナップショット時間を yyyy/mm/dd hh24miss 形式で入力します例) 2018/12/10 120000 12時のスナップショットを含める場合、少し余裕をもたせた方がいいかもしれません。2018/12/10 120500　など