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_interval '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) ★
Qiitaにも投稿してみますた。
Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。 http://qiita.com/ora_gonsuke777/items/d2965a105feebac9f25a
