Загружаем 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'));