오라클 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)[편집]
- 캡쳐주기 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분전