以前担当したOracle案件で、面白い依頼がありました。
曰く、「テーブルごとにDMLの実行回数を調査してほしい」とのこと。
依頼の経緯は後述しますが、とりあえず作ったのがこれです。
kaisu.sql
--統計情報が採取されたテーブルに対し、一日あたりのinsert,update,deleteの件数を表示する
select
AD.TABLE_NAME,
round(AD.INSERTSUM / to_number(sysdate - b.LAST_ANALYZED)) "1日のINSERT件数",
round(AD.UPDATESUM / to_number(sysdate - b.LAST_ANALYZED)) "1日のUPDATE件数",
round(AD.DELETESUM / to_number(sysdate - b.LAST_ANALYZED)) "1日のDELETE件数"
from
user_tables b inner join (
select
a.TABLE_NAME,
sum(a.INSERTS) INSERTSUM,
sum(a.UPDATES) UPDATESUM,
sum(a.DELETES) DELETESUM
from
user_tab_modifications a
where
a.TABLE_NAME not like 'BIN%'
group by a.TABLE_NAME
) AD
on b.TABLE_NAME = AD.TABLE_NAME
where
b.LAST_ANALYZED is not null
order by 1
;
書いていて何ですが、これ実は検証中のスクリプトなので結果の保証ができかねます。
たぶん正しいと思いますが、ご参考までに。
経緯について
- アーカイブログファイルの出力量が多すぎてディスクがパンクしそうだからなんとかしたい。
- でもこんなん業務上の都合じゃない（あくまでOracleの仕様の問題）だから、ディスクを買ってお客さんにお金を出させるわけにはいかない。
- じゃあログを出しているＳＱＬの量を調査して、削減するないし、テーブルの余剰カラムを削除する、あるいはSQLにログ削減ロジックを組み込もう
というわけです。大まかには。
ログが増えた一番の原因は結局なんだったのかというと、判別不明な点がまだあります。
ですが、余計なワークテーブル（ここでは、データを加工した結果を一時的に格納しておく変数みたいに使われるテーブルのこと）をアプリチームが勝手気ままに作ったりして無駄にログを増やしたのが大きいと言われていたので、なんとなく人災だったのだという気がしています。
Oracleの機能でアーカイブログ出力量を減らす方法はいくつかありますけど、やっぱり限界はあるわけですから少しは頭使って欲しいなあ、とその当時はアプリチームに文句たらたらでした。まあ、機能実装主眼なんですからしょうがないのはわかりきっていましたけど。
ちなみにOracleの設定でログを減らすには、大体次の方法を取ります。
- SQLのログ出力量を減らすようOracleのパラメータを変更
- DDL文やDML文でnologgingオプションを使う
- ノーアーカイブモードのOracleインスタンスを同じサーバに作ってワークテーブルを移する
最後はかなり荒業ですから薦めません。
パフォーマンスも落ちるし、ディザスタリカバリサイトを作成している場合はData Guardがうまく働きませんのでアウトです。