행위

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

DB CAFE

(새 문서: <SOURCE LANG=SQL> CREATE OR REPLACE PROCEDURE CYKIM_DBA.sp_dbm_sql_gather →‎1.SNAPSHOT번호 생성 2. 3. 4.: IS -- DECLARE --변수,상수 선언 v_msg long;...)
 
1번째 줄: 1번째 줄:
 +
== 수집 테이블 ==
 +
<SOURCE LANG=SQL>
 +
 +
-- 1.스냅샷 테이블
 +
-- ALTER TABLE CYKIM_DBA.TB_TUNE_SNAP
 +
-- DROP PRIMARY KEY CASCADE;
 +
 +
-- DROP TABLE CYKIM_DBA.TB_TUNE_SNAP CASCADE CONSTRAINTS;
 +
 +
CREATE TABLE CYKIM_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
 +
)
 +
TABLESPACE TS_CYKIM_DBA_ADM_D01
 +
RESULT_CACHE (MODE DEFAULT)
 +
PCTUSED    0
 +
PCTFREE    10
 +
INITRANS  1
 +
MAXTRANS  255
 +
STORAGE    (
 +
            INITIAL          64K
 +
            NEXT            1M
 +
            MINEXTENTS      1
 +
            MAXEXTENTS      UNLIMITED
 +
            PCTINCREASE      0
 +
            BUFFER_POOL      DEFAULT
 +
            FLASH_CACHE      DEFAULT
 +
            CELL_FLASH_CACHE DEFAULT
 +
          )
 +
LOGGING
 +
NOCOMPRESS
 +
NOCACHE
 +
NOPARALLEL
 +
MONITORING;
 +
 +
 +
CREATE UNIQUE INDEX CYKIM_DBA.PK_TUNE_SNAP ON CYKIM_DBA.TB_TUNE_SNAP
 +
(SNAP_ID)
 +
LOGGING
 +
TABLESPACE TS_CYKIM_DBA_ADM_D01
 +
PCTFREE    10
 +
INITRANS  2
 +
MAXTRANS  255
 +
STORAGE    (
 +
            INITIAL          1M
 +
            NEXT            1M
 +
            MINEXTENTS      1
 +
            MAXEXTENTS      UNLIMITED
 +
            PCTINCREASE      0
 +
            BUFFER_POOL      DEFAULT
 +
            FLASH_CACHE      DEFAULT
 +
            CELL_FLASH_CACHE DEFAULT
 +
          )
 +
NOPARALLEL;
 +
 +
 +
ALTER TABLE CYKIM_DBA.TB_TUNE_SNAP ADD (
 +
  CONSTRAINT PK_TUNE_SNAP
 +
  PRIMARY KEY
 +
  (SNAP_ID)
 +
  USING INDEX CYKIM_DBA.PK_TUNE_SNAP
 +
  ENABLE VALIDATE);
 +
 +
-- 2.SQL/SESSION 저장 테이블
 +
-- DROP TABLE CYKIM_DBA.TB_TUNE_SQL_SESS CASCADE CONSTRAINTS;
 +
 +
CREATE TABLE CYKIM_DBA.TB_TUNE_SQL_SESS
 +
(
 +
  SNAP_ID        NUMBER                        NOT NULL,
 +
  SID            NUMBER                        NOT NULL,
 +
  SQL_ID          VARCHAR2(13 BYTE),
 +
  SQL_HASH_VALUE  NUMBER,
 +
  SERIAL#        NUMBER,
 +
  SQL_ADDRESS    RAW(8),
 +
  CHILD_ADDRESS  RAW(8),
 +
  CHILD_NUMBER    NUMBER,
 +
  HASH_VALUE      NUMBER,
 +
  BIND_DATA      RAW(2000),
 +
  WAIT_TIME      NUMBER,
 +
  SQL_TEXT        VARCHAR2(1000 BYTE),
 +
  SQL_FULLTEXT    CLOB,
 +
  PGA_USED_MEM    NUMBER,
 +
  PGA_ALLOC_MEM  NUMBER,
 +
  MACHINE        VARCHAR2(64 BYTE),
 +
  OSUSER          VARCHAR2(30 BYTE),
 +
  LOGON_TIME      DATE,
 +
  CREATED        DATE                          DEFAULT SYSDATE              NOT NULL,
 +
  UPDATED        DATE                          DEFAULT SYSDATE              NOT NULL
 +
)
 +
LOB (SQL_FULLTEXT) STORE AS (
 +
  TABLESPACE  TS_CYKIM_DBA_ADM_D01
 +
  ENABLE      STORAGE IN ROW
 +
  CHUNK      8192
 +
  RETENTION
 +
  NOCACHE
 +
  LOGGING
 +
      STORAGE    (
 +
                  INITIAL          64K
 +
                  NEXT            1M
 +
                  MINEXTENTS      1
 +
                  MAXEXTENTS      UNLIMITED
 +
                  PCTINCREASE      0
 +
                  BUFFER_POOL      DEFAULT
 +
                  FLASH_CACHE      DEFAULT
 +
                  CELL_FLASH_CACHE DEFAULT
 +
                ))
 +
TABLESPACE TS_CYKIM_DBA_ADM_D01
 +
RESULT_CACHE (MODE DEFAULT)
 +
PCTUSED    0
 +
PCTFREE    10
 +
INITRANS  1
 +
MAXTRANS  255
 +
STORAGE    (
 +
            INITIAL          64K
 +
            NEXT            1M
 +
            MINEXTENTS      1
 +
            MAXEXTENTS      UNLIMITED
 +
            PCTINCREASE      0
 +
            BUFFER_POOL      DEFAULT
 +
            FLASH_CACHE      DEFAULT
 +
            CELL_FLASH_CACHE DEFAULT
 +
          )
 +
LOGGING
 +
NOCOMPRESS
 +
NOCACHE
 +
NOPARALLEL
 +
MONITORING;
 +
 +
-- 3.바인드 변수 저장 테이블
 +
-- DROP TABLE CYKIM_DBA.TB_TUNE_SQL_BIND_VAL CASCADE CONSTRAINTS;
 +
 +
CREATE TABLE CYKIM_DBA.TB_TUNE_SQL_BIND_VAL
 +
(
 +
  SNAP_ID        NUMBER                        NOT NULL,
 +
  SID            NUMBER                        NOT NULL,
 +
  SQL_ID        VARCHAR2(13 BYTE),
 +
  POSITION      NUMBER,
 +
  ADDRESS        RAW(8),
 +
  CHILD_ADDRESS  RAW(8),
 +
  CHILD_NUMBER  NUMBER,
 +
  BIND_NAME      VARCHAR2(120 BYTE),
 +
  BIND_VALUE    VARCHAR2(4000 BYTE),
 +
  CREATED        DATE                          DEFAULT SYSDATE              NOT NULL,
 +
  UPDATED        DATE                          DEFAULT SYSDATE              NOT NULL
 +
)
 +
TABLESPACE TS_CYKIM_DBA_ADM_D01
 +
RESULT_CACHE (MODE DEFAULT)
 +
PCTUSED    0
 +
PCTFREE    10
 +
INITRANS  1
 +
MAXTRANS  255
 +
STORAGE    (
 +
            INITIAL          64K
 +
            NEXT            1M
 +
            MINEXTENTS      1
 +
            MAXEXTENTS      UNLIMITED
 +
            PCTINCREASE      0
 +
            BUFFER_POOL      DEFAULT
 +
            FLASH_CACHE      DEFAULT
 +
            CELL_FLASH_CACHE DEFAULT
 +
          )
 +
LOGGING
 +
NOCOMPRESS
 +
NOCACHE
 +
NOPARALLEL
 +
MONITORING;
 +
 +
</SOURCE>
 +
== 수집 프로시져 ==
 
<SOURCE LANG=SQL>
 
<SOURCE LANG=SQL>
  

2019년 4월 12일 (금) 18:14 판

thumb_up 추천메뉴 바로가기

1 수집 테이블[편집]

-- 1.스냅샷 테이블 
-- ALTER TABLE CYKIM_DBA.TB_TUNE_SNAP
-- DROP PRIMARY KEY CASCADE;

-- DROP TABLE CYKIM_DBA.TB_TUNE_SNAP CASCADE CONSTRAINTS;

CREATE TABLE CYKIM_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
)
TABLESPACE TS_CYKIM_DBA_ADM_D01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX CYKIM_DBA.PK_TUNE_SNAP ON CYKIM_DBA.TB_TUNE_SNAP
(SNAP_ID)
LOGGING
TABLESPACE TS_CYKIM_DBA_ADM_D01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


ALTER TABLE CYKIM_DBA.TB_TUNE_SNAP ADD (
  CONSTRAINT PK_TUNE_SNAP
  PRIMARY KEY
  (SNAP_ID)
  USING INDEX CYKIM_DBA.PK_TUNE_SNAP
  ENABLE VALIDATE);

-- 2.SQL/SESSION 저장 테이블 
-- DROP TABLE CYKIM_DBA.TB_TUNE_SQL_SESS CASCADE CONSTRAINTS;

CREATE TABLE CYKIM_DBA.TB_TUNE_SQL_SESS
(
  SNAP_ID         NUMBER                        NOT NULL,
  SID             NUMBER                        NOT NULL,
  SQL_ID          VARCHAR2(13 BYTE),
  SQL_HASH_VALUE  NUMBER,
  SERIAL#         NUMBER,
  SQL_ADDRESS     RAW(8),
  CHILD_ADDRESS   RAW(8),
  CHILD_NUMBER    NUMBER,
  HASH_VALUE      NUMBER,
  BIND_DATA       RAW(2000),
  WAIT_TIME       NUMBER,
  SQL_TEXT        VARCHAR2(1000 BYTE),
  SQL_FULLTEXT    CLOB,
  PGA_USED_MEM    NUMBER,
  PGA_ALLOC_MEM   NUMBER,
  MACHINE         VARCHAR2(64 BYTE),
  OSUSER          VARCHAR2(30 BYTE),
  LOGON_TIME      DATE,
  CREATED         DATE                          DEFAULT SYSDATE               NOT NULL,
  UPDATED         DATE                          DEFAULT SYSDATE               NOT NULL
)
LOB (SQL_FULLTEXT) STORE AS (
  TABLESPACE  TS_CYKIM_DBA_ADM_D01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE TS_CYKIM_DBA_ADM_D01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

-- 3.바인드 변수 저장 테이블 
-- DROP TABLE CYKIM_DBA.TB_TUNE_SQL_BIND_VAL CASCADE CONSTRAINTS;

CREATE TABLE CYKIM_DBA.TB_TUNE_SQL_BIND_VAL
(
  SNAP_ID        NUMBER                         NOT NULL,
  SID            NUMBER                         NOT NULL,
  SQL_ID         VARCHAR2(13 BYTE),
  POSITION       NUMBER,
  ADDRESS        RAW(8),
  CHILD_ADDRESS  RAW(8),
  CHILD_NUMBER   NUMBER,
  BIND_NAME      VARCHAR2(120 BYTE),
  BIND_VALUE     VARCHAR2(4000 BYTE),
  CREATED        DATE                           DEFAULT SYSDATE               NOT NULL,
  UPDATED        DATE                           DEFAULT SYSDATE               NOT NULL
)
TABLESPACE TS_CYKIM_DBA_ADM_D01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

2 수집 프로시져[편집]

CREATE OR REPLACE PROCEDURE CYKIM_DBA.sp_dbm_sql_gather
/* 
1.SNAPSHOT번호 생성
2.
3.
4.
*/
    IS 
-- DECLARE
--변수,상수 선언
    v_msg       long;
    v_snap_id   number default 0;
BEGIN
    
    /* 0.SNAPSHOT번호 생성 */
    SELECT SNAP_ID+1 into v_snap_id 
      FROM (
            SELECT /*+ INDEX_DESC(A PK_TUNE_SNAP) */ SNAP_ID SNAP_ID 
              FROM CYKIM_DBA.TB_TUNE_SNAP
             UNION ALL 
            SELECT 0 FROM DUAL
             ORDER BY SNAP_ID DESC                       
            )                 
      WHERE ROWNUM <= 1;

     
    /* 1. SNAPSHOT ID 생성 및 SNAPSHOT 시작 시간  */
    INSERT INTO CYKIM_DBA.TB_TUNE_SNAP (SNAP_ID,SNAP_START_DT)
         VALUES (v_snap_id,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))
    ;
    
    /* 2. 세션별 SQL 신규 추가  */
    INSERT INTO CYKIM_DBA.TB_TUNE_SQL_SESS 
           ( 
             SNAP_ID,SQL_HASH_VALUE, SID, SERIAL#, SQL_ID
           , SQL_ADDRESS,BIND_DATA, WAIT_TIME, SQL_TEXT, SQL_FULLTEXT 
           , CHILD_ADDRESS,CHILD_NUMBER,PGA_USED_MEM, PGA_ALLOC_MEM
           , MACHINE, OSUSER, LOGON_TIME
           )
    SELECT v_snap_id,B.SQL_HASH_VALUE, B.SID, B.SERIAL#, B.SQL_ID
         , B.SQL_ADDRESS, C.BIND_DATA, B.WAIT_TIME, C.SQL_TEXT, C.SQL_FULLTEXT
         , C.CHILD_ADDRESS, C.CHILD_NUMBER , A.PGA_USED_MEM, A.PGA_ALLOC_MEM
         , B.MACHINE, B.OSUSER, B.LOGON_TIME --이 쿼리를 호출한 시간                                
      FROM V$PROCESS A
         , V$SESSION B
         , V$SQL     C     
     WHERE A.ADDR = B.PADDR        
       AND B.SQL_HASH_VALUE = C.HASH_VALUE
       AND B.TYPE<>'BACKGROUND'
/* -- 추가건만 적재        A
       AND NOT EXISTS ( SELECT 1 FROM CYKIM_DBA.TB_TUNE_SQL_SESS X
                         WHERE X.SQL_ID = B.SQL_ID
                           AND X.BIND_DATA = C.BIND_DATA
                      )
*/                           
    ;
    -- COMMIT;
    
    /* SQL 바인드 변수 추가 */  
    INSERT INTO TB_TUNE_SQL_BIND_VAL
    (SNAP_ID,SID,SQL_ID, POSITION, ADDRESS, BIND_NAME, BIND_VALUE)
    SELECT v_snap_id
         , S.SID
         , B.SQL_ID
         , B.POSITION
         , B.ADDRESS
         , B.NAME BIND_NAME
         , B.VALUE_STRING BIND_VALUE 
    FROM V$SQLAREA T 
    INNER JOIN V$SQL_BIND_CAPTURE B  
            ON (T.SQL_ID = B.SQL_ID)
    INNER JOIN V$SESSION  S
            ON (S.SQL_ID= B.SQL_ID 
           AND B.ADDRESS = S.SQL_ADDRESS)         
    WHERE B.VALUE_STRING IS NOT NULL
      AND B.WAS_CAPTURED='YES'
      AND S.TYPE<>'BACKGROUND'    
      AND NOT EXISTS ( SELECT 1 FROM CYKIM_DBA.TB_TUNE_SQL_SESS X
                        WHERE X.SQL_ID    = T.SQL_ID
                          AND X.BIND_DATA = T.BIND_DATA
                          )         
    ;
    /*
    SELECT B.SQL_ID
         , B.POSITION
         , B.ADDRESS
         , B.NAME BIND_NAME
         , B.VALUE_STRING BIND_VALUE 
    FROM V$SQLAREA T 
    INNER JOIN V$SQL_BIND_CAPTURE B  
            ON (T.SQL_ID = B.SQL_ID)
    WHERE B.VALUE_STRING IS NOT NULL
      AND NOT EXISTS ( SELECT 1 FROM CYKIM_DBA.TB_TUNE_SQL_SESS X
                        WHERE X.SQL_ID    = T.SQL_ID
                          AND X.BIND_DATA = T.BIND_DATA
                          )         
    ;
    */


    UPDATE CYKIM_DBA.TB_TUNE_SNAP 
       SET SNAP_END_DT = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
     WHERE SNAP_ID = v_snap_id
    ;
    
    COMMIT;
           
  
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
        v_msg := 'ERROR : [ NO_DATA_FOUND ] '; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
--에러처리
END;
/
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_SQL_수집_프로그램&oldid=1076"