GAGA LIFE.

DBAブログ

Oracle SQL解析の前提知識(SQL_ID/PLAN_HASH_VALUE/CURSOR)

概要

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

SQL_ID

各SQL文を識別するための文字列。
Oracleは実行されたSQL文を13桁の「文字列」で管理しています。この文字列のことをがSQL識別子(SQL_ID)と呼ばれます。
SQL_IDは、13文字の文字列で表される基数32の数字です。各文字は数字または32の小文字のいずれかとなります。
※例) ddzxfryd0uq9t
文字e、i、l、oは、転記エラーのリスクを制限するために使用されていません。
SQL_IDは、実際にはSQLステートメントの文字から生成されたハッシュ(MD5)です。
そのため、その大文字小文字と空白が保持されていると仮定すると、同じSQLステートメントは、使用されるすべてのデータベースで同じSQL_IDを持ちます。
※DBバージョンや環境に関わらず、SQLテキストが同じであればSQL_IDは同一になります。

PLAN_HASH_VALUE

実行計画を一意の識別子(識別番号)で管理していますが、この識別子のことをPLAN_HASH_VALUEと呼びます。
PLAN_HASH_VALUEが同一であれば同一の実行計画とみなせますので、例えば検証環境と本番環境のPLAN_HASH_VALUEが同一であれば、実行計画が同じと言えます。

カーソル

SQLを実行すると、SQLがHard Parseされて実行計画が共有プールに格納されます。
共有プールに格納されたSQL文と実行計画のセットを「カーソル」と呼びます。
実行計画生成時には、1つの親カーソルに対し、1つ以上の子カーソルが生成される動作となります。
カーソルはV$SQLやその関連表に格納され、V$SQLの1レコードが1カーソルに対応します。
カーソルはスキーマや実行計画の違いを管理するために使用されますので、ある1つのSQL文(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文のテキストを示します。

参考資料

Expert Oracle SQL
http://www.lab128.com/all_these_oracle_ids/article_text_sql_ids.html
http://cosol.jp/tech/detail/d3_sql_identifier_sql_id_hash_value.shtml
https://qiita.com/nieve/items/b9f3a4762d3ed684dbbe
https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_UTILITY.htm#GUID-26D40CD3-44A4-4FE1-B9CB-95BE872137FD
https://docs.oracle.com/cd/E82638_01/REFRN/title.htm