user::f158fe25-5652-4bc3-b247-b1c0f1c371b4

--AWR로 Daily SQLset 생성 스케줄러 등록

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'spa_awr_create_daily',
        job_type => 'PLSQL_BLOCK',
        job_action => '
declare
    mycur dbms_sqltune.sqlset_cursor;
    bf VARCHAR2(500);
    l_begin number;
    l_end number;
    l_sts_name varchar2(100);
    l_cdate varchar2(8) := to_char(sysdate-1,''yyyymmdd'');
begin

    l_sts_name := ''SPA_AWR_''||l_cdate;

    dbms_sqltune.create_sqlset(sqlset_name => l_sts_name, sqlset_owner => ''DB_MONITORING'');

    select min(snap_id), max(snap_id) into l_begin, l_end
    from dba_hist_snapshot where trunc(begin_interval_time) = l_cdate;

    bf := q''[parsing_schema_name not in (''SYS'',''SYSTEM'',''DB_MONITORING'')]'';


    open mycur for
    select value (P)
    from table(dbms_sqltune.select_workload_repository(l_begin,l_end, bf, null, null, null, null,1, null, ''ALL'')) P;


    dbms_sqltune.load_sqlset(sqlset_name => l_sts_name, populate_cursor => mycur);
end;',
        start_date => to_timestamp('20190404220000','YYYYMMDDHH24MISS'),
        repeat_interval => 'FREQ=DAILY',
        enabled => true,
        comments => 'Create SQLSET from AWR For SPA');
END;
/
 

-- Unsupport SQL 검증을 위해 Plan 비교용 프로그램 작성

CREATE TABLE DB_MONITORING.UPGRADE_PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(80 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE)
)
TABLESPACE PREBILLING;