読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

ORACLEでテーブルごとのDML回数を取得する

テーブルごとの1時間あたりのDML(INSERT,UPDATE,DELETE)の平均を取得するために考えた方法。
確認手段としてDBA_TAB_MODIFICATIONSを使用する。

ALL_TAB_MODIFICATIONS

以下のように確認できる。

SELECT
   table_name,inserts,updates,deletes,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') as timestamp
FROM
   dba_tab_modifications
WHERE
   TABLE_OWNER in ('USER') 
   and TABLE_NAME not like 'BIN$%';

TABLE_N    INSERTS    UPDATES    DELETES TIMESTAMP
------- ---------- ---------- ---------- -------------------
TABLE00        868       2446       1974 2016-04-26 18:00:01
TABLE01        445          0         10 2016-04-26 17:15:28
TABLE02          1          1          0 2016-02-22 18:06:50
TABLE03      16635          0      16635 2016-04-26 18:00:01
TABLE04          6          0          4 2016-04-26 15:54:26
TABLE05          0        124          0 2016-04-26 18:00:01
TABLE06        335       3071          0 2016-04-26 18:00:01
TABLE07         12          0          0 2016-04-06 17:29:01
TABLE08      58694          0      40023 2016-04-26 18:00:01
TABLE09     131440          0     102863 2016-04-26 18:00:01

マニュアルによると、

前回、表の統計情報を収集した時点から変更されている

とあるので、統計情報を取得したタイミングで更新される模様。

これを定期的に更新してあげるには↓を実行する。

begin DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; end;

そうすると実行したタイミングで更新がある場合はTIMESTAMPが書き換えられ、更新回数が累積される。
前回との差分を比較することでDML回数がわかる。
1時間置き実行して結果をfluentdで送ったり、グラフ化することができる。

うちでは
1. 1時間置きにFLUSH_DATABASE_MONITORING_INFOを実行
2. SELECT FROM dba_tab_modificationsを実行しMySQLに格納
3. Muninでグラフ化
みたいにしている。

直接グラフでもいいけど、MySQLに入れておけばSQLでいろいろ拡張やら調査が可能なので。

注意としては統計情報を取得したタイミングで更新情報がリセットされるのでその時は差分を取得するのではなくその値を初期値にする。 MySQLで取得しているデータはこんな感じ↓

カラム名 説明
TABLE_NAME テーブル名
INSERTS 前回との差分insert回数
SUM_INSERTS 累積insert回数
UPDATES 前回との差分update回数
SUM_UPDATES 累積update回数
DELETES 前回との差分delete回数
SUM_DELETES 累積delete回数
TIMESTAMP Oracle側の更新時間
+-----------+---------+-------------+---------+-------------+---------+-------------+---------------------+
| TABLE_NAME| INSERTS | SUM_INSERTS | UPDATES | SUM_UPDATES | DELETES | SUM_DELETES | TIMESTAMP           |
+-----------+---------+-------------+---------+-------------+---------+-------------+---------------------+
| TABLE00   |      14 |       88710 |      64 |      118695 |       0 |           0 | 2016-03-31 13:53:11 |↑
| TABLE00   |       5 |       88715 |      24 |      118719 |       0 |           0 | 2016-03-31 15:00:02 |
| TABLE00   |      17 |       88732 |      57 |      118776 |       0 |           0 | 2016-03-31 15:57:26 |
| TABLE00   |      18 |       88750 |     228 |      119004 |       0 |           0 | 2016-03-31 17:00:02 |
| TABLE00   |     726 |       89476 |     325 |      119329 |       0 |           0 | 2016-03-31 18:00:01 |累積値
| TABLE00   |     483 |       89959 |     299 |      119628 |       0 |           0 | 2016-03-31 19:00:01 |
| TABLE00   |      49 |       90008 |      11 |      119639 |       0 |           0 | 2016-03-31 20:00:01 |
| TABLE00   |       8 |       90016 |      31 |      119670 |       0 |           0 | 2016-03-31 20:58:26 |
| TABLE00   |      16 |       90032 |      22 |      119692 |       0 |           0 | 2016-03-31 22:00:01 |↓
| TABLE00   |      19 |          19 |      13 |          13 |       0 |           0 | 2016-03-31 23:00:01 |←統計情報が取得されたのでリセットされた
| TABLE00   |      28 |          47 |      17 |          30 |       0 |           0 | 2016-03-31 23:59:41 |↑
| TABLE00   |       8 |          55 |      10 |          40 |       0 |           0 | 2016-04-01 01:00:01 |
| TABLE00   |       7 |          62 |       5 |          45 |       0 |           0 | 2016-04-01 01:51:06 |
| TABLE00   |       4 |          66 |       2 |          47 |       0 |           0 | 2016-04-01 02:42:32 |
| TABLE00   |       2 |          68 |       2 |          49 |       0 |           0 | 2016-04-01 03:46:31 |
| TABLE00   |       1 |          69 |       1 |          50 |       0 |           0 | 2016-04-01 04:25:24 |累積値
| TABLE00   |       4 |          73 |       4 |          54 |       0 |           0 | 2016-04-01 06:00:01 |
| TABLE00   |       4 |          77 |       2 |          56 |       0 |           0 | 2016-04-01 06:36:07 |
| TABLE00   |       8 |          85 |       2 |          58 |       0 |           0 | 2016-04-01 07:56:18 |
| TABLE00   |       3 |          88 |       1 |          59 |       0 |           0 | 2016-04-01 08:46:48 |↓