STS(SQL Tuning Set) で SELECT文のみをキャプチャしてみる(Oracle Database)

Last updated at 2025-02-28Posted at 2025-02-28

STS(SQL Tuning Set) は Oracle Database の SQL情報(SQLテキスト、実行計画、性能統計、バインド変数など)を保持するオブジェクトです。

SQLチューニング・ガイド 23ai
24 SQLチューニング・セットでのワークロードの取得
https://docs.oracle.com/cd/G11854_01/tgsql/managing-sql-tuning-sets.html

今回は STS にキャプチャされる SQL を SELECT文のみにしてみます彡(^)(^)

1. データの準備

下記の SQL でデータを準備します。

-- Create Table
DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE (
   C1 NUMBER
  ,C2 VARCHAR2(100)
  ,C3 DATE
);

-- Dummy Data
INSERT INTO TEST_TABLE
SELECT LEVEL
     , 'TEST' || TO_CHAR(LEVEL)
     , TRUNC(SYSDATE, 'DD') + (LEVEL/24/60/60)
  FROM DUAL
CONNECT BY LEVEL <= 10000;

-- Commit
COMMIT;

-- Primary Key
ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY(C1) USING INDEX;

結果は以下のとおりです。

Table dropped.

Table created.

10000 rows created.

Commit complete.

Table altered.

2. SQL の実行

SQL を実行してみます。試しにデータをカウントしてみます。

SELECT COUNT(*) FROM TEST_TABLE;
SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

結果は以下の通りです。

SQL> SELECT COUNT(*) FROM TEST_TABLE;

  COUNT(*)
----------
     10000

SQL> SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

  COUNT(*)
----------
      2801

SQL>

3. STS のキャプチャと確認(command_type属性でフィルタしないバージョン)

まず command_type属性でフィルタしないバージョンで STS をキャプチャしてみます。
スキーマ(parsing_schema_name)はフィルタします。以下のコマンドを実行します。

-- Drop and create the tuning set
EXEC DBMS_SQLSET.DROP_SQLSET('ayu_sts');
EXEC DBMS_SQLSET.CREATE_SQLSET('ayu_sts');

-- Load the sql tuning set from the shared SQL area
DECLARE
  cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLSET.SELECT_CURSOR_CACHE(
           'parsing_schema_name = ''AYSHIBAT2''', -- basic_filter
           NULL,                                  -- object_filter
           NULL,                                  -- ranking_mesure1
           NULL,                                  -- ranking_mesure2
           NULL,                                  -- ranking_mesure3
           1,                                     -- result_percentage
           NULL,                                  -- result_limit
           NULL,                                  -- attribute_list
           DBMS_SQLSET.NO_RECURSIVE_SQL           -- recursive_sql
        )
     ) P;
  DBMS_SQLSET.LOAD_SQLSET(
    sqlset_name => 'ayu_sts',
    populate_cursor => cur
  );
END;
/

結果は以下の通りです。

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

DBMS_SQLSET.SELECT_SQLSETファンクションで STS の中身を確認してみます。

SET LINESIZE 300;
SET PAGESIZE 100;
COL SQL_ID FOR A15
COL SQL_TEXT FOR A50 TRUNC
COL PARSING_SCHEMA_NAME FOR A15
COL MODULE FOR A10 TRUNC
SELECT PARSING_SCHEMA_NAME
     , MODULE
     , SQL_ID
     , PLAN_HASH_VALUE
     , SQL_TEXT
  FROM TABLE(DBMS_SQLSET.SELECT_SQLSET('ayu_sts'))
 ORDER BY 1, 2, 3, 4;

結果は以下の通りです。

PARSING_SCHEMA_ MODULE     SQL_ID          PLAN_HASH_VALUE SQL_TEXT
--------------- ---------- --------------- --------------- --------------------------------------------------
AYSHIBAT2       SQL*Plus   00qnpnwg1zkv0                 0 DECLARE
AYSHIBAT2       SQL*Plus   0p9169r2h1vy4                 0 DECLARE
AYSHIBAT2       SQL*Plus   1q5txdwnv3adr         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 <= SYSDAT
AYSHIBAT2       SQL*Plus   1rzb7pxq1ysg5        2558420059 SELECT command_type, command_name
AYSHIBAT2       SQL*Plus   2mryuw87nvxxf        3967443709 SELECT PARSING_SCHEMA_NAME
AYSHIBAT2       SQL*Plus   2q983ru5vx8vn        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   2yygdv3dqjdc8        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   330ruq40ntq81                 0 DECLARE
AYSHIBAT2       SQL*Plus   3dqh192z0yagk                 0 DECLARE
AYSHIBAT2       SQL*Plus   3u5cbhpg17tud                 0 DECLARE
AYSHIBAT2       SQL*Plus   43tdm52www26p                 0 BEGIN DBMS_SQLSET.DROP_SQLSET('ayu_sts'); END;
AYSHIBAT2       SQL*Plus   4hcb48104uchb                 0 DECLARE
AYSHIBAT2       SQL*Plus   4mt4k256t4ufh        1236776825 INSERT INTO TEST_TABLE
：
AYSHIBAT2                  gn6mqxp5gdnct         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER

49 rows selected.

DECLARE文やINSERT文などいろいろ取れています。

4. STS のキャプチャと確認(command_type属性でSELECTのみキャプチャするバージョン)

次は command_type属性で SELECT文のみをフィルタして STS をキャプチャします。
後述しますが SELECT文の command_type属性は 3(※) となります。
※バージョンやプラットフォーム(CPU, OS)によって異なる可能性あり
スキーマ(parsing_schema_name)もフィルタします。以下のコマンドを実行します。

-- Drop and create the tuning set
EXEC DBMS_SQLSET.DROP_SQLSET('ayu_sts');
EXEC DBMS_SQLSET.CREATE_SQLSET('ayu_sts');

-- Load the sql tuning set from the shared SQL area
DECLARE
  cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLSET.SELECT_CURSOR_CACHE(
           'parsing_schema_name = ''AYSHIBAT2'' and command_type = 3', -- basic_filter
           NULL,                                  -- object_filter
           NULL,                                  -- ranking_mesure1
           NULL,                                  -- ranking_mesure2
           NULL,                                  -- ranking_mesure3
           1,                                     -- result_percentage
           NULL,                                  -- result_limit
           NULL,                                  -- attribute_list
           DBMS_SQLSET.NO_RECURSIVE_SQL           -- recursive_sql
        )
     ) P;
  DBMS_SQLSET.LOAD_SQLSET(
    sqlset_name => 'ayu_sts',
    populate_cursor => cur
  );
END;
/

結果は以下の通りです。

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

DBMS_SQLSET.SELECT_SQLSETファンクションで STS の中身を確認してみます。

SET LINESIZE 300;
SET PAGESIZE 100;
COL SQL_ID FOR A15
COL SQL_TEXT FOR A50 TRUNC
COL PARSING_SCHEMA_NAME FOR A15
COL MODULE FOR A10 TRUNC
SELECT PARSING_SCHEMA_NAME
     , MODULE
     , SQL_ID
     , PLAN_HASH_VALUE
     , SQL_TEXT
  FROM TABLE(DBMS_SQLSET.SELECT_SQLSET('ayu_sts'))
 ORDER BY 1, 2, 3, 4;

結果は以下の通りです。

PARSING_SCHEMA_ MODULE     SQL_ID          PLAN_HASH_VALUE SQL_TEXT
--------------- ---------- --------------- --------------- --------------------------------------------------
AYSHIBAT2       SQL*Plus   1q5txdwnv3adr         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 <= SYSDAT
AYSHIBAT2       SQL*Plus   1rzb7pxq1ysg5        2558420059 SELECT command_type, command_name
AYSHIBAT2       SQL*Plus   2mryuw87nvxxf        3967443709 SELECT PARSING_SCHEMA_NAME
AYSHIBAT2       SQL*Plus   2q983ru5vx8vn        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   2yygdv3dqjdc8        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   4qt314uwkk1f4         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(
AYSHIBAT2       SQL*Plus   5d1djbtbdwhpa        3967443709 SELECT PARSING_SCHEMA_NAME
AYSHIBAT2       SQL*Plus   5na8zpmv29pdj         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(
AYSHIBAT2       SQL*Plus   5ut0916yg0dcd         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= SYSDAT
AYSHIBAT2       SQL*Plus   6183guz8tqpyn        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   65j92m5kzbtvc        1200554957 SELECT COUNT(*) FROM TEST_TABLE
AYSHIBAT2       SQL*Plus   66kvgus6875ya        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLTUNE.SELECT_CU
AYSHIBAT2       SQL*Plus   6c9p04fh9qd79        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   6kpkfs4w3a834        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   7wy3kr47a118w        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   8g82q15uxwwwu        1388734953 SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT2', NU
AYSHIBAT2       SQL*Plus   8r18cn95tj5nn        3967443709 SELECT PARSING_SCHEMA_NAME
AYSHIBAT2       SQL*Plus   8ttyrqwgmygb7         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(
AYSHIBAT2       SQL*Plus   99hvq2ys1gnbz        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLSET.SELECT_CUR
AYSHIBAT2       SQL*Plus   bcapxw0b5k2mp        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLTUNE.SELECT_CU
AYSHIBAT2       SQL*Plus   bch0bp2z3kqmu         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(
AYSHIBAT2       SQL*Plus   cbnbjy0mjs2fc         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= SYSDAT
AYSHIBAT2       SQL*Plus   ddjzaqw7u2pcf         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(
AYSHIBAT2       SQL*Plus   gnq1uf4um6wcv         711311523 SELECT COUNT(*) FROM TEST_TABLE WHERE C3 >= SYSDAT
AYSHIBAT2       SQL*Plus   gq8tr437y1py6        1869011320 SELECT VALUE(P) FROM TABLE( DBMS_SQLTUNE.SELECT_CU
AYSHIBAT2       sqlplus@lo 6u5zqzz2nm55c        1388734953 SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS
AYSHIBAT2                  2g0n96st49uu8         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
AYSHIBAT2                  67h66vdzzqkjc         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
AYSHIBAT2                  af2pwyhxwr6sx         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
AYSHIBAT2                  cffz93mg1zxq7         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
AYSHIBAT2                  d4tpdhq8vb3f5         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
AYSHIBAT2                  gn6mqxp5gdnct         711311523 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER

32 rows selected.

SELECT文のみをフィルタしてキャプチャできました。

5. SQLSET_ROWオブジェクトと COMMAND_TYPE属性

SQLSET_ROWオブジェクトの定義は下記マニュアルに記載されています。

PL/SQLパッケージおよびタイプ・リファレンス 23ai
171.3.1 SQLSET_ROWオブジェクト・タイプ
https://docs.oracle.com/cd/G11854_01/arpls/DBMS_SQLTUNE.html#GUID-1F4AFB03-7B29-46FC-B3F2-CB01EC36326C

parsing_schema_name, sql_id, command_type など様々な属性が定義されています。
SQLSET_ROWオブジェクトに定義された属性は STS のフィルタ条件などで活用可能です。

command_type属性に指定する数値は V$SQLCOMMAND動的ビューを参照します。

データベース・リファレンス 23ai
10.91 V$SQLCOMMAND
https://docs.oracle.com/cd/G11854_01/refrn/V-SQLCOMMAND.html

V$SQLCOMMANDビューを確認してみます。

SQL> SELECT * FROM V$SQLCOMMAND;

COMMAND_TYPE COMMAND_NAME         CON_ID
------------ ---------------- ----------
           0                           0
           1 CREATE TABLE              0
           2 INSERT                    0
           3 SELECT                    0
           4 CREATE CLUSTER            0
           5 ALTER CLUSTER             0
           6 UPDATE                    0
           7 DELETE                    0
：

SELECT の COMMAND_TYPE が 3 なのが確認できました。

6. まとめ

SQLSET_ROWオブジェクトのcommand_type属性でSELECT文のみをキャプチャできました。
それ以外の組み合わせもフィルタしてキャプチャできるので、活用して下さいね彡(^)(^)

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

STS(SQL Tuning Set) で SELECT文のみをキャプチャしてみる(Oracle Database)

1. データの準備

2. SQL の実行

3. STS のキャプチャと確認(command_type属性でフィルタしないバージョン)

4. STS のキャプチャと確認(command_type属性でSELECTのみキャプチャするバージョン)

5. SQLSET_ROWオブジェクト と COMMAND_TYPE属性

6. まとめ

5. SQLSET_ROWオブジェクトと COMMAND_TYPE属性