Wednesday, December 22, 2010

Manual STS

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MY_SQL_TUNING_SET_TEST',
    description  => 'Testing');
END;
/

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set_test',
             populate_cursor => baseline_cursor);
END;
/




-- TO select sql statements within snapshot range
declare
   baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
    open baseline_ref_cursor for
     select VALUE(p) from             table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(10, 20,
   'executions > 1 and disk_reads >   10000',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 

 DBMS_SQLTUNE.LOAD_SQLSET('MY_SQL_TUNING_SET_TEST',baseline_ref_cursor);
 

end;
/

No comments:

Post a Comment