「さらに高度なSQL実行計画の取得」のために理解しておくべきこと：しばちょう先生の試して納得！ DBAへの道（改）（3）（2/3 ページ）

[柴田長，日本オラクル株式会社]

演習3：V$SQLビューでSQL_IDとCHILD_NUMBERを確認

　「SQL*Plus」から「SYS」ユーザーでデータベースに接続し、動的パフォーマンスビューV$SQLに問い合わせて、演習2で実行したSELECT文の「SQL_ID」と「CHILD_NUMBER」を確認しましょう。

$ sqlplus / as sysdba
SQL>  
set pagesize 100 linesize 120
col SQL_TEXT for a90
　
select SQL_ID, CHILD_NUMBER, SQL_TEXT
  from V$SQL
where SQL_TEXT like 'select /* practiceSQL1 */%' ;
　
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------------------------
2vrywhbx1jxtb            0 select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record" from TBL2 where ROWNUM <=3

　まさにこの時のために演習2でSELECT文へコメントを追加しておいたわけです。V$SQLビューには「SQL_TEXT列」に対してLIKE検索を実行すれば、目的のSQL文の「SQL_ID」と「CHILD_NUMBER」を確認できます。

　次に、Oracle Databaseリファレンスマニュアルを参照して、V$SQLビューにはどのような情報が格納されているのかを確認してみましょう。次のように記述されています

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

　「共有SQL領域」とは、前述した共有プール内のライブラリキャッシュ内にある領域のことです。つまり、V$SQLビューに対してSELECT文で問い合わせるということは、共有プール内の情報を参照するということです。

　ユーザーが作成した表だけではなく、データベース内で管理されている内部情報に関してもこのように表形式で管理されていること、そしてそれにアクセスできるという事実に気が付いたとき、私は少しだけOracle Databaseの仕組みに近づいた印象を覚えています。

　ちなみにV$SQLビューの説明文（日本語訳）の出だし「GROUP BY句のないSQL文」は、本来は「GROUP BYでまとめていない共有SQL領域の情報」という意味だと推測しています。そして、中間から後半にかけても興味深い情報が記載されています。意外と知られていない情報なので、覚えておくと良いでしょう。

演習4：V$SQLビューから確認できるその他の情報

　さらに深く、動的パフォーマンスビュー V$SQLで確認できる情報を確認してみましょう。演習3で確認した「SQL_ID（2vrywhbx1jxtb）」を利用して問い合わせを実行します。

$ sqlplus / as sysdba
SQL>  
set pagesize 100 linesize 120
col LAST_LOAD_TIME      for a20
col PARSING_SCHEMA_NAME for a4
col MODULE              for a8
select SQL_ID, CHILD_NUMBER, LAST_LOAD_TIME, EXECUTIONS, PARSING_SCHEMA_NAME, MODULE, CPU_TIME, ELAPSED_TIME
  from V$SQL
 where SQL_ID = '2vrywhbx1jxtb';
　
SQL_ID        CHILD_NUMBER LAST_LOAD_TIME       EXECUTIONS PARS MODULE   CPU_TIME ELAPSED_TIME
------------- ------------ -------------------- ---------- ---- -------- -------- ------------
2vrywhbx1jxtb            0 2012-04-23/15:42:23           1 TRY  SQL*Plus   169974      1178483

　この問い合わせで参照した列は、とても大切なことです。

　「LAST_LOAD_TIME」列は、SQL文の実行計画がライブラリキャッシュにロードされた時刻、つまりハードパースにより実行計画が作成されたタイミングを示します。「EXECUTIONS」列はSQLが実行された回数、「PARSING_SCHEMA_NAME」列は実行計画の作成に使用されたスキーマ名、「MODULE」列はハードパースが実行された際に対象のSQL文をデータベースに対して実行したモジュール名（プログラム名）を示しています。続いて、「CPU_TIME」列はこのSQL文を実行するために要した合計CPU時間（マイクロ秒）、「ELAPSED_TIME」列はSQL文を実行するために要した合計経過時間（マイクロ秒）です。これらは全てマニュアルに記載されています。

　特に、CPU_TIME列（CPU時間）とELAPSED_TIME列（経過時間）の関係性はキッチリと理解しておきましょう。この差（ELAPSED_TIME - CPU_TIME）の原因を理解することが、「パフォーマンスチューニングを実施する」上でとても重要になります。

　ちなみに上の例では、SQL文を一回だけ実行していて、そのタイミングでCPU時間が169974マイクロ秒だったのに対して経過時間は1178483マイクロ秒で、経過時間の方が1桁多い値となっています。これは、「このSQL文を1回実行するのに要した時間は1178483マイクロ秒でしたが、その時間のうち、CPUを使用していた時間は169974マイクロ秒でした」ということを示しています。つまりは、CPUを使っていない時間が多いということですね。

　では、それ以外の何に時間がかかっていたのか？　と疑問に思ってもらったところで次の演習に行きましょう。