Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。

表題の通り STS(SQL Tuning Set) を使って、
SQL の 性能統計 や 実行計画 を キャプチャするやで彡(ﾟ)(ﾟ)

まず DBMS_SQLTUNE.CREATE_SQLSETファンクション を
実行して、STS を作成しまする。

VAR v_sts_name VARCHAR2(30);
EXEC :v_sts_name := DBMS_SQLTUNE.CREATE_SQLSET;

PL/SQL procedure successfully completed.

PRINT v_sts_name

V_STS_NAME
--------------------------------
STS_5

上記の時点で STS は空です。この空の STS に SQL を ロードするやで彡(ﾟ)(ﾟ)

STS に SQL をロードするやり方は幾つかあるんですが、今回は
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSETプロシージャを
使用して、共有プール上のカーソル・キャッシュを定期ポーリング
する方法でやってみます。

下記の例だとカーソル・キャッシュを6秒毎に1分間ポーリングしてます。

BEGIN
  -- カーソル・キャッシュの定期ポーリング(6秒毎1分間)
  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
    :v_sts_name,                         -- sqlset_name
    60,                                  -- time_limit
    6,                                   -- repeat_interva
    'MERGE',                             -- capture_option
    DBMS_SQLTUNE.MODE_REPLACE_OLD_STATS, -- capture_mode
    'parsing_schema_name NOT IN (
      ''SYS'', ''SYSTEM'', ''SYSMAN''
    )',                                  -- basic_filter
    NULL
  );
END;
/

PL/SQL procedure successfully completed.

STS にキャプチャされた SQL の各種統計は、
DBMS_SQLTUNE.SELECT_SQLSETテーブル・ファンクションで
見れるで(｀･ω･)Ъ

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 PARSING_SCHEMA_NAME
     , MODULE
     , SQL_ID
     , PLAN_HASH_VALUE
     , SQL_TEXT, EXECUTIONS
     , ELAPSED_TIME
     , CPU_TIME
     , BUFFER_GETS
     , DISK_READS
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(:v_sts_name))
 ORDER BY 1, 2, 3, 4;

★6種類の sql_id/plan_hash_value が STS にキャプチャされていて、各種性能統計も確認できる。★
PARSING_S MODULE     SQL_ID          PLAN_HASH_ SQL_TEXT            EXECUTIONS ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS
--------- ---------- --------------- ---------- ------------------- ---------- ------------ ---------- ----------- ----------
AYSHIBAT  DBMS_SCHED ax51s81797wdk   3432972933 SELECT ABS(MAX(PRIV         22       149512       8630          22          5
AYSHIBAT  SQL*Plus   bpvjtmt8rrky4   2824937026 SELECT                      19      1429922    1293970        5105         16
DBSNMP    JDBC Thin  bnk69f12mthy2            0 begin dbms_applicat     139354     40132300   33623176        3618        600
DBSNMP    JDBC Thin  gj5r9jj2xad7f   2529664852                          31929      5628797    5631380          76          0
DBSNMP    emagent_SQ 4mua4wc69sxyf    702510694                           1598     43455392     989359         100          0
DBSNMP    emagent_SQ g1n7yg84rqj0y   1662736584                           1598       635315     636398         233          0

6 rows selected.

STS に取り込まれた SQL の 実行計画は
DBMS_XPLAN の DISPLAY_SQLSETファンクションで見れるやで彡(^)(^)

SET PAGESIZE 300;
SET LINESIZE 170;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET(:v_sts_name, 'ax51s81797wdk', NULL));

★STS に 取り込まれた sql_id="ax51s81797wdk" の実行計画が確認できる。★
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL Tuning Set Name: STS_5
SQL Tuning Set Owner: AYSHIBAT
SQL_ID: ax51s81797wdk
SQL Text: SELECT ABS(MAX(PRIVILEGE)) FROM SYS.SYSTEM_PRIVILEGE_MAP WHERE NAME =
          :B1

Plan hash value: 3432972933

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE             |                        |     1 |    25 |            |          |
|   2 |   FIRST ROW                 |                        |     1 |    25 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| I_SYSTEM_PRIVILEGE_MAP |     1 |    25 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------


18 rows selected.

マニュアル(↓)も見とくんやで！ ↑の事項はだいたい載っとるで 彡(-)(-)

Oracle Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06
17.4 SQLチューニング・セットの管理
http://docs.oracle.com/cd/E16338_01/server.112/b56312/sql_tune.htm#i34915

　

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
11g リリース2(11.2)
B56262-06
140 DBMS_SQLTUNE
SQLチューニング・セット・サブプログラム
http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_sqltun.htm#CHDGHEAG

STSの中身は↓(SQLSET_ROWオブジェクト・タイプ)で確認できます。

SQLSET_ROWオブジェクト・タイプ
http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_sqltun.htm#CACCIEIG

STS を使うには、Tuning Pack 又は RAT(Real Application Testing) の
オプション・ライセンスが要ります。(｀･ω･)ゞ

Oracle Databaseライセンス情報
11gリリース2 (11.2)
B56284-10
Oracle Tuning Pack
http://docs.oracle.com/cd/E16338_01/license.112/b56284/options.htm#CIHFIHFG
Oracle Tuning Packに含まれる機能は、次のとおりです。
：
SQLチューニング・セット ★
：

　

Oracle® Databaseライセンス情報
11gリリース2 (11.2)
B56284-10
Oracle Real Application Testing
http://docs.oracle.com/cd/E16338_01/license.112/b56284/options.htm#CJAGBGHH
Oracle Real Application Testingオプションに含まれる機能は、次のとおりです。
：
SQLチューニング・セット(STS) ★