Edited at

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

More than 3 years have passed since last update.

表題の通り 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) ★