本番リリースしたばかりの環境で、CPU使用率が上がり続けていると連絡がありました。
1日単位で見ると昼がCPU使用率のピークで朝と夜には下がるのですが、そのピークが日々高くなり、なおかつ谷の部分も前日に比べると高くなる状態がずっと続いているとの事です。
その時点ではCPU使用率は50%に満たなかったのですが、そのまま増え続けたら問題になりそうなので、調査する事になりました。
環境は以下の通りです。
まず、AWRレポートからDB CpuTimeを集計してみました。
「Load Profile」セクションの「DB CPU(s)」を使用します。
1日単位の山と谷はありますが、日ごとに増えているようには見えませんでした。
次に、CPU使用時間に影響を及ぼす論理読み込み数と、OS負荷の指標となるLoad Averageも確認します。
論理読み込み数は「Load Profile」セクションの「Logical read (blocks)」、Load Averageは「Host CPU」セクションの「Load Average Begin」を使用します。
グラフ化した所、論理読み込み数とLoad Averageに相関関係が見られ、なおかつ山と谷を繰り返しながら日々増えていく状態である事が確認できました。
ここまでで論理読み込みが多いSQLが原因であるとあたりがついたので、「SQL ordered by CPU Time」「SQL ordered by Gets」セクションを確認します。
その結果、CPU時間・Gets共に上位のSQLについて、Getsが増加し、処理時間が長くなっている事が判りました。
更に「Top SQL with Top Events」セクションを見ると、先ほどCPU時間と論理読み込みで上位に挙がっていたSQLが、「CPU + Wait for CPU」で待機し、「TABLE ACCESS - BY INDEX ROWID」である事もわかりました。
つまり、インデックスは使われているものの実行計画が不適切で、その結果論理読み込みが増大し、CPU使用率増加につながったと考えられます。
追加情報として、それらSQLがselectしているテーブルは、統計ロックがかかっていてリリース以降、一度も統計情報が取得されていなかった事も判明しました。
統計ロックの確認方法は以下の通りです。
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED from dba_tab_statistics where OWNER='TEST' and TABLE_NAME='T1';
OWNER TABLE_NAME STATT
-------------------- -------------------- -----
TEST T1 ALL
解除方法は以下になります。
SQL> exec dbms_stats.unlock_table_stats('TEST', 'T1');
PL/SQLプロシージャが正常に完了しました。
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED from dba_tab_statistics where OWNER='TEST' and TABLE_NAME='T1';
OWNER TABLE_NAME STATT
-------------------- -------------------- -----
TEST T1
どうして統計ロックがかかってしまったかですが、0件データをインポートするとロックがかかる仕様になっています。
impdpであればcontent=metadata_only、従来型インポートであればrows=nでインポートした場合ですね。
問題のテーブルもcontent=metadata_onlyの実行後、データを移行したので統計ロックがかかってしまったようです。
