Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Technical
Discussion
Night
～今宵のテーマ：
「 DB 12cクエリー・オプティマイザ
（パフォーマンス・チューニング）」を語ろう～
日本オラクル株式会社
クラウド・テクノロジー事業統括
•
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するもの
です。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込
むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコ
ミットメント（確約）するものではないため、購買決定を行う際の判断材料に
なさらないで下さい。オラクル製品に関して記載されている機能の開発、リリース
および時期については、弊社の裁量により決定されます。
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
Technical Discussion Night
～「 DB 12cクエリー・オプティマイザ（パフォーマンス・チューニング）」を語ろう～
•
皆様が、本当に必要としている技術やTipsについて、熱く語り合いましょう！
–
お申し込み時に頂いたご質問に対して、
可能な限り、日本オラクルのエキスパートが回答させて頂きます
•
本日のファシリテーター
–
日本オラクル株式会社
クラウド・テクノロジー事業統括
Database & Exadataプロダクトマネジメント本部
ディレクター
柴田 長
Topic#1
実行計画って、どう読めばよいので
すか？
will be answered
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
Profile
Oracleユニバーシティー シニア・インストラクター 中村 真理子
・前職では、開発に携わりながら、DBAもやっていました。
・ （苦労話が理解できる）インストラクターです。
今回は2つの質問にお答えします。
5
実行計画の基本的な読み方が知りたいです。
•
基本の読み方
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---
| 0 | SELECT STATEMENT | | 3 | 66 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 66 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 |
---
・ インデントが一番深いものから実行
・ 同一インデントならば上のステップを実行
・
アクセスパス
：TABLE ACCESS FULL、TABLE ACCESS BY INDEX ROWID
・
結合方法
の次に駆動表
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
結合方法
•
ネステッド・ループ結合
7
① 駆動行ソースがスキャンされる
② 駆動行ソースから1件ずつ、内部行ソースを検索
③ 結果行が返される
DEPT（駆動表）
EMP（内部表）
② 探す
① 1件読み込み
LOOP
③ 2件目（全件繰り返し）
--- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 3 | 66 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 3 | 66 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 | ---結合方法
•
ハッシュ結合
① 駆動表： 結合キーを元にハッシュ表を作成
② 内部表： 結合キーを順にハッシュ表をハッシングし、合致する行を特定
DEPT
EMP
PGA
ハッシュ表
HASH HASH --- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 3 | 66 | 4 (0)| 00:00:01 | |* 1 | HASH JOIN | | 3 | 66 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 | ---Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
結合方法
•
ソート・マージ結合
9
① 表1の結果セットを、結合列でソート
② 表2の結果セットを、結合列でソート
③ 表1のデータセット各行に対して2つ目の開始点が検索され、
結合されない行が検出されるまでスキャン
--- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 14 | 308 | 5 (0)| 00:00:01 || 1 | MERGE JOIN | | 14 | 308 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
DEPT
① 並び替えて
EMP
② 並び替えて
結合方法
•
デカルト結合
一方のデータソースにあるすべての行を、他方にあるすべての行と直積演算
--- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 56 | 1064 | 10 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 56 | 1064 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 14 | 84 | 7 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 | ---
DEPT
全ての組み合わせを作成EMP
直
積
演
算
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
想定通りの結合方法にならない場合は
ex. 結合行は少ないのに、ハッシュ結合が選択されている
◆確認ポイント
・ 統計情報
・ 誤ったヒント句 ※構文エラーを含む
11
Topic#2
良くある待機イベントが知りたい
will be answered
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
よくある待機イベント
その1: buffer busy waits
13
Buffer busy waits
更新のためにサーバプロセスが確保している「確
保済みバッファ」へ、別のプロセスがアクセスしよ
うとすると、
buffer busy waits
が記録される
buffer busy waitsの待機が多い場合は
・ アプリケーション競合をなくす
SQLチューニングで、アクセスするブロックを極力減らす
DML操作を分散して、競合をなくす
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
よくある待機イベント
その2: log file sync
15
ユーザー・セッションをコミットするには、
セッションのREDO情報を
REDOログ・ファイルにフラッシュする必要がある
COMMIT
REDOログ・ファイルに書き込むまでの間、
フォアグラウンド・プロセスは待機する
⇒ この待機が、
log file sync
log file syncの待機が多い場合は
COMMIT回数が多すぎる
REDOログファイルがいっぱいになるのがはやすぎる
書き込み速度に原因がある
◆確認ポイント
- COMMITをまとめて行う
- REDOログサイズを大きくする
- 低速ディスクから高速ディスクへ切り替える
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
Topic#3
性能の良い
SQL
の書き方を
教えて下さい。
will be answered
皆様の声(from アンケート)
•
よいSQLの書き方が知りたい
•
パフォーマンスが向上するSQLの書き方
•
優秀なSQLコーディングルールの実例などがあれば数多く教えてください。
•
パフォーマンスの良いSQLの書き方を知りたい
•
また、良いSQLの書き方だけではなく、悪いSQLの書き方も知りたいです。
•
よいSQLの書き方(複数の副問合せ)を知りたい。
本題の前に、ちょっと前半を振り返ってみる。
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
Oracle Database 12cR1 における 実行計画生成の全体像
•
予測
精度向上のための、様々なクエリー・オプティマイザ機能をご紹介
オプティマイザ
(CBO)
⑥ヒント句
①SQLテキスト/Bind変数
②オブジェクト構造
③初期化パラメータ
⑦アウトライン
／SPM(11g以降)
⑨SQL Profile
凡例 実線(－)⇒必須情報 破線(…)⇒追加情報④システム統計
⑤オプティマイザ統計
実データ
SQL性能
(レスポンス)
H/W
⑩Cardinality
Feedback(11gR2以降)
⑪Dynamic Sampling
(12c以降:動的統計)
実行計画
の生成
⑭SQL計画ディレク
ティブ(12c以降)
⑫適応計画(12c以降)
⑬SQLワークロード
(COL_USAGE$)
⑧Bind Peek/
適応カーソル共有
実行計画
19DDD 2016／前半資料より
SQL性能で重要になるのは「CBOの予測精度」
•
SQLの性能を上げるには「良い実行計画」が必要
•
「良い実行計画」を生成するには、
「CBOの予測精度」を上げると云う考え方が重要
–
SQLの書き方も重要なんですが、それ以外の
Inputの精度も上げていくことが必要です。
–
でも全てのInputを闇雲に揃えて行くのは、
はっきり言って現実的ではない。
•
ではどう云う風に考えて行くかというと……
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
「全体最適」と「個別最適」の適用イメージ
SQLの重要度
処理
時間
短
長
低
高
全体最適
個別最適
・適切な統計
・適切なSQL
・実行計画
最適化機能
(Bind Peek,
Dynamic Sampling,
Cardinality Feedback, 等)
・適切な索引
・ヒストグラム
・拡張統計
・ヒント
・SPM(or アウトライン)
・SQLプロファイル
DDD 2013 SQLチューニングに必要な考え方と最新テクニック より
「全体最適」と「個別最適」の適用イメージ(少し追記)
SQLの重要度
処理
時間
短
長
低
高
全体最適
個別最適
・適切な統計
・適切なSQL
・適切なデータ構造
・実行計画
最適化機能
(Bind Peek,
Dynamic Sampling,
Cardinality Feedback, 等)
・適切な索引
・ヒストグラム
・拡張統計
・SQL計画ディレクティブ
・ヒント
・SPM(or アウトライン)
・SQLプロファイル
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
基本的な部分を良くして全体の予測精度を底上げする。
SQLの重要度
処理
時間
短
長
低
高
全体最適
個別最適
・適切な統計
・適切なSQL
・適切なデータ構造
・実行計画
最適化機能
(Bind Peek,
Dynamic Sampling,
Cardinality Feedback, 等)
・適切な索引
・ヒストグラム
・拡張統計
・SQL計画ディレクティブ
・ヒント
・SPM(or アウトライン)
・SQLプロファイル
「適切な統計」 「適切なSQL」 「適切なデータ構造」 「各種実
行計画の最適化機能」で、SQL全体の実行計画予測精度を
上げていく。 ※適切な～の定義については後述
適切な～の定義とは？⇒「アンチパターンの回避」
•
では「適切なSQL」「適切な統計」
「適切なデータ構造」の定義とは何だろうか？
•
それは「CBOの予測精度を下げる要素を
回避／排除する事」だと考えます。
•
「CBOの予測精度を下げる要素」で、
最も一般的なのは「アンチパターン」
•
「SQL」「データ構造」「統計」に関する、
代表的な「アンチパターン」をご紹介
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
SQLのアンチパターン
参考：ハズレの実行計画例・結合表が多い(1/2)
•
Oracleのオプティマイザは、実行計画作成時に表の適切な結合
順序を見つけるために全ての組み合わせを解析しようとします。
表Ａ
表B
表B
表A
結合する表が２つの場合
⇒ ２！（２の階乗）＝ ２×１＝２通り
表A
表B
表C
表A
表C
表B
表B
表A
表C
表B
表C
表A
表C
表A
表B
表B
表B
結合する表が３つの場合
…３！（３の階乗）＝３× ２×１＝６通り
DDD 2016／前半資料より
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
参考：ハズレの実行計画例・結合表が多い(2/2)
•
結合する表が多い場合は、Oracleのオプティマイザ
は結合順序の解析を途中で止めます。
–
全組み合わせの解析には、時間が掛かり過ぎるからです。
–
デフォルトで2000通りまでの結合順序を解析します。
•
結合表が多過ぎるSQLは、適切な結合順序に辿り着けず、
ハズレの実行計画を引くリスクが高いと言えます。
結合する表が8つの場合 ⇒ 8!（8の階乗）＝8*7*6*5*4*3*2*1＝
40320通り
表A
表B
表C
表D
表E
表F
表G
表H
27DDD 2016／前半資料より
参考：某チューニング案件の超巨大SQL実行計画
•
SQLテキストで6700行以上、40表以上を結合したSELECT文の実行計画
–
実行計画のステップ数換算で 、実に500ステップ以上の超巨大SQL
…(中略)…
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
WHERE句の列(カラム)に関数を適用
•
WHERE句の列(カラム)に関数を適用しているのは、
SQLのアンチパターンの一つです。
•
本パターンが性能劣化し易いのは、
下記２つの理由に依ります。
–
(1). 列に作成された索引が使用されない。
–
(2). CBO が列統計を使用できず、
実行計画の予測精度
が落ちる。
SELECT /*+ MONITOR */ A.* FROM TEST_TABLE_A A , TBL_B BWHERE A.P_NO2 = B.P_NO AND A.P_CHAR = B.P_CHAR
AND TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801';
P_DATE列にTO_CHAR関数を適用
結合条件の欠如 と それによる直積
•
結合条件が欠如して 直積(MERGE JOIN CARTESIAN)が
発生するのは、典型的なアンチパターンと言えます。
--- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 56 | 1064 | 10 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 56 | 1064 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 14 | 84 | 7 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 | ---
DEPT
全ての組み合わせを作成EMP
直
積
演
算
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
•
一見シンプルなSQLなのに複雑な実行計画
•
ヒント等による実行計画制御もやり辛く、地獄
Viewのネスト や View内の結合、ViewのViewのView…
SELECT * FROM TBL_A
WHERE ～
View_A
SELECT * FROM TBL_B
WHERE ～
View_B
SELECT A.*, B.*, C.*
FROM View_A A
, View_B B
, TBL_C C
WHERE A.ID = B.ID
AND B.PID = C.PID
：
View_C
TBL_C
SELECT C1.* FROM View_C C1
WHERE C1.PID = (
SELECT MAX(C1.PID)
FROM View_C C2
)
View_D
SELECT *
FROM View_D
WHERE ID = XXX
--- | Id | Operation | Name | --- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | HASH GROUP BY | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL SCAN | TBL_A | | 5 | HASH JOIN | | | 6 | TABLE ACCESS FULL SCAN | TBL_B | | 7 | TABLE ACCESS FULL SCAN | TBL_C | | 8 | HASH JOIN | | | 9 | TABLE ACCESS FULL SCAN | TBL_A | | 10 | HASH JOIN | | | 11 | TABLE ACCESS FULL SCAN | TBL_B | | 12 | TABLE ACCESS FULL SCAN | TBL_C | ---一見シンプル
なSQL
複雑な
実行計画
INリストに指定する値が多い
•
下記のようなINリストに沢山の値を設定するSQLは、
Parseの負荷が高くなる傾向が有り、可能なら回避したい。
SELECT * FROM TBL_A WHERE C1 IN (:B1, :B2, :B3, :B4, :B5, :B6, :B7, :B8, :B9, :B10, :B11, :B12, :B13, :B14, :B15, :B16, :B17, :B18, :B19, :B20, :B21, :B22, :B23, :B24, :B25, :B26, :B27, :B28, :B29, :B30, :B31, :B32, :B33, :B34, :B35, :B36, : : : , :B719, :B720, :B721, :B722, :B723, :B724, :B725, :B726, :B727, :B728, :B729, :B730, :B731, :B732, :B733, :B734, :B735, :B73 6, :B737, :B738, :B739, :B740, :B741, :B742, :B743, :B744, :B745, :B746, :B747, :B748, :B749, :B750, :B751, :B752, :B753, :B7 54, :B755, :B756, :B757, :B758, :B759, :B760, :B761, :B762, :B763, :B764, :B765, :B766, :B767, :B768, :B769, :B770, :B771, :B 772, :B773, :B774, :B775, :B776, :B777, :B778, :B779, :B780, :B781, :B782, :B783, :B784, :B785, :B786, :B787, :B788, :B789, : B790, :B791, :B792, :B793, :B794, :B795, :B796, :B797, :B798, :B799, :B800, :B801, :B802, :B803, :B804, :B805, :B806, :B807, :B808, :B809, :B810, :B811, :B812, :B813, :B814, :B815, :B816, :B817, :B818, :B819, :B820, :B821, :B822, :B823, :B824, :B825, :B826, :B827, :B828, :B829, :B830, :B831, :B832, :B833, :B834, :B835, :B836, :B837, :B838, :B839, :B840, :B841, :B842, :B843 , :B844, :B845, :B846, :B847, :B848, :B849, :B850, :B851, :B852, :B853, :B854, :B855, :B856, :B857, :B858, :B859, :B860, :B86 1, :B862, :B863, :B864, :B865, :B866, :B867, :B868, :B869, :B870, :B871, :B872, :B873, :B874, :B875, :B876, :B877, :B878, :B8 79, :B880, :B881, :B882, :B883, :B884, :B885, :B886, :B887, :B888, :B889, :B890, :B891, :B892, :B893, :B894, :B895, :B896, :B 897, :B898, :B899, :B900, :B901, :B902, :B903, :B904, :B905, :B906, :B907, :B908, :B909, :B910, :B911, :B912, :B913, :B914, : B915, :B916, :B917, :B918, :B919, :B920, :B921, :B922, :B923, :B924, :B925, :B926, :B927, :B928, :B929, :B930, :B931, :B932, :B933, :B934, :B935, :B936, :B937, :B938, :B939, :B940, :B941, :B942, :B943, :B944, :B945, :B946, :B947, :B948, :B949, :B950, :B951, :B952, :B953, :B954, :B955, :B956, :B957, :B958, :B959, :B960, :B961, :B962, :B963, :B964, :B965, :B966, :B967, :B968 , :B969, :B970, :B971, :B972, :B973, :B974, :B975, :B976, :B977, :B978, :B979, :B980, :B981, :B982, :B983, :B984, :B985, :B98 6, :B987, :B988, :B989, :B990, :B991, :B992, :B993, :B994, :B995, :B996, :B997, :B998, :B999);
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
SQLアンチパターンのまとめ
•
結合表が多い
–
SQLの結合を全て6表以下に抑えるのは難しいかもですが、
40表結合の超巨大SQLとかは止めた方が良いです。実行計画ブレまくり。
•
WHERE句の列(カラム)に関数を適用
–
索引が使われなくなる(有名)と共に、CBOの予測精度も低下(無名?)します。
•
結合条件の欠如 と それによる直積(CARTESIAN)
–
予測精度の話とは若干ズレるのですが、いわずもがななので。
•
Viewのネスト、View内の結合、ViewのViewのView……
–
地獄、一言で表すと地獄。ある程度のルール(制限)を設けるべき。
–
後フェーズで発覚することが多くて、手遅れな場合も多いんですけどね……
•
INリストに指定する値が多い
–
高負荷 な Parse となり易い傾向
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
•
行方向で減らし（索引・パーティション…）
•
列方向で減らす（カバリングインデックス…）
パフォーマンスで最も重要なのはデータ構造
索引スキャン
表
カバリング
インデックス
パーティション
列を絞る
フ
ル
ス
キ
ャ
ン
索引
表
行を絞る
仕事量を減らすには
データ構造が肝
35行き過ぎた正規化(データモデル)
•
正規化が行き過ぎていると、結合表が多くなってしまう。
–
下記例では「個人」のデータを取得するのに7表結合する必要がある。
個人ID
名前ID
性別ID
電話番号ID
住所ID
性別ID
：
個人
個人ID
名前ID
姓
名
：
個人姓名
個人ID
住所ID
郵便番号ID
番地番号
建物名
：
個人住所
個人ID
電話番号ID
電話番号
電話番号種別
：
個人電話番号
性別ID
性別
性別マスタ
郵便番号ID
県ID
市区町村ID
番地名
：
郵便番号マスタ
県ID
県名
県マスタ
市区町村ID
市区町村名
市区町村マスタ
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
オプティマイザ統計のアンチパターン
オプティマイザ統計のアンチパターン
•
0件統計／Null統計／実体と乖離した古い統計
–
前半の資料でお話し済み
–
特に
0件統計
は典型的なアンチパターン、絶対回避すること！
•
索引やMView実体表の 0件統計／Null統計
–
こちらも前半の資料でお話し済み
–
通常の表は意識していても、索引やMviewの統計には気が付かないことも
•
(サブ)パーティション の 0件統計／Null統計
–
GLANURALITY => 'AUTO'(※デフォルト) だと
サブパーティション統計が取られないので、
'ALL', 'SUBPARTITION'等を明示的に指定するようにしましょう。
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
参考：統計アンチパターンのオススメ回避方法
•
性能テスト等の突入前に、以下のディクショナリをチェックして、
アンチパターン(0件統計/Null統計)を
確認するのがオススメです。
–
(DBA | ALL | USER)_TAB_STATISTICS
–
(DBA | ALL | USER)_IND_STATISTICS
•
これらのディクショナリには表／索引の統計が、以下の
3段階全てで格納されており、網羅的なチェックが可能
–
グローバル統計(※表／索引全体の統計)
–
パーティション統計
まとめ・SQLの性能を良くするには……
アンチパターン
を回避して、
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
Topic#4
SE(Standard Edition)
でも
SQL
チューニングしたい
!
will be answered
DBMS_XPLAN と SQLトレース で SE でも闘える！
•
DBMS_XPLAN(.DISPLAY_CURSOR) の 参考資料
–
SQLの実行計画に実行統計を併記して表示する方法
(DBMS_XPLAN.DISPLAY_CURSOR)
(KROWN:141531) (ドキュメントID 1749298.1)
–
DDD 2013【A-1】オラクル・コンサルが語る！
SQLチューニングに必要な考え方と最新テクニック
http://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/A-1.pdf
•
SQLトレース の 参考資料
–
SQL トレースを使用したSQLチューニング
(KROWN:137157) (ドキュメントID 1747064.1)
–
EVENT: 10046 "SQL 文のトレースを有効にする (バインド/待機含む)" (ドキュメント
ID 1965967.1)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
でも EE/Tuning Pack を 諦めないで欲しい…
•
EE(Enterprise Edition) は 高くて手が出ない？
⇒ Oracle Database Cloud Service が 有るじゃないか！
•
Tuning Pack が 使用可能 な High Performance Package は 時間当
たり $6.72～ から利用可能
※2017年2月24日現在
•
詳細は下記URL をご参照ください。
Topic#5
SQL
チューニング関連で
よくある問い合わせ
will be answered
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
Profile
データベースサポート部 シニア・プリンシパル・サポートエンジニア 松本 尚子
・1997年新卒入社。サポートエンジニア歴 19年。
SQLが突然遅くなった
•
調査アプローチ
–
時間がかかっている要素の確認
•
SQL監視
•
AWR/STATSPACK レポート
•
AWR/STATSPACK の SQLレポート
•
SQLトレース
•
Active Session History (ASH)
•
OSWatcher Black Box （MOS Doc ID 1763622.1）
–
統計情報の内容確認
•
DBA_TAB_STATISTICS, DBA_TAB_COL_STATISTICS, DBA_IND_STATISTICS
•
いつ収集されたものか LAST_ANALYZED列
•
0行で採取してしまっていないか NUM_ROWS列
STATSPACK、 OSWatcher Black Box を
導入しましょう
STATSPACKのドキュメント：
$ORACLE_HOME/rdbms/admin/spdoc.txt
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
SQLが突然遅くなった
47
•
原因
–
ディスク関連の待機イベントの平均待機時間が延びている
実行計画は変わっていない
–
SQL監視でActualとEstimateに大きな差が見える
アクセスバッファ数がとても増えている
•
対処
–
ストレージの応答速度の確認
–
統計情報収集の運用確認
ディスク遅延が起きている可能性あり
適切な実行計画で実行されていない可能性あり
ヒント句を指定したが有効になっていないようだ
•
調査アプローチ
–
コストベースオプティマイザトレース（event 10053）
–
アウトラインヒント
•
原因
–
ヒント構文の間違い
•
エイリアスを指定している場合はエイリアスをヒント句に
–
ビューマージなどの考慮不足
•
複数のクエリブロックがあるSQLへのヒント句指定は難しい
•
対処
–
正しいヒント構文を使用。わからない場合はアウトラインヒントを参考に。
–
固定SQL計画ベースライン（SPM）、アウトラインヒントの導入
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘<SQL_ID>',
NULL, 'ADVANCED'));
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
Oracle12.1ってハードパースが遅くないですか
49
オプティマイザのゴールはフェッチも含むトータルの実行時間を短くすること
バージョンがあがるにつれオプティマイザの処理ステップ/時間は増加傾向
•
調査アプローチ
–
SQLトレース
•
原因
–
SQLの書き方
–
適応統計による最適化
•
対処
–
SQLの書き換え
–
適応統計機能の無効化
データベース・パフォーマンスの
MOSドキュメント
Doc ID 1946305.2
インフォメーション・センター: データベース
および Enterprise Manager 日本語ドキュメント
左メニュー：パフォーマンス
を選択
〜 みなさまの投稿をお待ちしております 〜
#OracleTechNight
Copyright © 2017 Oracle and/or its affiliates. All rights reserved.
