"오라클 플랜 수집 프로그램"의 두 판 사이의 차이
DB CAFE
(→스냅샷 테이블) |
(→스케쥴 등록) |
||
(같은 사용자의 중간 판 6개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
= 오라클 플랜수집 / 성능 진단 프로그램 개발 = | = 오라클 플랜수집 / 성능 진단 프로그램 개발 = | ||
− | == 프로그램 테이블 == | + | == SQL 수집 프로그램 테이블 == |
=== 스냅샷 테이블 TB_TUNE_SNAP === | === 스냅샷 테이블 TB_TUNE_SNAP === | ||
<source lang=sql> | <source lang=sql> | ||
25번째 줄: | 25번째 줄: | ||
</source> | </source> | ||
− | === SQL 테이블=== | + | === SQL 테이블 TB_TUNE_SQL === |
<source lang=sql> | <source lang=sql> | ||
CREATE TABLE RTIS_DBA.TB_TUNE_SQL | CREATE TABLE RTIS_DBA.TB_TUNE_SQL | ||
48번째 줄: | 48번째 줄: | ||
</source> | </source> | ||
− | === SQL | + | |
+ | === SQL 쿼리 테이블 TB_TUNE_SQL_TEXT === | ||
<source lang=sql> | <source lang=sql> | ||
+ | CREATE TABLE RTIS_DBA.TB_TUNE_SQL_TEXT | ||
+ | ( | ||
+ | SQL_ID VARCHAR2(13 BYTE), | ||
+ | SQL_TEXT VARCHAR2(1000 BYTE), | ||
+ | SQL_FULLTEXT CLOB | ||
+ | ) | ||
+ | CREATE INDEX RTIS_DBA.IX_TUNE_SQL_TEXT_CTX ON RTIS_DBA.TB_TUNE_SQL_TEXT | ||
+ | (SQL_FULLTEXT) | ||
+ | INDEXTYPE IS CTXSYS.CONTEXT; | ||
+ | |||
</source> | </source> | ||
− | === SQL 통계 테이블 | + | |
+ | === SQL 통계 테이블 TB_TUNE_SQL_STATS === | ||
<source lang=sql> | <source lang=sql> | ||
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS | CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS | ||
95번째 줄: | 107번째 줄: | ||
</source> | </source> | ||
− | === 바인드 변수 테이블 | + | === 바인드 변수 테이블 TB_TUNE_SQL_BIND_VAL === |
<source lang=sql> | <source lang=sql> | ||
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL | CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL | ||
116번째 줄: | 128번째 줄: | ||
) | ) | ||
</source> | </source> | ||
− | === 실행계획 테이블 | + | === 실행계획 테이블 TB_TUNE_SQL_PLAN === |
<source lang=sql> | <source lang=sql> | ||
165번째 줄: | 177번째 줄: | ||
</source> | </source> | ||
+ | |||
== 스케쥴 등록 == | == 스케쥴 등록 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | |||
+ | BEGIN | ||
+ | SYS.DBMS_SCHEDULER.DROP_JOB | ||
+ | (job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'); | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | BEGIN | ||
+ | SYS.DBMS_SCHEDULER.CREATE_JOB | ||
+ | ( | ||
+ | job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,start_date => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm') | ||
+ | ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30' | ||
+ | ,end_date => NULL | ||
+ | ,job_class => 'DEFAULT_JOB_CLASS' | ||
+ | ,job_type => 'PLSQL_BLOCK' | ||
+ | ,job_action => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;' | ||
+ | ,comments => 'SQL GATEHRING FOR 5MIN EVERY DAY' | ||
+ | ); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'RESTARTABLE' | ||
+ | ,value => FALSE); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'LOGGING_LEVEL' | ||
+ | ,value => SYS.DBMS_SCHEDULER.LOGGING_OFF); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'MAX_FAILURES'); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'MAX_RUNS'); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'STOP_ON_WINDOW_CLOSE' | ||
+ | ,value => FALSE); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'JOB_PRIORITY' | ||
+ | ,value => 3); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'SCHEDULE_LIMIT'); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'AUTO_DROP' | ||
+ | ,value => TRUE); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'RESTART_ON_RECOVERY' | ||
+ | ,value => FALSE); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'RESTART_ON_FAILURE' | ||
+ | ,value => FALSE); | ||
+ | SYS.DBMS_SCHEDULER.SET_ATTRIBUTE | ||
+ | ( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER' | ||
+ | ,attribute => 'STORE_OUTPUT' | ||
+ | ,value => TRUE); | ||
+ | |||
+ | SYS.DBMS_SCHEDULER.ENABLE | ||
+ | (name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'); | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | |||
+ | </source> | ||
+ | |||
+ | === 통계정보 수집 프로시져 === | ||
+ | |||
+ | <source lang=sql> | ||
+ | CREATE OR REPLACE PROCEDURE RTIS_DBA.sp_dbm_sql_gather | ||
+ | /* | ||
+ | 0. SNAPSHOT ID 생성 | ||
+ | 1. TB_TUNE_SNAP -- SNAPSHOT 시작 시간 | ||
+ | 2. TB_TUNE_SQL_STATS -- SQL_STATS UPSERT 처리 | ||
+ | 3. TB_TUNE_SQL_TEXT -- SQL_TEXT | ||
+ | 4. TB_TUNE_SQL_BIND_VAL -- SQL 바인드 변수 추가 | ||
+ | 5. TB_TUNE_SQL -- SQL(SQLAREA) 추가 | ||
+ | 6. TB_TUNE_SQL_PLAN -- SQL PLAN | ||
+ | 7. TB_TUNE_SNAP -- SNAPSHOT 종료 시간 | ||
+ | */ | ||
+ | |||
+ | IS | ||
+ | -- DECLARE | ||
+ | --변수,상수 선언 | ||
+ | v_msg long; | ||
+ | v_snap_id number default 0; | ||
+ | v_snap_end_dt date; | ||
+ | BEGIN | ||
+ | |||
+ | /* 0.SNAPSHOT번호 생성 */ | ||
+ | SELECT SNAP_ID+1 | ||
+ | , to_date(SNAP_END_DT,'YYYYMMDD HH24MISS') | ||
+ | INTO v_snap_id,v_snap_end_dt | ||
+ | FROM ( | ||
+ | SELECT /*+ INDEX_DESC(A PK_TUNE_SNAP) */ SNAP_ID SNAP_ID | ||
+ | , SNAP_END_DT | ||
+ | FROM RTIS_DBA.TB_TUNE_SNAP | ||
+ | UNION ALL | ||
+ | SELECT 0,'' FROM DUAL | ||
+ | |||
+ | ORDER BY SNAP_ID DESC | ||
+ | ) | ||
+ | WHERE ROWNUM <= 1; | ||
+ | |||
+ | |||
+ | /* 1. SNAPSHOT ID 생성 및 SNAPSHOT 시작 시간 */ | ||
+ | INSERT INTO RTIS_DBA.TB_TUNE_SNAP (SNAP_ID,SNAP_START_DT) | ||
+ | VALUES (v_snap_id,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')) | ||
+ | ; | ||
+ | |||
+ | /* 2. TB_TUNE_SQL_STATS UPSERT 처리 */ | ||
+ | MERGE /*+ PARALLEL(T,1) APPEND */ INTO TB_TUNE_SQL_STATS T | ||
+ | USING ( | ||
+ | SELECT A.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 | ||
+ | , B.INST_ID,B.OSUSER,B.MACHINE | ||
+ | FROM GV$SQLSTATS A | ||
+ | INNER JOIN GV$SESSION B | ||
+ | ON B.SQL_ID = A.SQL_ID | ||
+ | AND B.INST_ID = A.INST_ID | ||
+ | AND B.CON_ID = A.CON_ID | ||
+ | AND B.OSUSER = 'jeus' | ||
+ | WHERE LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | ) 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 , T.INST_ID = S.INST_ID ,T.OSUSER = S.OSUSER,T.MACHINE = S.MACHINE | ||
+ | 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,INST_ID ,OSUSER,MACHINE) | ||
+ | VALUES (v_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,S.INST_ID,S.OSUSER,S.MACHINE) | ||
+ | ; | ||
+ | -- COMMIT; | ||
+ | |||
+ | /* 3. SQL_TEXT INSERT => TB_TUNE_SQL_TEXT */ | ||
+ | INSERT /*+ APPEND */ INTO TB_TUNE_SQL_TEXT | ||
+ | (SQL_ID,SQL_TEXT,SQL_FULLTEXT) | ||
+ | SELECT A.SQL_ID,A.SQL_TEXT,A.SQL_FULLTEXT | ||
+ | FROM GV$SQLSTATS A | ||
+ | INNER JOIN GV$SESSION B | ||
+ | ON B.SQL_ID = A.SQL_ID | ||
+ | AND B.INST_ID = A.INST_ID | ||
+ | AND B.CON_ID = A.CON_ID | ||
+ | AND B.OSUSER = 'jeus' | ||
+ | WHERE LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | AND NOT EXISTS (SELECT 1 FROM TB_TUNE_SQL_TEXT X | ||
+ | WHERE X.SQL_ID = A.SQL_ID) | ||
+ | ; | ||
+ | |||
+ | /* 4.SQL 바인드 변수 추가 */ | ||
+ | INSERT /*+ APPEND */ INTO TB_TUNE_SQL_BIND_VAL | ||
+ | ( SNAP_ID,ADDRESS, HASH_VALUE, SQL_ID, CHILD_ADDRESS | ||
+ | , CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED | ||
+ | , LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA ) | ||
+ | SELECT v_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 | ||
+ | FROM GV$SQL_BIND_CAPTURE A | ||
+ | INNER JOIN GV$SQLAREA B | ||
+ | ON B.HASH_VALUE = A.HASH_VALUE | ||
+ | AND B.ADDRESS = A.ADDRESS | ||
+ | AND B.INST_ID = A.INST_ID | ||
+ | WHERE B.LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | AND EXISTS (SELECT 1 FROM GV$SESSION X | ||
+ | WHERE X.SQL_ID = A.SQL_ID | ||
+ | AND X.INST_ID = A.INST_ID | ||
+ | AND X.OSUSER = 'jeus' | ||
+ | ) | ||
+ | ; | ||
+ | |||
+ | |||
+ | /* 5.SQL 추가 */ | ||
+ | INSERT /*+ APPEND */ INTO TB_TUNE_SQL | ||
+ | ( SQL_ID, SID, HASH_VALUE,SNAP_ID, PLAN_HASH_VALUE | ||
+ | , SERIAL#, BIND_DATA, ADDRESS, MACHINE, OSUSER | ||
+ | , ELAPSED_TIME, EXECUTIONS, END_OF_FETCH_COUNT, LAST_ACTIVE_TIME | ||
+ | ) | ||
+ | SELECT C.SQL_ID, B.SID, C.HASH_VALUE, v_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 | ||
+ | FROM GV$SESSION B | ||
+ | , GV$SQLAREA C | ||
+ | -- , v$sql c | ||
+ | WHERE B.SQL_HASH_VALUE = C.HASH_VALUE | ||
+ | AND B.INST_ID = C.INST_ID | ||
+ | AND B.SQL_ID = C.SQL_ID | ||
+ | AND C.LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | ; | ||
+ | |||
+ | |||
+ | /* 6.SQL PLAN */ | ||
+ | MERGE /*+ PARALLEL(T,1) APPEND */ INTO TB_TUNE_SQL_PLAN T | ||
+ | USING ( | ||
+ | SELECT 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 | ||
+ | FROM GV$SQL_PLAN A | ||
+ | , GV$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 A.INST_ID = B.INST_ID | ||
+ | AND B.LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | ) S | ||
+ | ON (T.SQL_ID = S.SQL_ID AND | ||
+ | T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE | ||
+ | ) | ||
+ | -- 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,ADDRESS,HASH_VALUE,SQL_ID,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 | ||
+ | ) | ||
+ | VALUES (v_snap_id,S.ADDRESS,S.HASH_VALUE,S.SQL_ID,S.PLAN_HASH_VALUE | ||
+ | ,S.CHILD_ADDRESS,S.CHILD_NUMBER,S.TIMESTAMP,S.OPERATION,S.OPTIONS | ||
+ | ,S.OBJECT_NODE,S.OBJECT#,S.OBJECT_OWNER,S.OBJECT_NAME,S.OBJECT_ALIAS | ||
+ | ,S.OBJECT_TYPE,S.OPTIMIZER,S.ID,S.PARENT_ID,S.DEPTH | ||
+ | ,S.POSITION,S.SEARCH_COLUMNS,S.COST,S.CARDINALITY,S.BYTES | ||
+ | ,S.OTHER_TAG,S.PARTITION_START,S.PARTITION_STOP,S.PARTITION_ID,S.OTHER | ||
+ | ,S.DISTRIBUTION,S.CPU_COST,S.IO_COST,S.TEMP_SPACE,S.ACCESS_PREDICATES | ||
+ | ,S.FILTER_PREDICATES,S.PROJECTION,S.TIME,S.QBLOCK_NAME ) | ||
+ | |||
+ | ; | ||
+ | /* | ||
+ | INSERT INTO TB_TUNE_SQL_PLAN | ||
+ | ( SNAP_ID, ADDRESS, HASH_VALUE, SQL_ID, 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 | ||
+ | ) | ||
+ | SELECT v_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 | ||
+ | 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 B.LAST_ACTIVE_TIME > v_snap_end_dt | ||
+ | ; | ||
+ | */ | ||
+ | /* 7. SNAPSHOT 종료 시간 */ | ||
+ | UPDATE 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; | ||
+ | / | ||
+ | </source> | ||
+ | |||
== 수집결과 플랜 확인 == | == 수집결과 플랜 확인 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | |||
+ | -- SQL PLAN VIEW | ||
+ | select a.sql_id, a.child_number child, | ||
+ | a.id | ||
+ | , a.parent_id pid | ||
+ | ---------------------------------------------------------------- | ||
+ | , lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ') | ||
+ | || decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')') | ||
+ | || decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name | ||
+ | || ' ('|| a.object_type ||')') | ||
+ | ----------------------------------------------------------------( | ||
+ | || decode(a.cost||a.cardinality||a.bytes,null,null,' (') | ||
+ | || decode(a.cost, null, null, ' Cost='||a.cost) | ||
+ | || decode(a.cardinality, null, null, ' Card='||a.cardinality) | ||
+ | || decode(a.bytes, null, null, ' Bytes='||a.bytes) | ||
+ | || decode(a.cost||a.cardinality||a.bytes,null,null,' )') | ||
+ | ----------------------------------------------------------------) | ||
+ | || decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation" | ||
+ | --------------------------------------------------------------- | ||
+ | , a.object_type "Type" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.cardinality "E-Rows" | ||
+ | , a.bytes "E-Bytes" | ||
+ | , a.cost | ||
+ | , a.time "E-Time" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.partition_start "Pstart" | ||
+ | , a.partition_stop "Pstop" | ||
+ | , a.partition_id "Pid" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.qblock_name "QB" | ||
+ | , a.access_predicates | ||
+ | , a.filter_predicates | ||
+ | ---------------------------------------------------------------- | ||
+ | --from v$sql_plan a | ||
+ | from TB_TUNE_SQL_PLAN A --v$sql_plan a | ||
+ | where 1 = 1 | ||
+ | AND A.SNAP_ID = (SELECT MAX(SNAP_ID) FROM TB_TUNE_SQL_PLAN B WHERE B.SQL_ID = A.SQL_ID) | ||
+ | and a.sql_id = 'fwyjdtd7dudvr' --and plan_hash_value = '' | ||
+ | and a.child_number = 0 | ||
+ | order by a.sql_id, a.id, a.child_number; | ||
+ | </source> | ||
+ | [[Category:oracle]] |
2020년 3월 2일 (월) 16:55 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 오라클 플랜수집 / 성능 진단 프로그램 개발[편집]
1.1 SQL 수집 프로그램 테이블[편집]
1.1.1 스냅샷 테이블 TB_TUNE_SNAP[편집]
CREATE TABLE RTIS_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
)
;
CREATE UNIQUE INDEX RTIS_DBA.PK_TUNE_SNAP ON RTIS_DBA.TB_TUNE_SNAP
(SNAP_ID)
;
ALTER TABLE RTIS_DBA.TB_TUNE_SNAP ADD (
CONSTRAINT PK_TUNE_SNAP
PRIMARY KEY
(SNAP_ID)
USING INDEX RTIS_DBA.PK_TUNE_SNAP
ENABLE VALIDATE);
1.1.2 SQL 테이블 TB_TUNE_SQL[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL
(
SQL_ID VARCHAR2(13 BYTE),
SID NUMBER,
HASH_VALUE NUMBER,
SNAP_ID NUMBER,
PLAN_HASH_VALUE NUMBER,
SERIAL# NUMBER,
BIND_DATA RAW(2000),
ADDRESS RAW(8),
MACHINE VARCHAR2(64 BYTE),
OSUSER VARCHAR2(30 BYTE),
ELAPSED_TIME NUMBER,
EXECUTIONS NUMBER,
END_OF_FETCH_COUNT NUMBER,
LAST_ACTIVE_TIME DATE,
CREATED DATE,
UPDATED DATE
);
1.1.3 SQL 쿼리 테이블 TB_TUNE_SQL_TEXT[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_TEXT
(
SQL_ID VARCHAR2(13 BYTE),
SQL_TEXT VARCHAR2(1000 BYTE),
SQL_FULLTEXT CLOB
)
CREATE INDEX RTIS_DBA.IX_TUNE_SQL_TEXT_CTX ON RTIS_DBA.TB_TUNE_SQL_TEXT
(SQL_FULLTEXT)
INDEXTYPE IS CTXSYS.CONTEXT;
1.1.4 SQL 통계 테이블 TB_TUNE_SQL_STATS[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS
(
SNAP_ID NUMBER,
SQL_ID VARCHAR2(13 BYTE),
LAST_ACTIVE_TIME DATE,
LAST_ACTIVE_CHILD_ADDRESS RAW(8),
PLAN_HASH_VALUE NUMBER,
PARSE_CALLS NUMBER,
DISK_READS NUMBER,
DIRECT_WRITES NUMBER,
BUFFER_GETS NUMBER,
ROWS_PROCESSED NUMBER,
SERIALIZABLE_ABORTS NUMBER,
FETCHES NUMBER,
EXECUTIONS NUMBER,
END_OF_FETCH_COUNT NUMBER,
LOADS NUMBER,
VERSION_COUNT NUMBER,
INVALIDATIONS NUMBER,
CPU_TIME NUMBER,
ELAPSED_TIME NUMBER,
AVG_HARD_PARSE_TIME NUMBER,
APPLICATION_WAIT_TIME NUMBER,
CONCURRENCY_WAIT_TIME NUMBER,
USER_IO_WAIT_TIME NUMBER,
PLSQL_EXEC_TIME NUMBER,
JAVA_EXEC_TIME NUMBER,
SORTS NUMBER,
SHARABLE_MEM NUMBER,
TOTAL_SHARABLE_MEM NUMBER,
TYPECHECK_MEM NUMBER,
IO_INTERCONNECT_BYTES NUMBER,
PHYSICAL_READ_REQUESTS NUMBER,
PHYSICAL_READ_BYTES NUMBER,
PHYSICAL_WRITE_REQUESTS NUMBER,
PHYSICAL_WRITE_BYTES NUMBER,
CREATED DATE DEFAULT SYSDATE NOT NULL,
UPDATED DATE DEFAULT SYSDATE NOT NULL
)
1.1.5 바인드 변수 테이블 TB_TUNE_SQL_BIND_VAL[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL
(
SNAP_ID NUMBER,
ADDRESS RAW(8),
HASH_VALUE NUMBER,
SQL_ID VARCHAR2(13 BYTE),
CHILD_ADDRESS RAW(8),
CHILD_NUMBER NUMBER,
NAME VARCHAR2(120 BYTE),
POSITION NUMBER,
DATATYPE_STRING VARCHAR2(60 BYTE),
WAS_CAPTURED VARCHAR2(3 BYTE),
LAST_CAPTURED DATE,
VALUE_STRING VARCHAR2(4000 BYTE),
VALUE_ANYDATA SYS.ANYDATA,
CREATED DATE,
UPDATED DATE
)
1.1.6 실행계획 테이블 TB_TUNE_SQL_PLAN[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_PLAN
(
SNAP_ID NUMBER,
ADDRESS RAW(8),
HASH_VALUE NUMBER,
SQL_ID VARCHAR2(13 BYTE),
PLAN_HASH_VALUE NUMBER,
CHILD_ADDRESS RAW(8),
CHILD_NUMBER NUMBER,
TIMESTAMP DATE,
OPERATION VARCHAR2(120 BYTE),
OPTIONS VARCHAR2(120 BYTE),
OBJECT_NODE VARCHAR2(160 BYTE),
OBJECT# NUMBER,
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(100 BYTE),
OBJECT_ALIAS VARCHAR2(65 BYTE),
OBJECT_TYPE VARCHAR2(80 BYTE),
OPTIMIZER VARCHAR2(80 BYTE),
ID NUMBER,
PARENT_ID NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
SEARCH_COLUMNS NUMBER,
COST NUMBER,
CARDINALITY NUMBER,
BYTES NUMBER,
OTHER_TAG VARCHAR2(140 BYTE),
PARTITION_START VARCHAR2(256 BYTE),
PARTITION_STOP VARCHAR2(256 BYTE),
PARTITION_ID NUMBER,
OTHER VARCHAR2(4000 BYTE),
DISTRIBUTION VARCHAR2(80 BYTE),
CPU_COST NUMBER,
IO_COST NUMBER,
TEMP_SPACE NUMBER,
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE),
PROJECTION VARCHAR2(4000 BYTE),
TIME NUMBER,
QBLOCK_NAME VARCHAR2(30 BYTE),
CREATED DATE,
UPDATED DATE
)
1.2 스케쥴 등록[편집]
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,start_date => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;'
,comments => 'SQL GATEHRING FOR 5MIN EVERY DAY'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'AUTO_DROP'
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTART_ON_RECOVERY'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTART_ON_FAILURE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'STORE_OUTPUT'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/
1.2.1 통계정보 수집 프로시져[편집]
CREATE OR REPLACE PROCEDURE RTIS_DBA.sp_dbm_sql_gather
/*
0. SNAPSHOT ID 생성
1. TB_TUNE_SNAP -- SNAPSHOT 시작 시간
2. TB_TUNE_SQL_STATS -- SQL_STATS UPSERT 처리
3. TB_TUNE_SQL_TEXT -- SQL_TEXT
4. TB_TUNE_SQL_BIND_VAL -- SQL 바인드 변수 추가
5. TB_TUNE_SQL -- SQL(SQLAREA) 추가
6. TB_TUNE_SQL_PLAN -- SQL PLAN
7. TB_TUNE_SNAP -- SNAPSHOT 종료 시간
*/
IS
-- DECLARE
--변수,상수 선언
v_msg long;
v_snap_id number default 0;
v_snap_end_dt date;
BEGIN
/* 0.SNAPSHOT번호 생성 */
SELECT SNAP_ID+1
, to_date(SNAP_END_DT,'YYYYMMDD HH24MISS')
INTO v_snap_id,v_snap_end_dt
FROM (
SELECT /*+ INDEX_DESC(A PK_TUNE_SNAP) */ SNAP_ID SNAP_ID
, SNAP_END_DT
FROM RTIS_DBA.TB_TUNE_SNAP
UNION ALL
SELECT 0,'' FROM DUAL
ORDER BY SNAP_ID DESC
)
WHERE ROWNUM <= 1;
/* 1. SNAPSHOT ID 생성 및 SNAPSHOT 시작 시간 */
INSERT INTO RTIS_DBA.TB_TUNE_SNAP (SNAP_ID,SNAP_START_DT)
VALUES (v_snap_id,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))
;
/* 2. TB_TUNE_SQL_STATS UPSERT 처리 */
MERGE /*+ PARALLEL(T,1) APPEND */ INTO TB_TUNE_SQL_STATS T
USING (
SELECT A.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
, B.INST_ID,B.OSUSER,B.MACHINE
FROM GV$SQLSTATS A
INNER JOIN GV$SESSION B
ON B.SQL_ID = A.SQL_ID
AND B.INST_ID = A.INST_ID
AND B.CON_ID = A.CON_ID
AND B.OSUSER = 'jeus'
WHERE LAST_ACTIVE_TIME > v_snap_end_dt
) 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 , T.INST_ID = S.INST_ID ,T.OSUSER = S.OSUSER,T.MACHINE = S.MACHINE
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,INST_ID ,OSUSER,MACHINE)
VALUES (v_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,S.INST_ID,S.OSUSER,S.MACHINE)
;
-- COMMIT;
/* 3. SQL_TEXT INSERT => TB_TUNE_SQL_TEXT */
INSERT /*+ APPEND */ INTO TB_TUNE_SQL_TEXT
(SQL_ID,SQL_TEXT,SQL_FULLTEXT)
SELECT A.SQL_ID,A.SQL_TEXT,A.SQL_FULLTEXT
FROM GV$SQLSTATS A
INNER JOIN GV$SESSION B
ON B.SQL_ID = A.SQL_ID
AND B.INST_ID = A.INST_ID
AND B.CON_ID = A.CON_ID
AND B.OSUSER = 'jeus'
WHERE LAST_ACTIVE_TIME > v_snap_end_dt
AND NOT EXISTS (SELECT 1 FROM TB_TUNE_SQL_TEXT X
WHERE X.SQL_ID = A.SQL_ID)
;
/* 4.SQL 바인드 변수 추가 */
INSERT /*+ APPEND */ INTO TB_TUNE_SQL_BIND_VAL
( SNAP_ID,ADDRESS, HASH_VALUE, SQL_ID, CHILD_ADDRESS
, CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED
, LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA )
SELECT v_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
FROM GV$SQL_BIND_CAPTURE A
INNER JOIN GV$SQLAREA B
ON B.HASH_VALUE = A.HASH_VALUE
AND B.ADDRESS = A.ADDRESS
AND B.INST_ID = A.INST_ID
WHERE B.LAST_ACTIVE_TIME > v_snap_end_dt
AND EXISTS (SELECT 1 FROM GV$SESSION X
WHERE X.SQL_ID = A.SQL_ID
AND X.INST_ID = A.INST_ID
AND X.OSUSER = 'jeus'
)
;
/* 5.SQL 추가 */
INSERT /*+ APPEND */ INTO TB_TUNE_SQL
( SQL_ID, SID, HASH_VALUE,SNAP_ID, PLAN_HASH_VALUE
, SERIAL#, BIND_DATA, ADDRESS, MACHINE, OSUSER
, ELAPSED_TIME, EXECUTIONS, END_OF_FETCH_COUNT, LAST_ACTIVE_TIME
)
SELECT C.SQL_ID, B.SID, C.HASH_VALUE, v_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
FROM GV$SESSION B
, GV$SQLAREA C
-- , v$sql c
WHERE B.SQL_HASH_VALUE = C.HASH_VALUE
AND B.INST_ID = C.INST_ID
AND B.SQL_ID = C.SQL_ID
AND C.LAST_ACTIVE_TIME > v_snap_end_dt
;
/* 6.SQL PLAN */
MERGE /*+ PARALLEL(T,1) APPEND */ INTO TB_TUNE_SQL_PLAN T
USING (
SELECT 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
FROM GV$SQL_PLAN A
, GV$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 A.INST_ID = B.INST_ID
AND B.LAST_ACTIVE_TIME > v_snap_end_dt
) S
ON (T.SQL_ID = S.SQL_ID AND
T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE
)
-- 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,ADDRESS,HASH_VALUE,SQL_ID,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
)
VALUES (v_snap_id,S.ADDRESS,S.HASH_VALUE,S.SQL_ID,S.PLAN_HASH_VALUE
,S.CHILD_ADDRESS,S.CHILD_NUMBER,S.TIMESTAMP,S.OPERATION,S.OPTIONS
,S.OBJECT_NODE,S.OBJECT#,S.OBJECT_OWNER,S.OBJECT_NAME,S.OBJECT_ALIAS
,S.OBJECT_TYPE,S.OPTIMIZER,S.ID,S.PARENT_ID,S.DEPTH
,S.POSITION,S.SEARCH_COLUMNS,S.COST,S.CARDINALITY,S.BYTES
,S.OTHER_TAG,S.PARTITION_START,S.PARTITION_STOP,S.PARTITION_ID,S.OTHER
,S.DISTRIBUTION,S.CPU_COST,S.IO_COST,S.TEMP_SPACE,S.ACCESS_PREDICATES
,S.FILTER_PREDICATES,S.PROJECTION,S.TIME,S.QBLOCK_NAME )
;
/*
INSERT INTO TB_TUNE_SQL_PLAN
( SNAP_ID, ADDRESS, HASH_VALUE, SQL_ID, 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
)
SELECT v_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
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 B.LAST_ACTIVE_TIME > v_snap_end_dt
;
*/
/* 7. SNAPSHOT 종료 시간 */
UPDATE 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;
/
1.3 수집결과 플랜 확인[편집]
-- SQL PLAN VIEW
select a.sql_id, a.child_number child,
a.id
, a.parent_id pid
----------------------------------------------------------------
, lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
|| decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
|| decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name
|| ' ('|| a.object_type ||')')
----------------------------------------------------------------(
|| decode(a.cost||a.cardinality||a.bytes,null,null,' (')
|| decode(a.cost, null, null, ' Cost='||a.cost)
|| decode(a.cardinality, null, null, ' Card='||a.cardinality)
|| decode(a.bytes, null, null, ' Bytes='||a.bytes)
|| decode(a.cost||a.cardinality||a.bytes,null,null,' )')
----------------------------------------------------------------)
|| decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
---------------------------------------------------------------
, a.object_type "Type"
----------------------------------------------------------------
, a.cardinality "E-Rows"
, a.bytes "E-Bytes"
, a.cost
, a.time "E-Time"
----------------------------------------------------------------
, a.partition_start "Pstart"
, a.partition_stop "Pstop"
, a.partition_id "Pid"
----------------------------------------------------------------
, a.qblock_name "QB"
, a.access_predicates
, a.filter_predicates
----------------------------------------------------------------
--from v$sql_plan a
from TB_TUNE_SQL_PLAN A --v$sql_plan a
where 1 = 1
AND A.SNAP_ID = (SELECT MAX(SNAP_ID) FROM TB_TUNE_SQL_PLAN B WHERE B.SQL_ID = A.SQL_ID)
and a.sql_id = 'fwyjdtd7dudvr' --and plan_hash_value = ''
and a.child_number = 0
order by a.sql_id, a.id, a.child_number;