<Insert Picture Here>
Oracle
Direct Seminar
オラクルコンサルタントが語るSQLチューニングの真髄
解決編Part5
アジェンダ
•
はじめに
•
これまでの振り返り(Part1~Part4)
•
本日の範囲とゴール
•
設計と各種最適化の関係
•
設計の定義とフォーカスする範囲
•
設計、実装レベルのチューニング
•
データ主観の設計 正規化と統合化
-•
業務を最適化する設計
•
性能を最適化する設計
•
データベースの機能を使用した対処案
•
まとめ
•
Appendix 付録
・SQL Serverからの移行アセスメント
・MySQLからの移行相談
・PostgreSQLからの移行相談
・Accessからの移行アセスメント
・Oracle Database バージョンアップ支援
・Oracle Developer/2000 Webアップグレード相談
・パフォーマンス・クリニック
・Oracle Database 構成相談
・Oracle Database 高可用性診断
・システム連携アセスメント
・システムセキュリティ診断
・簡易業務診断
<Insert Picture Here>
はじめに
•
これまでの振り返り(Part1~4)
•
本日の範囲とゴール
•
解決編、とうとうPart5
•
SQLチューニングのアプローチ
•
パフォーマンス問題発生の理由の考察
•
定型的なチューニング
•
非定型的なチューニング
•
アーキテクチャを意識したチューニング
•
APロジックや設計を意識したチューニング
and more..
オプティマイザへの インプット情報は妥当か? 自身で実行計画を検討 オプティマイザが生成した 実行計画は適切か? インプット情報を修正できるか? NO YES NO YES NO YES NO YES チューニング完了 NO パフォーマンスは妥当か? SQL単体以外のチューニング アプリケーション チューニング 設計 チューニング 多重処理 チューニングこれまでの振り返り(Part1~4)
コンサルタントのチューニング・アプローチ
アーキテクチャを意識した
SQLチューニング
定型的な
SQLチューニング
非定型的な
SQLチューニング
2
1
3
システムのパフォーマンス問題のほとんどがSQL
パフォーマンス問題に帰着する
SQLパフォーマンス問題が減らない
SQLパフォーマンス問題の解決に時間がかかる
何をどのように調査すればよいのかわからない
言語的特徴に起因する理由がある
•
記述に対する柔軟性が高い
•
処理ロジックを意識させないコーディングができる
•
同一の処理内容に対して、複数の記述方法が可能
•
処理方法はデータベースに任されている
開発時の状況や意識に及ぶ理由がある
•
正しい結果が返ることには気にしても、処理の効率化や
データベース内での処理を意識した開発に気が回らない
現場の声・現状
原因・理由
SQLパフォーマンス問題解決へのアプローチ
問題発生の原因と理由の考察
予防
チューニング
問題がおきたら最低限
これだけはチェックする
最低限これだけは守って
コーディングする
「定型的なSQLチューニング」 =「最低限のSQLコーディング・ルール」
•
柔軟な言語だからこそ、守るべきルールが存在する
•
大きく4つのカテゴリに分かれる
•
アーキテクチャに伴う性能問題を避けるためのルール
•
使用方法やノウハウを元に性能問題を避けるためのルール
•
可読性や管理性を高めるためのルール
•
運用ポリシーを考慮したルール
•
ルールを活用するにはポイントがある
•
開発者にも直観的にわかりやすいものにする
•
ルールが必要となる理由を明確にし、指針、注意点、例なども加える
•
プログラムレビューと同様にSQLコーディングもレビュー(チェックシートでチェック)
SQLパフォーマンス問題解決へのアプローチ
定型的なSQLチューニング
•
CBOのインプット、アウトプット情報
•
「実行計画」を作成するために必要な情報は何か
CBO(コストベース
オプティマイザ)
SQLテキスト
パラメータ
オブジェクト構造
環境
実行計画
レスポンス
統計情報
データの実態
SQLパフォーマンス問題解決へのアプローチ
非定型的なSQLチューニング
妥当性の判断
全パターンを検証
するのは難しい
意識するのは
・SQL単体の
最適化
・システム全体の
最適化
表結合方法
索引を利用して
参照
表を直接参照
データ
アクセス方法
全表スキャン
(TABLE ACCESS FULL)
索引のレンジスキャン
(INDEX RANCE SCAN)
索引の一意スキャン
(INDEX UNIQUE SCAN)
索引のフルスキャン
(INDEX FULL SCAN)
索引の高速フルスキャン
(INDEX FAST FULL SCAN)
ネステッドループ結合
索引のスキップスキャン
(INDEX SKIP SCAN)
ハッシュ結合
ソートマージ結合
直積結合
判断のポイント
分岐の種類
SQLパフォーマンス問題解決へのアプローチ
非定型的なSQLチューニング
TAB2
ID
CLASS
TAB1
ID
START_DATE <= :b3+1
END_DATE > :b3
SQLパフォーマンス問題解決へのアプローチ
非定型的なSQLチューニング
アクセス1回 ⇒絞込でX件ヒット NLアクセス X回 ⇒X*1件ヒット①
TAB3
ID
CLASS
NLアクセス X回 ⇒X*63.5件ヒット②
TAB4
CODE = :b2
FLAG = ‘Y’
CLASS
③
NL アクセスX*63.5回 ⇒絞込で2件ヒットTAB5
NUM = :b1
CLASS
④
NL アクセス2回 ⇒絞込で1 or 2件ヒット Start Point 表 アクセス回数 カーディナリティ TAB1 1回 X行 TAB2 X回 X×1行 TAB3 X回 X×63.5行 TAB4 X×63.5回 2行 TAB5 2回 1 or 2行SQL解析
SQLパフォーマンス問題解決へのアプローチ
アーキテクチャを意識したSQLチューニング
PGA
DBバッファキャッシュ
共有プール
REDOログバッファ
SGA
SQL
実行計画
AP
6 5 4 3 2 1 6 5 4 3 2 1ディスクからの読込み
バッファからの読込み
発生している待機「cursor: pin S」「latch : cache buffers chains」を考察
ここ!
サーバ
プロセス
ここ!
cursor: pin S
latch : cache buffers chains
•
何の(どこに関連した)処理か?
「cursor: pin S」 共有プール
SQLは本当に必要なのか :
Case ①:
SQL関数の使用
Case ②:
アプリケーションの一時情報の格納
Case ②:
アプリケーションの一時的な情報の格納場所として、データベースを使用している場合
永続的な保護が必要なデータのみデータベースに格納する
go 1 STATUS Id ステータス表 DBサーバ APサーバ SQL発行 1 go STATUS Idt ステータス情報 DBサーバ APサーバ 実データ 実データ SQL発行 SQL発行•
SQLの必要性 / SQLの発行回数を減らす/ SQLの発行形態
SQLパフォーマンス問題解決へのアプローチ
アプリケーションロジックや設計を意識したSQLチューニング
【解決編 Part1~2】
•
SQL単体の最適化
•
定型的なSQLチューニング
•
非定型的なSQLチューニング
SQLパフォーマンス問題解決へのアプローチ
これまでとこれから
オプティマイザへの インプット情報は妥当か? 自身で実行計画を検討 オプティマイザが生成した 実行計画は適切か? インプット情報を修正できるか? NO YES NO YES NO YES NO YES NO パフォーマンスは妥当か? SQL単体以外のチューニング アプリケーション チューニング SQL単体 システム全体【Part3~】
•
システム全体の最適化
•
SQL単体を最適化しても発生する(解
決しない)パフォーマンス問題がある
設計 チューニング 多重処理 チューニング本セミナーの目的とゴール
本日
全体
•
SQLパフォーマンス問題を減らし、予防する仕組み
を身につける
•
システム全体で発生するSQLパフォーマンス問題を減らす
•
表面的なチューニングではなく裏に潜む真の問題を見つけて解決する
•
SQLパフォーマンス問題を予防する仕組みを実現する
•
設計時の不備をSQLの観点から改善
できるようになる
•
SQLのパフォーマンス問題と論理設計の関係をクリアにする
•
問題発生時にどのような点に着目して設計を確認すべきか整理する
<Insert Picture Here>
設計と各種最適化の関係
•
設計の定義とフォーカスする範囲
•
SQL単体の実行計画などは問題がないが性能が出ないケースがある。
① 設計時に考慮が足りないことがある
② システム開発の前フェーズでスルーされてしまうことがある
③ 運用中に問題が発覚して策がないことがある
設計の定義とフォーカスする範囲
設計の不備は設計者だけのせい?
SQLの観点から、設計の不備を発見していく
今回は、特に論理設計にフォーカスする
論理設計:
アプリケーションから見たデータベースの構造を定義する
•
APから見たデータベースの構造設計
•
表、列、データ型、桁数などの決定
•
キー、リレーション、整合性規約などの決定
•
索引候補、パーティション候補の決定
•
表領域、データファイルの設計
•
各ファイルのディスク上への物理配置を決定
•
インスタンス設計(初期化パラメータ)
•
容量設計
論理設計
物理設計
system01.dbf 表 索引System Data Index Temp UNDO データブロック エクステント 各種エンティティ データベース オブジェクトの 物理属性 表領域 temp01.dbf data01.dbf index01.dbf undo01.dbf ファイルや ディスクへの マッピング