오라클 SQL 수집 프로그램

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;
/
오라클 SQL 수집 프로그램

DB CAFE
