「.NETアプリケーションとDBのチューニング手法を一晩でマスターしよう!」に行ってきた

オラクル青山センターに「.NETアプリケーションとDBのチューニング手法を一晩でマスターしよう!」(http://eventreg.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=114368&src=7013395&src=7013395&Act=218) に行ってきた。

アジェンダ

18:30-19:30
- タイトル：おら! オラ! Oracle - どっぷり検証生活　現場で役立つ　Oracle DBのパフォーマンスチューニング
- 内容：徹底検証された結果から、本当に役立つOracle Databaseのパフォーマンスチューニングについて解説。　　
- 講師：株式会社インサイトテクノロジー　松尾亮さん

19:30-20:30
- タイトル：現場で役立つ .NET + Oracle Database アプリケーションチューニング
- 内容：.NETアプリケーションからOracle Databaseの機能を最大限に引き出すコーディング手法を徹底解説
- 講師：日本オラクル　大田浩さん

メモ

おら! オラ! Oracle - どっぷり検証生活　現場で役立つ　Oracle DBのパフォーマンスチューニング

Oracle のチューニングポイントは待機イベントを減らすこと。待機イベントの解消=パフォーマンス向上。
待機イベントとはサーバープロセスが CPU を使用していない時間。
ある期間におけるインスタンス全体の待機イベントを確認したい場合はパフォーマンス統計レポート（STATSPACK or AWR）で確認する。
- STATSPACK: Oracle 8.1.6 〜
- AWR: Oracle 10.1.0 〜、EE のみ利用可
今回のセミナーでは、Standard Edition でも利用可能な STATSPACK を例にして説明する。
ある特定の処理における待機イベントを確認したい場合は SQL トレース、動的パフォーマンスビュー（V$表）で確認する。
STATSPACK はデフォルトは level5 だが level7 の情報が役に立つことが多い。level 10 は情報量が多くスナップショット取得にかかる負荷が高くなるため、サポートから依頼された場合を除き、基本的に使用しない。
STATSPACK レポートでは Top 5 待機イベントが最も注目すべきポイント。
- Waits: イベントのために待機した合計回数
- Time(s): 合計待機時間(秒)
- Avg wait(ms): 平均待機時間
- %Total Call Time: 全ての待機時間に対する占有比率
上位の待機イベントの解消 = チューニング効果の期待大
STATSPACK での調査ステップ
1. Top 5 待機イベントを確認
2. 上位の待機イベントの意味を確認（マニュアル、KROWN等）
3. 待機イベントの発生要因に関連するセクションを調査
  - db file scattered read、db file sequential read 等 → SQL ordered By XXX セクションを確認 → SQLチューニング？
  - enqueue → Enqueue activity、Segments by Row Lock Waits セクションを確認 → アプリ処理の見直し
  - buffer busy waits → Segments by Buffer Busy Waits セクションを確認 → 空きリスト競合？逆キー索引、パーティション化？
例えば、db file scattered read、db file sequential read が上位に出現している場合
- SQL の I/O 関連イベントなので、SQL ordered by Elapsed Time、SQL ordered by Gets 等を確認。
- SQL ordered by XXX で確認した SQL 分析レポートを確認する
  - ?/rdbms/admin/sprepsql.sql を使う
特定の SQL 調査（SQL トレースを取得）
- 特定の SQL の待機イベント情報を含めた SQL トレースを取得

SQL> alter session set events '10046 trace name context forever, level8';

問題のSQLを実行 ...

SQL> alter session set events '10046 trace name context off';

- 実行中のセッションに対して SQL トレースを取得

-- v$session 等の情報からセッションを特定
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SQL> select username, program, server, status, sid, serial#, last_logon_time from v$session where username is not null;
-- 特定した sid、serial# に対してSQLトレースを取得
SQL> exec sys.dbms_system.set_ev(&sid, &serial, 10046, 8, '');
SQL> exec sys.dbms_system.set_ev(&sid, &serial, 10046, 0, '');

- 取得したトレースファイルを整形

$ tkprof <トレースファイル名> <整形後ファイル名> sys=no
※初期化パラメータ user_dump_dest or background_dump_dest に出力される

特定のSQL調査（SQL実行計画を確認）

-- PLAN_TABLE を作成
SQL> @?/rdbms/admin/utlxplan.sql
-- SQL を解析
SQL> explain plan for 解析したいSQL文;
-- 解析結果を表示
SQL> select * from table (dbms_xplan.display());

チューニング事例紹介
- インデックスを追加したら、連番データのinsertが多重実行されるという特性であった為、同一ブロックへの競合が発生。逆キー索引は範囲検索できないため、ハッシュパーティショニング化。（11gR1 2node RAC）
- 表と索引の再編成後に統計情報を収集しなかったため、適切な実行計画が選択されず、CPU使用率が100%に。enqueue のTS（Temp Segment）とSS（Sort Segment）が増加。TEMP表領域への物理アクセスが多い。過剰なソートを実行しているSQLがある。CPU_TIMEとELAPSED_TIMEの差が大きい。待機イベント時間が長い。統計情報を収集して共有プールをクリアして解決。（10GR2 4node RAC）

SQL> alter table EMP move;
SQL> alter index PK_EMP rebuild;
SQL> alter index IDX_EMP_1 rebuild;
SQL> alter index IDX_EMP_2 rebuild;

...

SQL> exec dbms_stats.gather_table_stats(
 ownname => 'SCOTT',
 tabname => 'EMP',
 cascade => true);

...

SQL> aleter system flush shared_pool;

統計情報を収集しても、実行計画はすぐに変わらない。dbms_gather_table_stats の no_invalidate オプションが auto_invalidate（10gR2以降のデフォルト値）だった為。この auto_invalidate の場合、新しい統計情報を利用した再解析が行われるまでにタイムラグがある。（しばらくの間は既存のカーソルを再利用する）新しい実行計画に変えるには以下のいずれかを行う。
1. no_invalidate を flase にして実行する
2. 共有プールをフラッシュする