津島博士のパフォーマンス講座チューニングの基礎から次期リリース新機能まで 日本オラクル株式会社 クラウド テクノロジー事業統括 Database & Exadata プロダクトマネジメント本部 担当ディレクター津島浩樹 Copyright 2016, Oracle and/or its affiliates. All rights reserved.
以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社 関連会社の米国及びその他の国における登録商標です 文中の社名 商品名等は各社の商標または登録商標である場合があります 2
本日の内容 1 2 3 4 AWR からの解析 SQL チューニングオプティマイザ統計 12.2 新機能 3
AWR からの解析 解析できないもの DB Timeベースのチューニング AWRについて 待機イベント サンプルAWR 4
AWR からの解析解析できないもの データベースに負荷が掛っていない アプリケーション側の問題 データベース処理以外の時間が多い どんな処理か 大量のデータを持ってきてアプリケーションで処理する 必要な ( 処理をした ) データだけを持ってきましょう Row by Row 処理 ( 繰返し処理でデータベースに 1 行づつアクセスする ) 結合などはデータベースで行いましょう バッチ処理で多い OS 統計でリソース (CPU I/O など ) の使用状況から判断 5
AWR からの解析 DB Time ベース チューニング (DB Time とアクティブ セッション ) DB Time( データベース時間 ) すべてのデータベース内処理に要したセッション ( フォアグランド ) の合計時間 (CPU 時間 I/O 時間 非アイドル待機時間が含む ) Active Session( アクティブ セッション ) 現在データベース内で処理を行っている (DB Time 中の ) セッション %Activity( 平均アクティビティ ) 実経過時間とアクティブな ( データベースを使用した ) 時間の割合 アクティブな時間の内訳 (CPU I/O など ) を分析 本を閲覧する一冊の本のレビューを読むカートに入れる精算する SQL = データベースを使用した時間 アクティブ セッション 時間 ユーザ 1 6
AWR からの解析 DB Time ベース チューニング ( 複数セッションのとき ) DB Time: すべてのセッションのデータベース時間の合計 Average Active Sessions( 平均アクティブ セッション ) すべてのセッションの平均アクティビティ (%Activity) の合計 アクティブ セッションが多い時間帯が負荷が多い 時間 t には 2 つのアクティブ セッションが存在する ユーザ 1 ユーザ 2 ユーザ 3 ユーザ n = データベースを使用した時間 t 時間 7
AWR からの解析 DB Time ベース チューニング (DB Time の可視化 ) 平均アクティブ セッション = 全データベース時間 全アクティブ セッションの経過時間 ユーザ1 ユーザ2 ユーザ3 ユーザn t0 t1 4 3 2 1 経過時間 アクティブ セッションの経過時間 アクティブ セッション (DB Time 中のセッション ) 数から負荷を見る 多いところの内訳を分析する 8
AWR からの解析 DB Time ベース チューニング (EM パフォーマンス ページ ) 待機クラスごとのアクティブ セッションの経過 (ASH:Active Session History) カラー領域の合計 = DB Time ある範囲の合計が AWR の Top 5 Timed Events 9
AWR からの解析 DB Time ベース チューニング ( システムのパフォーマンスと DB Time) システム負荷の増加 ( セッション数の増加など ) 競合待機時間などの増加 (=> DB Time の増加 ) DB マシンが CPU ネック CPU 実行時間の増加 (=> DB Time の増加 ) I/O パフォーマンスの低下 I/O 時間の増加 (=> DB Time の増加 ) 待機時間 待機時間 バックグランド (BG) プロセスについて BG プロセスが原因でも DB Time に現れる ( 例 log parallel write が多いと log file sync も増える ) BG プロセスは H/W リソースが問題なければ影響は少ない db file sequential read 競合待機 CPU 実行 db file sequential read 競合待機 CPU 実行 ユーザ 1 I/O 時間 I/O 時間 10
AWR からの解析 AWR について (AWR 内での時間 ) DB Time( データベース時間 ) フォアグランドがデータベース内で処理した合計時間 応答時間 (Elapsed Time) SQL ordered by Elapsed Time(SQL 経過時間の合計 ) SQL で使用した DB Time SQL の応答時間 (Elapsed Time) パラレル実行では全スレーブ プロセスの合計時間が含む DB CPU(CPU 時間 ) DB Time 内での CPU 時間 (DB CPU / DB Time CPU 使用率 ) バックグランド プロセスは (Oracle データベース以外も ) 含まれない CPU リソースの限界は判断できない (CPU% の分母は利用可能な CPU 時間 ) 待機時間が存在しても CPU 使用率が 100% になるときもある CPU 使用率は OS 統計から (AWR はスナップショット間の平均値なので判断できない ) スパイク ( 急激な上昇 ) などが問題になる 11
AWR からの解析 AWR について (Report Summary) Load Profile Oracle データベースに対する負荷 Oracle データベースに対する負荷の変化などを監視 / 比較 Instance Efficiency Percentages (Target 100%) インスタンス内の効率良さ (100% に近いほど効率が良い ) インスタンス チューニングの判断 ( そうでなければ SQL チューニング =>SQL 統計 : by Elapsed Time ) バッファ キャッシュ ヒット率など Top 5 Timed Foreground Events フォアグランド イベント時間 (DB Time の内訳 ) の上位 5( または上位 10) DB CPU が多いときは待機が少ない ( または実行計画が非効率 ) ここで問題は何か ( どのイベントが多いのか ) を特定する 12
AWR からの解析待機イベント ( 体表的なもの ) I/O 非ダイレクト I/O, ダイレクト I/O など <= 第 26 回 エンキュー HW(HWM), SQ( シーケンス ), TX( 行ロック ) など <= 第 18 回 ライブラリ キャッシュ 共有カーソル関係 (cursor: xxx, library cache: xxx など )<= 第 32 回 ライブラリ キャッシュ オブジェクト ( 同じ SQL や PL/SQL パッケージの実行 )<= 第 50 回 マニュアルにない待機イベント SR で問い合わせてください 13
AWR からの解析待機イベント (I/O) 代表的な I/O 統計 db file sequential read( 索引スキャン )/ db file parallel read( 索引の prefech) db file scattered read / direct path read( フル スキャン ) direct path write( ダイレクト ライト その他のライトはバッファ キャッシュ関係の待機 ) direct path read temp / direct path write temp( 一時表への I/O) I/O 性能の確認 ( 問題なければ SQL チューニングへ ) AWR の I/O 統計の Av Rd(ms) など OS 統計 (sar, iostat) の %Busy など SQL の特定 (SQL 統計の SQL ordered by User I/O Wait Time) 実行計画の出力 (AWR のスナップショットから ) $ORACLE_HOME/rdbms/admin/awrsqrpt.sql(Actual Rows が出力されない ) 14
AWR からの解析待機イベント ( 子カーソル ) 子カーソルとは ( 第 7 回 ) 同じ SQL で別の実行計画を作成すること ( ハード パースが増える ) 子カーソルの待機イベント 11.1 以上は cursor: mutex 関係 10.2 以前は library cache pin SQL 統計の SQL ordered by Version Count( 子カーソルの多い SQL) 子カーソルが作成される原因は V$SQL_SHARED_CURSOR を参照 代表的なもの カーディナリティ フィードバック バイド ピーク /Adaptive Cursor Sharing( 優れたカーソル共有 ) バインド変数の属性が異なる スキーマ オブジェクトが異なるなど 15
AWR からの解析サンプル AWR サンプルの AWR を見てみる Execute to Parse % Parse CPU to Parse Elapsd % パースなし ( カーソルキャッシュ ) で実行された割合 パース時間に対する CPU 時間の割合 CPU 時間以外 ( ラッチなど ) が多いか % non-parse CPU パース以外で使用された CPU 時間の割合 16
本日の内容 1 2 3 4 AWRからの解析 SQLチューニングオプティマイザ統計 12.2 新機能 17
SQL チューニング SQL の基本知識 索引を使用しない条件 結合条件 (1=1) WHERE 句条件 (1=2) の注意点 問合せ変換 (Query Transformation) 実行計画の見方 必要最低限のもの 実行計画のチューング チューニング手順 SQL ヒントなど 18
SQL チューニング SQL の基礎知識 ( 索引を使用しない条件 ) 演算している (BI ツールなどは注意 ) NULL 比較 NOT(!=) OR 条件 (IN リスト ) OR 拡張 (UNION ALL に変換 ) で索引を使用 後方一致 ( 中間一致 ) 条件 索引スキップ スキャン (9i から ) 全表スキャンより効果的なとき ( 先頭の個別値が少ないときなど ) に使用 意識して使用する ( 第 9 回 ) 19
SQL チューニング SQL の基礎知識 ( 結合条件 (1=1) WHERE 句条件 (1=2) の注意点 ) 異なる結合を同じ構文で行うようなとき ( 動的 SQL で多い ) 結合 ( 内部結合以外 ) の効率が悪いので行わない ( 第 44 回 ) /* すべてを FULL OUTER JOIN で行う */ IF abc = '01' THEN - すべての行にMAX(c1) を入れる v_from := '(SELECT MAX(c1) c1 FROM tab2 WHERE c2 = 1) B ON (1=1)'; ELSIF abc = '02' THEN -- 結合しない v_from := '(SELECT c1 FROM tab2 WHERE (1=2)) B ON ON A.c1 = B.c1'; ELSE - 通常のFULL OUTER JOIN v_from := 'tab2 B ON A.c1 = B.c1'; END IF v_stmt_str := 'SELECT FROM tab1 A FULL OUTER JOIN ' v_from; OPEN v_tab3_cursor FOR v_stmt_str; 20
SQL チューニング SQL の基礎知識 ( 問合せ変換 ) 無駄な処理の排除 結合 ( 外部キー ) ORDER BY( インライン ビュー内 ) DISTINCT( 主キー )<= 第 34 回 GROUP BY( 結合をしないインライン ビュー内 )<=12c から ビュー ( インライン ビュー ) の最適化 ( 次頁 ) 副問合せのネスト解除 ( セミ結合 アンチ結合 ) IN の方が制約が多いので EXISTS を使用する ( 第 29 回 ) OR 条件で変換されないときがある ( 第 44 回 第 52 回 ) UNION ALL に変換 (OR 拡張 表拡張 <= 索引が使用可と使用不可のパーティションに ) 索引を使用するように ( 第 9 回 第 34 回 ) パラレル実行時の OR 拡張は一部シリアル処理に ( 第 52 回 ) 21
SQL チューニング SQL の基礎知識 ( ビュー インライン ビュー ) ビューとは アクセス制御などで使用 ( 条件は外で指定 ) 最初に実行される 特性 ( メリット / デメリット ) 結合順が調整できない <= 例の tab1 は最後の結合に ビュー実行後は索引が使用できない <= 例のB.c3<100の索引を使用しない 結合前に行数を削減できる (DISTINCT Group Byなど ) <= 例のインライン ビューにGroup byがなくても結果は同じ ( 主問合せで同じSUM 集計を行っているから ) ビューの問合せ変換 ビューを使用しない 結合順を変えたい 索引を使用したい View Merging( ビュー マージ )<= 第 29 回 ビューが効果的なとき ( ビューを作成 ) Group by Placement(Group byの配置 )<= 第 42 回 ビューをマージできない / しない Group by 後にネステッド ループ結合するなど Predicate Pushing( 述語のプッシュ )<= 第 29 回 SQL> SELECT A.c2, SUM(b2), SUM(b3) FROM tab1 A, 2 (SELECT C.c1,C.c3,SUM(C.c2) b2,sum(d.c2) b3 3 FROM tab2 C,tab3 D 4 WHERE C.c1 = D.c1 GROUP BY C.c1,C.c3) B 5 WHERE A.c1 = B.c1 AND B.c3 < 100 6 GROUP BY A.c2 ; 22
SQL チューニング実行計画の見方 ( 注目する項目 ) カーディナリティ 述語を適用した行数 (Rows) アクセス方法 索引スキャン 全表スキャン ビューアクセス 結合方法 ネステッド ループ結合 ハッシュ結合 ソート マージ結合 直積結合 結合タイプ 内部結合 外部結合 セミ結合 アンチ結合 結合順序 SQL によって決まる場合も パーティション パーティション プルーニング ( 第 22 回 第 46 回 ) 静的 ( アクセスする番号 ) 動的 (KEY KEY(OR) など ) 11gR2 から KEY(AP) が追加 AND Pruning( 静的 + 動的 ) パラレル実行 スキャンのデータ分散 ( 第 20 回 第 39 回 ) ブロック単位で分割 (PX BLOCK ITERATOR) パーティション単位 (PX PARTITION RANGE ALL など ) パーティション ワイズ結合など 23
SQL チューニング実行計画の見方 ( 注目する項目 ) パラレル実行 ( 続き ) スキャン以外のデータ再分散 ( 第 20 回 第 39 回 ) HASH( 基本はこれを使用する ) RANGE( ソートなど )<= 偏りやすい BROADCAST( 結合時の片方が小さい ) 同じデータをすべてのプロセスに PARTITION( パーティション ワイズ結合 ) パーティション分割 ( 片方がパーティション表 ) NONE( データ分散しない / データ通信しない ) PX SEND xxxxとpx RECEIVEがない PARTITIONやBROADCASTなどと使用 偏りが発生すると効果が低下する PQ_DISTRIBUTE ヒントで調整 作間パラレル化PX SEND xxxx PX RECEIVE QC OrderBy PQ PQ PQ PQ 結合表操PQ PQ PQ PQ スキャン PQ PQ PQ PQ 操作内パラレル化 3 1 2 24
SQL チューニング実行計画の見方 リーフ ステップ ( インデントの一番深いステップ ) から実行して 結合 ( 同一インデント ) は上位に表示されたものが最初になる SQL> SELECT FROM tab1,tab2,tab3 WHERE tab1.c2=tab2.c2 AND tab1.c3=tab3.c3 AND tab1.c1<100 2 GROUP BY ORDER BY ; TABLE ACCESS BY INDEX ROWID TAB1 結合 INDEX RANGE SCAN IX_TAB1 実行計画 ----------------------------------------------< >----------------- 索引アクセス Id Operation Name Rows Pstart Pstop VIEW ----------------------------------------------< >----------------- HASH GROUP BY 0 SELECT STATEMENT TABLE ACCESS FULL TAB1 1 SORT GROUP BY 3 ビュー アクセス * 2 HASH JOIN 2 * 3 HASH JOIN 1 パーティション プルー * 4 (1) TABLE ACCESS FULL TAB1 10 1 1 ニング ( 動的 :KEYなど) 5 (2) TABLE ACCESS FULL TAB2 50 カーディナリティ 6 (3) TABLE ACCESS FULL TAB3 100 ( 絞り込まれた行数 ) 25
SQL チューニング実行計画の見方 ( パラレル実行 ) SQL> SELECT * FROM tab01,tab02 WHERE tab01.c1 = tab02.c1 ORDER BY c0; 実行計画 結合プロセス ( 同じ ) ------------------------------------------------< >------------------------------- Id Operation Name TQ IN-OUT PQ Distrib ------------------------------------------------< >------------------------------- 0 SELECT STATEMENT 1 PX COORDINATOR 2 PX SEND QC (ORDER) :TQ10003 Q1,03 P->S QC (ORDER) 3 SORT ORDER BY Q1,03 PCWP 4 PX RECEIVE Q1,03 PCWP 5 PX SEND RANGE :TQ10002 Q1,02 P->P RANGE 6 HASH JOIN BUFFERED Q1,02 PCWP 7 PX RECEIVE Q1,02 PCWP 8 PX SEND HASH :TQ10000 Q1,00 P->P HASH 9 PX BLOCK ITERATOR Q1,00 PCWC 10 TABLE ACCESS FULL TAB01 Q1,00 PCWP 11 PX RECEIVE Q1,02 PCWP 12 PX SEND HASH :TQ10001 Q1,01 P->P HASH 13 PX BLOCK ITERATOR Q1,01 PCWC 14 TABLE ACCESS FULL TAB02 Q1,01 PCWP PX SEND PARTITION PX SEND BROADCAST データ分散 PX PARTITION RANGE ALL スキャンの分散 26
SQL チューニング実行計画の見方 後半には 述語の情報と Note 部が出力される 実行計画 -------------------------------------- Id Operation Name -------------------------------------- 0 SELECT STATEMENT 1 SORT GROUP BY * 2 HASH JOIN * 3 HASH JOIN * 4 TABLE ACCESS FULL TAB1 5 TABLE ACCESS FULL TAB2 Predicate Information (identified by operation id): --------------------------------------------------- 述語の情報 2 - access("tab1"."c2"="tab2"."c2") 3 access("tab1"."c3"="tab3"."c3") 4 filter("tab1"."c1"<100) Note 部 Note ----- - dynamic sampling used for this statement (level=2) 27
SQL チューニング実行計画の見方 ( ネステッド ループ結合 ) 通常のネステッド ループ結合 基本は内部表の索引を使用して結合 駆動表の 1 行に対して索引アクセス Multi Join Key Pre-fetching(9i から ) 第 34 回 索引範囲スキャンのデータ ブロックの先読み (Index Range Scan のみ ) Nested Loops Join Batching(11gから ) 第 34 回 索引で結合後にROWIDを並べ替えてテーブルにアクセス Index Unique Scanでも可 SQL> SELECT FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY ; 実行計画 ------------------------------------------------- Id Operation Name ------------------------------------------------- 0 SELECT STATEMENT 1 HASH GROUP BY 2 NESTED LOOPS 実行計画 3 TABLE ACCESS FULL TAB2 <- 駆動表 ------------------------------------------------ 4 TABLE ACCESS BY INDEX ROWID TAB1 <- 内部表 * Id 5 Operation INDEX RANGE SCAN Name IX_TAB1 ------------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 TABLE ACCESS BY INDEX ROWID TAB1 内部表をPer-fetchする 3 NESTED LOOPS 実行計画 4 TABLE ACCESS FULL TAB2 駆動表 ------------------------------------------------- * 5 INDEX RANGE SCAN IX_TAB1 内部表 Id Operation Name ------------------------------------------------- 0 SELECT STATEMENT 結果を駆動表 (2) として 1 HASH GROUP BY 2 NESTED LOOPS Nested Loops Join(2) 3 NESTED LOOPS Nested Loops Join(1) 4 TABLE ACCESS FULL TAB2 駆動表 (1) * 5 INDEX RANGE SCAN IX_TAB1 内部表 (1) 6 TABLE ACCESS BY INDEX ROWID TAB1 内部表 (2)( ここを改善 ) 28
SQL チューニング実行計画の見方 ( ソート マージ結合 直積結合 ) ソート マージ結合 索引の代わりにソートして結合 等価結合以外など 直積結合 結合条件がないので効率が悪い できるだけ行わない ( ソートも行われる ) SQL> SELECT FROM tab1,tab2 WHERE tab1.c1 > tab2.c1 2 GROUP BY ; 実行計画 --------------------------------------------- Id Operation Name Rows --------------------------------------------- 0 SELECT STATEMENT 1 HASH GROUP BY 2 MERGE JOIN xxx 3 SORT JOIN 100 4 TABLE ACCESS FULL TAB2 100 * 5 SORT JOIN 100K 6 TABLE ACCESS FULL TAB1 100K SQL> SELECT FROM tab1,tab2 GROUP BY ; 実行計画 ---------------------------------------------- Id Operation Name Rows ---------------------------------------------- 0 SELECT STATEMENT 1 HASH GROUP BY 2 MERGE JOIN CARTESIAN 3 TABLE ACCESS FULL TAB2 100 4 BUFFER SORT 100K 5 TABLE ACCESS FULL TAB1 100K 29
SQL チューニング実行計画の見方 ( ハッシュ結合 外部結合 ) ハッシュ結合 ( 等価結合のみ ) 索引の代わりにメモリ上にハッシュ テーブルを作成 ( 最初にアクセスした表に ) スター スキーマはRight-deep Joinが効果的 (SWAP_JOIN_INPUTSヒント)<= 第 46 回 外部結合 LEFT OUTER JOIN, RIGHT OUTER JOIN ハッシュ結合で核でないテーブルを先にアクセス可能に (10gから) SQL> SELECT FROM tab1,tab2,tab3 2 WHERE tab1.c1=tab2.c1 AND tab1.c2=tab3.c2 3 AND tab2.c3=xxx AND tab3.c2=xxx GROUP BY ; 実行計画 (Left-deep Join) 実行計画 (Right-deep Join) ------------------------------------- ------------------------------------- Id Operation Name Id Operation Name ------------------------------------- ------------------------------------- 0 SELECT STATEMENT 0 SELECT STATEMENT 1 HASH GROUP BY 1 HASH GROUP BY * 2 HASH JOIN * 2 HASH JOIN * 3 HASH JOIN * 3 TABLE ACCESS FULL TAB3 * 4 TABLE ACCESS FULL TAB2 * 4 HASH JOIN 5 TABLE ACCESS FULL TAB1 * 5 TABLE ACCESS FULL TAB2 * 6 TABLE ACCESS FULL TAB3 6 TABLE ACCESS FULL TAB1 SQL> SELECT FROM tab1 LEFT OUTER JOIN tab2 USING (c1) GROUP BY ; 実行計画 ------------------------------------ Id Operation Name ------------------------------------ 実行計画 (Oracle Database 10gから ) 0 SELECT STATEMENT --------------------------------------- 1 HASH GROUP BY Id Operation Name 2 HASH JOIN OUTER --------------------------------------- 3 TABLE ACCESS FULL TAB1 0 SELECT STATEMENT 4 TABLE ACCESS FULL TAB2 1 HASH GROUP BY 2 HASH JOIN RIGHT OUTER 3 TABLE ACCESS FULL TAB2 4 TABLE ACCESS FULL TAB1 30
SQL チューニング実行計画の見方 ( セミ結合 アンチ結合 ) セミ結合 EXISTS, IN 条件の副問合せ セミ ハッシュ結合で副問合せのテーブルを先にアクセス可能に (10gから) アンチ結合 NOT EXISTS, NOT IN 条件の副問合せ アンチ ハッシュ結合で副問合せのテーブルを先にアクセス可能に (10gから) SQL> SELECT FROM tab1 WHERE EXISTS 2 (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ; 実行計画 ----------------------------------- Id Operation Name ----------------------------------- 0 SELECT STATEMENT 1 HASH JOIN SEMI 2 TABLE ACCESS FULL TAB1 3 TABLE ACCESS FULL TAB2 実行計画 (Oracle Database 10gから ) ------------------------------------- Id Operation Name ------------------------------------- 0 SELECT STATEMENT 1 HASH JOIN RIGHT SEMI 2 TABLE ACCESS FULL TAB2 3 TABLE ACCESS FULL TAB1 SQL> SELECT FROM tab1 WHERE NOT EXISTS 2 (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ; 実行計画 ----------------------------------- Id Operation Name ----------------------------------- 0 SELECT STATEMENT 1 HASH JOIN ANTI 2 TABLE ACCESS FULL TAB1 3 TABLE ACCESS FULL TAB2 実行計画 (Oracle Database 10gから ) ------------------------------------- Id Operation Name ------------------------------------- 0 SELECT STATEMENT 1 HASH JOIN RIGHT ANTI 2 TABLE ACCESS FULL TAB2 3 TABLE ACCESS FULL TAB1 31
SQL チューニング実行計画のチューニング チューニングの手順 1. オプティマイザ統計の再収集 2. 索引の作成 ( 索引のチューニング ) 3. SQL の変更 ( ヒントの追加 ) SQL の変更ができない SPM (SQL Plan Management) の SQL 計画手動ロード ( 第 38 回 ) ヒントを入れた実行計画をベースラインとして登録する SQL 翻訳フレームワーク (12c から ) 第 51 回 SQL を変更したいとき (SQL の置き換え方法を登録 ) 問題を特定できない SQL チューニング アドバイザ ( 第 38 回 ) 32
SQL チューニング問題となる実行計画 ( 主な SQL) と対処 問題点統計ヒント SQL などの変更 結合方法や結合順が最適でない 副問合せの追加 / 削除など 索引が使用できていない 索引を使用できるように変更 索引を作成 問合せ変換が最適でない 明示的に SQL を変更 同じような副問合せがある SQL を分割する (WITH 句を使用する ) 第 11 回 同じ表に異なる条件で SELECT している SQL を CASE 式で 1 つに ( 第 24 回 ) 同じ表に異なる条件で INSERT している SQL をマルチ テーブル インサートに ( 同じ表でも可 ) 第 24 回 同じ表を UPDATE 文と INSERT 文でアクセス SQL を MERGE 文で 1 つに ( 第 30 回 ) 結合した UPDATE 文 SQL( 複雑な副問合せを使用する ) を MERGE 文に ( 第 30 回 ) Redo ログ出力がネック ( 特にパラレル DML) ダイレクト パス インサートに (UPDATE DELETE も ) 第 15 回 TEMP 領域を使用 パラレル度を上げる プログラムを分割 / 並列化 索引 パーティション ( パーティション ワイズ ) を使用 ( 第 45 回 ) PL/SQLプロシージャが遅い バルク処理 パラレル化 (DBMS_PARALLEL_EXECUTE) 第 11 回 テーブル ファンクションの使用を検討 ( 第 25 回 ) 33
SQL チューニングオプティマイザ統計の再収集 行数の見積もりが正しくない ( 実行時と大きく異なるとき ) 実行計画の見積もり行数 (E-Rows) と実行行数 (A-Rows) を比較 SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1,c2,c3 FROM tab01 WHERE c1 = 11 ; レコードが選択されませんでした SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'typical allstats last')); 実行計画 --------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows E-Bytes A-Rows --------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 0 1 TABLE ACCESS BY INDEX ROWID TAB01 1 21 189 0 * 2 INDEX RANGE SCAN IX_TAB01 1 21 0 34
SQL チューニング実行計画の確認 (DBMS_XPLAN.DISPLAY_CURSOR 関数 ) 見積もり以外に実行時の統計も出力できる (STATISTICS_LEVEL=ALL またはヒント ) SQL> SELECET /*+ GATHER_PLAN_STATISTICS */ ; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'<sql_id>',format=>'typical allstats last')); 実行計画省略すると最後のSQL ------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time ------------------------------------------------------------------------------------------------- ------------------------------------------- Buffers OMem 1Mem Used-Mem Used-Tmp ------------------------------------------- < 実行統計 > Starts: 実行された回数 Buffers: バッファのアクセス数 A-Rows: 処理行数 A-Time: 処理時間 Used-Mem: 使用されたメモリサイズと処理方法 (0:Optimal,1:1-pass,N;Multi-pass) Used-Tmp: 使用されたTEMPサイズ 35
SQL チューニング実行計画の確認 ( リアルタイム SQL 監視 ) 実行中の実行計画を表示 ( デフォルトで 5 秒以上の SQL MONITOR ヒントで強制的に ) 時間のかかっている SQL が自動的に監視されリストされる ( 経過時間等でソート可能 ) この SQL 実行全体の統計 実行計画のステップごとの統計など ステップごとの待機イベント 36
SQL チューニングオプティマイザ統計の再収集 サンプル サイズを大きくしても精度が上がらないとき ヒストグラムと拡張統計を確認 DBA_TAB_COL_STATISTICS DBA_STAT_EXTENSIONS 拡張統計は自動的に作成されない <=12c で改善 列グループ ( ないときは列値の組合せは均等となる ) WHERE c1 = xxx AND c2 = xxx( フィルター条件 結合 ) GROUP BY c1,c2 式 (BI ツールを使用しているときなど ) WHERE UPPER(c1) = xxx 一意値が多い列のヒストグラムには限界がある バケット数の最大が 254(12c から 2048 に拡張 ) 列サイズも 12c から拡張 (32 バイトから 64 バイト ) 37
SQL チューニング索引の作成 ( 索引のチューニング ) 全表スキャンを行っている表は索引スキャンした方が良くないか 実行計画の Rows を確認 索引スキャンの効率が悪くないか 索引の列の組合せ 索引の列の順番などを確認 例えば 以下のような SQL SQL> SELECT WHERE c1=xx AND c2=xx AND c3=xx; SQL> SELECT WHERE c1=xx AND c3=xx; SQL> SELECT WHERE c1=xx AND c2=xx AND c4=xx; 優先順位を明確にして作成する これが難しい ( そのため フル スキャンでも高速な Exadata が効果的 ) 作り過ぎないように使用しない索引は削除する 38
SQL チューニング SQL の変更 ( 主な SQL の変更 ) 結合の UPDATE 文を MERGE 文に SQL> UPDATE t01 A 2 SET A.c3 = A.c3 + (SELECT c2 FROM t02 B WHERE A.c1 = B.c1) 3 WHERE EXISTS (SELECT 0 FROM t02 B WHERE A.c1 = B.c1); SQL> MERGE INTO t01 A 2 USING (SELECT c1,c2 FROM t02) B 3 ON (A.c1 = B.c1) 4 WHEN MATCHED THEN UPDATE SET A.c3 = A.c3 + B.c2; 繰り返し副問合せは WITH 句で SQL> SELECT * FROM 2 (SELECT 部門,sum( 売上 ) 部門売上 FROM 売上表 GROUP BY 部門 ) w_a 3 WHERE 部門売上 < (SELECT avg( 部門売上 ) FROM 4 (SELECT 部門,sum( 売上 ) 部門売上 FROM 売上表 GROUP BY 部門 )); SQL> WITH w_a AS (SELECT 部門, sum( 売上 ) FROM 売上表 GROUP BY 部門 ) 2 SELECT * FROM w_a 3 WHERE 部門売上 < (SELECT avg( 部門売上 ) FROM w_a); UPDATE DELETE もダイレクト インサートに Nologging にすると効果的だが Data Guard などで災害対策サイトを作成するときは表圧縮が効果的 ブロック イメージの Redo ログが削減される SQL> DELETE FROM tab000 2 WHERE 日付 < TO_DATE( 20101001, YYYYMMDD ) ; SQL> CREATE TABLE tab001 NOLOGGING PARALLEL AS 2 SELECT * FROM tab000 3 WHERE 日付 >= TO_DATE( 20101001, YYYYMMDD ) ; SQL> DROP TABLE tab000 ; SQL> RENAME tab001 TO tab000 ; 39
SQL チューニング SQL の変更 ( 主な SQL の変更 ) 同じ表に異なる条件での SELECT は CASE 式に 索引アクセスが効果的なときは UNION ALL SQL> SELECT c2,c4,... FROM tab01 WHERE c1 = '01'... 2 UNION ALL 3 SELECT c3,c5,... FROM tab01 WHERE c1!= '01'... ; SQL> SELECT CASE WHEN c1 = '01' THEN c2 ELSE c3 END a1, 2 CASE WHEN c1 = '01' THEN c4 ELSE c5 END a2,... 3 FROM tab01 WHERE... ; 異なる条件での INSERT はマルチ テーブル インサートに SQL> INSERT INTO tab02 2 SELECT c2,c4,... FROM tab01 WHERE c1 = '01'... ; SQL> INSERT into tab02 2 SELECT c3,c5,... FROM tab01 WHERE c1!= '01'... ; SQL> INSERT FIRST 2 WHEN c1 = '01' THEN INTO tab02 VALUES (c2,c4,...); 3 WHEN c1!= '01' THEN INTO tab02 VALUES (c3,c5,...); 4 SELECT c2,c3,c4,c5,... FROM tab1 WHERE... ; テーブル ファンクション SQL> SELECT * 2 FROM TABLE(test_pkg.func02(CURSOR(SELECT * FROM tab02))) 3 JOIN tab01 USING (c1); 40
SQL チューニング SQL の変更 ( ヒント ) 表の別名があるときは別名を SQL> SELECT /*+ INDEX(A ix_tab1) */ * FROM tab1 A WHERE ; ビュー ( 副問合せ ) 内の表には -- グローバル ヒント (ANSI 準拠の結合文では使用できない ) SQL> SELECT /*+ INDEX(A.tab1 ix_tab1) */ * 2 FROM (SELECT * FROM tab1 WHERE ) A ; -- 問合せブロック付きヒント SQL> SELECT /*+ INDEX(@SEL$2 tab1 ix_tab1) */ * 2 FROM (SELECT * FROM tab1 WHERE ) A ; ビュー マージされても使用されるが LEADING ヒントではオブジェクト別名を SQL> SELECT /*+ LEADING(tab1 v2.tab2 v2.tab3) MERGE(v2) */ * 2 FROM tab1,v2 WHERE tab1.c2=v2.c2; SQL> SELECT /*+ LEADING(tab1@SEL$1 tab2@sel$2 tab3@sel$2) MERGE(v2) */ 2 * FROM tab1,v2 WHERE tab1.c2=v2.c2; 主に使用するヒント 結合順を変える (ORDERED, LEADING) ビュー マージを止める (NO_MERGE) 索引を使用する (INDEX, INDEX_FFS) 索引を使用しない (FULL, NO_INDEX) 結合方法を変える (USE_HASH, USE_NL, USE_MERGE) パラレル実行関係 (PARALLEL, PQ_DISTRIBUTE) 問合せ変換関連のヒント <= 第 55 回 41
本日の内容 1 2 3 4 AWRからの解析 SQLチューニングオプティマイザ統計 12.2 新機能 42
オプティマイザ統計 統計の種類 どのように使用されるか 収集方法 収集方法とデフォルトで収集されるもの 統計の補正 どこまで行えるか 43
オプティマイザ統計統計の種類 表統計 行数 データ ブロック数 行連鎖 行移行の数 平均行長 索引統計 索引内の個別値数 索引の深さ (BLEVEL) リーフ ブロック数 クラスタ化係数 列統計 表アクセス コスト 索引アクセス コスト カーディナリティ サイズ 個別値数 NULL 数 平均列データ長 データ分布 ( 最小値と最大値 ヒストグラム ) システム統計 (I/O+CPU コスト モデル ) CPU 性能 I/O 性能 ( 単一ブロック リード マルチ ブロック リード ) パーティション表には パーティション サブ パーティションごとにも同様の情報を収集する アクセスする行数やブロック数を求め CPU 性能や I/O 性能でコストを計算する 44
オプティマイザ統計統計の種類 列統計 ( ヒストグラム ) ヒストグラムは完全には設定できないものも (12c で拡張 )<= 第 35 回 異なる値が 255 以上の高さ調整済ヒストグラム バケット数が最大 254 なので 各値を別バケットに入れられない 拡張統計 ( 自動作成されない ) システム統計 (CPU 性能 I/O 性能 ) Exadata は固有のシステム統計を取得する (DB 稼働後一度だけで良い ) DBMS_STATS.GATHER_SYSTEM_STATS( EXADATA ); Exadata 以外 初回起動時のデフォルト値 ( 単一ブロック リード マルチ ブロック リード時間などがない ) で良いが 索引スキャンとフル スキャンを使用する場合は一度収集した方が良い 45
オプティマイザ統計統計の種類 ( 拡張統計 ) 列グループの統計 (Column Groups) 同一表内の複数列に跨る統計を保持することで 列データ間の相関関係を考慮したカーディナリティの計算を可能とする 例 : 顧客表の住所列と年代列など 12c からの SQL 計画ディレクティブ ( 第 52 回 ) と自動列グループ検出 ( 第 49 回 ) 式の統計 (Expression Statistics) 関数や演算式を含めた統計を保持することで WHERE 句におけるカーディナリティの計算を可能する 例 :Where UPPER( 氏名 ) = :B1 46
オプティマイザ統計統計収集 自動オプティマイザ統計収集 22:00 から 26:00( 土日は 6:00 から 26:00) に自動実行する 独自の方式で収集しているシステム以外はこれを使用する ディクショナリだけの自動収集も可能 DBMS_STATS.SET_GLOBAL_PREFS( AUTOSTATS_TARGET, ORACLE ) オプティマイザ統計の手動収集 必要な ( 大量に変更があった ) ときを判断して実行する ディクショナリの手動収集 DDL を多く発行された後などに実行 DBMS_STATS.GATHER_DICTIONARY_STATS 47
オプティマイザ統計統計収集 ( 自動オプティマイザ統計収集 ) 自動的に収集するものを決める デフォルトは 10% 以上変更されたオブジェクト デフォルト値 収集する適切なサイズを Oracle が決定 (AUTO_SAMPLE_SIAZE) Oracle11g で拡張されたハッシュ アルゴリズム ( サンプリングより高速 Compute モード統計と同等精度 ) 索引統計の収集を自動判断 (AUTO_CASCADE) ヒストグラムの収集を自動判断 (FOR ALL COLUMNS SIZE AUTO) カーソルの無効化の時期を Oracle が決定 (AUTO_INVALIDATE) 収集後に共有プール上の実行計画が正しくなくなる ディクショナリもデフォルトで収集される 48
オプティマイザ統計統計収集 ( 自動オプティマイザ統計収集 ) デフォルトで収集しないものがあるので注意 拡張統計 列グループ統計 式の統計 固定オブジェクト 動的パフォーマンス ビュー (v$ ビュー ) の実表 (x$ 表 ) アプリケーションの変更やデータベース構成の変更の時に手動で再収集する DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; AWR や Statspack でも使用されているので 取得に時間が掛るような時も 一時表 (Global Temporary Table) 正しく収集できないので動的サンプリングを使用する 12c からセッション固有統計が提供されたので手動でも収集しやすくなった <= 第 35 回 49
オプティマイザ統計統計の補正 動的サンプリング (11g まで ) 11gR2 からパラレル実行時にレベルを自動決定 ( 大きな表 複雑な WHERE 句 ) カーディナリティ フィードバック (11g まで ) 見積りと実行時の統計が大きく異なると記録して 2 回目から使用する ( 単一表カーディナリティのみ ) 適応問合せ最適化 (Adaptive Query Optimization)<=12c から ( 第 33 回 ) 適応計画 (Adaptive Plans) 実行時の最適化 結合方法 (Join Methods) パラレル分散方法 (Parallel Distribution Methods) 適応統計 (Adaptive Statistics) 次回実行時以降の最適化 動的統計 (Dynamic Statistics) SQL 計画ディレクティブからも起動 サンプル サイズの自動調整 統計の再利用や他の問合せでも利用可 自動再最適化 (Automatic Reoptimization) 統計フィードバック ( 結合カーディナリティも ) SQL 計画ディレクティブ (SQL Plan Directives) <= 第 52 回 50
オプティマイザ統計統計の補正 ( 適応問合せ最適化 ) SQL 文実行時に統計が不十分な場合の動作 使用可能な SQL 計画ディレクティブがある SQL 文の実行 ( 初回 ) 適用計画 実行時統計のメモリ上への保存 SQL 計画ディレクティブの作成 カーソルがエージアウトなどで存在しない場合 同じ SQL 文の実行 カーソルが残っている場合 ディレクティブから動的統計の収集 ( 統計の再利用も可 ) 自動再最適化 ( 統計フィードバック ) を実施 統計収集 (GATHER_xxx_STATS) SQL 計画ディレクティブから列グループ統計を追加 (SQL 計画ディレクティブは使用されなくなる ) 51
本日の内容 1 2 3 4 AWRからの解析 SQLチューニングオプティマイザ統計 12.2 新機能 52
12.2 新機能 オプティマイザ 適応問合せ最適化 (Adaptive Query Optimization) の変更 拡張統計 ( 式の統計 ) オプティマイザ統計アドバイザー (Optimizer Statistics Advisor) インクリメンタル グローバル統計の強化 SQL 処理 OR Expansion(OR 拡張 ) の強化 Recursive WITH( 再帰問合せ ) の強化 Cursor-Duration Temporary Tables( 一時表変換の強化 ) Partition-wise DISTINCTの強化 Band Join( 範囲条件結合 ) の強化 B ツリー索引圧縮の強化 53
12.2 新機能 ( オプティマイザ ) Adaptive Query Optimization( パラメータの変更 ) Oracle 12.1 optimizer_adaptive_features optimizer_adaptive_plans Default: TRUE optimizer_adaptive_statistics Default: FALSE Oracle 12.2 Adaptive Plans Adaptive Statistics 新しいデフォルトとより細かい制御 パフォーマンスの安定性重視 (Minimal Adaptive) やや保守的な Adaptive アプローチ (Default Adaptive) optimizer_adaptive_plans FALSE TRUE TRUE optimizer_adaptive_statistics FALSE FALSE TRUE 新規の複雑な問合せにもベストパフォーマンス (Fully Adaptive) 54
12.2 新機能 ( オプティマイザ ) Adaptive Query Optimization( 列グループ統計の収集 ) 列グループ統計を自動的に作成するかを指定可能に AUTO_STAT_EXTENSIONS の追加 DBMS_STATS のプリファレンス パラメータ デフォルトは OFF SQL 計画ディレクティブから列グループ統計を作成しない 12.1 でもパッチを当てれば同様 相関関係 国 姓 55
12.2 新機能 ( オプティマイザ ) Adaptive Query Optimization(Fully Adaptive) Adaptive Plans 12c Optimizer 統計フィードバック ( 単一表カーディナリティと結合カーディナリティ ) 動的統計 (Adaptive) Fully Adaptive 列使用情報統計 ヒストグラム GATHER_xxx_STATS (FOR ALL COLUMNS SIZE AUTO) SQL 計画ディレクティブ DBMS_STATS プリファレンス : AUTO_STAT_EXTENSIONS=ON ( デフォルト OFF) 列グループ統計 56
12.2 新機能 ( オプティマイザ ) Adaptive Query Optimization(Default Adaptive) 12c Optimizer 統計フィードバック ( 単一表カーディナリティのみ ) Adaptive Plans 動的統計 Default Adaptive 列使用情報統計 ヒストグラム GATHER_xxx_STATS (FOR ALL COLUMNS SIZE AUTO) SQL 計画ディレクティブ DBMS_STATS プリファレンス : AUTO_STAT_EXTENSIONS=ON ( デフォルト OFF) 列グループ統計 57
12.2 新機能 ( オプティマイザ ) Adaptive Query Optimization(Minimal Adaptive) 12c Optimizer 統計フィードバック ( 単一表カーディナリティのみ ) 動的統計 列使用情報統計 SQL 計画ディレクティブ Minimal Adaptive ヒストグラム GATHER_xxx_STATS (FOR ALL COLUMNS SIZE AUTO) 58
12.2 新機能 ( オプティマイザ ) OPTIMIZER_ADAPTIVE_STATISTICS=TRUE 有効なとき 長時間実行の問合せ 複雑な問合せ 複雑なスキーマ 複雑なデータ Ad-hoc 問合せの強化 SQL 計画ディレクティブにより オプティマイザが SQL から学び その情報を他と共有することが可能 より多くの SQL を最適な実行計画にする FALSE では単一表の統計フィードバックと列グループ統計の作成 / 収集のみ Ad-hoc 問合せでは統計収集するまで初回実行が遅くなる ( 結合が多いと速くできない場合も ) 59
12.2 新機能 ( オプティマイザ ) 式の統計 (Expression Statistics Store :ESS) SELECT リスト WHERE 句 GROUP BY 句などの演算式を格納 DBA_EXPRESSION_STATISTICS で参照可能 SQL> SELECT c3 FROM abc1 WHERE c1+c2 < 105; SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SQL> SELECT table_name,expression_id,snapshot,evaluation_count,fixed_cost,dynamic_cost,expression_text,last_modified 2 FROM dba_expression_statistics WHERE table_name = 'ABC1'; TABLE_NAME EXPRESSION_ID SNAPSHOT EVALUATION_COUNT FIXED_COST DYNAMIC_COST EXPRESSION_TEXT LAST_MOD ---------- ------------- ---------- ---------------- ---------- ------------ ------------------------------ -------- ABC1 1.6850E+19 LATEST 1 3.1710E-08 0 "C3" 16-09-09 ABC1 1.1808E+19 LATEST 1 1.5855E-06 0 "C1"+"C2" 16-09-09 オプティマイザ統計には反映されない DBIM の In-Memory Expressions で使用され仮想列を作成する 60
12.2 新機能 ( オプティマイザ ) In-Memory Expressions( 自動キャプチャ ) Expression Statistic Store(ESS) Capture Top N Expressions In-Memory Expressions automatically added to IM Column Store EXPRESSION TEXT COUNT COST IM Column store A+5 UPPER(x) C*D ESS は 常にワークロードを監視 問合せパース中に 頻度とコストをベースに hot な演算式を記録 新しいプロシージャを使用して Top N の式をキャプチャ DBMS_INMEMORY_ADMIN. IME_CAPTURE_EXPRESSIONS 新しいプロシージャを使用して IM 列ストアに隠し仮想列を作成してポピュレートする DBMS_INMEMORY_ADMIN. IME_POPULATE_EXPRESSIONS 61
12.2 新機能 ( オプティマイザ ) Optimizer Statistics Advisor オプティマイザ統計の収集が現在のベストプラクティスに従っているかをルールセットを使用して確認する 過去から引き継いでいる資産 ( スクリプト ) が使われていることがよくあり 最適ではない設定でオプティマイザ統計が収集されている 調査結果と推奨事項を提示するレポートを生成 アクションを使用して推奨事項を実施するための SQL スクリプトを生成 メンテナンス ウィンドウ中のジョブでの実行がデフォルトで設定される Rules Findings Recommendations Actions 62
12.2 新機能 ( オプティマイザ ) Optimizer Statistics Advisor( アドバイザーのルール ) 12 個のルールからアドバイスする ID NAME RULE_TYPE DESCRIPTION 1UseAutoJob SYSTEM 自動ジョブで統計収集する 2CompleteAutoJob SYSTEM メンテナンス時間枠 (window) でジョブ実行が失敗している 3MaintainStatsHistory SYSTEM 統計履歴表が肥大化している 4UseConcurrent SYSTEM 統計収集に Concurrent プリファレンスを使用している 5UseDefaultPreference SYSTEM 統計収集にデフォルト設定を使用している 6TurnOnSQLPlanDirective SYSTEM SQL Plan Directive(SPD) を有効化すべき 7AvoidSetProcedures OPERATION Set Statistics プロシージャを避ける 8UseDefaultParams OPERATION 統計収集はデフォルト パラメータを使用する 9UseGatherSchemaStats OPERATION gather_schema_stats プロシージャを使用する 10AvoidInefficientStatsOprSeq OPERATION 非効率的な統計の動作シーケンスを避ける 11AvoidUnnecessaryStatsCollection OBJECT 不要な統計収集を避ける 12AvoidStaleStats OBJECT 古くなった または統計のないオブジェクトを避ける 63
12.2 新機能 ( オプティマイザ ) Optimizer Statistics Advisor( カスタマイズ ) アドバイザーのスコープは 3 種類のフィルターによって狭めることが可能 オブジェクト ( スキーマ名 オブジェクト名 ) オペレーション (DBMS_STATS のプロシージャ ) ルール ( アドバイザー ルール ) 特定のオブジェクト (SH スキーマの COUNTRIES 表 ) を除外する例 -- Turn off validation/reporting for table SH.COUNTRIES DECLARE tname VARCHAR2(32767) := 'demo'; -- タスク名 filter_report clob; -- report of operation BEGIN filter_report := dbms_stats.configure_advisor_obj_filter(tname, NULL, NULL,'SH','COUNTRIES','DISABLE ); END; / 64
12.2 新機能 ( オプティマイザ ) Optimizer Statistics Advisor( 統計プリファレンスの制御 ) 強制的なデフォルト値での収集 プリファレンス PREFERENCE_OVERRIDES_PARAMETER の追加 統計プリファレンス パラメータを強制的にデフォルト値で収集可能に DBMS_STATS.GATHER_XXX_STATS の入力パラメータを無視する TRUE で入力パラメータを無視する ( デフォルトは FALSE) 様々なところで行っているオプティマイザ統計収集に対して 統計プリファレンス パラメータの値を強制的に設定したいときなどに有効 アドバイザーで デフォルト パラエメータを使用する とアドバイスされても簡単に変えられない 65
12.2 新機能 ( オプティマイザ ) インクリメンタル グローバル統計の強化 ( 第 33 回 ) Sales Table 2012/10/18 1. パーティション レベル統計が収集されシノプシスを作成 2012/10/19 2012/10/20 2012/10/21 2012/10/22 2. パーティションレベルの統計とシノプシスを集計することでグローバル統計を作成する 6. 既存と新しい一つのパーティションのシノプシスを集計することでグローバル統計を作成する 2012/10/23 2012/10/24 4. 新しいパーティションのパーティション統計を収集 3. 新しいパーティションをテーブルに追加してデータロード SYSAUX 表領域 5. SYSAUX から他のパーティションのシノプシスを取得 66
12.2 新機能 ( オプティマイザ ) インクリメンタル グローバル統計の強化 ( 新しい収集アルゴリズム ) シノプシスの収集を新しいアルゴリズムに DBMS_STATS プレファレンスに NDV 収集アルゴリズム指定を追加 AUTO_SAMPLE_SIZE と同等のアルゴリズム プレファレンス APPROXIMATE_NDV_ALGORITHM の追加 REPEAT OR HYPERLOGLOG ( デフォルト ) 古いフォーマットのシノプシスが存在する場合古いアルゴリズムを使い続ける シノプシスが存在しない または 新しいフォーマットのものがある場合 新アルゴリズムを使用 ADAPTIVE SAMPLING 強制的に古いアルゴリズムを使用 'HYPERLOGLOG 強制的に新しいアルゴリズムを使用 67
12.2 新機能 (SQL 処理 ) OR Expansion(OR 拡張 ) の強化 CONCATENATION( 連結演算子 ) から UNION-ALL(UNION-ALL 演算子 ) に変更 パラレル実行も改善 <= 第 52 回 新しいヒント (OR_EXPAND NO_OR_EXPAND) が追加 SQL> SELECT c11,c12,count(*) FROM tab10 WHERE c11=1 OR c12=2 GROUP BY c11,c12; 実行計画 (12.1) ------------------------------------------------------------ Id Operation Name ------------------------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 CONCATENATION 3 TABLE ACCESS BY INDEX ROWID BATCHED TAB10 * 4 INDEX RANGE SCAN TAB10_IX12 * 5 TABLE ACCESS BY INDEX ROWID BATCHED TAB10 * 6 INDEX RANGE SCAN TAB10_IX11 実行計画 (12.2) ------------------------------------------------------------------ Id Operation Name ------------------------------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 VIEW VW_ORE_943AE5F6 3 UNION-ALL 4 TABLE ACCESS BY INDEX ROWID BATCHED TAB10 * 5 INDEX RANGE SCAN TAB10_IX11 * 6 TABLE ACCESS BY INDEX ROWID BATCHED TAB10 * 7 INDEX RANGE SCAN TAB10_IX12 68
12.2 新機能 (SQL 処理 ) Recursive WITH( 再帰問合せ ) の強化 再帰問合せがパラレル実行可能に <= 第 31 回 階層問合せ (CONNECT BY) ではできない SQL> WITH temp (oya,ko, cnt, lvl) AS ( 2 SELECT oya, ko, cnt, 1 FROM bom WHERE oya = 'A01' 3 UNION ALL 4 SELECT B.oya, B.ko, B.cnt, T.lvl+1 5 FROM bom B, temp T WHERE B.oya = T.ko 6 ) SELECT * FROM temp; B01 A01 B02 親部品 (oya) 子部品 (ko) 所要数 (cnt) A01 B01 1 A01 B02 1 OYA KO CNT LVL ----- ----- ---------- ---------- A01 B01 1 1 A01 B02 1 1 B01 C01 1 2 B01 C02 1 2 B02 C21 1 2 B02 C22 1 2 C01 D01 1 3 C01 D02 1 3 C01 SQL> SELECT oya,ko,cnt,level FROM bom 2 START WITH oya = 'A01 3 CONNECT BY PRIOR ko = oya ; C02 C03 B01 C01 1 B01 C02 1 B02 C03 1 69
12.2 新機能 (SQL 処理 ) Recursive WITH( 再帰問合せ ) の強化 ( パラレルの実行計画 ) 一時表変換を使用 CURSOR DURATION MEMORY も 12.2 新機能 (Cursor-Duration Temporary Tables) 一時表変換がメモリ上で可能に 通常の WITH 句などでも使用 BUFFER SORT (REUSE) を使用 表スキャンは 1 回だけに 初期化ブランチ処理 ( 以前はここだけパラレル実行 ) 再帰的ブランチ処理 ---------------------------------------------------------------------------------- Id Operation Name ---------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 TEMP TABLE TRANSFORMATION 2 LOAD AS SELECT (CURSOR DURATION MEMORY) SYS_TEMP_0FD9D662A_15287C 3 UNION ALL (RECURSIVE WITH) BREADTH FIRST 4 PX COORDINATOR 5 PX SEND QC (RANDOM) :TQ20000 6 LOAD AS SELECT (CURSOR DURATION MEMORY) SYS_TEMP_0FD9D662A_15287C 7 PX BLOCK ITERATOR * 8 TABLE ACCESS FULL BOM 9 PX COORDINATOR 10 PX SEND QC (RANDOM) :TQ10000 11 LOAD AS SELECT (CURSOR DURATION MEMORY) SYS_TEMP_0FD9D662A_15287C * 12 HASH JOIN 13 BUFFER SORT (REUSE) 14 TABLE ACCESS FULL BOM 15 PX BLOCK ITERATOR * 16 TABLE ACCESS FULL SYS_TEMP_0FD9D662A_15287C 17 PX COORDINATOR 18 PX SEND QC (RANDOM) :TQ30000 19 VIEW 20 PX BLOCK ITERATOR 21 TABLE ACCESS FULL SYS_TEMP_0FD9D662A_15287C 70
12.2 新機能 (SQL 処理 ) Partition-wise DISTINCT の強化 パラレル実行でのパーティション ワイズ DISTINCT が可能に TEMP 領域の削減にも効果的 <= 第 45 回 12.1 までは Group By のみ SQL> select /*+ parallel(2) */ distinct c1,c2 from tab1; 実行計画 (12.1) 実行計画 (12.2) -------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- Id Operation Name Pstart Id Operation Pstop TQ IN-OUT PQ Name Distrib Pstart Pstop TQ IN-OUT PQ Distrib -------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 0 SELECT STATEMENT 1 PX COORDINATOR 1 PX COORDINATOR 2 PX SEND QC (RANDOM) :TQ10001 2 PX SEND QC Q1,01 (RANDOM) P->S QC :TQ10000 (RAND) Q1,00 P->S QC (RAND) 3 HASH UNIQUE 3 PX PARTITION Q1,01 RANGE PCWP ALL 1 4 Q1,00 PCWC 4 PX RECEIVE 4 HASH UNIQUE Q1,01 PCWP Q1,00 PCWP 5 PX SEND HASH :TQ10000 5 TABLE Q1,00 ACCESS FULL P->P HASH TAB1 1 4 Q1,00 PCWP 6 PX BLOCK ITERATOR 1 4 Q1,00 PCWC 7 HASH UNIQUE Q1,00 PCWP 8 TABLE ACCESS FULL TAB1 1 4 Q1,00 PCWP 71
12.2 新機能 (SQL 処理 ) Band Join( 範囲条件結合 ) の強化 等価条件以外の索引を使用しない結合ではソート マージ結合 範囲条件の結合を改善 FILTER 操作がなくなる SQL> SELECT tab1.* FROM tab1 A,tab2 B WHERE A.c1 BETWEEN B.c1-10 AND B.c1+10; 実行計画 (12.1) ------------------------------------- Id Operation Name ------------------------------------- 0 SELECT STATEMENT 1 MERGE JOIN 2 SORT JOIN 3 TABLE ACCESS FULL TAB1 * 4 FILTER * 5 SORT JOIN 6 TABLE ACCESS FULL TAB2 ------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("a"."c1"<="b"."c1"+10) 5 - access(internal_function("a"."c1")>="b"."c1"-10) filter(internal_function("a"."c1")>="b"."c1"-10) 実行計画 (12.2) ------------------------------------- Id Operation Name ------------------------------------- 0 SELECT STATEMENT 1 MERGE JOIN 2 SORT JOIN 3 TABLE ACCESS FULL TAB1 * 4 SORT JOIN 5 TABLE ACCESS FULL TAB2 ------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(internal_function("a"."c1")>="b"."c1"-10) filter("a"."c1"<="b"."c1"+10 AND INTERNAL_FUNCTION("A"."C1")>="B"."C1"-10) 72
12.2 新機能 B ツリー索引圧縮の強化 11g 以前 (8.1.3 から ): Prefix 圧縮 別名 Key 圧縮 ユニーク値が多いとサイズが増える 12.1(12.1.0.2): ADVANCED LOW<= 第 49 回 ブロック毎に圧縮法調節 ( 圧縮列数 圧縮有無 ) バッファ キャッシュにより多く載るため高速になる場合も 12.2: ADVANCED HIGH より高い圧縮率 更新と検索に多少のオーバーヘッド 単一列ユニーク索引にも対応 要 :Advanced Compression Option(ACO) 73
最後に AWR は DB Time ベースで解析 まずは Report Summary から SQL チューニングは基本的なレベルから BI ツールなどの SQL 自動生成ツールに注意 オプティマイザ統計は必要なものを正しく設定する 限界があることも忘れずに 12.2 については 今後連載で説明していく予定 よろしければ 津島博士のパフォーマンス講座 を読んでみてください 74
Oracle Database 12c 対応研修コースのご案内 基礎から上級スキルまで Oracle Database 12c の製品機能を学習できる多彩な研修コースでスキルアップを Platinum Oracle Database 12c: RAC 管理 (4 日間 ) Oracle Database 12c: ASM 管理 (2 日間 ) Oracle Database 12c: Clusterware 管理 (4 日間 ) Oracle Database 12c: パフォーマンス チューニング (5 日間 ) Oracle Database 12c: SQL チューニングワークショップ (3 日間 ) Oracle Database 12c: セキュリティ (5 日間 ) Oracle Database 12c: Database Vault (2 日間 ) Gold Silver Oracle Database 12c: バックアップ リカバリ (5 日間 ) Oracle Database 12c: インストール & アップグレード (2 日間 ) Oracle Database 12c: マルチテナント アーキテクチャ (2 日間 ) Oracle Database 12c: 管理ネクスト ステップ (3 日間 ) Oracle Database 12c: 管理クイック スタート (2 日間 ) Oracle Database 12c: 管理ワークショップ (5 日間 ) Oracle Database 12c: 新機能 (5 日間 ) Oracle Database 12c: SQL 基礎 II (2 日間 ) データベース設計 (3 日間 ) Oracle Database 12c: PL/SQL プログラム開発 (3 日間 ) Oracle Database 12c: PL/SQL 基礎 (2 日間 ) Advanced Analytics Option 対応コース Oracle R Enterprise エッセンシャルズ (2 日間 ) Oracle Database 11g: データ マイニング手法 (2 日間 ) Oracle ではじめる統計入門 (1 日間 ) Bronze Oracle Database 12c: SQL 基礎 I (3 日間 ) Oracle Database 12cR2 対応研修は順次提供予定です 詳しくはオラクルユニバーシティまでお問い合わせください 75
Oracle Digital は オラクル製品の導入をご検討いただく際の総合窓口 電話とインターネットによるダイレクトなコニュニケーションで どんなお問い合わせにもすばやく対応します もちろん 無償 どんなことでも ご相談ください 76
77
78