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

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


![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/6020d496-d35e-4392-522b-4c6b21c83034.png)



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

>・[Python OracleアラートログのORAエラー傾向分析をしてみる](https://qiita.com/kngsym2018/items/eeaf8ce69b3552bb3a11)

#ORAエラー分析 Pythonスクリプト
月別、日別、時間別分析する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スクリプトを実行。※実行方法は[こちら](https://qiita.com/kngsym2018/items/21b6efe4547a78661951)参考


##月別でグラフ化
  
**pythonスクリプト(alertcsv_to_graph.py)を実行**
  
**１．DBアラートログcsvのファイルを指定**
　　　(絶対パス or 相対パス)
  
**２．集計単位を入力**
　　　M
　　と入力

**３．月別ORAエラーグラフが表示される**
　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/dc40dba5-6567-9bc6-d5a4-6c96acdb98b2.png)


##日別でグラフ化
**pythonスクリプト(alertcsv_to_graph.py)を実行**

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

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

**３．集計対象を年度を入力**
　　　　YYYY形式で入力
　　　例) 2018 、2017など
   
**４．集計対象を月度を入力**
　　　　MM形式で入力
　　　例) 01、02、03、09、12 
   　　※２桁で入力します

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

　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/ed32e1e5-63ca-2c07-3abb-8603efdab29b.png)

##時間別でグラフ化

**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エラーグラフが表示される**
　
　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/98871c91-9703-f234-be2c-7fd4e3ba90ad.png)


#時間別 ORAエラー分析

**◆分析サマリ**
　
**１．月別 ORAエラー分析**
　　２月、５月にORAエラー発生が多い　　
　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/a09c3212-4672-c585-cb8f-070e81db6dfb.png)


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

　**２月**　　
　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/27923010-acbd-bc07-9806-555187406813.png)


　　２月２５日
　　　ORA-01653、ORA-01654　が多く発生
　　２月２８日
　　　ORA-01555が多く発生
 
　**５月**　
　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/29c17491-a14b-9bb3-23b9-626d714b8c01.png)

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

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


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

**３．時間別 ORAエラー分析**

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/8ac81380-8f89-0538-f00f-6d0af9385b53.png)


　**５月２５日と５月３１日(月末)**　
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/5f066e8d-5ce4-780c-d85d-c9ac97176481.png)







**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チューニングを実施する対応になると思います


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

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