행위

오라클 SQL 수집 프로그램

DB CAFE

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;
/

3 바인드변수 확인(v$sql_bind_capture)

  • 캡쳐주기 15분 _CURSOR_BIND_CAPTURE_INTERVAL Parameter값 으로 변경
  • 메모리 기본값 : 2000(Byte) _CURSOR_BIND_CAPTURE_AREA_SIZE Parameter 값에 의해 결정
SELECT /*+ LEADING(SESION)*/ SESION.SID
     , SESION.SERIAL#
     , SESION.USERNAME
     , SESION.SQL_ID
     , SESION.SQL_CHILD_NUMBER
     , SQL_BIND_CAPTURE.NAME
     , SQL_BIND_CAPTURE.VALUE_STRING
  --   , SQLAREA.SQL_FULLTEXT
  FROM V$SQL_BIND_CAPTURE SQL_BIND_CAPTURE
     , V$SESSION SESION
     , V$SQLAREA SQLAREA
 WHERE SESION.SQL_HASH_VALUE = SQL_BIND_CAPTURE.HASH_VALUE
   AND SESION.SQL_ADDRESS    = SQL_BIND_CAPTURE.ADDRESS
   AND SESION.SQL_ID         = SQLAREA.SQL_ID
   -- AND SESION.STATUS = 'ACTIVE'
   -- AND SESION.USERNAME = 'WEB'
   -- AND SESION.SID = 764
    AND SQLAREA.SQL_FULLTEXT  LIKE '%'|| 'cykim' ||'%'
 ORDER BY SQL_BIND_CAPTURE.LAST_CAPTURED, SESION.SID, SESION.SERIAL#, SQL_BIND_CAPTURE.NAME;
;

--
WITH BB AS
 (SELECT DISTINCT NAME BIND_VAL, VALUE_STRING VAL_VAL,DATATYPE_STRING,SQL_ID
    FROM V$SQL_BIND_CAPTURE A
   WHERE (LAST_CAPTURED, SQL_ID) IN
         (SELECT MAX(LAST_CAPTURED), SQL_ID FROM V$SQL_BIND_CAPTURE GROUP BY SQL_ID)),
     CC AS 
 (SELECT 1 RNUM FROM DUAL UNION ALL SELECT 2 FROM DUAL)
SELECT SQL_ID,DECODE(MOD(ROWNUM, 2), 1, A, B)
  FROM (SELECT SQL_ID,REPLACE('VARIABLE BIND_VAL '||DATATYPE_STRING,'BIND_VAL',BIND_VAL) A,
               REPLACE(REPLACE('EXEC :BIND_VAL :=''VAL_VAL'';','VAL_VAL',VAL_VAL),'BIND_VAL',BIND_VAL) B
          FROM BB,
               CC)
WHERE sql_id = '';

4 수정

-- 1. TB_TUNE_SQL_STATS  UPSERT  처리 
MERGE INTO TB_TUNE_SQL_STATS T
     USING (SELECT 1 SNAP_ID
                 , SQL_ID, LAST_ACTIVE_TIME, LAST_ACTIVE_CHILD_ADDRESS
                 , PLAN_HASH_VALUE, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS
                 , ROWS_PROCESSED, SERIALIZABLE_ABORTS, FETCHES, EXECUTIONS, END_OF_FETCH_COUNT
                 , LOADS, VERSION_COUNT, INVALIDATIONS, CPU_TIME
                 , ELAPSED_TIME, AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME
                 , USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM
                 , TYPECHECK_MEM, IO_INTERCONNECT_BYTES, PHYSICAL_READ_REQUESTS, PHYSICAL_READ_BYTES
                 , PHYSICAL_WRITE_REQUESTS, PHYSICAL_WRITE_BYTES
              FROM V$SQLSTATS 
             WHERE LAST_ACTIVE_TIME BETWEEN SYSDATE-1/(24/60)*5 AND SYSDATE -- 10분전 ([TODO] SNAPID 시간으로 변경)
          ) S
       ON (T.SQL_ID = S.SQL_ID 
          )
  WHEN MATCHED THEN
    -- SQL_ID가 같으면 업데이트 
   UPDATE SET T.LAST_ACTIVE_TIME = S.LAST_ACTIVE_TIME , T.LAST_ACTIVE_CHILD_ADDRESS = S.LAST_ACTIVE_CHILD_ADDRESS , T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE , T.PARSE_CALLS = S.PARSE_CALLS , T.DISK_READS = S.DISK_READS , T.DIRECT_WRITES = S.DIRECT_WRITES , T.BUFFER_GETS = S.BUFFER_GETS , T.ROWS_PROCESSED = S.ROWS_PROCESSED , T.SERIALIZABLE_ABORTS = S.SERIALIZABLE_ABORTS , T.FETCHES = S.FETCHES , T.EXECUTIONS = S.EXECUTIONS , T.END_OF_FETCH_COUNT = S.END_OF_FETCH_COUNT , T.LOADS = S.LOADS , T.VERSION_COUNT = S.VERSION_COUNT , T.INVALIDATIONS = S.INVALIDATIONS , T.CPU_TIME = S.CPU_TIME , T.ELAPSED_TIME = S.ELAPSED_TIME , T.APPLICATION_WAIT_TIME = S.APPLICATION_WAIT_TIME , T.CONCURRENCY_WAIT_TIME = S.CONCURRENCY_WAIT_TIME , T.USER_IO_WAIT_TIME = S.USER_IO_WAIT_TIME , T.PLSQL_EXEC_TIME = S.PLSQL_EXEC_TIME , T.JAVA_EXEC_TIME = S.JAVA_EXEC_TIME , T.SORTS = S.SORTS 
    WHEN NOT MATCHED THEN 
    -- SQL_ID가 없으면 인서트  
    INSERT (SNAP_ID,SQL_ID ,LAST_ACTIVE_TIME ,LAST_ACTIVE_CHILD_ADDRESS ,PLAN_HASH_VALUE ,PARSE_CALLS ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,ROWS_PROCESSED ,SERIALIZABLE_ABORTS ,FETCHES ,EXECUTIONS ,END_OF_FETCH_COUNT ,LOADS ,VERSION_COUNT ,INVALIDATIONS  ,CPU_TIME ,ELAPSED_TIME ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME  ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,SORTS)
    VALUES (S.SNAP_ID,S.SQL_ID ,S.LAST_ACTIVE_TIME ,S.LAST_ACTIVE_CHILD_ADDRESS ,S.PLAN_HASH_VALUE ,S.PARSE_CALLS ,S.DISK_READS ,S.DIRECT_WRITES ,S.BUFFER_GETS ,S.ROWS_PROCESSED ,S.SERIALIZABLE_ABORTS ,S.FETCHES ,S.EXECUTIONS ,S.END_OF_FETCH_COUNT ,S.LOADS ,S.VERSION_COUNT ,S.INVALIDATIONS  ,S.CPU_TIME ,S.ELAPSED_TIME ,S.APPLICATION_WAIT_TIME ,S.CONCURRENCY_WAIT_TIME  ,S.USER_IO_WAIT_TIME ,S.PLSQL_EXEC_TIME ,S.JAVA_EXEC_TIME ,S.SORTS)
    ;

-- 2. SQL_TEXT INSERT  => TB_TUNE_SQL_TEXT

INSERT INTO TB_TUNE_SQL_TEXT 
       (SQL_ID,SQL_TEXT,SQL_FULLTEXT)
SELECT SQL_ID,SQL_TEXT,SQL_FULLTEXT
  FROM V$SQLSTATS A
 WHERE LAST_ACTIVE_TIME BETWEEN SYSDATE-1/(24/60)*5 AND SYSDATE -- 5분전 ([TODO] SNAPID 시간으로 변경)
   AND NOT EXISTS (SELECT 1 FROM TB_TUNE_SQL_TEXT X
                    WHERE X.SQL_ID = A.SQL_ID)

-- 3. TB_TUNE_SQL 입력 
CREATE TABLE TB_TUNE_SQL
          AS 
        SELECT C.SQL_ID
             , B.SID
             , C.HASH_VALUE
             , (SELECT 1 FROM DUAL) AS SNAP_ID             
             , C.PLAN_HASH_VALUE
             , B.SERIAL#
             , C.BIND_DATA             
             , C.ADDRESS               
             , B.MACHINE
             , B.OSUSER  
             , C.ELAPSED_TIME
             , C.EXECUTIONS
             , C.END_OF_FETCH_COUNT
             , C.LAST_ACTIVE_TIME
             , SYSDATE AS CREATED
             , SYSDATE AS UPDATED
          FROM V$SESSION B
             , V$SQLAREA C     
--             , v$sql c
         WHERE B.SQL_HASH_VALUE = C.HASH_VALUE
           AND TO_CHAR(C.LAST_ACTIVE_TIME,'YYYYMMDDHH24MI') >= TO_CHAR(SYSDATE - 1/(24*60)*60,'YYYYMMDDHH24MI') -- 5분전  
;
-- 4. BIND  값 입력 
CREATE TABLE TB_TUNE_SQL_BIND_VAL 
    AS 
SELECT (SELECT 1 FROM DUAL) AS SNAP_ID, A.ADDRESS, A.HASH_VALUE, A.SQL_ID, CHILD_ADDRESS
     , CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED
     , LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA
     , SYSDATE AS CREATED
     , SYSDATE AS UPDATED     
  FROM V$SQL_BIND_CAPTURE A
 INNER JOIN V$SQLAREA     B
         ON B.HASH_VALUE = A.HASH_VALUE
        AND B.ADDRESS = A.ADDRESS
 WHERE TO_CHAR(B.LAST_ACTIVE_TIME,'YYYYMMDDHH24MI') >= TO_CHAR(SYSDATE - 1/(24*60)*5,'YYYYMMDDHH24MI') -- 5분전         
  ;      
  
-- 5. SQL_PLAN  

CREATE TABLE TB_TUNE_SQL_PLAN
   AS 
SELECT (SELECT 1 FROM DUAL) AS SNAP_ID      
     , A.ADDRESS, A.HASH_VALUE, A.SQL_ID, A.PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER, TIMESTAMP, OPERATION, OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER, ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME--, REMARKS, OTHER_XML
     , SYSDATE AS CREATED
     , SYSDATE AS UPDATED     
     
  FROM V$SQL_PLAN A
     , V$SQLAREA B
 WHERE A.ADDRESS = B.ADDRESS
   AND A.HASH_VALUE = B.HASH_VALUE
   AND A.SQL_ID = B.SQL_ID
   AND A.PLAN_HASH_VALUE = B.PLAN_HASH_VALUE     
   AND TO_CHAR(B.LAST_ACTIVE_TIME,'YYYYMMDDHH24MI') >= TO_CHAR(SYSDATE - 1/(24*60)*5,'YYYYMMDDHH24MI') -- 5분전