{SQL計画管理}SQL計画ベースラインにSTSから手動ロード

 

https://docs.oracle.com/cd/F19136_01/tgsql/managing-sql-tuning-sets.html#GUID-5F6C1400-AD6F-4BEB-A0D8-12AADA127ADD

https://docs.oracle.com/cd/F19136_01/tgsql/managing-sql-tuning-sets.html#GUID-4E5240CD-B021-4728-89A9-95F5B81CBC59
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLTUNE.html#ARPLS68430

https://docs.oracle.com/cd/F19136_01/tgsql/managing-sql-plan-baselines.html#GUID-93DCD1D7-DCAA-4135-97B3-6CDAB37AC73B

 

grant execute on DBMS_WORKLOAD_REPOSITORY to sh;
grant ADMINISTER SQL TUNING SET to sh;
grant ADMINISTER SQL MANAGEMENT OBJECT to sh;

-- shユーザで実行
conn sh/sh

select * from DBA_HIST_SNAPSHOT;

 

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'tset1');
END;
/

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'tset1', 
    description  => 'SPM test');
END;
/

DECLARE 
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(144,151) ) p;

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

 

DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/

 

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,origin, created, SQL_TEXT
FROM   DBA_SQL_PLAN_BASELINES
order by CREATED;