"오라클 SQL 수집 프로그램"의 두 판 사이의 차이
DB CAFE
(새 문서: <SOURCE LANG=SQL> CREATE OR REPLACE PROCEDURE CYKIM_DBA.sp_dbm_sql_gather →1.SNAPSHOT번호 생성 2. 3. 4.: IS -- DECLARE --변수,상수 선언 v_msg long;...) |
(→v$sql_bind_capture) |
||
(다른 사용자 한 명의 중간 판 10개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | == 수집 테이블 == | ||
+ | |||
+ | <SOURCE LANG=SQL> | ||
+ | |||
+ | -- 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; | ||
+ | |||
+ | </SOURCE> | ||
+ | |||
+ | == 수집 프로시져 == | ||
<SOURCE LANG=SQL> | <SOURCE LANG=SQL> | ||
118번째 줄: | 293번째 줄: | ||
/ | / | ||
</SOURCE> | </SOURCE> | ||
+ | |||
+ | == 바인드변수 확인(v$sql_bind_capture) == | ||
+ | * 캡쳐주기 15분 _CURSOR_BIND_CAPTURE_INTERVAL Parameter값 으로 변경 | ||
+ | * 메모리 기본값 : 2000(Byte) _CURSOR_BIND_CAPTURE_AREA_SIZE Parameter 값에 의해 결정 | ||
+ | <source lang=sql> | ||
+ | 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 = ''; | ||
+ | |||
+ | </source> | ||
+ | |||
+ | == 수정 == | ||
+ | <source lang="sql"> | ||
+ | |||
+ | -- 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분전 | ||
+ | |||
+ | </source> | ||
+ | [[Category:oracle]] |
2020년 3월 13일 (금) 12:49 기준 최신판
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분전