kenken0807_DBメモ

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

オンラインでサプリメンタルロギングを有効にしたメモ

ORACLE SE

とある理由でOracleサプリメンタルロギングを有効にすることがあり、
本番サーバのトラフィックがある中で実行してみたのでメモ。

実行するSQLは以下、

alter database add supplemental log data (primary key,unique) columns;

すでに最小サプリメンタルロギングはONになっている状態で、識別キーロギング（プライマリキーとユニークキー）を付与した。

SQL>select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

実行後はこうなる。

SQL>select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      YES YES

マニュアルを見てみると、

識別キー・ロギングが有効な場合にデータベースがオープンしていると、 カーソル・キャッシュ内のすべてのDMLカーソルが無効になります。 したがって、カーソル・キャッシュに再移入を行うまで、パフォーマンスに影響する場合があります。

というように影響がけっこうありそうな予感。

とりあえず実行してみてわかったこと。

1. 未コミットのトランザクションがある中で実行するとenq: TX – row lock contentionで待機する。

  • セッション１
SQL>insert into test values (1);
未コミット
  • セッション2
SQL>alter database add supplemental log data (primary key,unique) columns;
ここで待機が発生する

セッション１のコミット後はセッション２が終了される。

2、キャッシュされたカーソルが無効というよりフラッシュされた。

v$sgastatをみると共有プールの共有SQL領域が大きく縮小した。

  • サプリメンタルロギング有効化前と有効化後比較
パラメータ名 有効化前(MB) 有効化後(MB)
KGLH0 5459.67253 2374.2757
SQLA 5343.10341 53.1073456
  • SQL領域の動的パフォーマンスビューの件数比較（count(*)）
V$VIEW名 有効化前 有効化後
V$SQL 300976 1271
V$SQLAREA 146777 1188
V$SQLTEXT 502664 501235
V$SQLSTATS 165937 165074
V$SQL_PLAN 1305767 7423
V$SQL_SHARED_CURSOR 300588 1680

ほとんどビューはフレッシュされていたがV$SQLTEXTとV$SQLSTATSは変わらなかった。

まとめ

  • 実際にサプリメンタルロギング有効化のSQL自体は3秒ほどで終了した。
  • オンラインで実行する場合は長いトランザクション（バッチ処理など）がないときに実行する。
  • カーソルを無効化する時に共有プールラッチ競合が起こるかと思ったがほぼなかった模様。
  • サプリメンタルロギング有効後はSQLを再度キャッシュするので少しパフォーマンスが劣化する模様。
  • 思ったより影響なく終わるが、負荷が少ない時間に行ったほうが無難。