행위

"오라클 플랜 수집 프로그램"의 두 판 사이의 차이

DB CAFE

(→‎프로그램 테이블)
(→‎스케쥴 등록)
179번째 줄: 179번째 줄:
  
 
== 스케쥴 등록 ==
 
== 스케쥴 등록 ==
 +
 +
<source lang=sql>
 +
 +
BEGIN
 +
  SYS.DBMS_SCHEDULER.DROP_JOB
 +
    (job_name  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
 +
END;
 +
/
 +
 +
BEGIN
 +
  SYS.DBMS_SCHEDULER.CREATE_JOB
 +
    (
 +
      job_name        => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
      ,start_date      => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
 +
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
 +
      ,end_date        => NULL
 +
      ,job_class      => 'DEFAULT_JOB_CLASS'
 +
      ,job_type        => 'PLSQL_BLOCK'
 +
      ,job_action      => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;'
 +
      ,comments        => 'SQL GATEHRING FOR 5MIN  EVERY DAY'
 +
    );
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'RESTARTABLE'
 +
    ,value    => FALSE);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'LOGGING_LEVEL'
 +
    ,value    => SYS.DBMS_SCHEDULER.LOGGING_OFF);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'MAX_FAILURES');
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'MAX_RUNS');
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'STOP_ON_WINDOW_CLOSE'
 +
    ,value    => FALSE);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'JOB_PRIORITY'
 +
    ,value    => 3);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'SCHEDULE_LIMIT');
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'AUTO_DROP'
 +
    ,value    => TRUE);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'RESTART_ON_RECOVERY'
 +
    ,value    => FALSE);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'RESTART_ON_FAILURE'
 +
    ,value    => FALSE);
 +
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 +
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
 +
    ,attribute => 'STORE_OUTPUT'
 +
    ,value    => TRUE);
 +
 +
  SYS.DBMS_SCHEDULER.ENABLE
 +
    (name                  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
 +
END;
 +
/
 +
 +
 +
</source>
 +
 
== 수집결과 플랜 확인 ==
 
== 수집결과 플랜 확인 ==

2019년 7월 19일 (금) 14:25 판

thumb_up 추천메뉴 바로가기

1 오라클 플랜수집 / 성능 진단 프로그램 개발[편집]

1.1 SQL 수집 프로그램 테이블[편집]

1.1.1 스냅샷 테이블 TB_TUNE_SNAP[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SNAP
(
  SNAP_ID        NUMBER                         DEFAULT 1                     NOT NULL,
  SNAP_START_DT  VARCHAR2(16 BYTE),
  SNAP_END_DT    VARCHAR2(16 BYTE),
  CREATED        DATE                           DEFAULT SYSDATE               NOT NULL,
  UPDATED        DATE                           DEFAULT SYSDATE               NOT NULL
)
;
CREATE UNIQUE INDEX RTIS_DBA.PK_TUNE_SNAP ON RTIS_DBA.TB_TUNE_SNAP
(SNAP_ID)
;
ALTER TABLE RTIS_DBA.TB_TUNE_SNAP ADD (
  CONSTRAINT PK_TUNE_SNAP
  PRIMARY KEY
  (SNAP_ID)
  USING INDEX RTIS_DBA.PK_TUNE_SNAP
  ENABLE VALIDATE);

1.1.2 SQL 테이블 TB_TUNE_SQL[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SQL
(
  SQL_ID              VARCHAR2(13 BYTE),
  SID                 NUMBER,
  HASH_VALUE          NUMBER,
  SNAP_ID             NUMBER,
  PLAN_HASH_VALUE     NUMBER,
  SERIAL#             NUMBER,
  BIND_DATA           RAW(2000),
  ADDRESS             RAW(8),
  MACHINE             VARCHAR2(64 BYTE),
  OSUSER              VARCHAR2(30 BYTE),
  ELAPSED_TIME        NUMBER,
  EXECUTIONS          NUMBER,
  END_OF_FETCH_COUNT  NUMBER,
  LAST_ACTIVE_TIME    DATE,
  CREATED             DATE,
  UPDATED             DATE
);

1.1.3 SQL 쿼리 테이블 TB_TUNE_SQL_TEXT[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_TEXT
(
  SQL_ID        VARCHAR2(13 BYTE),
  SQL_TEXT      VARCHAR2(1000 BYTE),
  SQL_FULLTEXT  CLOB
)
CREATE INDEX RTIS_DBA.IX_TUNE_SQL_TEXT_CTX ON RTIS_DBA.TB_TUNE_SQL_TEXT
(SQL_FULLTEXT)
INDEXTYPE IS CTXSYS.CONTEXT;

1.1.4 SQL 통계 테이블 TB_TUNE_SQL_STATS[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS
(
  SNAP_ID                    NUMBER,
  SQL_ID                     VARCHAR2(13 BYTE),
  LAST_ACTIVE_TIME           DATE,
  LAST_ACTIVE_CHILD_ADDRESS  RAW(8),
  PLAN_HASH_VALUE            NUMBER,
  PARSE_CALLS                NUMBER,
  DISK_READS                 NUMBER,
  DIRECT_WRITES              NUMBER,
  BUFFER_GETS                NUMBER,
  ROWS_PROCESSED             NUMBER,
  SERIALIZABLE_ABORTS        NUMBER,
  FETCHES                    NUMBER,
  EXECUTIONS                 NUMBER,
  END_OF_FETCH_COUNT         NUMBER,
  LOADS                      NUMBER,
  VERSION_COUNT              NUMBER,
  INVALIDATIONS              NUMBER,
  CPU_TIME                   NUMBER,
  ELAPSED_TIME               NUMBER,
  AVG_HARD_PARSE_TIME        NUMBER,
  APPLICATION_WAIT_TIME      NUMBER,
  CONCURRENCY_WAIT_TIME      NUMBER,
  USER_IO_WAIT_TIME          NUMBER,
  PLSQL_EXEC_TIME            NUMBER,
  JAVA_EXEC_TIME             NUMBER,
  SORTS                      NUMBER,
  SHARABLE_MEM               NUMBER,
  TOTAL_SHARABLE_MEM         NUMBER,
  TYPECHECK_MEM              NUMBER,
  IO_INTERCONNECT_BYTES      NUMBER,
  PHYSICAL_READ_REQUESTS     NUMBER,
  PHYSICAL_READ_BYTES        NUMBER,
  PHYSICAL_WRITE_REQUESTS    NUMBER,
  PHYSICAL_WRITE_BYTES       NUMBER,
  CREATED                    DATE               DEFAULT SYSDATE               NOT NULL,
  UPDATED                    DATE               DEFAULT SYSDATE               NOT NULL
)

1.1.5 바인드 변수 테이블 TB_TUNE_SQL_BIND_VAL[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL
(
  SNAP_ID          NUMBER,
  ADDRESS          RAW(8),
  HASH_VALUE       NUMBER,
  SQL_ID           VARCHAR2(13 BYTE),
  CHILD_ADDRESS    RAW(8),
  CHILD_NUMBER     NUMBER,
  NAME             VARCHAR2(120 BYTE),
  POSITION         NUMBER,
  DATATYPE_STRING  VARCHAR2(60 BYTE),
  WAS_CAPTURED     VARCHAR2(3 BYTE),
  LAST_CAPTURED    DATE,
  VALUE_STRING     VARCHAR2(4000 BYTE),
  VALUE_ANYDATA    SYS.ANYDATA,
  CREATED          DATE,
  UPDATED          DATE
)

1.1.6 실행계획 테이블 TB_TUNE_SQL_PLAN[편집]

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_PLAN
(
  SNAP_ID            NUMBER,
  ADDRESS            RAW(8),
  HASH_VALUE         NUMBER,
  SQL_ID             VARCHAR2(13 BYTE),
  PLAN_HASH_VALUE    NUMBER,
  CHILD_ADDRESS      RAW(8),
  CHILD_NUMBER       NUMBER,
  TIMESTAMP          DATE,
  OPERATION          VARCHAR2(120 BYTE),
  OPTIONS            VARCHAR2(120 BYTE),
  OBJECT_NODE        VARCHAR2(160 BYTE),
  OBJECT#            NUMBER,
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(100 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_TYPE        VARCHAR2(80 BYTE),
  OPTIMIZER          VARCHAR2(80 BYTE),
  ID                 NUMBER,
  PARENT_ID          NUMBER,
  DEPTH              NUMBER,
  POSITION           NUMBER,
  SEARCH_COLUMNS     NUMBER,
  COST               NUMBER,
  CARDINALITY        NUMBER,
  BYTES              NUMBER,
  OTHER_TAG          VARCHAR2(140 BYTE),
  PARTITION_START    VARCHAR2(256 BYTE),
  PARTITION_STOP     VARCHAR2(256 BYTE),
  PARTITION_ID       NUMBER,
  OTHER              VARCHAR2(4000 BYTE),
  DISTRIBUTION       VARCHAR2(80 BYTE),
  CPU_COST           NUMBER,
  IO_COST            NUMBER,
  TEMP_SPACE         NUMBER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               NUMBER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  CREATED            DATE,
  UPDATED            DATE
)

1.2 스케쥴 등록[편집]

BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
      ,start_date      => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;'
      ,comments        => 'SQL GATEHRING FOR 5MIN  EVERY DAY'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTART_ON_RECOVERY'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTART_ON_FAILURE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/

1.3 수집결과 플랜 확인[편집]

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_플랜_수집_프로그램&oldid=1376"