PowerPoint プレゼンテーション

Transcription

1 Oracle9i Oracle実践研修3 パフォーマンス チューニング ~8 1

2 自己紹介 コミュニケーションテクノロジーズ株式会社 ソリューション事業部 畠山基裕 連絡先 [Oracleの経験] Oracle Applications(現E-business Suite)を約4年 販売系2つ 生産系3つ セットアップ 機能評価 Extension設計/テスト チューニング 運用支援 その他 システム開発 2

3 Oracleのドキュメント(9i) Oracle9i データベース パフォーマンス プランニング(J06251) (62ページ) Oracle9i データベース パフォーマ ンス チューニング ガイドおよびリ ファレンス(J06248) (798ページ) OTN( ロード可能 3

4 Oracleのドキュメント(10g) 2日でパフォーマンス チューニング ガイ ド(B40062) (142ページ) Diagnositics Pack Database Tuning Pack Enterprise Managerで自動 で容易にチューニング パフォーマンス チューニング ガ イド (B19207) (474ページ) OTN( ロード可能 4

5 実習環境 インスタンス名 smpl ユーザー/パスワード scott/tiger oe/oe system/smpladmin Oracleディレクトリ ORACLE_HOME c:\oracle\product\10.2.0\db_1 実習ディレクトリ c:\oracle\jisshu ポート %ORACLE_HOME%\install\portlist.ini 5

6 チューニングの対象 HW(メモリ CPU ディスク ネット ワーク) Oracle設定 SQL PL/SQL アプリケーションサーバー 6

7 アンケート 7/8/8i/9i/10g/11g Enterprise Edition/Standard Edition (EEの場合)Diagnostics Pack/Tuning Pack を導入しているか 各375,000円(オラクル セールスガイド Ver5.0~9i) 7

8 カリキュラムの確認 チューニング概要 Oracleチューニング手法 アプリケーション Oracleインスタンス Oracleデータベース StatsPack実習 リソース競合 OEM 1時間 1時間 6時間 1時間 2時間 0.5時間 0.5時間 8

9 チューニング概要 9

10 パフォーマンス プランニング パフォーマンスを考慮した設計と開発 アプリケーションのパフォーマンスの監視 と改善 いつチューニングを行うか 設計 設計時が 最も重要 開発 テスト 運用 保守 すべてのフェーズで行う 10

11 システム性能の変化 処理時間 データ量/トランザクション量の増大 理想 実際 量 11

12 チューニング対象 HW増設 Oracleインスタンス設定(メモリなど) アプリケーション(SQL PL/SQL インデッ クス) データベース(表領域 ファイル配置) 12

13 HWによる解決 CPU増設 メモリ増設 ディスク増設 一時的な解決 効果が出ないことも 13

14 Oracleインスタンス 共有プール領域(SGA) 共有SQL データベースバッファキャッシュ(SGA) REDOログバッファ(SGA) その他(REDOログファイル/チェックポイン ト) SGA=システムグローバル領域 14

15 Oracleデータベース ブロックサイズ 記憶域パラメータ 表領域 ファイル配置 15

16 アプリケーション アプリケーションの効率化が最も 効果的 16

17 アプリケーション設計の概要 17

18 表および索引の設計 非正規化 索引への列の追加または索引構成表の使 用 異なる索引タイプ(B*Tree/Bitmap..)の 使用 索引のコストに関する考察 索引内のシリアライズ化 18

19 非正規化 表の設計は 最低でも第3 正規形に正規 化する ただし 特定のトランザクションは パ フォーマンス向上のために非正規化も検 討 例 商品 売上月 金額 商品 4月 5月 2月 3月 売上 売上 売上 売上 19

20 索引への列の追加または 索引構成表の使用 索引に 必要なすべての列を入れる 表と索引をアクセス 索引だけをアクセ ス CREATE TABLE文にORGANIZATION INDEX句 を指定 (参照)Oracle9iデータベース管理者ガイド [J06242]表の管理15-25, 26 SE利用可 20

21 異なる索引タイプの使用 B ツリー索引 標準的な索引タイプ ビットマップ索引 EEのみ利用可 カーディナリティが低いデータに適する AND操作とOR 操作を効率よく実行 ファンクション ベース索引 SE利用可 データ上の関数から導出された値にB ツリーからアクセ ス コストベースオプティマイザを使用可能にしておく 必要 例 販売価格 - 値引 数量の計算結果がある値を超える受注の 問合せ UPPER関数をデータに適用して大/ 小文字を区別しない検索 21

22 異なる索引タイプの使用 パーティション索引 EEでPartitioning オプション必要 パーティションに分割しI/O を削減 レンジ パーティション 売上データを四半期分割 リスト パーティション 東北 関東などに分割 逆キー索引 SE利用可 列の順序は保ちながら 索引が定義されている 各列 ROWID を除く のバイトを逆にする 挿 入値は索引のリーフ キー全体に分散される 挿入パフォーマンスに優れているが レンジ スキャンには使用できない 22

23 索引のコストに関する考察 索引作成と保守には ディスク CPU I/O容量 などのリソースを消費する コストより 索引 使用による利点が上回るように設計する INSERT/DELETE/UPDATEで 索引には 表に対す る操作に比べ3倍のリソースが必要 3つの索引 がある表にINSERTすると 索引がない表に INSERTする場合に比べ約10倍かかる 更新DML 特にINSERT主体のアプリケーションの 場合 検索とINSERTのバランスを取る必要があ る 23

24 索引内のシリアライズ化 順序またはタイムスタンプを使用して索引 を生成すると 応答時間やスループットに 影響を与える場合がある 単調なキーの増 加による結果 回避するには 索引の全範囲にわたって挿 入するキーを生成する 逆キー索引 接頭辞("部門コード 順序") サイクル順序を 使用して生成(1 １０００をサイクリックに) 24

25 ビューの使用 有効なプログラミング インタ フェースを提供 最も不適切な例 ビューが他の複数 のビューを参照し 問合せ内で結合 されている 25

26 SQL の実行効率 適切なデータベース接続管理 コネクションプーリング等 適切なカーソルの使用方法と管理 アプリケーションでSQL 文を1回解析し そ のSQL文を繰返し実行するように設計 SQL文が共有プール内で共有されていること を確認 実行ごとに変化する問合せの部分 をバインド変数とする 文字列リテラルは 使用しない 26

27 SQL の実行効率 例 文字列リテラルがある文 SELECT * FROM emp WHERE ename LIKE 'KING%'; バインド変数がある文 SELECT * FROM emp WHERE ename LIKE :1; 後でサンプル提示(P.135) 27

28 その他 頻繁にアクセスし 変更がほとんどな く 繰返し取り出すのにコストがかか るデータはローカルに持つ 本日の日付 現行ユーザー名 税率 値引率 位置情報など 繰返し使 用するアプリケーション変数および定数 28

29 その他 外部キー参照を使用 アプリケー ションから参照整合性を適用する のはコストがかかる バッチ処理で 外部システムから受 注データImportなど 29

30 Oracleチューニング手法 30

31 ログファイルの確認 レスポンスが悪い まずは 問題の切り分け ログの種類 ALERTログ バックグラウ ンドトレース ユーザート レース 出力先 初期化パラメータ 出力ファイル名 1. Windows系OS 2. UNIX系OS BACKGROUND_DUMP_DEST 1. {SID}alert.log 2. alert_{sid}.log BACKGROUND_DUMP_DEST 1. {SID}プロセス名.trc 2. {SID}_プロセス名_{PID}.trc USER_DUMP_DEST 1. {SID}_ora_{PID}.trc 2. ORA{PID}.trc ログの内容 システムログメッセージとエラー情報 Oracleアーキテクチャ全プロセスから出力 常に出力される エラー情報 バックグラウンドプロセスより出力 常に出力される エラー情報とSQL文の統計情報 サーバプロセス 出力の制御による SQL> SHOW PARAMETERS background_dump_dest; 31

32 Oracleパフォーマンス 改善方法 パフォーマンス改善の概要 Oracleパフォーマンス改善の手順 ワークロードのテスト パフォーマンス監視と改善 32

33 パフォーマンス改善の概要 ユーザからのフィードバック オンラインのパフォーマンスが低いため ス タッフの仕事が停滞しています 請求処理の実行時間が長すぎます Web トラフィックが多くなると応答時間が長く なるため このままでは顧客を失いかねません 現在 1 日に5000 件の取引を行っていますが システムが限界に達しています 来月は すべ てのユーザーにロールアウトを行うため 取引 数は4 倍になる見込みです 33

34 パフォーマンス改善の概要 現実的なビジネス目標を設定 請求処理では 3 時間で1,000,000 件 を処理する Web サイトのピーク時には 1 つの ページ リフレッシュに対する応答時 間は5 秒以内にする システムでは 8 時間で25,000 件の 取引を処理可能にする 34

35 パフォーマンス改善の概要 パフォーマンス目標を達成 またはそれ以上 の改善は不可能と判断するまで実施 重要なボトルネックを正確かつ適時に特定す るスキルを養うには 時間と経験が必要 今日のシステムは それぞれが異なり複雑な ので パフォーマンス分析のための確実で手 間のかからない規則は作成できない 提供されたデータを利用し 様々な側面を検 討してパフォーマンスの問題を判断する 35

36 Oracleパフォーマンス改善の手 順 1. ユーザーから率直なフィードバックを取得 チューニングの適用範囲 今回の最終的な パフォーマンス目標 将来のパフォーマン ス目標を決定する 2. オペレーティング システム データベー ス アプリケーションの統計情報をすべて 取得 3. 全マシンのオペレーティング システムが 健全であることをチェックする 36

37 Oracleパフォーマンス改善の手 順 4. Oracle に関して最もよく見られる誤 りの上位10 項目をチェック(後述) 5. システム上で発生している状況を概念 的にモデル化し 問題の原因を把握 6. 一連の修正処理とシステムに対して予 想される動作を提示し アプリケー ションに対して最も有効な処理から順 に適用する 37

38 Oracleパフォーマンス改善の手 順 7. 変更によって予定通りにパフォーマン スが改善されたことを検証し ユー ザーがパフォーマンスの改善を認識し たかどうかを確認 ユーザーが認識し ない場合は 継続 8. パフォーマンス目標を達成するまで または他の制約によってそれ以上の改 善は不可能になるまで 5 7の手順を 繰り返します 38

39 本運用前のテスト ワークロードのテスト データのサイズ設定 ワークロードの見積り テスト デバッグおよび検証 本運用と同等のデータ量によるテスト 本運用と同じオプティマイザ モードの使用 シングル ユーザーのパフォーマンスのテスト 全SQL 文の計画の取得と文書化 マルチユーザーのテスト 本運用に近いHW構成でのテスト 安定した状態(キャッシュとか)でのパフォーマン スの測定 39

40 運用開始後の監視 パフォーマンス監視と改善 各種統計情報の利用 統計情報ツール 40

41 OS統計情報 CPU 統計情報 仮想メモリー統計情報 ディスク統計情報 ネットワーク統計情報 41

42 データベース統計情報 バッファ キャッシュ 共有プール 待機イベント 42

43 アプリケーション統計情報 各作業時間に処理されたユーザー トラ ンザクションに関する日次サマリー 処理されたトランザクションの詳細およ び各トランザクションの応答時間 各トランザクションで要した時間をアプ リケーション サーバー ネットワーク データベースなどに分解した統計情報 計測手段は 最初からアプリケーション に組み込むことをお薦め 43

44 統計情報ツール オペレーティング システム デー タ収集ツール データベース データ収集ツール 44

45 オペレーティング システ ム データ収集ツール UNIX CPU sar vmstat mpstat iostat メモリ sar vmstat ディスク sar iostat ネットワーク netstat Windows Performance Monitor 45

46 データベース データ収集 ツール Statspack データベース リポジトリ内のすべての統計情報を 格納 データベース統計情報を記録して収集するた めの最適な方法 Oracle Enterprise Manager EM パフォーマンス データの収集 格納およびレポー トを行うためのグラフィカル ユーザー インタ フェースを提供 BSTAT/ESTAT スクリプト 将来 サポート対象外 10gでも残っている %ORACLE_HOME%\RDBMS\admin\utlbstat.sql utlestat.sql 46

47 履歴データとベースラインの重 要性 問題が発生した時 正常稼動時の データと比較することで何が変化し たかを見る 47

48 StatsPack パフォーマンス データの収集 自動化 格納お よび表示ができるSQL PL/SQLおよびSQL*Plus ス クリプトのセット パフォーマンス統計をOracle表に永続的に格納し 後でレポート作成および分析用に使用 レポートには instance health and load サマ リー ページ リソースを多く使用するSQL 文お よび待機イベントと初期化パラメータ等が含まれ る 元々マニュアル記述少なく 10gでは記述削除 48

49 StatsPack 情報取得のたびにSNAP_IDが発行される SNAP_ID間の差異をレポートで見る Id Snap Started Level Jul : Jul : Jul : 年 2004年(ID=1 2)と 2004年 2005年 (ID=2 3)の2つの期間のレポートで比較 2003年 2004年(ID=1 2)のレポート内で 2003年 と2004年の違いを見る項目もある 49

50 StatsPack 他の例 毎月取得して どの月にどういう状況にな るかを見る 日ごとに取得して 1ヶ月の間で状況の変 化(月初 月中 月末)を見る 時間ごとに取得して 1日の間の状況の変 化を見る 50

51 StatsPack インストール %ORACLE_HOME% rdbms admin spcreate.sqlを 実行 データ取得 EXECUTE statspack.snap; レポート %ORACLE_HOME% rdbms admin spreport.sqlを 実行 SQLレポート %ORACLE_HOME% rdbms admin sprepsql.sqlを 実行 51

52 StatsPack 実習 c:\oracle\jisshu\jisshu_statspack.txt 52

53 StatsPack 定期的自動収集も可 レベル >= 5 リソース使用率の高いSQL 文に関するパフォーマ ンス データ >= 6 SQL計画およびSQL計画使用状況 >= 7 セグメント レベルの統計(logical reads db block changes physical reads physical writes physical reads direct physical writes direct global cache cr blocks served RAC 専用 global cache current blocks served RAC 専用 buffer busy waits ITL waits row lock waits) >=10;追加統計 親ラッチおよび子ラッチ SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>6); 53

54 StatsPack 不要データ削除 SPPURGE.SQL SPTRUNC.SQL StatsPack削除 SPDROP.SQL 統計収集自動化 SPAUTO.SQL マニュアル SPDOC.TXT 54

55 補足 オペレーティングシステムのチェック方 法 システム全体と各CPU について ユーザー領域 とカーネル領域でのCPU 使用率をチェック ページングまたはスワッピングがないことを確 認 マシン間のネットワーク待機時間が許容範囲内 であることをチェック 応答時間が長いか キューが長いディスクを検 索する ハードウェア エラーがないことを確認 55

56 補足 Oracleシステムにおける誤りの上位10項 目 1. 不適切な接続管理 2. カーソルと共有プールの不適切な使用 (バインド変数不使用) 3. 不適切なSQL 4. 標準以外の初期化パラメータ(隠しパラ メータ)の使用 5. 誤ったデータベースI/Oの取得(不適切 な配置) 56

57 補足 Oracleシステムにおける誤りの上位10項 目 6. REDO ログ設定の問題 7. 空きリスト 空きリスト グループ トラン ザクション スロット INITRANS または ロールバック セグメントの不足 8. 時間がかかる全表スキャン 9. 大量の再帰的SQL SYSユーザ エクステン ト割り当てなど) 10. 配置 移行時のエラー(移行時の索引欠落 古い統計) (10gで削除)ディスク内ソート 57

58 補足 Oracleシステムにおける誤りの上位10項 目 7. 空きリスト 空きリスト グループ トラ ンザクション スロット INITRANS ま たはロールバック セグメントの不足 自動UNDO管理 UNDO_MANAGEMENTをAUTOに 自動セグメント管理(ASSM) セグメント内空き 領域管理 create tablespace segment space management auto/manual 58

59 アプリケーション 59

60 チューニング たくさんの知識(ハッカー的) ツール 読み方 原因究明 対処 効果予測 総合力 60

61 SQLチューニング データベースのパフォーマンスは サービスに大きな影響を与える HW増設がすぐにできるわけでもなく 増設だけで解決するわけでもない チューニングの目的は 限られた システム リソースの中で 最大限 のパフォーマンス効果を出すこと 61

62 SQLチューニング チューニング 論理設計変更 物理設計変更 初期化パラメータの変更 SQLチューニング SQLチューニング 最も効果が期待できる手段 場合によって は 数倍から数百倍のパフォーマンス向上 62

63 相互影響度 OS ハードウェア リソースのチューニング CPUやメモリ ディスク ネットワークなど Oracleインスタンス チューニング SGA システムグローバル領域 PGA プログラムグローバル領域 各種バックグラウンド プロセスなど 初期化パラメータの調整 アプリケーションのチューニング プログラムロジックやアプリケーション サーバの設定などのアプリ ケーションの最適化 オブジェクトのチューニング 表や索引といったデータベース オブジェクトの設定や設計の変更など SQLチューニング SQL文のチューニング 63

64 SQLチューニング手順 64

65 I/O単位 I/O単位はデータブロック データブロック単位でキャッシュされ る select文が発行されると まずキャッ シュを探し なければファイルを探す キャッシュにあればキャッシュ ヒッ ト なければキャッシュ ミス 65

66 I/Oを減らす キャッシュにあれば速い キャッシュになくてもブロックが小 さければ負荷が低いし キャッシュ が効率的に使える ブロック内の断片化は効率を下げる 66

67 断片化の例 適切な記憶領域パラメータの指定 PCTFREE等 や マルチ ブロック サイズの使用によって SQL文実行時に アクセスされるデータ ブロック数調整が可能 67

68 オプティマイザ 表全体を読み込むのか 索引を使用 するのか どの索引を使用するのか といった実行計画を決める ルールベース 一定のルールに従う コストベース 統計情報に基づき最適な計画を作る 10gからはコストベースのみサポート!! 68

69 レコードアクセス 全表スキャン 索引スキャン ROWIDスキャン 69

70 結合方法 ネステッド ループ結合 ソート マージ結合 ハッシュ結合 70

71 ネステッド ループ結合 1. 結合処理の基準となる外部表を決定し 外部表から得られた結合条件列のデータ を基に索引スキャンが行われる表を内部 表とする 2. 外部表のレコードごとに内部表にアクセ スし 結合条件を満たすか検査する 3. 結合条件を満たすレコードを結合して結 果を返す 4. 表の一部を結合するのに有効 71

72 ネステッド ループ結合 件数の少な い方をフル 検索すると 効率がよい 72

73 ネステッド ループ結合 ルールベースでは 結合条件列に 対する索引が片方の表にしか存在 しない場合は 索引が存在する表 が内部表となり 双方に索引が存 在する場合には FROM句の指定順 番が後ろの表が外部表となる コストベースでは コストに基づ いて外部表が決定される 73

74 ネステッド ループ結合 どちらの表を外部表とするかによって アクセスするデータ ブロック数は 大きく異なってくる(deptno=10とか) ネステッド ループ結合を効率化する ためには 結合を試みるレコード数が より少ない方を外部表 レコード数に 大差がない場合には 結合条件列の索 引スキャンが より効率的な方を内部表 とする必要がある 74

75 参考 ネステッド ループ結合 select emp.empno,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno; 実行計画 dept表のdeptnoだけにインデックス(統計無し RBO) SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 75

76 参考 ネステッド ループ結合 実行計画 dept表のdeptnoとemp表のdeptno(emp_idx2)にインデックス (RBO) SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'DEPT' 4 2 INDEX (RANGE SCAN) OF 'EMP_IDX2' (NONUNIQUE) 76

77 ネステッド ループ結合 統計取得後(CBO) SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=280) 1 0 HASH JOIN (Cost=5 Card=14 Bytes=280) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98) 3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52) 77

78 参考 ネステッド ループ結合 SQL> select emp.empno,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.empno=7698; 実行計画 dept表のdeptnoとemp表のdeptno(emp_idx2)にインデックス RBO) SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 78

79 ネステッド ループ結合 統計取得後(CBO) SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20) 1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=20) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=7) 3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=13) 5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 79

80 ソート マージ結合 1. A表を結合条件列でソートする 2. B表を結合条件列でソートする 3. それぞれの表の結果が等しいレ コードを結合して結果を返す 80

81 ソート マージ結合 81

82 ソート マージ結合 表の大部分を結合するのに有効 結合対象が多く なおかつ結合条 件が等価条件ではない 場合に使用 の場合はハッシュ結合を使用 82

83 ハッシュ結合 1. レコード数の少ない表の結合条件列 をハッシュ関数にかけ メモリ上に ハッシュ テーブルを作成する 2. もう一方の表の結合条件列をハッ シュ関数にかけ 結合できるかを ハッシュ テーブルで確認する 3. ハッシュ値が等しいレコードを結合 して結果を返す 83

84 ハッシュ結合 84

85 ハッシュ結合 結合条件に等価条件 が指定され 大量のレコード あるいは表の大部 分を結合する場合に有効 コストベースの場合のみ使用可能 ソート マージ結合に比べ 事前の ソート処理が不要で効率的 小さいほうの表が使用可能なメモリー 内に収まる場合に最適 85

86 オブジェクト統計情報 表 索引などのレコード件数や 使用し ている領域 カーディナリティ データ 分布 などのデータ特性を表す情報 9iからは DBMS_STATSパッケージにてシ ステムの統計情報 システムのI/Oおよ びCPU性能など を取得することが可能 10gでは自動取得 DB作成時に GATHER.STATS_JOBでスケジューリング 86

87 オブジェクト統計情報 INS/UPD/DELで以下のときに取得 統計がない 行の10%以上が変更された select * from dba_scheduler_jobs where job_name= GATHER_STATS_JOB STATISTICS_LEVEL TYPICAL/ALL で有 効 87

88 オブジェクト統計情報 手動取得の注意 以下の場合 最適計 画が作れない 統計情報取得後に 大幅にデータ件数が 増減した 複数の表の結合処理を実行する場合 一 部の表のみ統計情報が古かった 複数の表の結合処理を実行する場合 一 部の表のみ統計情報が取得されていな かった 88

89 ヒント オプティマイザは全能ではない 適切な計画に対するヒントをユーザが 指定可能 最適化アプローチに関するヒント アクセス パスに関するヒント 問い合わせの変換に関するヒント 結合順序 結合操作に関するヒント パレレル実行のヒント そのほかのヒント 89

90 ヒント 注意 ヒントの指定方法が間違っていても エラー出力はされない ヒントの使用により 実行計画が固 定されてしまう ヒント RULEヒント以外 を使用す ると 強制的にコストベース アプ ローチとなる 90

91 ヒント ヒントの種類 ALL_ROWS ヒントの意味 最適化アプ 最高のスループットとなるように最適化される 全表スキャ ローチに関す ン ソート マージ結合が選択されやすくなる るヒント FIRST_ROWS(n) レスポンスタイムを最短にするように最適化される 索引ス キャンとネステッド ループ結合が選択されやすくなる RULE ルールベースのアプローチを選択する アクセス パ FULL 全表スキャンを選択する スに関するヒ INDEX 索引スキャンを選択する ント HASH ハッシュスキャンを選択する 結合順序に ORDERED FROM句に指定された順序で表を結合する 関するヒント LEADING 結合順序の最初の表を指定する STAR 可能な場合 スター問い合わせを選択する 結合方法に USE_NL ネステッド ループ結合を選択する 関するヒント USE_MERGE ソート マージ結合を選択する USE_HASH ハッシュ結合を選択する そのほかの APPEND ダイレクト パスINSERTを選択する ヒント CACHE 取得されたブロックが バッファ キャッシュ内でLRUリストの 最後に使用されたものの位置に配置される 91

92 ヒント SELECT /*+ INDEX(table名 index名) */ xx from SELECT /*+ APPEND */ xx from SELECT /*+ USE_NL(table名) */ xx from 指定tableが内部表 92

93 駄目なSQLとは 検討の対象となるのは 1実行当たりの実行時間が長いSQL ディスク読み取りブロック数が多い SQL バッファの読み取り数が極端に多い SQL 実行回数が極端に多いSQL 93

94 駄目なSQLを見つける アプリケーションが特定できている アプリケーションのSQLトレースを取 得する アプリが特定できていないか ト レースの負荷が心配な場合 動的パフォーマンスビューからSQLを 抽出する 94

95 トレースと動的ビュー 動的パフォーマンスビュー SQLトレース 調査 現在共有SQL領域にキャッシュされてい 可能 るSQL な 使用するV$表 SQL - V$SQL - V$SQL_TEXT - V$SQL_PLAN セッションで実行されたすべてのSQL 初期化パラメータSQL_TRACE=TRUEにする ことで 起動後確立したすべてのセッションの SQL DBMS_SYSTEMパッケージを使用し 指定し た別セッションのSQL 主に 確認 でき る項 目 - SQL - CPU時間 - 処理時間 - バッファからの読み込みブロック数 - ディスクからの読み込みブロック数 - 実行計画 - SQL - 累積CPU時間 - 累積処理時間 - バッファからの累積読み込みブロック数 - ディスクからの累積読み込みブロック数 - このSQLが実行された累積回数 - 実行計画 95

96 トレースと動的ビュー 動的パフォーマンスビュー SQLトレース メ リッ ト アプリケーションの処理速度に対する影響 が少ない SQLで簡単に確認可能 アプリケーション中で実行されている各SQLに 関して 詳細な情報を取得可能 アプリケーションの速度問題がRDBMS側にあ るか否かの切り分けに使用可能 ~セッション単 位で取得できるから デメ リッ ト 1回当たりの実行時間などは平均値しか取 得できない メモリ上にキャッシュされているSQLの情 報しか確認できない トレースの取得 ファイルへの書き込みに伴う オーバーヘッドがある トレースファイル取得のためのディスク領域が 必要 96

97 動的パフォーマンスビュー 主に V$SQL V$SQL_TEXT V$SQL_PLANの 3つを使用 これらは共有SQL領域に保持されて いるSQLの情報を表示する 97

98 動的パフォーマンスビュー ビュー名 主な格納情報 主な列名 V$SQL SQLの先頭1000bytes SQL_TEXT SQLの累積リソース使用 SORTS 状況 FETCHES EXECUTIONS USERS_EXECUTING PARSE_CALLS DISK_READS BUFFER_GETS ROWS_PROCESSED CPU_TIME ELAPSED_TIME ADDRESS HASH_VALUE 列値の意味 SQLの先頭1000bytes SORTの回数 FETCHの回数 実行回数 現在実行中のユーザー数 解析コールの回数 ディスク読み込み数 バッファ読み込み数 SQLが戻す行数 処理に使用したCPU時間 マイク ロ秒 処理に使用した経過時間 マイ クロ秒 ほかのViewとの結合に使用 ほかのViewとの結合に使用 98

99 動的パフォーマンスビュー ビュー名 主な格納情報 V$SQL_T SQLの全文 EXT V$SQL_P SQLの実行計画 LAN 主な列名 SQL_TEXT PIECE ADDRESS HASH_VALUE OBJECT_NAME OPERATION OPTIONS OPTIMIZER DEPTH ADDRESS HASH_VALUE 列値の意味 64bytes単位で分割されたSQL 分割されたSQLの断片番号 ほかのViewとの結合に使用 ほかのViewとの結合に使用 実行計画中の表 索引名 実行計画オペレーション 実行計画オペレーションのオプ ション オプティマイザのモード 実行計画のTreeの深さ ほかのViewとの結合に使用 ほかのViewとの結合に使用 99

100 動的パフォーマンスビュー 合計実行時間の長いSQLを見つける SET LINES 140 COL sql_text FORM A140 COL buffer_per_run FORM COL disk_per_run FORM COL cpu_time FORM COL elapsed_time FORM SELECT * FROM (SELECT sql_text,address,hash_value,parse_calls,executions, buffer_gets,disk_reads, buffer_gets/executions buffer_per_run, disk_reads/executions disk_per_run,cpu_time, elapsed_time FROM v$sql WHERE executions>0 ORDER BY elapsed_time desc) -- この条件を変更する WHERE rownum <= 10; -- 表示件数はこの値を変更する 100

101 動的パフォーマンスビュー 1実行あたりのバッファ読み取り数の多いSQL 累積バッファ読み取り数の多いSQL ORDER BY buffer_gets desc ディスク読み取り数の多いSQL ORDER BY buffer_gets/executions desc ORDER BY disk_reads desc 実行回数の多いSQL ORDER BY executions desc 101

102 SQL全文の取得 V$SQLからADDRESS列 HASH_VALUE列の 値を確認 V$SQL_TEXTを参照 set pages 100 feed off timing off echo off lines 140 SELECT sql_text FROM v$sqltext WHERE hash_value= and address='54b4df20' ORDER BY piece; 102

103 取得方法の比較 実行計 画の取 得 SQLトレース TKPROFユーティリ ティ SQL*Plusの AUTOTRACE機能 動的パフォーマンス ビューの利用 V$SQL V$SQL_TEXT V$SQL_PLAN 取得 の手 軽さ アプリケーショ 各SQLの実 ン処理全体の 行時間に関 情報取得 する情報 取得による システム負 荷の低さ (*) 情報取得範囲 インスタンス または 特定のセッションが実 行する全SQL 自セッションのSQL インスタンスで実行さ れたSQL (*) SQL*Plusでset timing onを設定することで代替可能 103

104 SQLトレース使用方法 時間に関連する統 $ sqlplus scott/tiger 計の収集を行う SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE; SQL> ALTER SESSION SET SQL_TRACE=TRUE; SQL> SELECT COUNT(*) FROM orders. SQL> ALTER SESSION SET SQL_TRACE=FALSE; (補足)alter session set tracefile_identifier = 'hata'; SQL> exit smpl_ora_3240_hata.trc 104

105 SQLトレース出力先 専用サーバ接続 初期化パラメータ USER_DUMP_DESTで指定されたディ レクトリ 共有サーバ接続 初期化パラメータ BACKGROUND_DUMP_DESTで指定 されたディレクトリ 105

106 tkprof使用方法 トレースファイルを見やすいように整形する $ tkprof ora_11111.trc prf explain=scott/tiger aggregate=no sys=no sort=fchela ora_11111.trc トレースファイル システム内部で prf 結果出力ファイル 発行するSQL explain=scott/tiger 実行計画出力 aggregate=no 重複SQLを個別出力 sys=no リカーシブコールを排除 sort=fchela フェッチ時の経過時間順にソート 106

107 tkprof使用方法 オプション名 EXPLAIN 説明 TKPROFユーティリティ実行時の実行計画を出力するためのユー ザー名 パスワードを指定する AGGREGATE DEFAULT YES YESを指定した場合 同一のSQLは集計されて 1回だけ出力される NOを指定した場合 SQL単位の集計は行われず 実行された回数 分出力される SQLを個別に調査したい場合には NOを設定する SORT 指定したオプションによって降順でSQLが出力される EXEELA 実行時の経過時間順 EXEDSK 実行時のディスクアクセスブロック数順 EXEQRY 実行時のアクセスブロック数順 FCHELA フェッチ時の経過時間順 FCHDSK フェッチ時のディスクアクセスブロック数順 FCHQRY フェッチ時のアクセスブロック数順 そのほかにも多数のオプションがある SYS DEFAULT YES NOを指定すると リカーシブコールを整形したファイルに含めない 107

108 EVENT 10046でのSQLトレースの取得 ドキュメントには記載なし EVENTと呼ばれるデバッグ用の機能によ り SQLトレースで取得できる情報に加え て より詳細な情報を取得可能 EVENTはOracleの内部動作変更や デ バッグなどに使用されるものであり 正式 にサポートされるものではない 使用する 場合は 自己責任において使用のこと 108

109 EVENT 10046でのSQLトレースの取得 レベル LEVEL LEVEL LEVEL LEVEL 内容 1 SQL_TRACE機能と同等 4 LEVEL 1の情報に追加して バインド変数情報が出力される 8 LEVEL 1の情報に追加して 待機イベント情報が出力される 12 LEVEL 1の情報に追加して バインド変数情報 待機イベント 情報が出力される SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; SQL> +++ チューニング対象SQLの実行 +++ SQL> ALTER SESSION SET EVENTS '10046 trace name context off'; 109

110 EVENT 10046でのSQLトレースの取得 tkprofで整形 バインド変数の値を確認する場合 は LEVEL 4もしくはLEVEL 12で取得した SQLトレースをTKPROFユーティリティで整 形せず 直接参照する 110

111 EVENT 10046でのSQLトレースの取得 PARSING IN CURSOR #2 len=57 dep=1 uid=35 oct=3 lid=35 tim= hv= ad='58cdd7d0' SELECT * FROM orders WHERE o_orderkey=:b2 AND o_clerk=:b1 END OF STMT PARSE #2:c=10000,e=8105,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim= BINDS #2: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0 bfp=405e2a4c bln=22 avl=02 flg=05 value=1 bind 1: dty=1 mxl=2000(1000) mal=00 scl=00 pre=00 acfl2=1 oacflg=13 size=2000 offset=0 bfp=405e2224 bln=2000 avl=15 flg=05 value="clerk# " EXEC #2:c=0,e=1637,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim= FETCH #2:c= ,e= ,p=23248,cr=24063,cu=0,mis=0,r=1,dep= 1,og=4,tim=

112 別セッションのトレース sysdba権限を持つユーザーでログインする $ sqlplus /nolog SQL>connect / as sysdba トレース取得対象セッションのSID SERIAL#を V$SESSION動的パフォーマンスビューで確認 SQL> SELECT sid,serial#,username,program,machine,status,last_call_et FROM v$session WHERE username='scott'; SID SERIAL# USERNAME PROGRAM MACHINE STATUS LAST_CALL_ET SCOTT sqlplus@linux006 (TNS V1V3) linux006 INACTIVE 2 112

113 別セッションのトレース SQLトレースを設定する SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESS ION(10,12,TRUE); SQLトレースの設定を解除する SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESS ION(10,12,FALSE); df Oracle Direct iseminarのテキスト 113

114 別セッションにEVENT EVENT 10046をLEVEL 12に設定する SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'') ; EVENT 10046の設定を解除する SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,''); シングルコー テーションが2 つ 114

115 トレースファイルの見方 115

116 トレースファイルの見方 (1) SQL 実行したSQL文 Parse Execute Fetchの各CPU時間と合計CPU時間を表示 (2) CPU時間 1/100秒単位 1/100秒以下の場合 0秒として計算される Parse Execute Fetchの各経過時間と合計経過時間を表示 (3) 経過時間 1/100秒単位 1/100秒以下の場合 0秒として計算される (4) ディスクアクセスブロック数 ディスクI/Oが発生し 読み取られたブロック数 (5) バッファアクセスブロック数 メモリ上でアクセスされたブロック数 (6) 行数 このSQLを実行した結果 処理された行数 値が0の場合 共有プール上の解析結果が存在したため 解 (7) ライブラリキャッシュでのミス 析処理が排除されたことを表す (8) オプティマイザモード SQLを実行したときのオプティマイザモードを表す (9) 解析したユーザー SQL文を解析したユーザーIDを表す SQLが実行されたときの実 (10) 行計画 SQLが実行されたときの実行計画を表す 詳細は後述 TKPROFが実行されたとき (11) の実行計画 TKPROFが実行されたときの実行計画を表す 詳細は後述 116

117 SQL実行時の実行計画 10 は SQLが実行されたときの実行計画を 表す 2行目に TABLE ACCESS FULL と出力されて いるため全表スキャンが行われたことが確認でき る OBJ# のカッコ内の数字はオブジェクト番号 OBJECT_ID を表しており DBA_OBJECTS ディクショナリ ビューで特定できる cr r w time のパラメータは次ペー ジ参照 117

118 SQL実行時の実行計画 cr r w time バッファ上から読み込まれた合計ブロック数 ディスクから読み込まれた合計ブロック数 ディスクに書き込まれた合計ブロック数 合計経過時間 1/ 秒 マイクロ秒 親 ここではSORT GROUP BY の値は子 TABLE ACCESS FULL の値を含む 実施するとrはpr wはpwと出力されるが マ ニュアル上はこの通り 118

119 TKPROF実行時の実行計画 11 は TKPROFユーティリティを実行 した時点での実行計画 つまり 現時点でこ のSQLを実行した際に使用される実行計画と なる SQLトレースを取得した時点から 表 索引 の統計情報が更新されている場合や 索引の 作成 削除などが行われていると SQLト レース取得時点の実行計画と異なってくる場 合がある 119

120 SQLの分析 11 の実行計画の結果は よりインデントが深い 右の方 に出力される 処理が先に実行され 結果が上のレベルに渡 される 同一のレベルの処理が存在する場合は より上にある処理が 先に実行される この例では LINEITEM表への全表スキャンが発生してから GROUP BY処理が行われていることがわかる 3 SQLの経過時間が 62.81秒 であり 10 から全表 スキャンのみの時間は 約60.5秒 time 処理時間のほとんどを全表スキャンが占めている この点を 改善することが実行時間の短縮につながる 4 と 5 から ほぼ全ブロックのアクセスがディスクI/O を伴っている 120

121 SQLトレース/tkprof 実習 c:\oracle\jisshu\jisshu_trace.txt 121

122 SQL*PlusのAUTOTRACE SQLの実行計画 および実行時に必要とした システムリソースなどを簡単に確認 SQLトレースには含まれないメモリソート ディスクソートの発生回数なども確認できる 122

123 AUTOTRACEの設定 初回のみ実施 SYSユーザでplustrce.sqlを実行 し PLUSTRACEロール作成 $ sqlplus /nolog SQL> CONNECT / AS SYSDBA ql 123

124 AUTOTRACEの設定 AUTOTRACE機能を使用するユーザに PLUSTRACEロールを付与 SQL> GRANT plustrace TO scott; 実行計画の情報を格納するための PLAN_TABLE表を作成 $ sqlplus scott/tiger 以上で準備完了 124

125 AUTOTRACEの使用方法 オプション SET AUTOTRACE SET AUTOTRACE SET AUTOTRACE SET AUTOTRACE SET AUTOTRACE 説明 ON 実行計画と実行統計をレポート出力する OFF レポート出力をしない ON EXPLAIN 実行計画のみレポート出力する ON STATISTICS 実行統計のみレポート出力する TRACEONLY データをフェッチするが 結果を出力せず に実行計画 実行統計をレポート出力す る 後ろに EXPLAIN STATISTICSオプ ションを付けることも可能 125

126 AUTOTRACEの使用方法 126

127 AUTOTRACEの使用方法 (1) (2) 実行計画 recursive calls db block gets (3) consistent gets (4) (5) (6) physical reads redo size bytes sent via (7) SQL*Net to client bytes received via (8) SQL*Net from client SQL*Net roundtrips (9) to/from client (10) sorts (memory) (11) sorts (disk) (12) rows processed SQLの実行計画を表示 SQL実行時に内部で発行されたリカーシブコール数 DMLやSELECT FOR UPDATEを発行したときなどに発生するカレントモー ドで読み込まれたブロック数 SELECTを発行したときなどに発生する読み取り一貫性モードで読み込ま れたブロック数 ディスクアクセスによって読み込まれたブロック数 REDOログに書き込まれたサイズ byte クライアントへ送られた合計byte数 クライアントから受信した合計byte数 クライアントに送受信されたNetメッセージの合計数 メモリソート回数 ディスクソート回数 SQLが処理した件数 127

128 V$SQL_PLANでの実行計画 9iから 実行計画が共有プール内にキャッ シュされている キャッシュに保持されている間は 過去に実 行されたSQLの実行計画を確認できる SQLが特定され ADDRESS 値 HASH_VALUE値が必要(V$SQL) 128

129 V$SQL_PLANでの実行計画 DEFINE W_ADDRESS=54B4DF20 --対象SQLのADDRESS DEFINE W_HASH_VALUE= 対象SQLのHASH_VALUE SELECT id, lpad (' ', depth) operation operation,options,object_name,optimizer,cost FROM v$sql_plan WHERE hash_value=&w_hash_value AND address='&w_address' START WITH id = 0 CONNECT BY (PRIOR id=parent_id AND PRIOR hash_value=hash_value AND PRIOR child_number=child_number) ORDER SIBLINGS BY id, position; ID OPERATION OPTIONS OBJECT_NAME OPTIMIZE COST SELECT STATEMENT CHOOSE 1 SORT GROUP BY 2 TABLE ACCESS FULL LINEITEM 129

130 メリット/デメリット メリット SQLトレース 詳細な情報が取得可能 TKPROFユーティ 時間統計の取得が可能 リティ アプリケーションで実行され るすべてのSQLを取得可能 SQL*Plusの 簡単に実行計画を確認する AUTOTRACE機 ことが可能 能 動的パフォーマン 過去に実行されたSQLの実 スビューの利用 行計画を確認することが可能 デメリット トレースファイルを格納するための ディスク領域が必要 取得時に多少の負荷が発生 環境設定が必要 共有SQL領域が大きい環境やシステ ム自体の負荷が非常に高い環境で は オーバーヘッドとなる場合がある 130

131 SQL記述法 チューニング対象のアプリケーションや バッチ処理で処理に時間がかかるSQLがない 場合 個々のSQLには問題がない SQL全体に目を向け WHERE条件のみ異な る類似SQLが数多く実行されていないか調査 OLTP系の処理などで 実行されるたびに条 件の値 リテラル が異なるSQLを実行して いるアプリケーションでは リテラル値部分 をバインド変数化し SQLの記述を統一する 131

132 SQL記述法 SQLの処理ステップ 解析処理 PARSE 実行処理 EXECUTE データの取り出し処理 FETCH SELECT時のみ 解析の詳細 ライブラリキャッシュをチェックし 同一SQLの解析結果を探す キャッシュされていた場合 残りの解析をスキップし キャッシュ されていた実行計画でSQLを実行 SOFT PARSE 同一解析結果がキャッシュ上にない場合 データディクショナリに 対して多くのリカーシブコール 再帰SQL を発行し 解析処理を 行う HARD PARSE 大量に発生するとデータベース全体の処 理パフォーマンスに影響を与える HARD PARSEを回避することで 解析処理によるCPU使用率の低 減 共有プールのメモリ使用量削減などを実現できる その結果 データベースのスループットを向上させる 132

133 解析処理手順 処理順序 処理内容 構文のチェック 表 列の定義チェック アクセスするオブジェクトへの権限 チェック 実行計画の生成 共有プール上に実行計画を含め解 析結果をキャッシュ 133

134 共有されないSQL例 134

135 記法の統一 大文字 小文字 改行位置 TABやスペースの個数など 例 SQLの構文 SELECT DELETE INSERT FROM WHEREなど は大文字 列名 表名などのオブジェクト名は小文字 スペースは1つ 135

136 記法の統一 136

137 バインド変数 WHERE句条件列のリテラル バインド変数 化 SQLが共有される 137

138 バインド変数 例 set serveroutput on VARIABLE w_empno number declare w_emp_rec emp%rowtype; begin :w_empno:=7902; SELECT * into w_emp_rec FROM emp WHERE empno=:w_empno; dbms_output.put_line('empno=' :w_empno ' ename=' w_emp_rec.ename); :w_empno:=7369; SELECT * into w_emp_rec FROM emp WHERE empno=:w_empno; dbms_output.put_line('empno=' :w_empno ' ename=' w_emp_rec.ename); :w_empno:=7900; SELECT * into w_emp_rec FROM emp WHERE empno=:w_empno; dbms_output.put_line('empno=' :w_empno ' ename=' w_emp_rec.ename); end; 138

139 共有カーソル機能 類似したSQL文が繰り返し実行されていても アプリ ケーションの修正ができない場合 類似SQLを内部的 にバインド変数化し 解析結果を共有させることがで きる パフォーマンス向上が期待できる 初期化パラメータ CURSOR_SHARING を設定する FORCE リテラルがわずかに異なっても同一のSQLとして共 有 SIMILAR 実行計画が同一であれば同一のSQLとして共有 EXACT 完全に同一のテキストを含むSQLのみ共有 デフォ ルト値 139

140 索引が使用されない NULL値の検索 B*Tree索引はNULL値のデータを含まない IS NULL検索では 索引を使用しない 対処 NULLを特定の値に置き換える ビットマップ索引を使用する 140

141 索引が使用されない 一覧 ケース NULL値の検索 SQLの例 列名 IS NULL 暗黙の型変換 CHAR列 = 1 VARCHAR2列 = 1 索引列に対して VARCHAR2列 '様' = '斉藤様' 関数や算術を実 NUMBER列 * 20 = 施 substr(varchar2列,1,2) = 'AB' LIKEの中間一 致 後方一致!= <>の使用 Not Equals 列名 列名 列名 列名 LIKE'%TEST%' LIKE'%TEST'!= '1' <> '1' 対処方法 NULL値を別のデータに置き換える ビットマップ索引を使用する 比較するデータ型を列のデータ型に合わせる CHAR列 = '1' CHAR列 = TO_CHAR(1) INDEXヒントを使用する 索引列にNOT NULL制約が必要 関数 演算を右辺 索引列でない方 に移動する VARCHAR2列 = '斉藤' NUMBER列 = 10000/20 VARCHAR2列 LIKE 'AB%' 関数索引を使用する Oracle 9i以上で使用可能 例 CREATE INDEX SUBSTR_IDX ON SUPPLIER ( SUBSTR(列名,4,6) ); INDEXヒントを使用する 索引列にNOT NULL制約が必要 INDEXヒントを使用する 索引列にNOT NULL制約が必要 inで置き換える 可能な場合 列名 in ('2','3') 注 inで指定できる最大リスト数は1000個 INDEXヒントを使用する 索引列にNOT NULL制約が必要 141

142 索引データの偏り 索引スキャンを行っているのに遅い 検索する割合 142

143 索引データの偏り 対処 コストベース (ヒント) 143

144 索引の格納効率 表データの更新 挿入 削除に より 徐々に格納 効率が悪化 144

145 索引の格納効率 確認 ANALYZEコマンドで索引の構造を分析する SQL >ANALYZE INDEX xx_idx VALIDATE STRUCTURE; INDEX_STATSビューから分析結果を取得 SQL >SELECT name,blocks,height FROM index_stats; NAME BLOCKS HEIGHT IX_INFO_TXT 格納ブ 階層 ロック の高 数 さ 145

146 索引の格納効率 対処 リビルド ALTER INDEX 索引名REBUILD ALTER INDEX 索引名COALESCE COMPUTE STATISTICS をつけると 統計情報も同時に収集してくれる 統計 はdbms_statsパッケージの使用を推奨 146

147 複合索引 複数の列を指定した索引 SQLで必要とされるすべての列 WHERE条 件列 参照列 が索引に含まれている場合 表にアクセスすることなく 索引へのアクセ スのみで処理を完了できる 147

148 複合索引 索引のみアクセスされる例 148

149 その他索引 ファンクションベース索引 通常 where句中で修飾されて索引を使用できな い場合も 索引スキャンが可能 統計情報が必要 ビットマップ索引 カーディナリティが低い場合有効 149

150 その他索引 逆キー索引 索引列の値が昇順で増加するようなInsertで有効 範囲検索 できない 150

151 ITLの競合 同時に大量のトランザクションが実行されるシステ ムでは 表や索引のデータブロック で ITL Interested Transaction List の競合が発生 している場合も考えらる ITLとは 各データブロック内に保持されるトランザ クション情報のことで ブロックを変更するトラン ザクションは 最初にITLを割り当てる必要がある ITLが不足した場合 MAXTRANSの値まで自動的に 拡張されるが 空き領域がないなどの理由により拡 張できない場合 トランザクションはITLが獲得でき るまで待機する 151

152 表の結合 USE_NL USE_MERGE USE_HASH 結合する表の 結合方式 パターン ネステッド ループ結合 ソート マージ結合 件数の多い表同士 不向き 結果を結合列でソートして出力 を結合し 全レコー する場合に有効 双方の結合列 ド出力する にNOT NULL制約が指定されて おり 索引が存在する場合 非 常に効率的 一方の表に絞り込 目安として索引を使用し 不向き み条件を指定して て表の15 以内の絞り 表を結合し 少数 込みであれば最適 のレコードを出力す る ハッシュ結合 システム リソースに余裕 がある場合には最適 目安として索引を使用して 表の15 以上の絞り込み で なおかつ等価条件が あれば使用を検討 152

153 マテリアライズド ビュー 結合処理などを含む複雑な検索処理のパ フォーマンス向上に有効 実データを持つ! 153

154 マテリアライズド ビュー MViewの元となる表データが更新された場合 リフレッシュ処理によって MViewにその 変更が反映される リフレッシュには 毎回すべてのデータを入 れ替える完全リフレッシュと 元表への変更 履歴を保持するマテリアライズド ビュー ログを利用し 差分のみを反映する高速リフ レッシュがある 154

155 マテリアライズド ビュー 例 CREATE MATERIALIZED VIEW Mview_l_s_n REFRESH fast on commit //高速 リアルタイム更新 AS SELECT l.rowid l_rowid,s.rowid s_rowid,n.rowid n_rowid, n.n_name,l.l_shipdate,l.l_shipmode,s.s_name,s.s_addres s FROM lineitem l,supplier s,nation n WHERE l.l_suppkey=s.s_suppkey AND s.s_nationkey=n.n_nationkey; クエリーリライト機能で元SQLを変更しないことも可 各 種設定により自動的にMビューが参照される 155

156 BITMAP JOIN INDEX 結合処理を排除 156

157 クラスタ 結合処理のパフォーマンスを向上させるため に使用されるデータ構造で 複数の表を結合 した状態でデータベースに格納 157

158 クラスタ create cluster emp_dept (deptno number(2)) index; create index emp_dept_idx on cluster emp_dept; create table t_emp cluster emp_dept(deptno) as select * from emp; create table t_dept cluster emp_dept(deptno) as select * from dept; 158

159 条件不要のDML MERGE文 9iから利用できるSQLで データが存在していれ ば更新 UPDATE データが存在しなければ 挿入 INSERT する アプリケーションで分岐処理をしたり PL/SQL などで繰り返し実行する必要がなくなる PL/SQLでは 列定義よりもデータ長の大きいデータ にUPDATEしたときにエラー処理や 事前のチェック ロジックでエラー発生個所の特定を行える 159

160 ダイレクト ロード イン サート 別表から大量データをINSERTする場合 APPENDヒントを利用することで ダイレ クトロードインサート処理ができる バッファ キャッシュを経由せずにデータ をINSERTすることができ また最低限の REDO情報のみが生成されるため 通常の INSERTよりも大きくパフォーマンスを改 善できる可能性がある 160

161 ダイレクト ロード イン サート SQL> INSERT /*+ APPEND */ INTO TBL.. パラレルDMLモード(EE)であれば不要 alter session enable/disable parallel dml; parallel_max_servers 0より大きい値 161

162 ダイレクト ロード イン サート 注意 注意点 ハイ ウォーター マーク 以 下 HWM 以降のブロックに INSERTされる 内容 通常のINSERT処理であれば 空きブロックにデータをINSERTする ことが可能ですが ダイレクトロードインサート処理ではHWM以降の ブロックにデータがINSERTされます そのため 通常のINSERT処 理よりも領域の使用効率が低下してしまいます 表単位のロックが獲得され 通常のINSERT処理ではレコード単位でロックが獲得されますが ダ る イレクトロードインサート処理では表単位でロックが獲得されます そのため ダイレクトロードインサート処理の実行中には ほかの セッションによる同一表への更新処理は待たされることになります パーティション表の1パーティションへのダイレクトロードインサート処 理でも同様となるため 注意が必要です ダイレクトロードインサート処 ダイレクトロードインサート処理の実行直後は 必ずトランザクション 理後 トランザクションを完 を完了させる COMMITもしくはROLLBACK 必要があります もし 了する必要がある 続けて同じ表にSELECTを実行すると ORA-12838: オブジェクト は パラレルで変更された後は読込み 変更できません というエ ラーが発生してしまいます 162

163 パラレルDML DMLをパラレルに実行 Oracle9i R2以前では パーティション表で のみ可 Oracle9i R2からは1パーティション 内や非パーティション表 通常の表 におい ても実行可 複数CPUが搭載され CPU使用率などシス テムリソースに余裕がある環境で有効 EEのみ ヒントで PARALLEL を指定する 163

164 不要インデックス削除 インデックスは検索には有用だが イ ンデックス数が多いと更新に時間がか かる 大量更新では 索引一時削除 表 データ更新 索引再作成 も検討 164

165 Oracleインスタンス 165

166 共有プール 共有プール全体のサイズを確認 SQL> SET SERVEROUTPUT ON; DECLARE sum_db_object_cache NUMBER; sum_s_sqlarea NUMBER; sum_u_sqlarea NUMBER; now_parameter NUMBER; result NUMBER; BEGIN SELECT SUM(SHARABLE_MEM) INTO sum_db_object_cache FROM V$DB_OBJECT_CACHE; SELECT SUM(SHARABLE_MEM) INTO sum_s_sqlarea FROM V$SQLAREA; SELECT SUM(250 * USERS_OPENING) INTO sum_u_sqlarea FROM V$SQLAREA; result := sum_db_object_cache + sum_s_sqlarea + sum_u_sqlarea; SELECT value INTO now_parameter FROM V$PARAMETER WHERE NAME = 'shared_pool_size'; DBMS_OUTPUT.PUT_LINE('パラメータSHARED_POOL_SIZE->' now_parameter); DBMS_OUTPUT.PUT_LINE('現在最低限必要なSHARED_POOL_SIZE->' result); DBMS_OUTPUT.PUT_LINE('適正なSHARED_POOL_SIZE( 1.3)->' result * 1.3); END; / 共有プール ライブラリキャッシュ ディクショナリキャッシュ 166

167 ライブラリキャッシュヒッ ト率 ヒット率99 以上を V$LIBRARYCACHEを見る ライブラリ キャッシュ ヒット率 1 - キャッシュ ミス RELOADS 合計 / キャッシュ ヒット PINS 合計 SQL> SELECT SUM(PINS) AS キャッシュヒット合計, SUM(RELOADS) AS キャッシュミス合計, ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2) '%' AS ライブラリキャッシュヒット率 FROM V$LIBRARYCACHE; / 167

168 ディクショナリキャッシュ ヒット率 ヒット率95 以上を V$ROWCACHEを見る ディクショナリ キャッシュ ヒット率 1 - キャッシュ ミス GETMISSES 合 計 / キャッシュ ヒット GETS 合計 SELECT SUM(GETS) AS キャッシュヒット合計, SUM(GETMISSES) AS キャッシュミス合計, ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) '%' AS ディクショナリヒット率 FROM V$ROWCACHE; / 168

169 共有プール 共有プールサイズの変更 SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 値; 169

170 DBバッファキャッシュヒッ ト率 ヒット率90 以上を SQL> SET SERVEROUTPUT ON; DECLARE d_gets NUMBER; c_gets NUMBER; p_reads NUMBER; result NUMBER; BEGIN SELECT VALUE INTO d_gets FROM V$SYSSTAT WHERE NAME = 'db block gets'; SELECT VALUE INTO c_gets FROM V$SYSSTAT WHERE NAME = 'consistent gets'; SELECT VALUE INTO p_reads FROM V$SYSSTAT WHERE NAME = 'physical reads'; result := ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100; DBMS_OUTPUT.PUT_LINE('データベース バッファキャッシュヒット率->' result '%'); END; / 170

171 DBバッファキャッシュ見積 数%の余裕を SQL> SET SERVEROUTPUT ON; DECLARE cnt_total NUMBER; cnt_free NUMBER; result NUMBER; message VARCHAR2(256); BEGIN SELECT COUNT(*) INTO cnt_total FROM X$BH; SELECT COUNT(*) INTO cnt_free FROM X$BH WHERE STATE = 0; result := ROUND((cnt_free / cnt_total), 3) * 100; SELECT DECODE(SIGN(result), 1, 'バッファキャッシュ余裕有り', 'バッファキャッシュ余裕なし') INTO message FROM DUAL; DBMS_OUTPUT.PUT_LINE('バッファ空き比率->' result '%'); DBMS_OUTPUT.PUT_LINE(message); END; / 171

172 DBバッファキャッシュ見積 現在のバッファ キャッシュサイズの 確認 SQL> SHOW PARAMETERS DB_CACHE_SIZE バッファ キャッシュサイズの変更 SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 値; 172

173 REDOログの問題 REDOログ バッファとLGWR REDOログ バッファの情報をREDOログ ファイルに書き 込むのが LGWR ログ スイッチ LGWRの書き込み先のREDOログ ファイルが切り替わる時 点のこと チェックポイント処理が伴う チェックポイント CKPTがDBWRに対して処理命令を送り データベース バッファ キャッシュ内の変更済みブロックをデータファイ ルに書き込む さらにすべての制御ファイルとデータファイ ルのヘッダにあるシステム変更番号 SCN を更新し シス テムが使用している制御ファイル 格納されているデータ ファイル REDOログ ファイルなど すべてのファイルの 整合性を成り立たせる 負荷が高い 173

174 REDOログの問題 REDOログ ファイルへの書き込みタイミング 3秒置き COMMIT要求時 ログ バッファの3分の1がいっぱいになったとき DBWRが書き込みを行う直前 ログ バッファの適正化 ログ バッファはSGA内のメモリ上に確保 パラメータ log_buffer で設定 統計情報の redo buffer allocation retries の回数が多い状態であ れば ログ バッファに書き込み待ち 再実行 が発生している ことになるので この再実行を減らすためにlog_bufferのサイズを 大きくする ほとんど0に近い状況が理想 SQL> select value from v$sysstat where name = 'redo buffer allocation retries'; 174

175 REDOログの問題 ログスイッチ 現行のログ グループが満杯になったとき 管理者によるコマンド要求時 SQL> select TOTAL_WAITS AS 待ち回数, 2 TOTAL_TIMEOUTS AS タイムアウト回数, 3 TIME_WAITED * 100 AS "待機時間合計 秒", 4 AVERAGE_WAIT * 100 AS "待機平均時間 秒", 5 MAX_WAIT * 100 AS "待機最大時間 秒" 6 from V$session_event 7 where event = 'log file switch completion'; 待ちが多い場合 REDOログファイルを大きくするかグ ループを追加する 175

176 REDOログの問題 チェックポイント ログ スイッチ時 管理者によるコマンド要求時 動的初期化パラメータで設定した値に達したとき ABORTオプション以外のデータベースのシャッ トダウン時 表領域のオフライン設定時 オンライン バック アップ開始時 1時間に1回程度が目安 V$LOG StatsPackのlog file switch等で確認 初期化パラメータで調整 176

177 REDOログファイル 全(REDOに限らず)データベース ファイルのI/O分散 REDOログ ファイルは 可能な限り 占有ディスクを割り当てることが推奨 される V$FILESTAT や V$SYSSTAT の redo size 列で どのファイルに 多くのI/Oが発生しているか調査 177

178 REDOログファイル アラートログに以下のようなメッセージが頻発 している場合 REDOログが小さいか個数不足 Thread 1 cannot allocate new log, sequence 8829 Checkpoint not complete 大きければよいわけではない 初期化パラメータの設定によりチェックポイントの 発生頻度を低くしてしまうと 障害発生時のクラッ シュ リカバリにより多くの時間を必要としてしま う 万一 カレントのオンラインREDOログ ファイル が障害を受けた場合に消失するトランザクション量 が多くなる 178

179 Objectのロード数 ロード回数の確認 SQL> SELECT OWNER AS 所有者, NAME AS パッケージ名, TYPE AS 形式, SHARABLE_MEM AS 使用サイズ, LOADS AS ﾛｰﾄﾞ回数, KEPT AS メモリ確保 FROM V$DB_OBJECT_CACHE WHERE TYPE = 'PACKAGE' AND OWNER = '[所有者]' ORDER BY LOADS DESC; 179

180 Objectのロード数 ロード回数が多い場合 共有プールに常駐させ る SQL> EXECUTE DBMS_SHARED_POOL.KEEP('[所有ユー ザ].パッケージ名',[フラグ 省略時パッケージ ]); 前頁の メモリ確保 が'YES'になる 開放する場合は UNKEEP パッケージ プロシージャ ファンクションなら'P' または'p' を タイプなら'T' または't' を トリガーな ら 'R' または'r' を 順序名なら 'Q' または'q' を指 定 180

181 PL/SQLコーディング Oracle Technology Seminar資料使用 181

182 データベース 182

183 データファイル 断片化を解消する SQL> ALTER TABLESPACE tblspc COALESCE; (ディクショナリ管理) export/import 表領域のディスク装置を分ける マスター系はDisk1 トランザクション系はDisk2な ど テーブルと そのテーブルに対するインデックス 結合対象のテーブル同士 パラレル処理の対象になるテーブルやインデックス システム表領域/UNDO表領域/一時表領域 183

184 チューニング実習 StatsPackを補足資料で詳説 実習 c:\oracle\jisshu\jisshu_tuning.txt 解説 184

185 リソース競合 185

186 ホストシステムの検査 CPU使用率 Oracle以外 Oracle V$SYSSTAT(CPU used by ｔｈｉｓ session=全 セッションで使用されているCPU合計 単位 1/100秒) V$SESSTAT(各セッションのCPU使用 率) チューニングに使用する動的パフォーマンスビューの詳細は J06248 Oracle9iデータベース パフォーマンス チューニング の 24.チューニングに使用する動的パフォーマンス ビュー を参照 186

187 ホストシステムの検査 I/O問題 Oracle V$SYSTEM_EVENT(db file sequential read db file scattered read db file single write db file parallel writeがi/o関連イベ ント 待機時間が大きいと競合の可能性) V$SQLAREA StatsPackの'SQL ordered by phisical reads'で物理読み込みの多いsqlを特 定しチューニング 187

188 ホストシステムの検査 ネットワーク OSユーティリティで調査 V$SESSION_EVENT V$SESSION_WAIT V$SES STAT等 Oracle Netトレース取得 sqlnet.ora listener.oraで設定 J06254 Oracle 9i Net Services 管理者ガイ ド 共有サーバー構成ならConnection Manager の利用 188

189 Oracle統計の調査 待機イベント サーバープロセスは次の症状に対して待機 リソースが使用可能になるまで(バッファ ラッチ) アクションが完了するまで(I/O) V$SESSION_WAIT V$SESSION_EVENT ラッチ ロック機能のようなもの V$SYSTEM_EVENT SGA内の共有データ構造を保護するための単純なシリアル化メカ ニズム たとえば 現在データベースにアクセスしているユー ザーのリストと バッファ キャッシュ内のブロックを説明して いるデータ構造を保護する サーバー プロセスまたはバックグ ラウンド プロセスは これらの構造の1 つを操作したり参照す189 る 非常に短い間のみラッチを取得する

190 Oracle統計の調査 待機イベント StatsPackレポート Top 5 Wait Events 190

191 Oracle統計の調査 待機イベント StatsPackレポート Wait Events 平均値 191

192 192

193 193

194 Oracle統計の調査 システム統計 V$SYSTEM_EVENT V$WAITSTAT V$SESSION_WAIT V$SYSSTAT ロールバック 論理および物理I/O 解析データな ど Oracle の様々な部分の統計全体が含まれる バッ ファ キャッシュ ヒット率などの比率を計算するには V$SYSSTAT からのデータを使用する V$FILESTAT ファイル当たりのI/O 回数や平均読込み時間など ファ イルごとの詳細なファイルI/O 統計が含まれる 194

195 Oracle統計の調査 システム統計 V$ROLLSTAT 詳細なロールバック セグメントおよびUNDO セグメン ト統計が含まれる V$ENQUEUE_STAT エンキュー(データベース リソースへのアクセスをシ リアル化するロック)が要求された回数やエンキューを 待機した回数 待機時間など 各エンキューの詳細なエ ンキュー統計が含まれる V$LATCH 各ラッチが要求された回数やラッチを待機した回数など 各ラッチの詳細なラッチ使用統計が含まれる 195

196 Oracle統計の調査 セグメントレベルの統計 V$SEGSTAT_NAME 収集するセグメント統計と 各種統計 たとえばサ ンプル統計など のプロパティがリストされる V$SEGSTAT リアルタイム監視が可能なこのビューには 統計値 統計名およびその他の基本情報が表示される V$SEGMENT_STATISTICS V$SEGSTAT のすべての列の他 セグメント所有者や 表領域名に関する情報がある 196

197 Oracle統計の調査 負荷の検査 StatsPackのLoad Profile 197

198 Oracle統計の調査 負荷の検査 閾値は各サイトで異なる 一般化された例 秒当たり100 を超えるハード解析率は システム上に非常 に大量なハード解析があることを示し 重大なパフォーマ ンスの問題を発生させる 通常は 高いハード解析率に共 有プール上のラッチの競合とライブラリ キャッシュ ラッチが伴う latch freeの待機が トップ5 の待機イベ ントに現れているかどうかをチェックし 現れていれ ば Statspackレポートのラッチ セクションを調べる 高いソフト解析率は 秒当たり300以上の率になる可能性が ある 不必要なソフト解析もアプリケーションの拡張性を 制限する SQL文をセッション当たり1回ソフト解析し 何 回も実行するよう最適化する 198

199 Oracle統計の調査 その他 REDOログ領域要求統計 V$SYSSTATのredo log space requestsはオンラ インredoログ内の領域待機回数を示す これが大きい場合 DBWR チェックポイントを チューニング(ログバッファを増やしても駄目) 199

200 Oracle統計の調査 その他 読み込み一貫性 大きいロールバックセグメントがほとんどない場合の対処 V$SYSSTATで 次の値は 1 に近い値であることが必要 ratio = transaction tables consistent reads undo records applied /transaction tables consistent read rollbacks 解決策は さらに多くの小さいロールバック セグメントを作成 するか 自動UNDO管理を使用すること ロールバックセグメントが充分にない場合の対処 WAITS 数をV$ROLLSTAT 内のGETS 数と比較する方法 GETS に対 するWAITS の比率は小さい値である必要がある V$WAITSTAT を調べて CLASS 'undo header' のバッファに対し て多数のWAITSがあるかどうかを確認する 解決策は さらに多くのロールバック セグメントを作成するこ と 200

201 Oracle統計の調査 その他 継続行による表フェッチ 移行と連鎖が発生するUPDATE文は遅い 移行行または連鎖行が追加入出力を実行する DBMS_STATS(ANALYZE)によるオプティマイザ統 計取得後 ディクショナリ(user_tablesなど) のCHAIN_CNT列を見る 201

202 Oracle統計の調査 チューニングに使用する動的パフォー マンス ビュー Oracle 9i データベース パフォーマン ス チューニング ガイド及びリファレン ス (J06248)の 24章 参照 10gでは記述なし 202

203 OEM 203

204 OEMのパフォーマンスツール 204

205 Diagnostics Pack(9i) Oracle Performance Manager Oracle Capacity Planner Oracle E-Business Management Tools Oracle TopSessions Oracle Trace Data Viewer Advanced Event Tests 205

206 Tuning Pack(9i) Oracle SQL Analyze 再編成ウィザード Oracle Expert 表領域マップ 索引チューニング ウィザード Outline Editor 9i新機能 Outline Management 9i新機能 206

207 10g(GUI)では 自動データベース診断モニター パフォーマンス自己分析 自動修正かソリューション 提示 外部ツール不要(DBカーネル内部に組み込み) Redoログ ファイルのサイジング アドバイザ 自動チェックポイント チューニング 自動共有メモリー チューニング トランザクションのロールバックとリカバリの監視 SQL Tuning Advisor 207

208 10gでは パフォーマンス統計の収集 自動ワーク ロード リポジトリ(AWR) STATISTICS_LEVEL=TYPICAL/ALL に BASICでは取得されない 自動データベース診断モニター(ADDM) 問題の指摘 推奨事項(CPU追加 SQLチューニングアドバ イザ実行 優先順位を付与) 実装(手動~ボタンで実装できるものもある) 208

209 10gでは SQLチューニングアドバイザ 推奨(書き方) 実装(ボタンクリック) SQLアクセスアドバイザ 推奨(マテリアライズドビュー インデックス 統 計取得) 実装(ボタンクリック) 209

210 10gでは Oracleで発生するパフォーマンス問題 CPUボトルネック(ADDM) SGA/PGAが小さい(ADDM) I/O容量(ADDM) 過剰なSQL解析特定データの競合(ADDM) 共有リソース競合(ある時間帯の大量ソートの 競合)(ADDM) データベース編成(ログファイルサイズ 過剰 チェックポイント)(ADDM) 210

211 10gでは 存続期間の短いパフォーマンス低下(ADDM ではわからない) 一定期間に渡るパフォーマンスの低下(期間 比較レポート AWR) 非効率SQL 高度なSQL(ADDM SQLチュー ニングアドバイザ) ホットオブジェクトへのデータアクセスパス (ADDM) 211

212 10gでは 212

213 10gでは 213

214 10gでは OEMを見てみよう 214

215 終了 お疲れ様でした 215