ハードパースとソフトパース

概要

ハードパースとソフトパースについての解説

ハードパース

様々な実行計画を生成し、最適な実行計画を選択

• 統計情報が収集済みのとき（ソフトパース）

    * 統計情報を利用   
    * 統計情報：表、列、索引、システム  
  

• 統計がない時or古い時（ハードパース）

    * 動的サンプリングで収集した統計情報を利用  
  

optimizer_dynamic_sampling

統計が不足している際、SQL実行時に自動的統計をサンプリング
サンプリングレベルを0-10で指定(0はOFF)

親カーソルと子カーソル

実行計画生成時、1つの親カーソルに対し、1つ以上の子カーソルが生成

ハードパースの流れ

以前に実行されていない場合⇒ハードパース(実行計画を生成)
ディクショナリ・キャッシュを利用し、SQL文の構文や権限をチェック

共有プールの共有SQL領域を確保⇒実行計画を生成

ソフトパース

共有SQL領域にキャッシュされている実行計画を利用
- オプティマイザや行ソースジェネレータによる処理が不要⇒CPU負荷の軽減
- 新たな共有SQL領域の確保が不要⇒メモリ使用量の低減

ソフトパースの流れ

以前に実行されている場合⇒ソフトパース(実行計画を再利用)

共有SQL領域上にある以前の実行計画を再利用

SQL文を実行した際のメモリ上の動作

概要

SQL文を実行した際のメモリ上の動作を説明

SQL実行時のメモリ上の動作

SQL文を発行した後、メモリ上では大きく以下のような流れとなる
1. SQLリクエストを受信
↓
2. カーソルのオープン
↓
3. 実行計画の生成/再利用
↓
4. SQL文の実行
↓
5. カーソルのクローズ

カーソル:特定のプライベートSQL領域

• SQL文は、共有SQL領域とプライベートSQL領域（カーソル）に格納される
• 共有SQL領域は共有プールのライブラリキャッシュ内にあり、SQL文の解析ツリーと実行計画が格納される
• プライベートSQL領域（カーソル）には以下の情報が格納される
  ・持続領域: バインド変数値が格納される
  ・ランタイム領域

  - 問合せ実行状況の情報: たとえば、全表スキャンの場合、この領域にはスキャンの進行情報が格納される  
  - SQL作業領域:ソートやハッシュ結合など、メモリー集中型の操作に必要な場合に割り当てられる  
  

  

実行計画生成

実行計画生成の前提知識(SQL_ID/PLAN_HASH_VALUE/CURSOR)

概要

SQLや実行計画を解析する際の最低限知っておくべきと考える情報を記載します。

SQL_ID(SQL識別子)

意味

Oracleは実行されたSQL文を13桁の「文字列」で管理していますが、この文字列のことをSQL識別子(SQL_ID)と呼びます。

特徴

SQL識別子(SQL_ID)はSQLテキストに依存して作成されます。そのため、以下の特徴があります。

その他

SQL_IDは、13文字の文字列で表される基数32の数字です。
各文字は数字または32の小文字のいずれかとなります。 ※例) ddzxfryd0uq9t

文字e、i、l、oは、転記エラーのリスクを制限するために使用されていません。
SQL_IDは、実際にはSQLステートメントの文字から生成されたハッシュ(MD5)です。
そのため、その大文字小文字と空白が保持されていると仮定すると、同じSQLステートメントは、使用されるすべてのデータベースで同じSQL_IDを持ちます。

PLAN_HASH_VALUE

意味

Oracleは実行計画も一意の識別子(識別番号)で管理していますが、この識別子のことをPLAN_HASH_VALUEと呼びます。

特徴

PLAN_HASH_VALUEは実行計画に依存して作成されます。
以下の特徴があります。

その他

PLAN_HASH_VALUEが同一であれば同一の実行計画とみなせます。
例えば検証環境と本番環境のPLAN_HASH_VALUEが同一であれば、実行計画が同じと言えます。

カーソル

意味

SQLを実行すると、SQLがHard Parseされて実行計画が共有プールに格納されます。
共有プールに格納された、SQL文と実行計画のセットをカーソルと呼びます。
カーソルはV$SQLやその関連表に格納され、V$SQLの1レコードが1カーソルに対応します。

特徴

カーソルはスキーマや実行計画の違いを管理するために使用されます。
このため、ある1つのSQL文(SQL_ID)であっても複数のカーソルが存在する場合があります。
例：同じSQL文(SQL_ID)を別のスキーマで実行した場合、同一のSQL_IDに複数のカーソルが作成されます。
場合によっては同じSQL_IDであっても複数の実行計画ができる場合があります。

SQL文や実行計画に関連するビュー

V$SQLAREA

V$SQLAREAは、共有SQL領域の統計情報を示し、SQL文字列ごとに1行ずつ表示します。
メモリー内にあり、解析済で、実行準備のできているSQL文に関する統計情報を提供します。

代表的な情報

• 全ての子カーソルに関する情報
• SHARABLE_MEM:カーソルが使用している共有メモリーの量。
• 子カーソルが複数存在する場合の、すべての子カーソルに使用される、すべての共有メモリーの合計。
• VERSION_COUNT:この親の下でキャッシュ内にある子カーソルの数
• EXECUTIONS:すべての子カーソルについて、実行の合計数
• PHYSICAL_READ_BYTES:監視対象のSQLによってディスクから読み取られたバイト数

V$SQL

V$SQLは、GROUP BY句のない共有SQL領域についての統計情報を示し、入力された元のSQLテキストの子カーソルごとに1行ずつ表示します。
V$SQLに表示される統計情報は、通常、問合せの実行が終了した時点で更新されます。
ただし、問合せの実行が長期にわたる場合は、5秒ごとに更新されます。
これによって、実行中に、長時間実行されるSQL文の影響を容易に確認できます。

代表的な情報

• SQL_ID:ライブラリ・キャッシュ内の親カーソルのSQL識別子
• 子カーソル固有の情報
• 実行統計
• ROWS_PROCESSED:解析されたSQL文が戻す行数の合計

V$SQL_PLAN

V$SQL_PLANは、ライブラリ・キャッシュにロードされる子カーソルごとの実行計画情報を示します。

V$SQLTEXT

V$SQLTEXTは、SGA内の共有SQLカーソルに属するSQL文のテキストを示します。

SQL_ID確認方法

参考資料

Expert Oracle SQL
www.amazon.co.jp

Oracle 主要な待機イベント(RAC固有)

主な待機イベント一覧

Category	Wait Class	Event
RAC	Cluster	gc cr/current (multi block) request
	Cluster	gc cr/current grant 2-way
	Cluster	gc cr/current block 2-way/3-way
	Cluster	gc buffer busy acquire/release

インターコネクト

RACにはインターコネクトを通したデータのやり取りに関する待機イベントがある。 これらの待機イベントは大きく2種類に分けられる。
- GES(Global Enqueue Service)の待機イベント:インスタンス間の排他制御に関連
- GCS(Global Cache Service)の待機イベント:インスタンス間のバッファキャッシュ一貫性に関連
本記事ではGCSの説明を行う
ブロックを取得する際、ディスクと自ノードのバッファキャッシュ以外に他インスタンスのバッファキャッシュも取得先の選択肢となるため、ブロック取得の手続きがシングルインスタンスより複雑になる

GCS

• バッファには以下の2種類がある
  • CURRENT:ブロックの最新バージョン(DML文で要求される)
  • CR(consistent read):特定時点のコミット済み変更が反映されたブロック(SELECTで要求される)

    GCS待機イベントは下記のように表示される

    

CURRENTとCRの違い

gc cr/current (multi block) request

• 意味
  CRブロックあるいはCURRENTブロックの要求中のためにサーバープロセスが待機していることを示す
• 状況
  リクエストが完了し、正確な待機イベントが現れるまでの一時的な待機イベント(プレースホルダ)と位置付けられる

gc cr/current grant 2-way

• 意味
  CRブロックあるいはCURRENTブロックの要求に対して、ディスクからの読込みが許可されたことを示す ※何らかの要因でこの許可に時間を要した場合は、gc cr/current grant busy待機イベントが発生する
• 状況
  リソースマスターへの要求とサーバープロセスへの許可で2方向のやり取りを行う

gc cr/current block 2-way

• 意味
  CRブロックあるいはCURRENTブロックの要求に対してキャッシュしているインスタンスからブロック転送が行われたことを示す
• 状況
  リソースマスターへの要求とサーバープロセスへの転送で2方向のやり取りを行う

gc cr/current block 3-way

• 意味
  CRブロックあるいはCURRENTブロックの要求に対してキャッシュしているインスタンスからブロック転送が行われたことを示す
• 状況
  リソースマスターへの要求/ブロック保持ノードへの転送指示/サーバープロセスへの転送、の3方向のやり取りを行う

gc buffer busy acquire

• 意味
  同一インスタンスの別セッションがキャッシュフュージョンによって他のキャッシュからバッファを読み取り終わるのをサーバープロセスが待機していることを示す
• 状況
  同一ブロックの取得要求による競合によって発生する

gc buffer busy release

• 意味
  別インスタンスの別セッションがキャッシュフュージョンによってローカルキャッシュからバッファを読み取り終わるのを待機していることを示す
• 状況
  同一ブロックの取得要求による競合によって発生する

Oracle 主要な待機イベント

主な待機イベント一覧

Category	Wait Class	Event
I/O	User I/O	db file sequential read
	User I/O	db file scattered read
	User I/O	direct path read
	Commit	log file sync
	System I/O	db file parallel write
Memory	Configuration	free buffer waits
N/W	Network	SQL*Net more data to client

db file sequential read

• 意味
  サーバープロセスがバッファキャッシュから単一ブロックを読み込む際にディスクからメモリデータが配置されるのを待機している状態を示す
• 状況
  多くの場合、索引を経由したブロックの読込みを行っていることを意味する
  

db file scattered read

• 意味
  サーバープロセスがバッファキャッシュから不連続な複数ブロックを読み込む際、ディスクからメモリへデータが配置されるのを待機している状態を示す
• 状況
  多くの場合、表のフルスキャンや高速全索引スキャンによる読込みを行っていることを意味する
  

direct path read

• 意味
  ディスクからPGAへの読込でサーバープロセスが待機していることを示す
• 状況
  大規模表へのフルスキャンなどバッファキャッシュを経由しないブロック読込みを行っていることを意味する
  

log file sync

• 意味
  LGWRプロセスがREDOログファイルへREDO情報の書込みを完了させることをサーバープロセスが待機していることを示す
• 状況
  COMMIT、ROLL BACK発行時に発生する
  

db file parallel write

• 意味
  ダーティバッファ（更新されたブロック）をディスクへ書き込むI/O処理が完了するのをDBWRn（データベースライター）が待機していることを示す
• 状況
  チェックポイントなどDBWRnがバッファをディスクへ反映されるタイミングで発生する
  

free buffer waits

• 意味
  利用可能なバッファが用意されるのをサーバープロセスが待機していることを示す
• 状況
  サーバープロセスがデータベースバッファキャッシュ上に利用可能バッファを見つけられなかった際、DBWRnに書き出しを要求し、このイベントで待機する
  

SQL*Net more data to client

• 意味
  結果セットをクライアントに返す際に送信が滞り、サーバープロセスが待機していることを示す
• 状況
  結果セットの返信時、SDUのサイズを超えた時に発生する
  

Oracle 待機イベント概要

待機イベント

待機イベントは、プロセスがCPUを使用できず、待機させられるイベントのこと。 待機の要因に基づき細分化されているため、この点を利用してパフォーマンス分析・チューニングの実施が可能

AWR/StatspackのDB CPUと待機イベントの関係性は以下のような流れ

主な待機イベントの関係図

負荷の傾向把握のために参照する指標の関係図は以下

Oracle ヒント句

Oracle ヒント句

ヒント句

オプティマイザが適切な実行計画を選択してくれない、または不具合の影響等で性能要件を満たせない実行計画になることを抑止・安定化させる。
ただし、ヒントは融通が利かないため最終手段と考えるべき。
理由としてヒントで実行計画を固定した場合、経年劣化・データや検索傾向の変化に追随することはできないため。
そのため、使用する場合には十分に考慮・検証する必要がある。

ヒント一覧

Category	Hint	Description
最適化目標	ALL_ROWS	スループット優先指示（バッチ/BI系向き。但しOLTP的なSQL文をループで繰り返し実行するタイプのバッチを除く）
	FIRST_ROWS	レスポンス優先、最初のN行を最も速く返すような実行計画を立てるように指示する（OLTP向き）
アクセス パス	INDEX (NO_INDEX) INDEX_ASC INDEX_DESC	指定表の指定索引を利用する（しない）を指示。但し索引がどのようにアクセスされるかは指定できない（範囲検索ではなく全索引スキャンやスキップスキャンでアクセスされる可能性がある）。スキャン方向も制御したい場合には、INDEX_ASC/INDEX_DESCを使う
	INDEX_SS(NO_INDEX_SS) INDEX_SS_ASC INDEX_SS_DESC	指定した索引のスキップスキャンを行うよう（行わないよう）指示する。スキャン方向も（昇順、降順）指示したい場合は、INDEX_SS_ASC/INDEX_SS_DESCを使う
	FULL	指定した表を全表走査を指示する
	INDEX_JOIN	指定された索引を結合したスキャンを行うよう指示する。必要な列が必要最小限の索引に含まれている必要がある。索引だけを参照する実行計画になる。
	NATIVE_FULL_OUTER_JOIN (NO_NATIVE_FULL_OUTER_JOIN)	ネイティブ完全外部結合を使用するようオプティマイザに指示する。ネイティブ完全外部結合は、ハッシュ結合に基づくネイティブ実行メソッド。
	INDEX_FFS (NO_INDEX_FFSヒント)	指定した索引を高速全索引走査を行う（行わない）よう指示
	INDEX_COMBINE	指定された索引を基に内部でビットマップを作成し、作成されたビットマップを利用したスキャンを行うよう指示
結合順	ORDERED	FROM句に施術された順に結合を指示（開発者が結合を順を意識してFROM句に記述する必要がある）
	LEADING	ヒントに記述した表の順に結合するよう指示
結合操作	USE_HASH (NO_USE_HASHヒント)	指定された各表をハッシュ結合を使用して別の行のソースに結合するようオプティマイザに指示（ハッシュ結合をしないように指示）
	USE_MERGE (NO_USE_MERGE)	指定された各表をソート/マージ結合を使用して別の行のソースに結合するようオプティマイザに指示（ソート/マージ結合しないように指示）
	USE_NL (NO_USE_NL)	指定された表を内部表として使用し、指定された各表をネステッド・ループ結合とともに別の行のソースに結合するようオプティマイザに指示（ネステッド・ループ結合を行わないように指示）
	USE_NL_WITH_INDEX	指定された表を内部表として使用し、指定された表をネストしたループ結合とともに別の行のソースに結合するようオプティマイザに指示。結合述語に対応する索引が必要。通常はUSE_NLヒントで問題ないが想定された索引が結合に利用されない場合、意図した索引を結合に利用させるために利用する
問合せ 変換	MERGE(NO_MERGE)	インラインビューやビューを分解し、主問合せの結合にマージする（しない）よう指示
	USE_CONCAT (NO_EXPAND)	WHERE句のOR条件をUNION ALLを利用したクエリに書き換える（書き換えない）よう指示
	REWRITE (NO_REWRITE)	クエリブロックを対応するMV参照へ書き換える（書き換えない）よう指示
	VECTOR_TRANSFORM (NO_VECTOR_TRANSFORM)	VECTOR GROUP BYを行うか、行わせないかを指示。BI系でより効果を発揮する。RIGHT DEEP TREE形式の実行計画が選択されてもファクトの結合やグルーピングでCPU負荷となっているケースで、結合負荷、およびグルーピングによる負荷軽減による性能改善を狙う場合に利用
	UNNEST(NO_UNNEST)	IN句の副問合せ、EXISTS句の相関副問合せ（集計などGROUP BYの無いもの）、スカラー副問合せのネストを解除し結合への書き換えを指示（書き換えないよう指示）
パラレル 関連	PARALLEL (NO_PARALLEL)	指定された並列度で実行するよう指示（シリアルで実行するよう指示）。文レベルのPARALLELヒントがオブジェクトレベルのPARALLELヒントより優先される。ソート操作またはグループ操作も実行する場合は指定された並列度の倍になる。
	PQ_FILTER	相関副問合せフィルタリング時の行の処理方法（分散処理するかどうか、分散方法）についてオプティマイザに指示
	PARALLEL_INDEX (NO_PARALLEL_INDEX)	オブジェクトレベルのヒント。パーティション索引の索引レンジ・スキャン、全体スキャンおよび高速全体スキャンのパラレル化を指示
	PQ_CONCURRENT_UNION (NO_PQ_CONCURRENT_UNION)	UNION操作とUNION ALL操作の並列処理を有効にするようオプティマイザに指示。（シリアル処理するよう指示）
	PQ_DISTRIBUTE	プロデューサおよびコンシューマ問合せサーバーに行を分散させる方法をオプティマイザに指示
	PQ_SKEW (NO_PQ_SKEW)	パラレル結合の結合キーの値の分散が著しく偏っている(つまり、同じ結合キー値を持つ行の割合が大きい)ことをオプティマイザに知らせる。（偏りがないことを知らせる）ハイブリッドハッシュ時で分散させる場合に利用できるヒント
その他	APPENDヒント APPEND_VALUE(NOAPPEND)	INSERT AS SELECTでダイレクトパスインサートを指示/INSERT　VALUES文でダイレクトパスインサートを指示（ダイレクトパスインサートしないように指示）
	DYNAMIC_SAMPLING	動的統計のサンプリングを方法をオプティマイザに指示
	OPT_PARAM	OPTIMIZER_DYNAMIC_SAMPLING、OPTIMIZER_INDEX_CACHING、OPTIMIZER_INDEX_COST_ADJ、OPTIMIZER_SECURE_VIEW_MERGINGおよびSTAR_TRANSFORMATION_ENABLEなど、いくつかの初期化パラメータと隠しパラメータを制御
	DRIVING_SITE	データベースリンクで結合された分散データベース環境において利用するヒント。SQL文を駆動するデータベースをオプティマイザに指示
	PUSH_PRED (NO_PUSH_PRED)	結合述語をビューにプッシュするようオプティマイザに指示（プッシュしないようオプティマイザに指示）
	PUSH_SUBQ (NO_PUSH_SUBQ)	実行計画の初期段階でマージされていない副問合せを評価するようオプティマイザに指示（副問合せの評価を最後に行うよう指示）。マージされていない副問合せは、実行計画の最終に実行される。副問合せのコストが比較的低く、副問合せによって行数が大幅に減少する場合、副問合せの早期評価によってパフォーマンス が向上が期待できる
	QB_NAME	問合せブロックの名前を定義。通常内部的な名称を自動的に付与するが、それに替えてユーザが扱いやすい名前を定義することができる。グローバルヒント構文で使用するために定義することが一般的。