오라클 SQL 수집 프로그램
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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 = '';