概要
Oracle DatabaseのSQL Tuning Set(STS)とは、データベースで実行された複数のSQLの下記のような情報を含むデータベースオブジェクトです。
- SQL文
- 実行計画
- 実行コンテキスト（スキーマ、アプリケーション・モジュール名など）
- 実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など）
データベース上で実行されている各SQLのカタログの情報を、データベースの中に記録するようなイメージです。STSだけでも利用できますし、下記のようなOracle Databaseの高度な機能と組み合わせて利用することもあります。また、STSはあるデータベースで記録したものを別のデータベースで移動させることができます。
- データベース上でのテストを実行する機能 ⇒ Real Application Testing(RAT)のSQL Performance Analyzer(SPA)
- チューニングのアドバイスを生成する機能 ⇒ SQL Tuning Advisor
- 実行計画を固定化するための機能 ⇒ SQL Plan Management
本記事では、Oracle Databaseへ実行されインスタンスのメモリ中にあるSQL群をSTSに記録する手順に関して説明します。紹介するのはOracle Enterprise Managerの画面からの実行手順ではなく、SQLの実行環境からコマンド(SQL,PL/SQL)でSTSへSQL群の情報を取得する方法です。
2019年12月ごろの情報・環境を元に本記事は記載しています。
0. STSに必要なOracle Databaseのエディション
STSはOracle Database Enterprise Editionの機能です。オプションのライセンスは不要です。Oracle Database Standard Edition 2では利用できません。
Oracle Database データベース・ライセンス情報ユーザー・マニュアル 12cリリース2 (12.2)
管理性 - 表1-6 の「SQLチューニング・セット」が参考になります。
以前はSTSの利用にオプションのTuning PackまたはReal Application Testingが必要でした。そのことに伴う注意事項を本記事の「注意事項：Enterprise Editionを利用しているが、STS取得時にORA-13718でエラーとなってしまう場合」に記載しています。
1. 本記事の作業の前提
本記事で紹介している手順は、Oracle Databaseの11g R2(11.2.0.4)で確認しています。実行するには下記が必要です。
- Oracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)
本記事の中で手順として説明はしていませんが、作業の前あるいは平行して対象のデータベースに対して、特定のユーザーで何かSQLを実行しておきます。簡単なSQLを手動で実行するのでも構いませんし、アプリケーション、ツールを動作させて複数のSQLを実行しておいても構いません。
そのデータベースインスタンスのメモリにある実行したSQLの情報を取得してSTSに格納させるイメージです。今回は「SQLを発行したユーザー」を条件に指定してメモリ中にあるSQLを取得します。取得するSQLの条件は、必要に応じてさまざまな条件が考えられます。
2. SYSAUX表領域の空き領域の確認
STSはデータベースのSYSAUX表領域に格納されます。多数のSQLをSTSに格納する場合、SYSAUX表領域が不足しないか注意する必要があります。必要な領域はSQL文に依存しますが、1つのSQLあたり5～20KB程度が目安、と説明されている資料もあります。あくまで目安です。
要件が厳しい場合は、可能であれば、対象となるデータベース環境で、SQLの数をそこまで大きくならないようにSTSへ取得して必要となる領域がどの程度だったか確認しておきます。下記のSQLはSYSAUXの空き領域を確認しています。
col tablespace_name for a20
col max_extendable format 9G999G999D99
col used_mb format 9G999G999D99
col curr_mb format 9G999G999D99
col free_mb format 9G999G999D99
SELECT
b.tablespace_name,
max_extendable_mb max_extendable,
b.curr_mb,
nvl(used_mb, 0) used_mb,
b.curr_mb - nvl(used_mb, 0) free_mb
FROM
(
SELECT
tablespace_name,
SUM(bytes) / 1024/1024 used_mb
FROM
dba_segments
GROUP BY
tablespace_name
) a,
(
SELECT
tablespace_name,
SUM(greatest(bytes, maxbytes)) / 1024 / 1024 max_extendable_mb,
SUM(bytes) / 1024 / 1024 curr_mb
FROM
dba_data_files
group BY
tablespace_name
) b
WHERE
a.tablespace_name (+) = b.tablespace_name
AND a.tablespace_name = 'SYSAUX'
order BY
tablespace_name;
(実行結果)
TABLESPACE_NAME MAX_EXTENDABLE CURR_MB USED_MB FREE_MB
-------------------- -------------- ------------- ------------- -------------
SYSAUX 32,767.98 600.00 346.19 253.81
3. STSの作成
まず空のSTSを作成します。ここではデータベースオブジェクトであるSTSのオーナーとSQLを発行したユーザーを同じユーザーにしてます（下記の例ではユーザー名SOEです）。そのユーザーにはアプリケーションを実行するための権限とロール以外に、ADVISOR権限、ADMINISTER ANY SQL TUNING SET権限、SELECT_CATALOG_ROLE権限を付与しています。下記が権限付与の実行例です。
SQL> GRANT ADVISOR TO SOE;
権限付与が成功しました。
SQL> GRANT ADMINISTER ANY SQL TUNING SET TO SOE;
権限付与が成功しました。
SQL> GRANT SELECT_CATALOG_ROLE TO SOE;
権限付与が成功しました。
まず対象のデータベースで作成済みのSTSを確認します。STSのオーナーとなるユーザー(ここの例ではSOE)で接続して、下記のSQLを実行します。作成済みのSTSと名前が重ならないようにするためです。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
レコードが選択されませんでした。
ここの例での結果では、作成済みのSTSはありません。次に空のSTSを作成します。今回の例では、STSのオーナーをSOEというユーザーにし、STS名をSTS01としています。
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_owner =>'SOE', -- STSのオーナーとなるユーザー名
sqlset_name => 'STS01' -- 作成するSTS名を指定
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
空のSTSが作成されたことを確認します。下記SQLの出力結果のSTATEMENT_COUNTで、そのSTSに格納されているSQLの数を確認できます。下記の例では、作成直後のSTSですから0になっています。
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 0
4. STSへのSQLのロード
作成したSTSにSQLをロードします。ここでは、対象データベースで実行されメモリ中にあるSQLの中から、条件を指定してロードします。
DECLARE
sqlset_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN sqlset_cur FOR
SELECT VALUE(P)
FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'UPPER(PARSING_SCHEMA_NAME) = ''SOE'' AND plan_hash_value != 0',
object_filter => NULL,
ranking_measure1 => 'ELAPSED_TIME',
result_limit => 1000,
recursive_sql => DBMS_SQLTUNE.NO_RECURSIVE_SQL
)
) P;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_owner => 'SOE', -- 対象のSTSのオーナーを指定
sqlset_name => 'STS01', -- 対象のSTSの名前を指定
populate_cursor=> sqlset_cur,
load_option => 'INSERT');
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
このPL/SQLプログラムは何度も実行して構いません。実行の際にメモリ中にあるSQLから条件にあったものをSTSにロードします。呼び出しているパッケージに関しては、下記のマニュアルが参考になります。さまざまな指定ができます。
マニュアルOracle Database SQLチューニング・ガイド:SQLチューニング・セットのロード
マニュアルDatabase PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス:DBMS_SQLTUNEのサブプログラム・グループ
上記の例では、DBMS_SQLTUNE.SELECT_CURSOR_CACHEパッケージで下記のように指定することで、SOEユーザーが発行しているSQLをSTSへロードする対象としています。また、plan_hash_value != 0と指定することで、「実行計画のあるSQL文だけを取得する」という指定をしています。（たとえばPL/SQLパッケージを呼び出すPL/SQLなどは、実行計画がありません。）
basic_filter => 'UPPER(PARSING_SCHEMA_NAME) = ''SOE'' AND plan_hash_value != 0',
ここではbasic_filterに2つの条件(PARSING_SCHEMA_NAMEとplan_hash_value)を指定していますが、SQL文のテキストに条件を指定したり、ほかにもさまざまな指定ができます。下記のマニュアルが参考になります。
マニュアルDatabase PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス:SQLSET_ROWオブジェクト・タイプ
さらに、下記の2つのパラメータにより、対象となるSQL群を各SQL実行の経過時間(ELAPSED_TIME)で並び直して、上から最大1000までをロードするようにしています。
ranking_measure1 => 'ELAPSED_TIME',
result_limit => 1000
下記の箇所はロードするSQLから再帰的SQLを除外するためのフィルタ(NO_RECURSIVE_SQL)を指定しています。
recursive_sql => DBMS_SQLTUNE.NO_RECURSIVE_SQL
上記の例では、DBMS_SQLTUNE.LOAD_SQLSETで下記のようにオプションを指定しています。この指定をした場合、メモリ中にあった条件にあうSQL群のうち、ロード先で指定したSTSの中にはないSQLを追加します。すでにSTSに記録されていたSQLに関して更新はしません。なんどかここの手順によるSQLのロードを実行することで、実行されるSQLをSTSに追加していくことになります。詳細は上記のマニュアルをご確認ください。
load_option => 'INSERT'
ここで紹介したパッケージとは別の方法として、DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSETパッケージを使用して、特定の期間にキャッシュを複数回ポーリングしてカーソルキャッシュからSQL群をSTSにロードする方法もあります。下記の記事が参考になります。
Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。
上記のようにPL/SQLを実行したのちに、STSにロードされたことを確認します。下記の例では60のSQLが記録されています。
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 60
5. STSの中身確認
SQL群を取得したSTSの中身を確認してみます。
5.1. STS全体のSQL IDとSQL文の一部、実行時の統計情報をリストで参照
まずSTSの中にあるSQL群の情報をリストで確認してみます。次のようなSQLで確認できます。STSのオーナーと名前を指定しています。
SET LINESIZE 170;
COL SQL_ID FOR A15
COL SQL_TEXT FOR A30 TRUNC
COL PARSING_SCHEMA_NAME FOR A15
COL MODULE FOR A10 TRUNC
SELECT
sql_id,
module,
sql_text,
executions,
elapsed_time,
cpu_time,
buffer_gets,
disk_reads
FROM
TABLE ( dbms_sqltune.select_sqlset(sqlset_owner => 'SOE', sqlset_name => 'STS01') )
ORDER BY
1;
(実行結果)
SQL_ID MODULE SQL_TEXT EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS
--------------- ---------- ------------------------------ ---------- ------------ ---------- ----------- ----------
0j95qvym9s35u Update Cus SELECT CARD_DETAILS_SEQ.NEXTVA 1457 19172 18106 12 0
0y1prvxqc2ra9 Browse Pro SELECT PRODUCTS.PRODUCT_ID, PR 61244 9782185 6853279 633066 0
0zg5scs7brcfg SQL Develo select count(1) from all_objec 1 173116 74740 36 0
(中略)
61行が選択されました。
このようにして、SQL文の最初の部分や、SQLを実行したモジュールの情報とともに、SQL実行に関する統計情報（実行回数など）をリストで確認できます。さらに、ここにあるSQL_IDを指定して、SQLの実行計画を表示させることができます。
5.2. SQL IDを指定してSTSに取得されている取得したSQLの実行計画の参照
STSに記録されているSQLの実行計画を表示させます。ここで実行計画を表示させたいSQLのSQL_IDを指定します。SQL_IDは前節のSTSの中にあるSQL群の情報をリストで確認した際に、参照することができます。次のようなSQLで確認できます。
SELECT
*
FROM
TABLE ( dbms_xplan.display_sqlset(sqlset_name => 'STS01', sql_id => '0y1prvxqc2ra9', sqlset_owner => 'SOE') );
(実行結果)
SQL Tuning Set Name: STS01
SQL Tuning Set Owner: SOE
SQL_ID: 0y1prvxqc2ra9
SQL Text: SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION,
CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID,
PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND
FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND
INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND ROWNUM < :B1
--------------------------------------------------------------------------------
Plan hash value: 1430386540
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 15 | 6045 | 7 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 15 | 6045 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 393 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 177 | 2 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 216 | 2 (0)| 00:00:01 |
| 8 | INDEX RANGE SCAN | PRD_DESC_PK | 1 | | 1 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | INVENTORY_PK | 15 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | INVENTORIES | 15 | 150 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
6. STSの削除
STSを削除するには、次のように実行します。別機能でSTSを利用中のときは削除できません。（RAT機能の複数のステップの途中のときなど）
次のようなSQLでSTSを削除できます。対象のSTSのオーナーと名前を指定します。
BEGIN
DBMS_SQLTUNE.DROP_SQLSET (
sqlset_owner => 'SOE',
sqlset_name => 'STS01'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
7. 注意事項：Enterprise Editionを利用しているが、STS取得時にORA-13718でエラーとなってしまう場合について
「0. STSに必要なOracle Databaseのエディション」で説明したように、STSはOracle DatabaseのEnterprise Editionで利用可能で、オプションは不要です。ただし、かつてはオプション（Tuning PackあるいはReal Application Testingオプション）が必要でした。その影響でEnteprise Editionのデータベースなのだけれど、STSを取得しようしてPL/SQLを実行すると、下記のようなエラーになるケースがあります。
ORA-13718: この機能を使用するには、Tuning PackのライセンスまたはReal Application Testingオプションが必要です。
このような場合、パラメータを一時的に変更することで対応できます（STSの取得ができます）。パラメータの変更はOracle Databaseを起動したままで実行できます。その方法に関して説明します。
SQL> show parameters CONTROL_MANAGEMENT_PACK_ACCESS
NAME TYPE VALUE
------------------------------ ------ -----
control_management_pack_access string NONE
このようにcontrol_management_pack_accessパラメータがNONEで、RATオプションもインストールされていない場合、STS取得の際に上記のようなエラーとなります。次のようにパラメータを変更（オンラインで変更できます）することでSTS取得が可能です。
SQL> ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';
システムが変更されました。
SQL> show parameters CONTROL_MANAGEMENT_PACK_ACCESS
NAME TYPE VALUE
------------------------------ ------ -----------------
control_management_pack_access string DIAGNOSTIC+TUNING
このようなパラメータ変更はSTS取得のための一時的な対応なので、STS取得後は元に戻すようにします。
8. 注意事項：STSの中のSQL文と実行計画は1対1ではありません
STSの中には、実行された1つ1つのSQLのSQL文と実行計画などの情報が格納されています。ここでSQL文と実行計画は1対1とは限りません。SQL文は異なるが実行計画が同じになるケースもあれば、同じSQL文で複数の実行計画が記録されるケースもあります。
本記事の例にあるSTSとは別のSTSになりますがSTS02に関して確認してみます。ここでは確認方法の紹介の意図で記載します。
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS02 19
SOE STS01 60
SYS SYS_AUTO_STS 0
DBA_SQLSETを検索して確認するとSTS02には19のSQL文の実行時の情報が格納されています。
SELECT
count( distinct SQL_ID ) SQL,
count( distinct PLAN_HASH_VALUE ) PLAN,
count( distinct SQL_ID||PLAN_HASH_VALUE ) 組み合わせ
FROM
DBA_SQLSET_STATEMENTS
WHERE
SQLSET_OWNER = 'SOE' and
SQLSET_NAME = 'STS02';
(実行結果)
SQL PLAN 組み合わせ
---------- ---------- ----------
19 18 20
DBA_SQLSET_STATEMENTSを検索して確認するとSTS02には、SQL文の種類は19、実行計画の種類は18、組み合わせで20あることがわかります。下記の結果は、このSTS02に関して具体的にSQL_IDを指定して確認してみたものです。
SELECT
SQL_ID,
PLAN_HASH_VALUE
FROM
DBA_SQLSET_STATEMENTS
WHERE
SQLSET_OWNER = 'SOE' and
SQLSET_NAME = 'STS02' and
SQL_ID IN ( '42gprmpmvh8px', '93ys4sq6c1yd4', '21gkdm463jwy8', '767pug2dbpqpc' );
(実行結果)
SQL_ID PLAN_HASH_VALUE
------------- ---------------
21gkdm463jwy8 3762034736
42gprmpmvh8px 1589381018
767pug2dbpqpc 3762034736
93ys4sq6c1yd4 1589381018
この結果から、ここの例では、SQL_IDが21gkdm463jwy8と767pug2dbpqpcの2つのSQL文は同じ実行計画(PLAN_HASH_VALUEが3762034736)であることがわかります。同様にSQL_IDが42gprmpmvh8pxと93ys4sq6c1yd4 も同じ実行計画です。
SELECT
SQL_ID,
PLAN_HASH_VALUE
FROM
DBA_SQLSET_STATEMENTS
WHERE
SQLSET_OWNER = 'SOE' and
SQLSET_NAME = 'STS02' and
SQL_ID = 'c13sma6rkr27c';
(実行結果)
SQL_ID PLAN_HASH_VALUE
------------- ---------------
c13sma6rkr27c 670080582
c13sma6rkr27c 3101473678
この結果から、ここの例では、SQL_IDがc13sma6rkr27cとであるSQL文に対して、2つの実行計画(PLAN_HASH_VALUEが670080582と3101473678)がSTSに格納されています。
9. (参考)SYSAUX上のSTSのサイズに関して
STSがSYSAUX表領域に格納されます。STSのサイズがどのくらいか、という点に関しては、下記の記事が参考になります。
ASK TOM:where are sql tuning sets stored and how can you tell how much space a specific one or all of them are using?
v$sysaux_occupantsを検索して利用しているサイズを出していますが、1つ1つのSTS毎のサイズでなく、SQL Plan Management(SPM)で利用する計画ベースラインとSQLプロファイルで利用している領域とあわせたサイズが表示されます。また、作成したSTSを削除しても、その領域が解放されたとして検索されるわけではありません。
また別の方法として、1つ1つのSTSをステージング表に出力させて、そのステージング表のサイズを検索させる方法も紹介しています。
10. 最後に
本記事で紹介した、STSへのSTS群の情報のロード方法は1つのサンプルでしかありません。特にSTSへのロードの際の設定は、さまざまなバリエーションが考えられるので、本記事の設定をスタートに、さまざまな設定を試すことで、より使いこなせるようになると思います。
11. 参考情報
マニュアルOracle Database SQLチューニング・ガイド:SQLチューニング・セットのロード
マニュアルDatabase PL/SQL Packages and Types Reference:DBMS_SQLTUNEのサブプログラム・グループ
Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。
SQL Performance AnalyzerによるSQLパフォーマンス比較実行例(API)
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSをデータベース間で移動（コピー）する