행위

오라클 플랜 수집 프로그램

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 7월 19일 (금) 14:19 판 (→‎SQL 통계 테이블 SQL_STATS)
thumb_up 추천메뉴 바로가기

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

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

1.1.1 스냅샷 테이블[편집]

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 테이블[편집]

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 TEXT 테이블[편집]

1.1.4 SQL 통계 테이블 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 바인드 변수 테이블 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 실행계획 테이블 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 스케쥴 등록[편집]

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

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