前回の振り返りと今回の投稿内容

前の投稿では、アラートログをcsvにして月別にORAエラー件数をグラフ化しました。
今回はその続きで日別、時間別にグラフ化します。

※ORAエラーで加工したcsvファイルは、前投稿のalert_orcl.log.csvを使用しています。
本投稿で使用する、「alert_orcl.log.csv」のサンプルは前投稿を参照ください

・Python OracleアラートログのORAエラー傾向分析をしてみる

ORAエラー分析 Pythonスクリプト

月別、日別、時間別分析するPythonスクリプトを作成した。

alertcsv_to_graph.py

import pandas as pd
import datetime as dt
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import datetime
import numpy as np
from matplotlib.ticker import *
import calendar
import sys

def alertcsv(csvfile,kbn,yyyy,mm,dd):

    csvpath = r"'"+ csvfile + "'"
    csvpath = csvpath.replace("'","")

    #集計単位によって条件を設定
    if kbn == "D":
        #月末の日を算出
        y,endmonth = calendar.monthrange(int(yyyy),int(mm))
        dd='01'
        START_TIME= yyyy + '-' + mm + '-' + dd  + ' 00:00:00'
        END_TIME  = yyyy + '-' + mm + '-' + str(endmonth)

    elif kbn =="H":
        START_TIME= yyyy + '-' + mm + '-' + dd  + ' 00:00:00'
        END_TIME  = yyyy + '-' + mm + '-' + dd  + ' 23:59:59'

    #１．Oracle DBアラートCSVファイルを読込む
    dfcsv = pd.read_csv(csvpath)

    #２．該当データ抽出
    if kbn != "M":
        dfcsv = dfcsv[(dfcsv['STIME'] >= START_TIME) & (dfcsv['STIME'] < END_TIME )]

    #３．日付補完データの作成
    if kbn =="M":
        df = dfcsv
    if kbn == "D":
        date_df = pd.DataFrame(pd.date_range(START_TIME , END_TIME , freq='D'), columns=['STIME'])
        df = pd.concat([dfcsv, date_df])
    elif kbn == "H":
        date_df = pd.DataFrame(pd.date_range(START_TIME , periods=24, freq='H'), columns=['STIME'])
        df = pd.concat([dfcsv, date_df])

    #４．インデックス列をSTIMEに設定
    df.set_index('STIME',inplace=True)

    #５．日付データ(文字型)を yyyy-mm 形式にして格納
    if kbn == "M":
        df.index = df.index.map(lambda x: datetime.datetime.strptime(str(x)[0:7],"%Y-%m"))
    elif kbn == "D":
        df.index = df.index.map(lambda x: datetime.datetime.strptime(str(x)[0:10],"%Y-%m-%d"))
    elif kbn == "H":
        df.index = df.index.map(lambda x: datetime.datetime.strptime(str(x)[0:13],"%Y-%m-%d %H"))

    #６．get_dummies を使用して ダミー変数を作成
    dfdummy = pd.get_dummies(df["ORA-ERROR"])

    #７．yyyy-mm形式にした集計キーで、Oraエラーの数を数える
    dfplot=dfdummy.groupby(['STIME']).sum()

    #８．棒グラフと日付は相性が悪いので、数字に変換
    if kbn == "M":
        dfplot.index = dfplot.index.map(lambda x: x.month)
    elif kbn == "D":
        dfplot.index = dfplot.index.map(lambda x: x.day)
    elif kbn == "H":    
        dfplot.index = dfplot.index.map(lambda x: x.hour)

    #９．表示設定
    fig = plt.figure(figsize=(8, 6))
    plt.style.use('ggplot')
    ax1 = fig.add_subplot(1,1,1)

    #棒グラフで表示
    dfplot.plot(kind='bar',ax =ax1)

    #書式設定
    if kbn == "M":
        plt.title("ORA ERROR COUNT (month)", fontsize=12) 
        plt.xlabel("month", fontsize=12)

    elif kbn == "D":    
        plt.title("ORA ERROR COUNT MONTH (" + yyyy + '-' + mm + ")", fontsize=12)  
        plt.xlabel("day", fontsize=12)

    elif kbn == "H":
        plt.title("ORA ERROR COUNT DAY (" + yyyy + '-' + mm + '-' + dd +")", fontsize=12) 
        plt.xlabel("hour", fontsize=12)

    plt.ylabel("count", fontsize=12) 
    plt.tick_params(labelsize=12)

    plt.show()


if __name__ == '__main__':

    yyyy = ""
    mm = ""
    dd = ""

    #csvファイル名を入力 (絶対パス or 相対パス)
    print ('====== Enter csv file  =====')
    print ('======')

    csvfile = input('>  ')

    #集計区分を入力
    print ('====== MONTH or DAY or Hour =====')
    print (' ex) "M" or "D" or "H"')
    kbn = input('>  ')

    #月単位集計
    if kbn == "M":
        pass

    #日付単位集計の情報入力
    elif kbn == "D":
        print ('====== INPUT YEAR =====')
        print (' ex) "2018" ')
        yyyy = input('>  ')

        print ('====== INPUT MONTH =====')
        print (' ex) "01" or "05" or "10" ')
        mm = input('>  ')

    #時間単位集計の情報入力
    elif kbn == "H":    

        print ('====== INPUT YEAR =====')
        print (' ex) "2018" ')
        yyyy = input('>  ')

        print ('====== INPUT MONTH =====')
        print (' ex) "01" 、 "05" 、 "10" 、"12"')
        mm = input('>  ')

        print ('====== INPUT DAY =====')
        print (' ex) "01" 、 "05" 、 "10"、"31" ')
        dd = input('>  ')

    else:
        print ('Incorrect input')
        sys.exit()

    alertcsv(csvfile,kbn,yyyy,mm,dd)

実行時の引数によって、月別、日別、時間別にグラフ化します。

pythonスクリプトを実行。※実行方法はこちら参考

月別でグラフ化

pythonスクリプト(alertcsv_to_graph.py)を実行

１．DBアラートログcsvのファイルを指定
　　　(絶対パス or 相対パス)

２．集計単位を入力
　　　M
　　と入力

３．月別ORAエラーグラフが表示される
　

日別でグラフ化

pythonスクリプト(alertcsv_to_graph.py)を実行

１．DBアラートログcsvのファイルを指定
　　(絶対パス or 相対パス)

２．集計単位を入力
　　　D
　　と入力

３．集計対象を年度を入力
　　　　YYYY形式で入力
　　　例) 2018 、2017など

４．集計対象を月度を入力
　　　　MM形式で入力
　　　例) 01、02、03、09、12
　　※２桁で入力します

５．日別ORAエラーグラフが表示される

時間別でグラフ化

pythonスクリプト(alertcsv_to_graph.py)を実行

１．DBアラートログcsvのファイルを指定
　　(絶対パス or 相対パス)

２．集計単位を入力
　　　H
　　と入力

３．集計対象を年度を入力
　　　　YYYY形式で入力
　　　例) 2018 、2017など

４．集計対象を月度を入力
　　　　MM形式で入力
　　　例) 01、02、03、09、12
　　　※２桁で入力します

５．集計対象を日付を入力
　　　　DD形式で入力
　　　例) 01、02、03、09、12
　　※２桁で入力します

６．時間別ORAエラーグラフが表示される
　
　

時間別 ORAエラー分析

◆分析サマリ
　
１．月別 ORAエラー分析
　　２月、５月にORAエラー発生が多い　　
　　

２．日別 ORAエラー分析
　　ORAエラーの多い２月、５月にフォーカス　　

　２月　　
　　

　　２月２５日
　　　ORA-01653、ORA-01654　が多く発生
　　２月２８日
　　　ORA-01555が多く発生

　５月　
　　

　　５月２５日
　　　ORA-01653、ORA-01654　が多く発生
　　５月３１日
　　　ORA-01555が多く発生

２月と５月のORAエラー傾向は、２５日に　ORA-01653、ORA-01654　が発生して、
月末に ORA-01555が発生する傾向になっています。

ORAエラー内容
ORA-01653: 表○○を拡張できません ⇒表を格納している表領域が不足
ORA-01654: 索引△△を拡張できません ⇒索引を格納している表領域が不足
ORA-01555：スナップショットが古すぎます⇒UNDO表領域が不足

３．時間別 ORAエラー分析

　２月２５日と２月２８日(月末)　

　５月２５日と５月３１日(月末)　

ORA-01653、ORA-01654
このシステムは４半期に集計処理が実行され、
２月、５月、８月、１１月の「２５日の夜間処理」で４半期締め処理が行われます。
※その処理では、大量データの追加・更新が行われるため、領域を必要とされます。

ORA-01555
月末に締め処理したデータを確認されるSQLを実行されており
こちらでORA-1555が発生しています。

こちらはSQLで大量データアクセスして時間が掛かっており、
データ読込みする前にUNDOから消えてしまっているためのORAエラーとなります。

ORAエラー分析からの対策

各ORAエラーに対策を事前に実施することで、
次回８月に実施される、４半期毎の処理でORAエラーの発生を防ぐ事ができます。

１．ORA-01653、ORA-01654対応

　　締め処理前に、テーブル領域、インデックス領域を拡張しておきます。
　　２月、５月のアラートログで、TS表領域(テーブル領域)、TSI表領域(インデックス領域)で、
　　エラーが発生しているので、締め処理前に領域の空き状況を確認して、
　　空き容量が少ないのであれば表領域にデータファイルを追加しておきます。

２．ORA-01555対応

　　ORA-01555は、UNDO領域が不足傾向にある場合にでるエラーで、
　　UNDO領域を増やす(UNDOファイルのサイズ拡張、UNDO_RETENTIONを増やす)対応が
　　考えられますが、アラートログを見るとsql_id:abc02h7f6vppt の実行時間(Query Duration)は
　　1時間(3600sec)以上かかっています。
　　

alert_orcl.log.csv抜粋

2018-02-28 12:33:10,ORA-01555,"abc02h7f6vppt, Query Duration=3600 sec, SCN: 0x08bf.b8bcc823):"
2018-02-28 13:11:10,ORA-01555,"abc02h7f6vppt, Query Duration=4101 sec, SCN: 0x08bf.dra3f8dd):"
2018-02-28 13:22:10,ORA-01555,"abc02h7f6vppt, Query Duration=3703 sec, SCN: 0x08bf.9ui3b834):"
2018-02-28 13:34:10,ORA-01555,"abc02h7f6vppt, Query Duration=4767 sec, SCN: 0x08bf.84a3b823):"

　　SQL実行計画が適切でないため、不要に大量データにアクセスしており、
　　その結果、SQL実行時間が掛かっている可能性があるので、
　　アクセスパスが適切かを確認してSQLチューニングを実施する対応になると思います

上記ORA-01653、ORA-01654、ORA-01555の対応を事前に実施することで
次回８月に実施される、４半期毎の処理でORAエラーの発生を防ぐ事ができます。

※今回のアラートログは投稿用に編集してますのでご了承ください。