SQLパフォーマンス・ツール SQLTXPLAIN を使ってみよう
*以下は、サポート契約締結中のお客様に毎月配信しているサポートレターより一部抜粋して掲載しています。
SQLTXPLAIN とは
SQLTXPLAIN (SQLT) は、Oracle Server Technologies Center of Expertise - ST CoE によって提供されている SQL 文に対するパフォーマンス・ツールです。SQLTXPLAIN は、1つの SQL 文を入力情報として、診断ファイルのセットを出力します。診断ファイルのセットには、実行計画、コストベース・オプティマイザ (CBO) 関連の統計や、スキーマ・ オブジェクトのメタデータ、パフォーマンス統計、構成パラメータ、解析された SQL 文のパフォーマンスに影響を及ぼす要素についての情報が含まれます。
SQLTXPLAIN は事前にインストール (データベース内にスキーマやパッケージを作成) が必要となります。
インストールなしでパフォーマンスに関する診断情報を取得したい場合は、SQLTXPLAIN のかわりにインストールが不要な SQLHC (SQL Health Check) を試してみてください。
SQLHC については、当社の WEB サイトにて情報公開しておりますので、是非ご一読ください。
https://www.intellilink.co.jp/column/oracle/2015/021600SQLTXPLAIN および SQLHC は EE / SE2 / SE / SEOne のどのライセンス形態でも利用可能です。
SQLTXPLAIN の入手
SQLTXPLAIN は My Oracle Support のナレッジ・ベース Document 215187.1 より入手可能です。
My Oracle Support のアカウントをお持ちの場合は、下記の URL より入手可能です。
「10.2, 11.1, 11.2 and 12.1 download」リンクをクリックし、sqlt_latest.zipをダウンロードしてください。
※ Oracle Database 10g Release 1 以前をご利用の場合は、「9.2 and 10.1 download」リンクをクリックし、sqlt9i_11.3.1.4.zip をダウンロードしてご利用ください。
SQLTXPLAIN のインストール
1. ダウンロードした sqlt_latest.zip を展開します。
$ unzip -qo sqlt_latest.zip
sqltディレクトリが作成されます。
2. sqltディレクトリに移動し、SYS ユーザで install/sqcreate.sql を実行します。
$ cd sqlt
$ sqlplus / AS SYSDBA
SQL> @install/sqcreate.sql
実行中に下記の情報の入力を求められます。
- 接続識別子
接続識別子 (tnsnames.ora に記載されているもの) を指定します。前に "@" をつけて入力してください。ローカル接続するように構成したい場合は、何も入力せずに Enter を入力してください。
例) tnsnames.ora に記載されている下記の内容でデータベースに接続するように構成したい場合
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl.us.oracle.com))
)
==> 「@ORCL」を指定します。
- SQLTXPLAIN ユーザのパスワード
SQLTXPLAIN の実行に必要な SQLTXPLAIN ユーザのパスワードを指定します。確認のため、2 度パスワードの 入力を求められますので、同じ文字列を入力します。
- SQLTXPLAIN ユーザのデフォルト表領域
SQLTXPLAIN ユーザのオブジェクトを格納する表領域を指定します。通常は大文字で入力してください。
- SQLTXPLAIN ユーザの一時表領域
SQLTXPLAIN ユーザが使用する一時表領域を指定します。通常は大文字で入力してください。
- アプリケーション・ユーザ
SQLTXPLAIN で解析する SQL を実行するユーザを指定します。ユーザは後から追加することもできます。
例) 後から scott ユーザを追加する方法
SQL> connect / as sysdba
SQL> grant sqlt_user_role to scott;
- Diagnostic Pack / Tuning Pack (EEオプション) ライセンスの有無
T : Diagnostic Pack と Tuning Pack のライセンスを所有している場合
D : Diagnostic Pack のライセンスのみ所有している場合
N : どちらのライセンスも所有していない場合
全ての情報を入力し終えると、インストールが開始されます。インストールは数分かかります。
インストール時のログは、カレント・ディレクトリにSQLT_installation_logs_archive.zip という名前のzip ファイルとして出力されます。必要に応じてご確認ください。
SQLTXPLAIN の実行
SQLTXPLAIN には、以下の 3 つの実行方法があります。
- XECUTE
実際に SQL を実行して情報を収集します。最も多くの診断情報を取得することができますが、実行負荷の高いSQL の情報を取得する場合は、情報取得時の負荷に注意する必要があります。
- XTRACT
V$SQL や AWR など、過去に実行した内容を元にデータを収集します。以前に実行された SQL の情報が共有プールやAWR に残っている必要があります。
- XPLAIN
SQL を実際には実行せず、Explain Plan を元にデータを収集します。実際に SQLを実行するわけではないため、XECUTE よりも負荷が軽く、XTRACT のように以前にSQL を実行した時の情報が残っている必要もありませんが、取得する診断情報はXECUTE や XTRACT と比較して最も少なくなります。
以下に、それぞれの実行方法を紹介します。
- XECUTE
下記のように、解析対象の SQL をファイルとして用意します。
< target.sql="">>
-----------------------
select ename, dname from emp, dept where emp.deptno = dept.deptno;
-----------------------
下記のように、バインド変数を使用することも可能です。
-----------------------
var test number;
execute :test:=0;
select /*+ index(test idx) */ * from test where c1 > :test;
-----------------------
SQL を実行するユーザでログインし、下記のように sqltxecute.sql を実行します。
SQL> connect scott/tiger
SQL> @$HOME/sqlt/run/sqltxecute.sql target.sql
実行中に SQLTXPLAIN ユーザのパスワードの入力を求められます。
実行結果は、カレント・ディレクトリに sqlt_s58315_xecute.zip のような名前のzip ファイルとして出力されます。
- XTRACT
対象の SQL の SQL_ID を V$SQL や AWR レポートから確認します。
例) V$SQL から SQL_ID を確認
SQL> connect / AS SYSDBA
SQL> select sql_id from v$sql
where sql_fulltext like '% from emp, dept %'
and sql_fulltext not like '%v$sql%';
SQL_ID
---------------------------------------
gthun5b1mva2n
SQL を実行するユーザでログインし、下記のように sqltxtract.sql を実行します。
SQL> connect scott/tiger
SQL> @$HOME/sqlt/run/sqltxtract.sql gthun5b1mva2n
実行中に SQLTXPLAIN ユーザのパスワードの入力を求められます。
実行結果は、カレント・ディレクトリに sqlt_s58316_xtract_gthun5b1mva2n.zipのような名前の zip ファイルとして出力されます。存在しない SQL_ID を指定した場合は、アラート・ログのみ含まれる sqlt_s58316_log.zip のような名前のzip ファイルが出力されます。
※ SGA のサイズが小さいと、XTRACT 実行時に解析対象の SQL が共有プールからエイジ・アウトされてしまう可能性がありますのでご注意ください。
- XPLAIN
下記のように、解析対象の SQL をファイルとして用意します。
< target.sql="">>
-----------------------
select ename, dname from emp, dept where emp.deptno = dept.deptno;
-----------------------
下記のように、バインド変数を使用することも可能です。
-----------------------
var test number;
execute :test:=0;
select /*+ index(test idx) */ * from test where c1 > :test;
-----------------------
SQL を実行するユーザでログインし、下記のように sqltxplain.sql を実行します。
SQL> connect scott/tiger
SQL> @$HOME/sqlt/run/sqltxplain.sql target.sql
下記のような WARNING が出力されます。指示に従い、「XPLAIN」を入力します。
WARNING:
~~~~~~~
You are about to use SQLT XPLAIN method.
If you were requested by Oracle Support to use XECUTE of XTRACT,
please do not use this XPLAIN method.
Be aware that the XPLAIN method cannot perform bind peeking.
Replacing binds variables with literal values does not guarantee the
generated plan to be the same than the one produced by XECUTE of XTRACT.
The plan generated by XPLAIN might not be useful to progress your issue.
If you still need to proceed with the XPLAIN method, enter XPLAIN below.
Enter value for sqlt_method:
実行中に SQLTXPLAIN ユーザのパスワードの入力を求められます。
実行結果は、カレント・ディレクトリに sqlt_s58317_xplain.zip のような名前のzip ファイルとして出力されます。
SQLTXPLAIN のレポートの確認
zip ファイルを展開し、sqlt_s58315_main.html や sqlt_s58315_lite.html のような名前のファイルをブラウザで開きます。
本稿ではレポートの内容や見方までは触れませんが、実際に 3 つの実行方法でそれぞれ実行した際のレポートを本文書に添付しておりますので、ダウンロードして比較してみてください。(sqlt_report.zip)
SQLTXPLAIN のアンインストール
1. ダウンロードした sqlt_latest.zip を展開します。
例)
$ unzip -qo sqlt_latest.zip
sqltディレクトリが作成されます。
2. sqltディレクトリに移動し、SYS ユーザで install/sqdrop.sql を実行します。
例)
$ cd sqlt
$ sqlplus / AS SYSDBA
SQL> @install/sqdrop.sql
まとめ
今号では SQLTXPLAIN をご紹介しました。SQLTXPLAIN を利用することで、SQL のチューニングに有用な多くの情報を容易に取得することができます。パフォーマンス・ツールとして多く採用されている AWR や STATSPACK とあわせて、SQLTXPLAIN の導入をぜひ検討してみてください。
おことわり
本文書に記載しているのインストール手順、実行方法などは、本文書の執筆時点での最新版であるSQLXPLAIN 12.1.14により動作確認を行っています。SQLTXPLAIN の仕様は予告なく変更される場合がありますのであらかじめご了承ください。
(オラクル事業部 サポートセンター 田中)