今回はOracleのSQLの動きを調査しました。
参照系はSELECT、更新系はUPDATEを使用してSQLの動きを見てみました。
SQLの処理の流れ
SQLが実行されると主に3つのフェーズに分けて処理されます。
PARSE(解析フェーズ)
・SQL文の構文、意味の妥当性も含めた文法チェック
・SQL文の実行権限の有無チェック
・SQL文の解析済み情報がライブラリキャッシュに存在するかチェック
※キャッシュに存在しない場合、ライブラリキャッシュに解析済みの情報を格納する
このフェーズでSQL文の実行計画も作成される
EXECUTE(実行フェーズ)
PARSE(解析フェーズ)で生成されたSQL文の実行計画をもとにSQL文を処理する
FETCH(フェッチフェーズ) ※SELECT文のみ
参照結果の行が選択され、ソート処理が必要な場合は順序付けされる
参照
たとえば「A」というデータをSELECTするときの流れを見てみます
① SQL文(SELECT文)を発行
② 発行されたSQL文を解析し、共有プール(ライブラリキャッシュ)にSQL文の解析情報(実行計画など)を格納する(解析フェーズ)
ライブラリキャッシュに解析済みSQL文が存在する場合、解析を終了し、ライブラリキャッシュの解析済み結果を再利用する
③ 解析フェーズで生成された実行計画を使用してSQLを実行し、「A」へアクセスする
「A」がデータベースバッファキャッシュに存在する場合、キャッシュから「A」を取得する
データベースバッファキャッシュに存在しない場合、サーバプロセスがデータファイルから「A」を取得し、キャッシュに格納後、キャッシュから「A」を取得する
④ ③で取得した「A」をサーバプロセスへ渡す
⑤ 検索結果(「A」)をユーザプロセスへ渡す
更新
たとえば「A」というデータを「B」にUPDATEするときの流れを見てみます
① SQL文(UPDATE文)を発行
② 発行されたSQL文を解析し、共有プール(ライブラリキャッシュ)にSQL文の解析情報(実行計画など)を格納する(解析フェーズ)
ライブラリキャッシュに解析済みSQL文が存在する場合、解析を終了し、ライブラリキャッシュの解析済み結果を再利用する
③ 解析フェーズで生成された実行計画を使用してSQLを実行し、「A」へアクセスする
「A」がデータベースバッファキャッシュに存在する場合、キャッシュから「A」を取得する
データベースバッファキャッシュに存在しない場合、サーバプロセスがデータファイルから「A」を取得し、キャッシュに格納後、キャッシュから「A」を取得する
④ 更新履歴(「A」→「B」)をREDOログバッファに書き込む
LGWRプロセスは非同期でREDOログバッファの更新履歴をREDOログファイルに書き込む
※UNDOデータ(更新前データ)をUNDOセグメントに書き込む
⑤ データベースバッファキャッシュの「A」を「B」に更新する
⑥ 更新結果をユーザプロセスへ渡す(サーバプロセスはLGWRの処理完了は待たない)
更新(COMMIT)
① SQL文(COMMIT文)を発行
② 発行されたSQL文を解析し、共有プール(ライブラリキャッシュ)にSQL文の解析情報(実行計画など)を格納する(解析フェーズ)
ライブラリキャッシュに解析済みSQL文が存在する場合、解析を終了し、ライブラリキャッシュの解析済み結果を再利用する
③ サーバプロセスがCOMMITをREDOログバッファに書き込む
④ LGWRプロセスはREDOログバッファのCOMMITをREDOログファイルに書き込む
REDOログバッファの更新履歴(「A」→「B」)がREDOログファイルに書き込まれていない場合、更新履歴を書き込んでからCOMMITを書き込む
DBWnプロセスはCOMMITとは非同期で更新内容(「B」)をデータファイルに書き込む
⑤ LGWRプロセスはCOMMITの書き込み完了をサーバプロセスへ通知する
⑥ サーバプロセスがCOMMIT結果をユーザプロセスへ渡す(サーバプロセスはLGWRの処理完了を待つ)
■参考資料
絵で見てわかるOracleの仕組み
パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング 本気で学ぶ実践的な考え方とテクニック
新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意
■おわりに
普段の業務で使用しているSELECTやUPDATEなどのSQLの挙動を復習する良い機会になりました。
挙動を知っているからと言ってSQLの性能改善ができるようになるかと言えば難しいですが、SQLを使用する人(特にアプリケーション開発者)は知っておいて損は無いと思います。
