Загружаем SQL statements в SQL Tuning Set из another SQL Tuning Set

-- Параметры функции: 

DBMS_SQLTUNE.SELECT_SQLSET (
  sqlset_name         IN   VARCHAR2,
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL)
  attribute_list      IN   VARCHAR2 := NULL,
  plan_filter         IN   VARCHAR2 := NULL,
  sqlset_owner        IN   VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;



sqlset_name  The SQL tuning set name

basic_filter The SQL predicate to filter the SQL from the SQL Tuning Set defined on attributes of the SQLSET_ROW

object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage  A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics)

plan_filter The plan filter

sqlset_owner The owner of the SQL tuning set, or NULL for the current schema owner

Return Values 
This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.



-- Имеется некий STS  my_sts:

SELECT *
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQLSET(
         sqlset_name       => 'my_sts',
         basic_filter      => 'disk_reads < 100',
         object_filter     => NULL,
         ranking_measure1  => NULL,
         ranking_measure2  => NULL,
         ranking_measure3  => NULL,
         result_percentage => 1,
         result_limit      => NULL,
         attribute_list    => NULL,
         plan_filter       => NULL,
         sqlset_owner      => NULL ));

        
-- Загрузим результат этого запроса в другой STS new_sts:

       
-- Создаем новый STS:
BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'new_sts'
,description => 'Prod workload sample');
END;
/


-- Загружаем SQL statements в SQL Tuning Set new_sts из SQL Tuning Set my_sts:
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQLSET(
         sqlset_name       => 'my_sts',
         basic_filter      => 'disk_reads < 100',
         object_filter     => NULL,
         ranking_measure1  => NULL,
         ranking_measure2  => NULL,
         ranking_measure3  => NULL,
         result_percentage => 1,
         result_limit      => NULL,
         attribute_list    => NULL,
         plan_filter       => NULL,
         sqlset_owner      => NULL )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'new_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
  CLOSE ref_cursor;
END;
/

-- Проверяем что загрузилось:
SELECT sqlset_name, count(*) FROM  DBA_SQLSET_STATEMENTS
group by sqlset_name;

new_sts    66
my_sts    69



-- Планы из STS можно посмотреть так:

dbms_xplan.display_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER  DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
sqlset_owner    IN VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;


select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'BASIC'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALL -projection'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED', 'SCOTT'));

Захват продуктивной SQL нагрузки

-- Создаем STS:

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
END;
/


--Захватываем SQL из CURSOR_CACHE в SQLSET:

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/


-- Просмотр STS:

SELECT name, created, statement_count
FROM dba_sqlset;

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;

SELECT sql_id, elapsed_time
,cpu_time, buffer_gets
,disk_reads, sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('PROD_WORKLOAD'));


-- Выборочно удалаем ненужные SQL statements из STS:

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,basic_filter => 'disk_reads < 100000');
END;
/
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;



-- Создаем таблицу для загрузки SQL statements из STS:

BEGIN
dbms_sqltune.create_stgtab_sqlset(
table_name => 'STS_TABLE'
,schema_name => 'SCOTT');
END;
/
select * from STS_TABLE;


-- Загружаем в неё SQL statements из STS:

BEGIN
dbms_sqltune.pack_stgtab_sqlset(
sqlset_name => 'PROD_WORKLOAD'
,sqlset_owner => 'SCOTT'
,staging_table_name => 'STS_TABLE'
,staging_schema_owner => 'SCOTT');
END;
/
select * from STS_TABLE;

SELECT name, owner, created, statement_count
FROM dba_sqlset;


-- Переносим  таблицу  STS_TABLE в другую СУБД:

drop database link source_db;
create database link source_db
connect to scott
identified by tiger
using 'testdb';

create table STS_TABLE as select * from STS_TABLE@source_db;

-- Создать все STS из таблицы (с опцией replace):

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%'
,replace => TRUE
,staging_table_name => 'STS_TABLE'
,staging_schema_owner=> 'SCOTT');
END;
/

-- Проверяем, что STS создан:

SELECT name, owner, created, statement_count
FROM dba_sqlset;

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;