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

kenken0807_DBメモ

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

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

とある理由で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で待機する。

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

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

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$SQLTEXTV$SQLSTATSは変わらなかった。

まとめ

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