アジェンダ はじめに これまでの振り返り (Part1~Part4) 本日の範囲とゴール 設計と各種最適化の関係 設計の定義とフォーカスする範囲 設計 実装レベルのチューニング データ主観の設計 - 正規化と統合化 - 業務を最適化する設計 性能を最適化する設計 データベースの機能を使用した対処案

Transcription

1 Oracle Direct Seminar <Insert Picture Here> オラクルコンサルタントが語る SQL チューニングの真髄 - 解決編 Part5 - 日本オラクル株式会社

2 アジェンダ はじめに これまでの振り返り (Part1~Part4) 本日の範囲とゴール 設計と各種最適化の関係 設計の定義とフォーカスする範囲 設計 実装レベルのチューニング データ主観の設計 - 正規化と統合化 - 業務を最適化する設計 性能を最適化する設計 データベースの機能を使用した対処案 まとめ Appendix 付録 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2010 Oracle. All rights reserved. 2

3 <Insert Picture Here> はじめに これまでの振り返り (Part1~4) 本日の範囲とゴール Copyright 2010 Oracle. All rights reserved. 3

4 これまでの振り返り (Part1~4) 解決編 とうとうPart5 SQLチューニングのアプローチ パフォーマンス問題発生の理由の考察 定型的なチューニング 非定型的なチューニング アーキテクチャを意識したチューニング APロジックや設計を意識したチューニング オプティマイザへのインプット情報は妥当か? YES NO オプティマイザが生成した実行計画は適切か? YES NO インプット情報を修正できるか? パフォーマンスは妥当か? YES NO 自身で実行計画を検討 NO and more.. YES NO SQL 単体以外のチューニング 多重処理チューニング アプリケーションチューニング 設計チューニング チューニング完了 Copyright 2010 Oracle. All rights reserved. 4

5 SQL パフォーマンス問題解決へのアプローチ問題発生の原因と理由の考察 現場の声 現状 システムのパフォーマンス問題のほとんどがSQL パフォーマンス問題に帰着する SQLパフォーマンス問題が減らない SQLパフォーマンス問題の解決に時間がかかる 何をどのように調査すればよいのかわからない 原因 理由 言語的特徴に起因する理由がある 記述に対する柔軟性が高い 処理ロジックを意識させないコーディングができる 同一の処理内容に対して 複数の記述方法が可能 処理方法はデータベースに任されている 開発時の状況や意識に及ぶ理由がある 正しい結果が返ることには気にしても 処理の効率化やデータベース内での処理を意識した開発に気が回らない 定型的な SQL チューニング コンサルタントのチューニング アプローチ 非定型的な SQL チューニング アーキテクチャを意識した SQL チューニング Part1/Part2 Part3 Copyright 2010 Oracle. All rights reserved. 5

6 SQL パフォーマンス問題解決へのアプローチ定型的な SQL チューニング 定型的な SQL チューニング = 最低限の SQL コーディング ルール 最低限これだけは守ってコーディングする 問題がおきたら最低限これだけはチェックする 予防 チューニング 柔軟な言語だからこそ 守るべきルールが存在する 大きく 4 つのカテゴリに分かれる アーキテクチャに伴う性能問題を避けるためのルール 使用方法やノウハウを元に性能問題を避けるためのルール 可読性や管理性を高めるためのルール 運用ポリシーを考慮したルール ルールを活用するにはポイントがある 開発者にも直観的にわかりやすいものにする ルールが必要となる理由を明確にし 指針 注意点 例なども加える プログラムレビューと同様に SQL コーディングもレビュー ( チェックシートでチェック ) Copyright 2010 Oracle. All rights reserved. 6

7 SQL パフォーマンス問題解決へのアプローチ非定型的な SQL チューニング CBO のインプット アウトプット情報 実行計画 を作成するために必要な情報は何か SQL テキスト オブジェクト構造 CBO( コストベースオプティマイザ ) パラメータ 実行計画 レスポンス データの実態 統計情報 環境 Copyright 2010 Oracle. All rights reserved. 7

8 SQL パフォーマンス問題解決へのアプローチ非定型的な SQL チューニング 判断のポイント 分岐の種類 妥当性の判断 表を直接参照 全表スキャン (TABLE ACCESS FULL) データアクセス方法 索引を利用して参照 索引のレンジスキャン (INDEX RANCE SCAN) 索引の一意スキャン (INDEX UNIQUE SCAN) 索引のフルスキャン (INDEX FULL SCAN) 索引の高速フルスキャン (INDEX FAST FULL SCAN) 索引のスキップスキャン (INDEX SKIP SCAN) 全パターンを検証するのは難しい 意識するのは SQL 単体の最適化 表結合方法 表結合順序 ネステッドループ結合ハッシュ結合ソートマージ結合直積結合 EMP 表から?DEPT 表から? それとも システム全体の最適化 Copyright 2010 Oracle. All rights reserved. 8

9 SQL パフォーマンス問題解決へのアプローチ非定型的な SQL チューニング TAB5 NUM = :b1 CLASS 4 TAB2 ID CLASS NL アクセス X 回 X*1 件ヒット アクセス 1 回 絞込で X 件ヒット NL アクセス 2 回 絞込で 1 or 2 件ヒット 1 TAB1 ID START_DATE <= :b3+1 Start Point TAB4 CODE = :b2 FLAG = Y CLASS 3 TAB3 ID CLASS 2 END_DATE > :b3 表アクセス回数カーディナリティ TAB1 1 回 X 行 NL アクセス X*63.5 回 絞込で 2 件ヒット NL アクセス X 回 X*63.5 件ヒット TAB2 X 回 X 1 行 TAB3 X 回 X 63.5 行 TAB4 X 63.5 回 2 行 TAB5 2 回 1 or 2 行 Copyright 2010 Oracle. All rights reserved. 9

10 SQL パフォーマンス問題解決へのアプローチアーキテクチャを意識した SQL チューニング 発生している待機 cursor: pin S latch : cache buffers chains を考察 SGA 共有プール DBバッファキャッシュ REDOログバッファ AP サーバプロセス PGA バッファからの読込み latch : cache buffers chains ここ! SQL 解析 ここ! cursor: pin S SQL 実行計画 ディスクからの読込み 何の ( どこに関連した ) 処理か? cursor: pin S 共有プール latch : cache buffers chains DBバッファキャッシュ メモリ領域に対する処理がボトルネックとなっている Copyright 2010 Oracle. All rights reserved. 10

11 SQL パフォーマンス問題解決へのアプローチアプリケーションロジックや設計を意識した SQL チューニング SQL の必要性 / SQL の発行回数を減らす / SQL の発行形態 SQLは本当に必要なのか : Case 1: SQL 関数の使用 Case 2: アプリケーションの一時情報の格納 Case 2: アプリケーションの一時的な情報の格納場所として データベースを使用している場合 AP サーバ SQL 発行 AP サーバ ステータス情報 Idt STATUS 1 go DB サーバ SQL 発行 DB サーバ SQL 発行 実データ Id STATUS 1 go ステータス表 実データ 永続的な保護が必要なデータのみデータベースに格納する Copyright 2010 Oracle. All rights reserved. 11

12 SQL パフォーマンス問題解決へのアプローチこれまでとこれから 解決編 Part1~2 SQL 単体の最適化 定型的なSQLチューニング 非定型的なSQLチューニング オプティマイザへのインプット情報は妥当か? YES NO オプティマイザが生成した実行計画は適切か? NO インプット情報を修正できるか? YES NO YES NO 自身で実行計画を検討 SQL 単体 Part3~ システム全体の最適化 SQL 単体を最適化しても発生する ( 解決しない ) パフォーマンス問題がある YES パフォーマンスは妥当か? 多重処理チューニング NO SQL 単体以外のチューニング アプリケーションチューニング 設計チューニング システム全体 チューニング完了 Copyright 2010 Oracle. All rights reserved. 12

13 本セミナーの目的とゴール 本日 設計時の不備を SQL の観点から改善できるようになる SQL のパフォーマンス問題と論理設計の関係をクリアにする 問題発生時にどのような点に着目して設計を確認すべきか整理する 全体 SQL パフォーマンス問題を減らし 予防する仕組みを身につける システム全体で発生するSQLパフォーマンス問題を減らす 表面的なチューニングではなく裏に潜む真の問題を見つけて解決する SQLパフォーマンス問題を予防する仕組みを実現する Copyright 2010 Oracle. All rights reserved. 13

14 <Insert Picture Here> 設計と各種最適化の関係 設計の定義とフォーカスする範囲 Copyright 2010 Oracle. All rights reserved. 14

15 設計の定義とフォーカスする範囲設計の不備は設計者だけのせい? SQL 単体の実行計画などは問題がないが性能が出ないケースがある 1 設計時に考慮が足りないことがある 2 システム開発の前フェーズでスルーされてしまうことがある 3 運用中に問題が発覚して策がないことがある SQLの観点から 設計の不備を発見していく今回は 特に論理設計にフォーカスする 論理設計 : アプリケーションから見たデータベースの構造を定義する Copyright 2010 Oracle. All rights reserved. 15

16 設計の定義とフォーカスする範囲論理設計と物理設計の違い 論理設計 物理設計 各種エンティティ データブロック エクステント データベースオブジェクトの物理属性 表 索引 System Data Index Temp UNDO 表領域 system01.dbf index01.dbf undo01.dbf data01.dbf temp01.dbf ファイルやディスクへのマッピング APから見たデータベースの構造設計 表 列 データ型 桁数などの決定 キー リレーション 整合性規約などの決定 索引候補 パーティション候補の決定 表領域 データファイルの設計 各ファイルのディスク上への物理配置を決定 インスタンス設計 ( 初期化パラメータ ) 容量設計 Copyright 2010 Oracle. All rights reserved. 16

17 設計の定義とフォーカスする範囲データ分析アプローチの種類 トップダウンアプローチ ユーザー要件やシステムの設計者の経験を基に システムに必要な情報を洗い出し エンティティや属性項目を決める ボトムアップアプローチ エンドユーザーが必要としている情報を 既存システムの画面や帳票 ファイルなどを基に抽出するアプローチ メリット デメリット 有効な使いどころ トップダウン システムに本来あるべき姿に近いモデリングを行うことができる ユーザーのニーズを全て収集するのが難しく 経験を要する 新規業務に関するデータを洗い出すときなど 既存システムから情報収集が行えない場合に有効 ボトムアップ ユーザーのニーズや現状に基づいたアプローチであるため わかりやすく漏れがない システムに本来あるべき姿に理想の形を見つけにくい 既存システムのリプレースなど エンドユーザーが必要としている情報が比較的明確になっている場合に有効 両方のアプローチを組み合わせてモデリングを行う事が重要 Copyright 2010 Oracle. All rights reserved. 17

18 <Insert Picture Here> 設計 実装レベルのチューニング データ主観の設計 - 正規化と統合化 業務を最適化する設計 性能を最適化する設計 Copyright 2010 Oracle. All rights reserved. 18

19 データ主観での設計正規化と統合化 データ主観での設計 正規化 リレーショナルデータベースの保持しやすい状態にデータを表形式化 統合化 正規化後 同じ識別項目を持つエンティティをまとめる フェーズ要件定義 分析設計構築 DB 設計作業 データ収集 非正規モデル作成 正規化 統合化 業務最適化 性能最適化 特定の RDBMS やシステム構成への変換 DB 構築 一般的な DB 設計工程 概念設計 論理設計物理設計 実装設計 実装 Copyright 2010 Oracle. All rights reserved. 19

20 正規化とは 概要 RDBMS の論理設計において 業務データを表形式に変換して行く際にデータの一貫性の維持と効率良いデータアクセスを導くための手法 メリット データの整合性 - 重複データが存在しないため データの一貫性が保ちやすい 更新処理の高速化 - 表当たりの索引数が減るため 更新処理が高速化できる 同時処理の改善 - 表が最適に分割されるため 表に対するロック影響を最小化できる Copyright 2010 Oracle. All rights reserved. 20

21 正規化されていないデータ 例 ) 正規化されていない表データ 社員番号氏名部署内線番号役職 1 オラクル太郎営業 100 課長 データの重複 2 オラクル次郎営業 100 主任 3 オラクル花子経理 200 主任 例 ) 正規化された表データ 社員番号 氏名 部署 役職 1 オラクル太郎 営業 課長 2 オラクル次郎 営業 主任 3 オラクル花子 経理 主任 レコードが削除されると経理部に関する情報が消失 部署 内線番号 営業 100 経理 200 重複項目 依存項目を別表へ分割 Copyright 2010 Oracle. All rights reserved. 21

22 正規化の手順 非正規形 第 1 正規化 繰り返し項目の排除 / 分離 第 1 正規形 第 2 正規化 キー項目の一部に依存した項目の排除 / 分離 第 2 正規形 第 3 正規化 第 3 正規形 データ項目の一部に依存した項目の排除 / 分離 導出項目の排除 Copyright 2010 Oracle. All rights reserved. 22

23 第 1 正規化 第 1 正規化では繰り返し項目の排除 / 分離を行う 繰り返し項目 受注番号 商品番号 (1) 商品名 (1) 値段 (1) 発注数 (1) 商品番号 (2) 商品名 (2) 値段 (2) 発注数 (2) 1 1 りんご /3 2 1 りんご みかん /4 3 3 なし /4 4 2 みかん なし /5 合計 受注日 受注番号 合計 受注日 / / / /5 繰り返し項目を分割 受注番号 商品番号 商品名 値段 発注数 1 1 りんご りんご みかん なし みかん なし Copyright 2010 Oracle. All rights reserved. 23

24 第 2 正規化 第 2 正規化ではキー項目の一部に依存した項目の排除 / 分離を行う 受注番号 商品番号 商品名 値段 発注数 1 1 りんご りんご みかん なし みかん なし 受注番号 商品番号 発注数 [ 商品番号 ] が決定すると [ 商品名 ] [ 値段 ] も決定するため 商品番号に依存している 商品番号商品名値段 1 りんご みかん 200 キー項目に依存している項目を別表として分割 3 なし 150 Copyright 2010 Oracle. All rights reserved. 24

25 第 3 正規化 第 3 正規化ではデータ項目の一部に依存した項目の排除 / 分離 導出項目の排除を行う 受注番号 商品番号 発注数 受注番号 合計 受注日 / / / /5 導出項目を排除 ( この例ではデータ項目に依存する項目はありません ) 商品番号 商品名 値段 1 りんご みかん なし 150 [ 発注数 ] と [ 値段 ] より [ 合計 ] が算出可能なため [ 合計 ] は導出項目にあたる 受注番号受注日 1 2/3 2 2/4 3 2/4 4 2/5 Copyright 2010 Oracle. All rights reserved. 25

26 統合化とは 統合化のメリット 複数のエンティティを 1 つのテーブルに統合して I/O 特性の向上を図る 運用管理単位ごとに統合することで 運用面の向上を図る 上位エンティティ ベース項目 ベース項目 サブセットエンティティ サブセット項目 1 サブセット項目 2 Null 統合 サブセット項目 1 サブセット項目 2 Null Copyright 2010 Oracle. All rights reserved. 26

27 統合化とは 各業務で正規化した後 同じ識別項目 ( 受注番号 ) を持つ エンティティを 1 つにまとめる 営業側で正規化した受注エンティティ 受注番号受注年月日営業担当者コード顧客コード販売区分備考 統合化した受注エンティティ 受注番号受注年月日営業担当者コード顧客コード販売区分受注備考納期年月日特注 / 標準区分生産管理担当者コード生産備考 生産側で正規化した受注エンティティ 受注番号納期年月日営業担当者コード顧客コード特注 / 標準区分生産管理担当者コード備考 Copyright 2010 Oracle. All rights reserved. 27

28 <Insert Picture Here> 設計 実装レベルのチューニング データ主観の設計 - 正規化と統合化 業務を最適化する設計 性能を最適化する設計 Copyright 2010 Oracle. All rights reserved. 28

29 業務を最適化する設計目的と手段 業務最適化の目的 業務要件や業務プロセスに基づいて業務機能が実現可能であるかを確認 業務最適化の手段 業務観点から必要な情報の抜け漏れの確認 時間経過を考慮した最適化 データサイクルを考慮した最適化 フェーズ要件定義 分析設計構築 DB 設計作業 データ収集 非正規モデル作成 正規化 統合化 業務最適化 性能最適化 特定の RDBMS やシステム構成への変換 DB 構築 一般的な DB 設計工程 概念設計 論理設計物理設計 実装設計 実装 Copyright 2010 Oracle. All rights reserved. 29

30 業務を最適化する設計時間経過を考慮した最適化 ER モデルでは時間経過が表現できない 業務プロセスや業務処理 データのライフサイクルの観点から確認 流れを意識しながらERモデルの最適化を行う 例 ) ビジネス プロセスとエンティティ抽出の確認 ビジネス プロセス : 受注出荷売上請求 入金 エンティティ : 受注売上請求入金 出荷のエンティティがないが大丈夫か? Copyright 2010 Oracle. All rights reserved. 30

31 業務を最適化する設計データサイクルを考慮した最適化 ER モデルではデータサイクルや実装機能は表現できない データ操作や機能の抜けもれ データの整合性の観点を確認 ユーザ インターフェースをイメージしながらERモデルの最適化を行う 例 ) プロセスとエンティティ抽出の確認 随時 月次 エンティティ 受注物流請求入金 リソース 顧客 R R U 商品 R R 営業担当者 R R イベント 受注 CRUD R 受注明細 CRUD R C: 生成 (Create) R: 参照 (Reference) U: 修正 (Update) D: 削除 (Delete) Copyright 2010 Oracle. All rights reserved. 31

32 <Insert Picture Here> 設計 実装レベルのチューニング データ主観の設計 - 正規化と統合化 業務を最適化する設計 性能を最適化する設計 Copyright 2010 Oracle. All rights reserved. 32

33 性能を最適化する設計目的と手段 性能最適化の目的 負荷が高い業務やテーブルに対する構造上の弱点を減らす 性能要件 ( 処理トラフィックやスケーラブル ) を満たす設計 業務プロセス 運用管理性が考慮された設計 性能最適化の手段 分割化 統合化 冗長化 要約化 フェーズ要件定義 分析設計構築 DB 設計作業 データ収集 非正規モデル作成 正規化 統合化 業務最適化 性能最適化 特定の RDBMS やシステム構成への変換 DB 構築 一般的な DB 設計工程 概念設計 論理設計物理設計 実装設計 実装 Copyright 2010 Oracle. All rights reserved. 33

34 性能観点から対策を検討する分割化と統合化による性能最適化 分割化 統合化とは 表をわけるか まとめるか どのようにデータにアクセスされるか SQL の性能観点で確認 I/O の効率化 開発 / 運用の生産性 男 個人 性別 顧客 顧客番号顧客名 女 R 個人 / 法人区分 法人 代表者名 サブセット項目 1 ベース項目 拡張項目 サブセット項目 2 Null 個人テーブル顧客番号 顧客名性別 個人テーブル顧客番号 (FK) 性別 顧客テーブル顧客番号顧客名 顧客テーブル顧客番号 顧客名個人 / 法人区分性別代表者名 法人テーブル顧客番号 顧客名代表者名 ベースサブセット分割 法人テーブル顧客番号 (FK) 代表者名 すべて統合 サブセット分割 Copyright 2010 Oracle. All rights reserved. 34

35 分割と統合による比較業務プロセスの頻度別の比較例 業務プロセス特徴別の実装シミュレーション 個人 法人 で個別アクセスの頻度が高い 個人 法人 が同時アクセスの頻度が高い Copyright 2010 Oracle. All rights reserved. 35

36 分割と統合による比較 Ⅰ. 個人 法人 で個別アクセスの頻度が高い テーブル構成実現方法 SQL アクセスデータのイメージ ( ) 特徴 1 分割 個人テーブル 顧客番号 顧客名性別 法人テーブル 顧客番号 顧客名代表者名 SELECT... FROM 個人テーブル ; テーブル指定 アクセス件数が尐ない 頻度高の業務プロセス ( 個人と法人は別フロー ) マッチ 他の絞り込み条件での処理でも生産性が高い 2 分割 個人テーブル 顧客テーブル 顧客番号 顧客名 法人テーブル SELECT... FROM 顧客テーブル kk, 個人テーブル kj WHERE kk. 顧客 NO= kj. 顧客 NO; 等価結合 複数テーブルを結合するオペレーション分オーバーヘッド有り 2 つのテーブルに対する更新処理が必要 先に個人テーブルで絞れる場合は索引スキャン 顧客番号 (FK) 顧客番号 (FK) 性別 代表者名 3 統合 顧客テーブル 顧客番号 顧客名個人 / 法人区分性別代表者名 SELECT... FROM 顧客テーブル WHERE 区分 = 個人 ; 絞り込み条件指定 カーディナリティが低く索引スキャンのメリットが低い ( 個人 法人区分を条件に絞り込む場合 ) 頻度高の業務プロセス ( 個人と法人は別フロー ) マッチしない 概要イメージ 条件や個別処理 索引使用有無などにより変動 動作については Appendix 参照 Copyright 2010 Oracle. All rights reserved. 36

37 分割と統合による比較 Ⅰ. 個人 法人 で個別アクセスの頻度が高い パターン 1 がより効率的 結合オペレーションが必要ない アクセス件数 ブロック数共に尐なくて済む 頻度が高い業務プロセスとマッチしており 生産性も高い Copyright 2010 Oracle. All rights reserved. 37

38 分割と統合による比較 Ⅱ. 個人 法人 が同時アクセスの頻度が高い テーブル構成実現方法 (SQL) アクセスデータのイメージ ( ) 特徴 1 分割 個人テーブル 顧客番号 顧客名性別 法人テーブル 顧客番号 顧客名代表者名 SELECT xxx, yyy FROM 個人テーブル UNION ALL SELECT xxx,yyy FROM 法人テーブル ; UNION ALL 句 2 つのテーブルの検索結果を読みこむ必要がある アクセスするブロックが多い 2 分割 個人テーブル 顧客テーブル 顧客名 顧客番号 (FK) 性別 顧客番号 法人テーブル 顧客番号 (FK) 代表者名 SELECT xxx,yyy,aaa FROM 顧客テーブル kk, 個人テーブル kj WHERE kk. 顧客 NO= kj. 顧客 NO(+); 外部結合 SELECT xxx,yyy FROM 顧客テーブル ; 単一テーブル 詳細項目が必要 複数テーブルを結合するオペレーション分オーバーヘッドがある 更新処理も 2 つのテーブルに対して必要 共通項目のみ 結合のオーバーヘッドなし 1 ブロックあたりの必要データ多 頻度高の業務プロセス ( 個人と法人は別フロー ) マッチ 3 統合 顧客テーブル 顧客番号 顧客名個人 / 法人区分性別代表者名 SELECT... FROM 顧客テーブル ; 単一テーブル 結合のオーバーヘッドがない 頻度高の業務プロセス ( 個人と法人は別フロー ) マッチ 1 ブロックあたりの必要データ尐 概要イメージ 条件や個別処理 索引使用有無などにより変動 Copyright 2010 Oracle. All rights reserved. 38

39 分割と統合による比較 Ⅱ. 個人 法人 が同時アクセスの頻度が高い パターン 2 パターン 3 がより効率的 同時アクセスが共通項目のみで済む場合 パターン2( 詳細項目は顧客番号指定でアクセス ) 共通項目のみであれば列数が尐なく 1ブロック内のレコード数が多い 取得行数が同じでも データの取得効率が高い 同時アクセスが個別項目も必要になる場合 パターン3 どの項目を抽出する場合でも単一表アクセスで結合オーバヘッドなし 更新処理も単一表で済み 生産性も高い Copyright 2010 Oracle. All rights reserved. 39

40 性能観点から対策を検討する冗長化による性能最適化 冗長化は非正規化の作業 冗長化のメリットは非正規化のデメリットであり 逆もしかり あるべき第 3 正規化を行ってから 冗長化を考える Copyright 2010 Oracle. All rights reserved. 40

41 冗長化 ( 非正規化 ) 冗長化のメリット (= 正規化のデメリット ) 正規化を徹底したデータはデータが分散しているが 冗長化により繰り返し項目を持たせることで 関連データを一行に収め I/O 特性の向上を図ることを目的としている 冗長化 ( 現在より低い正規形に戻す ) により結合表数を減らす SELECT 受注番号, 発注数, 発注日 FROM 第 3 正規形 : 結合回数 1 回 第 2 正規形 : 結合なし 受注番号商品番号発注数 受注番号 受注日 受注番号商品番号発注数受注日 /3 2 2/4 3 2/4 4 2/ / / / / /5 Copyright 2010 Oracle. All rights reserved. 41

42 冗長化 ( 非正規化 ) 冗長化のデメリット (= 正規化のメリット ) 1 行のサイズが大きくなるため 行移行 行連鎖が発生しやすい 汎用性が低くなり再利用しにくくなるので 仕様変更やシステムを新しくする際のコストがかかる 特別な条件 ( ) 以外は 原則として冗長化は禁止 第 3 正規形 : 結合回数 1 回 第 2 正規形 : 結合なし 受注番号商品番号発注数 受注番号 受注日 受注番号商品番号発注数受注日 /3 2 2/4 3 2/ / / / / / /5 繰り返しの数が非常に尐ない場合 複数表に対する結合階層や結合パターンが非常に多くなる場合など Copyright 2010 Oracle. All rights reserved. 42

43 性能観点から対策を検討する要約化による性能最適化 業務で必要となる要約したデータを持たせることで I/O 特性や業務ロジック性能向上を図ることを目的としている 1. 親表への要約項目追加親表子表 要約項目 2. 要約表の追加 明細表 伝票番号年月地域商品売上額 サマリ表 年月地域商品合計売上額 3. 導出項目の追加 4. 同一項目の二重管理 明細表 伝票番号商品単価数量 合計売上額 表 A 項目 1 表 B 項目 1 Copyright 2010 Oracle. All rights reserved. 43

44 要約化 受注番号ごとの発注数を要約した要約表の追加 受注項目表 受注表 受注番号 商品番号 発注数 受注番号受注日 1 2/3 2 2/4 3 2/4 4 2/5 SELECT 文による集計結果を格納 要約表 受注番号 受注日 総発注数 1 2/ / / /5 1 Copyright 2010 Oracle. All rights reserved. 44

45 <Insert Picture Here> データベースの機能を使用した対処案 クラスタ表による冗長化 マテリアライズドビューによる要約化 Copyright 2010 Oracle. All rights reserved. 45

46 クラスタ表による冗長化クラスタ表とは 複数の表を 1 つの領域 ( セグメント ) に格納し 同じデータブロックを共有する表のグループみたいなもの クラスタ表のメリット 論理的な関係を変更することなく物理的に同じ場所 ( 同じブロック ) に格納できる 別々にアクセスされることの多い表にはクラスタ表を使用しない! データブロッククラスタキー ( 親表のプライマリキー ) 親表のデータクラスタキーに属する子表のデータ クラスタ表による冗長化の対応 Copyright 2010 Oracle. All rights reserved. 46

47 クラスタ表による冗長化クラスタ表の構造 クラスタ化表 ( クラスタ化キー受注番号 ) 受注番号受注日 1 2/3 非クラスタ化表 受注番号 商品番号 発注数 受注番号受注日 1 2/3 2 2/4 3 2/4 4 2/5 商品番号発注数 1 1 受注番号受注日 2 2/4 商品番号発注数 受注番号受注日 3 2/4 商品番号発注数 3 2 受注番号 受注日 関連データを別々に格納するため必要領域も増大 関連データをまとめて格納できるため効率がよい 4 2/5 商品番号発注数 2 1 Copyright 2010 Oracle. All rights reserved. 47

48 マテリアライズドビューによる要約化要約表の追加例 受注番号ごとの発注数を要約した要約表の追加 受注項目表 受注表 受注番号 商品番号 発注数 受注番号受注日 1 2/3 2 2/4 3 2/4 4 2/5 SELECT 文による集計結果を格納 SELECT a. 受注番号, b. 受注日, SUM(a. 発注数 ) 総発注数 FROM 受注項目表 a, 受注表 b WHERE a. 受注番号 =b. 受注番号 GROUP BY a. 受注番号 ; 要約表 受注番号 受注日 総発注数 1 2/ / / /5 1 Copyright 2010 Oracle. All rights reserved. 48

49 マテリアライズドビューによる要約化マテリアライズドビューの構造 リモート データベース上に存在するデータをローカル データベース上に定期的にコピーするもの ( スナップショット ) データの集計や結合処理の結果を実体として持たせるもの 結合処理など 複雑な検索処理のパフォーマンス向上のために用いられる リモートコピー結合集計 ( 要約 ) sum sum count count マテリアライズドビューマテリアライズドビューマテリアライズドビュー Copyright 2010 Oracle. All rights reserved. 49

50 <Insert Picture Here> まとめ Copyright 2010 Oracle. All rights reserved. 50

51 SQL パフォーマンス問題解決へのアプローチ本日の範囲 解決編 Part1~2 SQL 単体の最適化 定型的なSQLチューニング 非定型的なSQLチューニング オプティマイザへのインプット情報は妥当か? YES NO オプティマイザが生成した実行計画は適切か? NO インプット情報を修正できるか? YES NO YES NO 自身で実行計画を検討 SQL 単体 Part3~ システム全体の最適化 SQL 単体を最適化しても発生する ( 解決しない ) パフォーマンス問題がある YES パフォーマンスは妥当か? 多重処理チューニング NO SQL 単体以外のチューニング アプリケーションチューニング 設計チューニング システム全体 チューニング完了 Copyright 2010 Oracle. All rights reserved. 51

52 SQL パフォーマンス問題解決へのアプローチまとめ SQL 単体では問題ないが性能がでないケース 1 設計時に考慮が足りないことがある データ主観の設計 ( 正規化と統合化 ) / 業務を最適化する設計 / 性能を最適化する設計について考慮された設計が行われている必要がある 2 システム開発の前フェーズでスルーされてしまうことがある 設計の知識と性能への関連性について (1) 開発者が知識を持つことで 設計の不備を SQL 観点から逆引きで指摘し改善することができる 3 運用中に問題が発覚して策がないことがある 運用時に問題が発生した場合 どのような点に着目して設計を確認すべきかが整理できている 1 つの案として データベースの機能を使用した解決案も提示できること 設計時の不備を SQL の観点から改善できるようになる Copyright 2010 Oracle. All rights reserved. 52

53 <Insert Picture Here> Appendix Copyright 2010 Oracle. All rights reserved. 53

54 統合と分割別アクセスの動作例 個人テーブル顧客番号顧客名 001 Aさん 002 Bさん 003 Cさん 004 Dさん 性別男女男男 個人テーブルを全表走査してデータを取得する パターン 1 最初から検索対象が絞り込まれているため走査件数が尐なくなる パターン 2 結合する分 1 行のデータを読むコストは高くなる 個人テーブル顧客番号性別 001 男 003 女 004 男 個人テーブルを全表走査 取得した顧客番号で顧客テーブルをインデックス スキャンして結合 顧客テーブル顧客番号顧客名 001 Aさん 002 B 社 003 Cさん 004 Dさん 005 E 社 顧客テーブル顧客番号顧客名 001 Aさん 002 B 社 003 Cさん 004 Dさん 005 E 社 区分個人法人個人個人法人 性別男 女男 代表者名 B さん Eさん 顧客テーブルを全表走査して区分によって個人を選択 パターン 3 個人 / 法人区分を検索条件として指定する必要がある Copyright 2010 Oracle. All rights reserved. 54

55 バーチャルカラムによる要約化 実データは持たず 他のカラムから関数によって定義される仮想的な列 例 ) acc_no( アカウント番号 ) の上 2 桁を acc_branch( 支店番号 ) として用いる場合 acc_no acc_name acc_branch Nakashima Aritaki Seo Aritaki Inoue 13 バーチャルカラムのメリット ある列に対するファンクションや計算式を記述する必要がない Copyright 2010 Oracle. All rights reserved. 55

56 OTN ダイセミでスキルアップ!! 一般的な技術問題解決方法などを知りたい! セミナ資料など技術コンテンツがほしい! Oracle Technology Network(OTN) を御活用下さい 一般的技術問題解決には OTN 掲示版の データベース一般 をご活用ください OTN 掲示版は 基本的に Oracle ユーザー有志からの回答となるため 100% 回答があるとは限りません ただ 過去の履歴を見ると 質問の大多数に関してなんらかの回答が書き込まれております 過去のセミナ資料 動画コンテンツは OTN の OTN セミナーオンデマンドコンテンツ へ ダイセミ事務局にダイセミ資料を請求頂いても お受けできない可能性がございますので予めご了承ください ダイセミ資料は OTN コンテンツオンデマンドか セミナ実施時間内にダウンロード頂くようお願い致します Copyright 2010, Oracle. All rights reserved. 56

57 Windows 環境でもシェア No.1!Database の合言葉はオラ 98( キュッパ ) 日頃ご利用 ご提案頂いている皆様の声を投票にてお聞かせください 抽選でプレミアム グッズをプレゼント 47 都道府県を超えたら 投票総数が 2,000 件を超えたら 賞品がグレードアップ!! 締め切りは 11 月 30 日 ( 火 ) まで 皆様のご応募をお待ちしております!! 日本全国オラ 98 Copyright 2010, Oracle. All rights reserved. 57

58 OTN セミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!! ダイセミのライブ感はそのままに お好きな時間で受講頂けます 最新情報つぶやき中 oracletechnetjp 人気コンテンツは? お勧め情報 公開予告など OTN オンデマンド 掲載のコンテンツ内容は予告なく変更になる可能性があります 期間限定での配信コンテンツも含まれております お早めにダウンロード頂くことをお勧めいたします Copyright 2010, Oracle. All rights reserved. 58

59 Oracle エンジニアのための技術情報サイトオラクルエンジニア通信 最新情報つぶやき中 oracletechnetjp 技術資料 コラム ダイセミの過去資料や製品ホワイトペーパー スキルアップ資料などを多様な方法で検索できます キーワード検索 レベル別 カテゴリ別 製品 機能別 オラクル製品に関する技術コラムを毎週お届けします 決してニッチではなく 誰もが明日から使える技術の あ そうだったんだ! をお届けします オラクルエンジニア通信 先月はこんな資料が人気でした Oracle 10gR2 が Windows 2008R2/Windows7 に対応 チュートリアル 意外と簡単!? Oracle Database 11g Release2 - Windows 版 データベース構築編 Oracle Database 11gR2 RAC インストレーション ガイド ASM 版 Microsoft Windows x86-64 Copyright 2010, Oracle. All rights reserved. 59

60 IT プロジェクト全般に渡る無償支援サービス パフォーマンス診断サービス Web システムボトルネック診断サービス データベースパフォーマンス診断サービス 移行支援サービス SQL Server からの移行支援サービス DB2 からの移行支援サービス Sybase からの移行支援サービス MySQL からの移行支援サービス Postgre SQL からの移行支援サービス Access からの移行支援サービス Oracle Direct Concierge サービス NEW Oracle Application ServerからWeblogicへ移行支援サービス NEW システム構成診断サービス Oracle Database 構成相談サービス サーバー統合支援サービス 仮想化アセスメントサービス メインフレーム資産活用相談サービス BI EE アセスメントサービス 簡易業務診断サービス バージョンアップ支援サービス Oracle Database バージョンアップ支援サービス Weblogic Server バージョンアップ支援サービス Oracle Developer/2000(Froms/Reports) Web アップグレード相談サービス NEW オラクル社のエンジニアが直接ご支援しますお気軽にご活用ください! オラクル無償支援 検索 Copyright 2010, Oracle. All rights reserved. 60

61 11 月 30 日まで!! 締め切り迫る あの Oracle Database Enterprise Edition が超おトク!! Oracle Database のライセンス価格を大幅に抑えて ご導入いただけます 多くのお客様でサーバー使用期間とされる 5 年間にライセンス期間を限定 期間途中で永久ライセンスへ差額移行 5 年後に新規ライセンスを購入し継続利用 5 年後に新システムへデータを移行 Enterprise Editionはここが違う!! 圧倒的なパフォーマンス! データベース管理がカンタン! データベースを止めなくていい! もちろん障害対策も万全! 詳しくはコチラ お問い合わせフォーム Copyright 2010, Oracle. All rights reserved. 61

62 あなたにいちばん近いオラクル Oracle Direct まずはお問合せください Oracle Direct 検索 システムの検討 構築から運用まで ITプロジェクト全般の相談窓口としてご支援いたします システム構成やライセンス / 購入方法などお気軽にお問い合わせ下さい Web 問い合わせフォームフリーダイヤル 専用お問い合わせフォームにてご相談内容を承ります フォームの入力には Oracle Direct Seminar 申込時と同じログインが必要となります こちらから詳細確認のお電話を差し上げる場合がありますので ご登録さ れている連絡先が最新のものになっているか ご確認下さい 月曜 ~ 金曜 9:00~12:00 13:00~18:00 ( 祝日および年末年始除く ) Copyright 2010, Oracle. All rights reserved. 62

63 以上の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle PeopleSoft JD Edwards 及び Siebel は 米国オラクル コーポレーション及びその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標の可能性があります Copyright 2010, Oracle. All rights reserved. 63