Create a SQL Tuning Set from the AWR

Create the Tuning Set

--
-- Create the Tuning Set
--
 
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_AWR');
END;
/

Load the Tuning Set from the AWR

--
-- Load the Tuning Set
--
 
  DECLARE
    l_cursor DBMS_SQLTUNE.sqlset_cursor;
  BEGIN
   OPEN l_cursor FOR
   SELECT VALUE(p)
   FROM TABLE (DBMS_SQLTUNE.select_workload_repository (12345,12456,NULL,NULL,NULL,NULL,NULL,NULL,10)) p;  -- The first two parameters are the Snap IDs.  The last parameter is The top L(imit) SQL from the (filtered) source ranked by the ranking measure.  
                                                                                                           -- Details of others can be found here https://docs.oracle.com/database/121/ARPLS/d_sqltun.htm#ARPLS68460
 
   DBMS_SQLTUNE.load_sqlset ( 
     sqlset_name => 'STS_AWR',
     populate_cursor => l_cursor);
  END;
  /

Published 1st November 2021

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License