「さらに高度なSQL実行計画の取得」のために理解しておくべきこと:しばちょう先生の試して納得! DBAへの道(改)(3)(2/3 ページ)
データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。今回は「より高度なSQL実行計画を取得するために、理解しておいてほしいこと」を解説します。
演習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を使っていない時間が多いということですね。
では、それ以外の何に時間がかかっていたのか? と疑問に思ってもらったところで次の演習に行きましょう。
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
- 【Oracle Database】忘れていませんか? 「アラートログ調査」に必要な、たった3つのキホン
データベース管理システムの運用でトラブルが発生したらどうするか。データベースサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は基本編として「アラートログの調査で押さえるべき3つのポイント」を解説します。【Oracle Database 12c対応版】 - 障害発生! 問題切り分けはスピード勝負
Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。(編集局) - パフォーマンス向上の最短コースを知る
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。(編集局) - SQL実践講座
SQLは、データを操作するために非常に簡単な構文で構成されているように見えます。ところが実際に使い込んでいくと、一見簡単に取得できるように見えるデータが取得できない場面にぶち当たることもあります。こういった場面のために、SQLの効率的な使い方をエッセンスにしてお伝えします。