SQL Tuning Set (STS)のフィルタリング Tweet

Previously on Mac De Oracle
前回は、やっつけすくりぷとを書いたところまででした。

ということで、今回のやっつけスクリプトの準備というかアイデア

SQLSET大量のSQL文キャプチャされていて、SQLSETにキャプチャされている大量のSQL文を一括でSPAでまわしたりすることが辛い場合があります。
そんなときはどうすればよいか。。なやみますよね。。。ほんと。。
SQL Performance AnalyzerによるSQLパフォーマンス比較実行例(API)

SQLSETから、より細かなSQLSETにほぼ当分に分割し、新たなSQLSETを作成したいような場合もあるかもしれません。
そんな時はどうするか？

手取りばやくやるなら、ORA_HASH()が便利ですよね。（SQLSETの全表走査はさけられないのですが、Exaならw）

たとえば、以下のSQLSETがあったとして、300件程度に均等に分割したいという場合。
ora_hash()のバケット数はだいたい12ぐらいでできそうですね。

orcl@SYS> select name,statement_count,created from dba_sqlset order by name;

NAME                           STATEMENT_COUNT CREATED
------------------------------ --------------- ---------
STS20171029                               3819 29-OCT-17

orcl@SYS> select statement_count/300 from dba_sqlset where name='STS20171029';

STATEMENT_COUNT/300
-------------------
              12.73

いい感じに分割できそうです。

orcl@SYS> r
  1  select
  2    ora_hash(sql_id,12) as hash#
  3   ,count(sql_id)
  4  from
  5    table(dbms_sqltune.select_sqlset(
  6      sqlset_name=>'STS20171029'
  7     ,basic_filter=>null
  8    ))
  9  group by
 10    ora_hash(sql_id,12)
 11* order by 1

     HASH# COUNT(SQL_ID)
---------- -------------
         0           289
         1           293
         2           278
         3           296
         4           297
         5           281
         6           282
         7           298
         8           288
         9           304
        10           305
        11           298
        12           310

13 rows selected.

実際に利用するには、basic_filterでwhere句の条件文そのものを書いてあげます。サブクエリも書けますが、データ量が多い場合は性能面に注意しながらいろいろ試してみるといいと思います。
basic_filterでは、SQLSET_ROWオブジェクト・タイプの属性を利用したフィルタリングができるのですが、インジェクションですよね。構文を見ている限り;)
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1) SQLSET_ROWオブジェクト・タイプ

orcl@SYS> set serveroutput on
orcl@SYS> r
  1  begin
  2    for i in 0..12 loop
  3      for sqlset_rec in
  4      (
  5        select count(sql_id) as num_sql
  6        from
  7          table(dbms_sqltune.select_sqlset(
  8            sqlset_name=>'STS20171029'
  9           ,basic_filter=>'ora_hash(sql_id,12)='||i
 10          ))
 11      )
 12      loop
 13        dbms_output.put_line('hash#='||to_char(i,'fm99')||':'||sqlset_rec.num_sql);
 14      end loop;
 15    end loop;
 16* end;
hash#=0:289
hash#=1:293
hash#=2:278
hash#=3:296
hash#=4:297
hash#=5:281
hash#=6:282
hash#=7:298
hash#=8:288
hash#=9:304
hash#=10:305
hash#=11:298
hash#=12:310

PL/SQL procedure successfully completed.

ハッシュ値10でsql_id数をカウント！
うまくできそうですよね。

orcl@SYS> r
  1  select
  2    count(sql_id)
  3  from
  4    table(dbms_sqltune.select_sqlset(
  5      sqlset_name=>'STS20171029'
  6     ,basic_filter=>'ora_hash(sql_id,12) = 10'
  7*   ))

COUNT(SQL_ID)
-------------
          305

参考
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス　12c リリース1 (12.1) DBMS_SQLTUNE
Oracle® Database SQL言語リファレンス 12cリリース1 (12.1) ORA_HASH

---

・DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw
・SQL Tuning Setのキャプチャから退避までのスクリプト（やっつけ）