Create Oracle SQL Tuning Set for an application's cached statements.

oracle_create_sts_from_application_sql.sql

--
-- Create SQL Tuning Set
--
begin
    dbms_sqltune.create_sqlset
    (
        sqlset_name  => 'AppName_STS_01',
        description  => 'SQL Tuning Set for AppName',
        sqlset_owner => 'SYS'
    );
end; 
/

--
-- Add Application Statements
--
declare
    sqlset_cur dbms_sqltune.sqlset_cursor;
    bf varchar2(43);
begin
    bf := 'upper(PARSING_SCHEMA_NAME) = ''APP_SCHEMA_NAME''';
    
    open sqlset_cur for
        select value(p)
        from table( dbms_sqltune.select_cursor_cache(attribute_list=>'TYPICAL', basic_filter=>bf) ) P;

    dbms_sqltune.load_sqlset
    (
        sqlset_name     => 'AppName_STS_01',
        populate_cursor => sqlset_cur,
        load_option     => 'MERGE',
        update_option   => 'ACCUMULATE',
        sqlset_owner    => 'SYS'
    );
end;
/