Improving the Oracle Optimizer - Oracle Database 11gR2 Performance Tuning - page 323

3. Create a tuning set named TEST_TUNING_SET :

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'test_tuning_set',
description => 'Demo tuning set');
END;
/

4. Load the TEST_TUNING_SET with the first five statements in cursor cache,

ordered by elapsed time:
DECLARE
sql_curs DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN sql_curs FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
RANKING_MEASURE1 => 'elapsed_time',
RESULT_LIMIT => 5)) p;

DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'test_tuning_set',
populate_cursor => sql_curs);
END;
/

5. View the contents of the tuning set:

SELECT *
FROM TABLE(
DBMS_SQLTUNE.SELECT_SQLSET('test_tuning_set', ''));
Clean the database—drop the tuning set and revoke privileges:
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'test_tuning_set');
END;
/

CONNECT / AS SYSDBA
REVOKE ADMINISTER SQL TUNING SET FROM sh;