Copyright © 2012, Oracle and/or its affiliates. All rights

1
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
データベースの症状から
アプリの問題点を見つける
パフォーマンス・ドクター
日本オラクル株式会社
テクノロジー製品事業統括本部
ソリューション本部 基盤技術部
プリンシパルエンジニア
柴田竜典
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは
できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン
ト（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さ
い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい
ては、弊社の裁量により決定されます。
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
3
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
シバタツって誰？
 日本オラクル株式会社
テクノロジー製品事業統括本部 ソリューション本部 基盤技術部
プリンシパルエンジニア 柴田竜典
 Oracle Technology Network にて全5回で
『シバタツ流！ DWHチューニングの極意』を連載
– これを読めばDWHデザインを間違わない！
– すべての回に似顔絵がついている！
シバタツ
4
検索
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
もう少しプロフィール
 Oracle Exadata リリース当初から、お客様のSQLやデータを使用した
PoC (Proof of Concept) を実施
– 本番稼働しているたくさんのシステムのパフォーマンス・チューニングを経験
 2010年には米オラクルの開発部門の一員として
サンフランシスコのヘッド・クォーターで勤務
– 米国のお客様のPoCを実施しつつ、そこから見えてきた
Oracle Database のパフォーマンス課題の解決に取り組む
5
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
アプリケーション・コードが
システム・パフォーマンスに
与える影響
6
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
アプリケーション・コードが
システム・パフォーマンスに与える影響
 アプリケーション・コードがシステム・パフォーマンスに
どのくらい影響するかは無視できない
– この事実は昔からよく知られている
– 一方でこの事実は昔から無視されている
 悪いアプリケーション・コードはハードウェアやソフトウェアへの
投資を無意味にする
 Developerは悪いアプリケーション・コードを書かない
DBAは悪いアプリケーション・コードを素早く見つけることが重要
7
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
本日紹介するパフォーマンス・ドクターの診断症例
 症例その1: 負荷が掛からない
 症例その2: 共有プールの具合が良くない
 症例その3: カーソルがいくつあっても足りない
 症例その4: しばらくすると負荷が止まりはじめる
 症例その5: 突然のサービス停止
8
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1:
負荷が掛からない
9
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
症状
 このアプリケーションではまったく負荷が掛からない
 レスポンス時間もスループットも良くない
 DBAはコネクション数を増やしたが改善しない
– 「データベースがバグってるんじゃないの？」
 冬のボーナス商戦に向けて、このアプリケーションは悪くても
レスポンス時間5ms、スループット3万tpsを実現しないといけない
10
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
診察
 レスポンス時間: 150ms
 スループット: 300tps
 CPU使用率のuserとsysが
同じくらい出ている
11
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
原因:
多発性ログイン=ログアウト症
病名はフィクションです
12
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
原因
for (i = 0; i < 1000000; i++) {
conn = ds.getConnection(userid, password);
pstmt =
conn.prepareStatement("SELECT name FROM employees WHERE id = " + i);
rset = pstmt.executeQuery();
......
conn.close();
}
13
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SQL文実行のたびに
ログイン / ログアウトしている
症例その1: 負荷が掛からない
処方箋
conn = ds.getConnection(userid, password);
for (i = 0; i < 1000000; i++) { {
pstmt =
conn.prepareStatement("SELECT name FROM employees WHERE id = " + i);
rset = pstmt.executeQuery();
......
}
conn.close();
14
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
必要最小限の
ログイン / ログアウト
症例その1: 負荷が掛からない
症状
 多発性ログイン=ログアウト症が完治したことで
レスポンス時間とスループットは多少良くなったが
目標にはだいぶ遠い
 Concurrencyの待機イベントがたくさん出ており
共有プールの具合が良くない
15
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
診察
 レスポンス時間: 20ms
 スループット: 3,000tps
 latch: shared pool
16
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
原因:
慢性ハード・パース病
病名はフィクションです
17
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
原因
conn = ds.getConnection(userid, password);
for (i = 0; i < 1000000; i++) { {
pstmt =
conn.prepareStatement("SELECT name FROM employees WHERE id = " + i);
rset = pstmt.executeQuery();
......
}
conn.close();
18
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
毎回ハード・パースしている
症例その1: 負荷が掛からない
処方箋
conn = ds.getConnection(userid, password);
for (i = 0; i < 1000000; i++) { {
pstmt =
conn.prepareStatement("SELECT name FROM employees WHERE id = ?");
pstmt.setInt(1, i);
rset = pstmt.executeQuery();
......
}
conn.close();
19
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
プレース・ホルダーを使うことで
毎回ハード・パースしない
症例その1: 負荷が掛からない
診察
 レスポンス時間: 1ms
 スループット: 25,000tps
 競合が解消したことで
CPUがよく動いてる
20
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
アドバイス
conn = ds.getConnection(userid, password);
pstmt =
conn.prepareStatement("SELECT name FROM employees WHERE id = ?");
for (i = 0; i < 1000000; i++) { {
pstmt.setInt(1, i);
rset = pstmt.executeQuery();
......
}
conn.close();
21
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ソフト・パースすらも毎回しない
症例その1: 負荷が掛からない
診察
 レスポンス時間: 1ms
 スループット: 34,000tps
 スループットがさらに向上
22
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その1: 負荷が掛からない
160
140
120
100
80
60
40
20
0
Logon/Transaction
Hard Parse
Transaction Rate x 1000
23
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Soft Parse
Response time(ms)
No Parse
症例その2:
共有プールの具合が良くない
24
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その2: 共有プールの具合が良くない
症状
 本番環境同等の検証環境を持っている
 検証環境での性能が本番環境だとまったく出ない
 DBAは共有プール競合が多発していることを見つけ
アプリのソース・コードをくまなく確認したが
不必要な構文解析は起きていない
 Developerは本番環境とテスト環境で
全く同じコードが動いていると主張している
25
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その2: 共有プールの具合が良くない
診察
 スループットが検証環境に比べて
大幅に悪い
 共有プール競合が多発
 V$SYSSTATビューの
parse count (failures) が
増えていないかどうか確認
26
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その2: 共有プールの具合が良くない
診察
SQL> SELECT name, value FROM v$sysstat
2> WHERE name = 'parse count (failures)';
NAME
VALUE
------------------------------ ---------parse count (failures)
25173781
27
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その2: 共有プールの具合が良くない
原因:
構文解析障害症候群
病名はフィクションです
28
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その2: 共有プールの具合が良くない
原因
 本番環境でだけ、ページ再読込み時のトリガーが
存在しない隠しパラメーターをセットしようとしていた
– ALTER SESSION SET _INSTANT_TUNING = AUTO;
 すべてのユーザーがこのSQLを構文解析しようとして
すべてのセッションが共有プールでシリアライズされていた
 SQL*Net break/reset to client 待機イベントの数からも
診断することができる
29
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3:
カーソルがいくつあっても
足りない
30
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3: カーソルがいくつあっても足りない
症状
 「ORA-01000: 最大オープン・カーソル数を超えました」が発生し
アプリケーションが動作しなくなり、アプリケーションの再起動が必要になる
 DeveloperはDBAにカーソル数を増やすことを依頼し
DBAは初期化パラメータのOPEN_CURSORに
十分と思われる30000をセットした
 エラーが出るまでの時間は延びたが、結局しばらくすると
ORA-01000が発生してしまう
31
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3: カーソルがいくつあっても足りない
診察
 エラーが発生すると処理が停止
 SQL*Net break/reset
to client が多発
 オープン中のカーソルを確認
32
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3: カーソルがいくつあっても足りない
診察
 回数が1より大きいものは
正しく閉じられていない可能性
33
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3: カーソルがいくつあっても足りない
原因:
カーソル閉鎖機能障害
［三大リーク病のひとつ］
病名はフィクションです
34
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その3: カーソルがいくつあっても足りない
原因
try {
......
rset = pstmt.executeQuery();
......
rset.close();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
35
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
例外処理に行ってしまうと
pstmtをクローズしていない
症例その3: カーソルがいくつあっても足りない
処方箋
try {
finallyで必ず
......
pstmtをクローズする
rset = pstmt.executeQuery();
......
rset.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...}
}
36
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その4:
しばらくすると
負荷が止まりはじめる
37
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その4: しばらくすると負荷が止まりはじめる
症状
 アプリケーションを動かしてからしばらく経つと
スループットが突然落ち始める
 アプリケーションのログではタイムアウトが記録されている
 DBAはデータベースに問題がなく、アプリケーションからの接続を
増やして負荷を高めることをDeveloperに要求
– 初期化パラメータのPROCESSESはすでに20000で十分
38
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その4: しばらくすると負荷が止まりはじめる
診察
 スループットがゼロに近づいていく
 負荷もゼロに近づいていく
 OSのリソース使用量も
ゼロに近づいていく
39
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その4: しばらくすると負荷が止まりはじめる
原因:
セッション閉鎖機能障害
［三大リーク病のひとつ］
病名はフィクションです
40
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その4: しばらくすると負荷が止まりはじめる
原因
try {
......
rset = pstmt.executeQuery();
......
rset.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
41
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
例外処理に行ってしまうと
connをクローズしていない
症例その4: しばらくすると負荷が止まりはじめる
処方箋
try {
finallyで必ず
......
connをクローズする
rset = pstmt.executeQuery();
......
rset.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...}
if (conn != null) try {conn.close();} catch (SQLException e) {...}
}
42
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5:
突然のサービス停止
43
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
症状
 何の前触れもなく、データベースがハングしたような状態になり
同時にアプリケーションもタイムアウトしはじめた
 DBAは、アクティブではない1個のセッションが取得しているロックを
残りすべてのセッションが待っていることを確認した
 この問題が起きるたびに、DBAはロックを取得しているセッションを
killすることでシステムを再稼働している
44
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
診察
 ロック待ちにより
すべてのセッションが待機
45
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
診察
 1個のセッションが
残りすべてのロック待ちを
引き起こしていることが
確認できる
46
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
原因:
ロック解放機能障害
［三大リーク病のひとつ］
病名はフィクションです
47
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
原因
 ロック・リークはセッション・リークと同時に起きやすく
例外処理内でのCOMMIT / ROLLBACKの処理に問題があることが多い
 コネクション・プーリングをしていると、リークしているセッションは
ロックを獲得したままコネクションを返却してしまい
データベースはCOMMITもROLLBACKもされないまま放置される
48
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
症例その5: 突然のサービス停止
原因
try {
......
rset = pstmt.executeUpdate();
......
conn.commit();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
49
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
例外処理に行ってしまうと
COMMITしていない
しかもセッション・リーク
している
症例その5: 突然のサービス停止
処方箋
try {
失敗したときには
......
ROLLBACK
rset = pstmt.executUpdate();
......
conn.commit();
} catch (SQLException e) {
if (conn != null ) try {conn.rollback();} catch (SQLException e) {...}
e.printStackTrace();
} finally {
if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...}
if (conn != null) try {conn.close();} catch (SQLException e) {...}
}
50
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
本セッションのまとめ
51
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
本セッションのまとめ
アプリケーション・コードがシステム・パフォーマンスに与える影響
 これらの原因の多くは一般的によく知られている問題である
 しかし無視されていることが現実には多い
 これらの問題が引き起こす影響はDBAの視点からは不可解
＿ 人人人人人人 ＿
＞ 問題の長期化 ＜
￣Y^Y^Y^Y^Y^Y￣
 Developerは悪いアプリケーション・コードを書かない
DBAは悪いアプリケーション・コードを素早く見つけることが重要
52
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Database
Oracle Database 11g : SQLチューニング・ワークショップ
Oracle Database 11gでSQLを効率的にチューニングするための知識とノウハウを習得！
本コースではチューニング方法のみならず、チューニングの方法論までも理解できます。使用するOracleの診断ツールおよび機能は、自動
SQLチューニング・コンポーネント、EXPLAIN、SQL TraceおよびTKPROF、SQL*Plus AUTOTRACEです。また、物理的なスキーマおよび
SQL構文を変更することによるオプティマイザの動作への影響についても習得できます。
本コース修了時には、以前のリリースで行っていた手動でのチューニング方法と比較した上で、現在のリリースで提供される自動SQLチュー
ニング機能を使用できるようになります。受講者は、Oracleオプティマイザの概念を理解し、体系化されたマシン演習を通じて、講義内容をよ
り深く理解できます。
コース内容
■Oracle Databaseアーキテクチャの検討
■SQLチューニングの概要
■オプティマイザの概要
■オプティマイザ演算子
■オプティマイザ・ヒントの使用
■SQLチューニングの自動化
対象者
・データベースアプリケーション開発者
受講料
定価￥２１８，２９５（税込）
（2012年9月現在）
集合研修
■実行計画の解釈
■ケース・スタディ: スター型変換
■オプティマイザ統計
■バインド変数の使用
■アプリケーション・トレース
※Oracle PartnerNetwork会員様は、パートナー割引価格で受講いただけます。
教室での集合研修も絶賛開催中。
集合研修の日程は http://education.oracle.co.jp/ 左上コース検索にて、「チューニング」で検索頂けます
お申込み・お問合せ http://www.oracle.com/jp/education
オラクルユニバーシティ Tel: ０１２０-１５５-０９２
53
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ToD
トレーニングオ
ンデマンドにて
提供中
54
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
55
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.