DB 점검(daily_check) 프로그램

※ 구현해야할 추가 기능 목록

튜닝 내역 관리 모듈

1. Menu

File

> Log In

> Log Out

> Exit

DB Check

> Run > Auto Run

         > Pick Date

> CPU Graph => 개발이 완료되지 않음

> TBS Report

> TBS Check

DB User

> Initialize...

> DB User Info Management

Admin

> SQL Profile

현재 위의 기능이 구현되어 있음

2. DB 구조

코드 테이블

CREATE TABLE MP_CODE

(

 PG_GB VARCHAR2(30)

,CD_GB VARCHAR2(30)

,CD_VAL VARCHAR2(30)

,REAL_VAL VARCHAR2(4000)

,ETC1 VARCHAR2(100)

,ETC2 VARCHAR2(100)

,ETC3 VARCHAR2(100)

);

INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('COMMON','PG_CD','CJHV_DBA_DC','CJHV DBA Daily Check','DBA 일간 점검용 프로그램');

INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','01','CandyTops','10.10.10.163:1521/SMARTPD');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','02','CandyMobile','10.10.10.213:1521/MVNO');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','03','CandyERP','10.10.10.52:1521/DMCSMS');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','04','CandyProvision','10.10.10.153:1521/PROVDB');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','05','CandyPhone','10.10.59.64:1521/VOIPPROD');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','SYSTEM_NAME','06','CandyDW','10.10.41.29:1521/SRV_CJCDWSMT');

INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','TUNER','01','J','정병관');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL,ETC1) VALUES('CJHV_DBA_DC','TUNER','02','P','박준호');

INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','01','힌트');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','02','인덱스생성');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','03','SQL수정');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','04','모델구조변경');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','05','DB설정변경');
INSERT INTO MP_CODE(PG_GB,CD_GB,CD_VAL,REAL_VAL) VALUES('CJHV_DBA_DC','TUNE_PATTERN','99','기타');

CREATE UNIQUE INDEX MP_CODE_PK ON MP_CODE(PG_GB, CD_GB,CD_VAL);

ALTER TABLE MP_CODE

ADD CONSTRAINT MP_CODE_PK PRIMARY KEY(PG_GB, CD_GB,CD_VAL)

USING INDEX;

튜닝내역 테이블

CREATE TABLE MP_DBA_DC_SQL_TUNE_HIST

(

 SEQ NUMBER

,TUNED_DATE VARCHAR2(8)

,SYSTEM_NAME VARCHAR2(100)

,TUNER VARCHAR2(2)

,SQL_ID VARCHAR2(20)

,FILE_NAME VARCHAR2(50)

,DAILY_EXEC NUMBER

,CPU_USAGE_PERC NUMBER(5,2)

,BLOCK_USAGE_PERC NUMBER(5,2)

,BF_ELAP NUMBER

,BF_BLOCK NUMBER

,BF_CPU_TIME NUMBER

,AF_ELAP NUMBER   => not null 속성이 필요해 보임

,AF_BLOCK NUMBER   => not null 속성이 필요해 보임

,AF_CPU_TIME NUMBER   => not null 속성이 필요해 보임

,TUNE_PATTERN VARCHAR2(2)

,TUNE_COMMENT VARCHAR2(1000)

);

CREATE UNIQUE INDEX MP_DBA_DC_SQL_TUNE_HIST_PK ON MP_DBA_DC_SQL_TUNE_HIST(SEQ);

ALTER TABLE MP_DBA_DC_SQL_TUNE_HIST

ADD CONSTRAINT MP_DBA_DC_SQL_TUNE_HIST_PK PRIMARY KEY(SEQ)

USING INDEX;

CREATE UNIQUE INDEX MP_DBA_DC_SQL_TUNE_HIST_IDX01 ON MP_DBA_DC_SQL_TUNE_HIST(SQL_ID, SYSTEM_NAE);

CREATE INDEX MP_DBA_DC_SQL_TUNE_HIST_IDX02 ON MP_DBA_DC_SQL_TUNE_HIST(TUNED_DATE,SYSTEM_NAME);

CREATE SEQUENCE MP_DBA_DC_SEQ_SQL_TUNE_HIST

INCREMENT BY 1
START WITH 1
NOMAXVALUE;

CREATE TABLE MP_DBA_DC_USER

(

 USERID VARCHAR2(30)

,PASSWORD VARCHAR2(30)

);

CREATE UNIQUE INDEX MP_DBA_DC_USER_PK ON MP_DBA_DC_USER(USERID);

ALTER TABLE MP_DBA_DC_USER

ADD CONSTRAINT MP_DBA_DC_USER_PK PRIMARY KEY(USERID)

USING INDEX;

SELECT TUNED_DATE "일자"
      ,SYSTEM_NAME "시스템명"
      ,TUNER "튜너"
      ,SEQ "순번"
      ,SQL_ID
      ,SYSTEM_NAME||'_'||TUNED_DATE||'_'||TUNER||'_'||LPAD(TO_CHAR(SEQ),5,'0')||'_'||SQL_ID||'.ptd' "튜닝결과파일"
      ,CASE WHEN BF_ELAP IS NOT NULL AND BF_BLOCK IS NOT NULL THEN
        '속도 개선 : '||TO_CHAR(ROUND((BF_ELAP/DECODE(AF_ELAP,0,1,AF_ELAP))*100,2))||' %, Block : 1/'||TO_CHAR(ROUND(BF_BLOCK/DECODE(AF_ELAP,0,1,AF_ELAP),2))||'로 감소'
        ELSE '값정보오류'
       END "DOC 코멘트용"
      ,CASE WHEN CPU_USAGE_PERC IS NOT NULL AND BF_CPU_TIME IS NOT NULL THEN
        TO_CHAR(CPU_USAGE_PERC)||' % -> '||TO_CHAR(ROUND((AF_CPU_TIME*CPU_USAGE_PERC)/BF_CPU_TIME,2))||' %'
        ELSE '값정보오류'
       END "Total CPU 사용율개선"
      ,CASE WHEN BLOCK_USAGE_PERC IS NOT NULL AND BF_BLOCK IS NOT NULL THEN
        TO_CHAR(BLOCK_USAGE_PERC)||' % -> '||TO_CHAR(ROUND((AF_BLOCK*BLOCK_USAGE_PERC)/BF_BLOCK,2))||' %'
        ELSE '값정보오류'
       END "Total Block 사용율개선"      
      ,DAILY_EXEC "일별 실행횟수"
      ,CPU_USAGE_PERC "CPU % Total"
      ,BLOCK_USAGE_PERC "Block % Total"
      ,BF_ELAP/1000000 "튜닝전 수행시간(s)"
      ,BF_CPU_TIME/1000000 "튜닝전 CPU Time(s)"
      ,BF_BLOCK "튜닝전 Block 사용량"
      ,AF_ELAP/1000000 "튜닝후 수행시간(s)"
      ,AF_CPU_TIME/1000000 "튜닝후 CPU Time(s)"
      ,AF_BLOCK "튜닝후 Block 사용량"
      ,TUNE_PATTERN "튜닝패턴"
      ,TUNE_COMMENT "비고"
FROM (SELECT A.TUNED_DATE
            ,(SELECT REAL_VAL
              FROM MP_CODE
              WHERE PG_GB = 'CJHV_DBA_DC'
              AND CD_GB ='SYSTEM_NAME'
              AND CD_VAL = A.SYSTEM_NAME) SYSTEM_NAME
            ,(SELECT REAL_VAL
              FROM MP_CODE
              WHERE PG_GB = 'CJHV_DBA_DC'
              AND CD_GB ='TUNER'
              AND CD_VAL = A.TUNER) TUNER
            ,A.SEQ
            ,A.SQL_ID
            ,A.DAILY_EXEC
            ,A.CPU_USAGE_PERC
            ,A.BLOCK_USAGE_PERC
            ,A.BF_ELAP
            ,A.BF_BLOCK
            ,A.BF_CPU_TIME
            ,A.AF_ELAP
            ,A.AF_BLOCK
            ,A.AF_CPU_TIME
            ,(SELECT REAL_VAL
              FROM MP_CODE
              WHERE PG_GB = 'CJHV_DBA_DC'
              AND CD_GB = 'TUNE PATTERN'
              AND CD_VAL = A.TUNE_PATTERN) TUNE_PATTERN
            ,A.TUNE_COMMENT
      FROM MP_DBA_DC_SQL_TUNE_HIST A
      WHERE 1=1
      ORDER BY A.SEQ DESC);

CREATE TABLE DB_MONITORING.DBAT_AWR_MEASUR_FACTOR
(
  BSNAP_ID         NUMBER,
  ESNAP_ID         NUMBER,
  SNAP_DATE        DATE,
  DBID             NUMBER,
  INSTANCE_NUMBER  NUMBER,
  TCPU             NUMBER,
  DBTIME           NUMBER,
  SLR              NUMBER
)
TABLESPACE USERS;

CREATE INDEX DB_MONITORING.DBAT_AWR_MEASUR_FACTOR_IDX01 ON DB_MONITORING.DBAT_AWR_MEASUR_FACTOR
(SNAP_DATE)
LOGGING
TABLESPACE USERS;

CREATE TABLE DB_MONITORING.DBAT_AWR_SQLLIST_CPU
(
  BSNAP_ID         NUMBER,
  ESNAP_ID         NUMBER,
  DBID             NUMBER,
  INSTANCE_NUMBER  NUMBER,
  SQL_ID           VARCHAR2(13 BYTE),
  EXEC             NUMBER,
  CPU_TIME         NUMBER,
  CPU_PER_EXEC     NUMBER(11,2),
  TOTAL_PER        NUMBER(5,2),
  ELAP_TIME        NUMBER(11,2),
  CPU_PER          NUMBER(5,2),
  IO_PER           NUMBER(5,2)
)
TABLESPACE USERS;

COMMENT ON TABLE DB_MONITORING.DBAT_AWR_SQLLIST_CPU IS 'SQL ordered by CPU Time';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.BSNAP_ID IS 'Begin Snapshot ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.ESNAP_ID IS 'End Snapshot ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.DBID IS 'DBID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.INSTANCE_NUMBER IS 'Instance Number';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.SQL_ID IS 'SQL ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.EXEC IS 'Executions';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.CPU_TIME IS 'SQL CPU Total Time(s)';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.CPU_PER_EXEC IS 'SQL 실행당 걸린 시간(s)';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.TOTAL_PER IS 'Total CPU Time 대비 SQL의 전체 CPU Time Percent';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.ELAP_TIME IS 'SQL Total Elapsed Time(s)';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.CPU_PER IS 'Total Elapsed Time vs Total CPU Time Percent';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_CPU.IO_PER IS 'IO Percent';

CREATE UNIQUE INDEX DB_MONITORING.DBAT_AWR_SQLLIST_CPU_IDX01 ON DB_MONITORING.DBAT_AWR_SQLLIST_CPU
(SQL_ID, INSTANCE_NUMBER, BSNAP_ID, ESNAP_ID)
LOGGING
TABLESPACE USERS;

CREATE UNIQUE INDEX DB_MONITORING.DBAT_AWR_SQLLIST_CPU_PK ON DB_MONITORING.DBAT_AWR_SQLLIST_CPU
(BSNAP_ID, ESNAP_ID, SQL_ID, DBID, INSTANCE_NUMBER)
LOGGING
TABLESPACE USERS;

ALTER TABLE DB_MONITORING.DBAT_AWR_SQLLIST_CPU ADD (
  CONSTRAINT DBAT_AWR_SQLLIST_CPU_PK
  PRIMARY KEY
  (BSNAP_ID, ESNAP_ID, SQL_ID, DBID, INSTANCE_NUMBER)
  USING INDEX DB_MONITORING.DBAT_AWR_SQLLIST_CPU_PK
  ENABLE VALIDATE);

CREATE TABLE DB_MONITORING.DBAT_AWR_SQLLIST_GETS
(
  BSNAP_ID         NUMBER,
  ESNAP_ID         NUMBER,
  DBID             NUMBER,
  INSTANCE_NUMBER  NUMBER,
  SQL_ID           VARCHAR2(13 BYTE),
  EXEC             NUMBER,
  BUFFER_GET       NUMBER,
  GET_PER_EXEC     NUMBER(15,2),
  TOTAL_PER        NUMBER(5,2),
  ELAP_TIME        NUMBER(15,2),
  CPU_PER          NUMBER(5,2),
  IO_PER           NUMBER(5,2)
)
TABLESPACE USERS;

COMMENT ON TABLE DB_MONITORING.DBAT_AWR_SQLLIST_GETS IS 'SQL ordered by CPU Time';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.BSNAP_ID IS 'Begin Snapshot ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.ESNAP_ID IS 'End Snapshot ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.DBID IS 'DBID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.INSTANCE_NUMBER IS 'Instance Number';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.SQL_ID IS 'SQL ID';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.EXEC IS 'Executions';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.BUFFER_GET IS 'SQL Buffer Gets';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.GET_PER_EXEC IS 'SQL 실행당 Buffer';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.TOTAL_PER IS 'Total CPU Time 대비 SQL의 전체 CPU Time Percent';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.ELAP_TIME IS 'SQL Total Elapsed Time(s)';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.CPU_PER IS 'Total Elapsed Time vs Total CPU Time Percent';

COMMENT ON COLUMN DB_MONITORING.DBAT_AWR_SQLLIST_GETS.IO_PER IS 'IO Percent';

CREATE UNIQUE INDEX DB_MONITORING.DBAT_AWR_SQLLIST_GETS_IDX01 ON DB_MONITORING.DBAT_AWR_SQLLIST_GETS
(SQL_ID, INSTANCE_NUMBER, BSNAP_ID, ESNAP_ID)
LOGGING
TABLESPACE USERS;

CREATE UNIQUE INDEX DB_MONITORING.DBAT_AWR_SQLLIST_GETS_PK ON DB_MONITORING.DBAT_AWR_SQLLIST_GETS
(BSNAP_ID, ESNAP_ID, SQL_ID, DBID, INSTANCE_NUMBER)
LOGGING
TABLESPACE USERS;

ALTER TABLE DB_MONITORING.DBAT_AWR_SQLLIST_GETS ADD (
  CONSTRAINT DBAT_AWR_SQLLIST_GETS_PK
  PRIMARY KEY
  (BSNAP_ID, ESNAP_ID, SQL_ID, DBID, INSTANCE_NUMBER)
  USING INDEX DB_MONITORING.DBAT_AWR_SQLLIST_GETS_PK
  ENABLE VALIDATE);