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

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

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 = '';

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

DB CAFE

2019년 4월 14일 (일) 23:29 판

1 수집 테이블[편집]

2 수집 프로시져[편집]

3 v$sql_bind_capture[편집]