user::769cc523-b0ba-4d74-8b17-91617b4ad83a

Script

Script
SQL Real Time SQL 모니터링용

--Real Time SQL 모니터링용 SELECT A.INST_ID ,A.SID ,B.SERIAL# ,B.MACHINE ,B.TERMINAL ,B.STATUS SESSION_STATUS ,B.USERNAME ,A.SQL_ID ,A.STATUS SQL_STATUS ,C.EXECUTIONS TOT_EXECUTIONS ,ROUND(A.ELAPSED_TIME/1000,2) ELAPSED_MS ,ROUND(DECODE(C.ELAPSED_TIME,NULL,'',DECODE(C.EXECUTIONS,0, C.ELAPSED_TIME,ROUND(C.ELAPSED_TIME/c.executions)))/1000,2) AVG_ELAPSED_MS ,ROUND(A.CPU_TIME/1000,2) CPU_MS ,ROUND(DECODE(C.CPU_TIME,NULL,'',DECODE(C.EXECUTIONS,0, C.CPU_TIME,ROUND(C.CPU_TIME/c.executions)))/1000,2) AVG_CPU_MS ,A.FETCHES ,ROUND(DECODE(C.FETCHES,NULL,'',DECODE(C.EXECUTIONS,0, C.FETCHES,ROUND(C.FETCHES/c.executions))),2) AVG_FETCHES ,A.BUFFER_GETS ,ROUND(DECODE(C.BUFFER_GETS,NULL,'',DECODE(C.EXECUTIONS,0, C.BUFFER_GETS,ROUND(C.BUFFER_GETS/c.executions))),2) AVG_BUFFER_GETS ,A.DISK_READS ,ROUND(DECODE(C.DISK_READS,NULL,'',DECODE(C.EXECUTIONS,0, C.DISK_READS,ROUND(C.DISK_READS/c.executions))),2) AVG_DISK_READS ,A.DIRECT_WRITES ,ROUND(DECODE(C.DIRECT_WRITES,NULL,'',DECODE(C.EXECUTIONS,0, C.DIRECT_WRITES,ROUND(C.DIRECT_WRITES/c.executions))),2) AVG_DIRECT_WRITES ,A.IO_INTERCONNECT_BYTES ,ROUND(DECODE(C.IO_INTERCONNECT_BYTES,NULL,'',DECODE(C.EXECUTIONS,0, C.IO_INTERCONNECT_BYTES,ROUND(C.IO_INTERCONNECT_BYTES/c.executions))),2) AVG_IO_INTERCONNECT_BYTES ,A.PHYSICAL_READ_BYTES ,ROUND(DECODE(C.PHYSICAL_READ_BYTES,NULL,'',DECODE(C.EXECUTIONS,0, C.PHYSICAL_READ_BYTES,ROUND(C.PHYSICAL_READ_BYTES/c.executions))),2) AVG_PHYSICAL_READ_BYTES ,A.PHYSICAL_WRITE_BYTES ,ROUND(DECODE(C.PHYSICAL_WRITE_BYTES,NULL,'',DECODE(C.EXECUTIONS,0, C.PHYSICAL_WRITE_BYTES,ROUND(C.PHYSICAL_WRITE_BYTES/c.executions))),2) AVG_PHYSICAL_WRITE_BYTES ,A.CONCURRENCY_WAIT_TIME ,ROUND(DECODE(C.CONCURRENCY_WAIT_TIME,NULL,'',DECODE(C.EXECUTIONS,0, C.CONCURRENCY_WAIT_TIME,ROUND(C.CONCURRENCY_WAIT_TIME/c.executions)))/1000,2) AVG_CONCURRENCY_WAIT_TIME ,A.CLUSTER_WAIT_TIME ,ROUND(DECODE(C.CLUSTER_WAIT_TIME,NULL,'',DECODE(C.EXECUTIONS,0, C.CLUSTER_WAIT_TIME,ROUND(C.CLUSTER_WAIT_TIME/c.executions)))/1000,2) AVG_CLUSTER_WAIT_TIME ,A.PLSQL_EXEC_TIME ,ROUND(DECODE(C.PLSQL_EXEC_TIME,NULL,'',DECODE(C.EXECUTIONS,0, C.PLSQL_EXEC_TIME,ROUND(C.PLSQL_EXEC_TIME/c.executions)))/1000,2) AVG_PLSQL_EXEC_TIME ,ROUND(DECODE(C.ROWS_PROCESSED,NULL,'',DECODE(C.EXECUTIONS,0, C.ROWS_PROCESSED,ROUND(C.ROWS_PROCESSED/c.executions))),2) AVG_ROWS_PROCESSED ,A.SQL_PLAN_HASH_VALUE ,A.EXACT_MATCHING_SIGNATURE ,B.SQL_CHILD_NUMBER ,C.FIRST_LOAD_TIME ,C.LAST_ACTIVE_TIME ,B.SQL_EXEC_START ,B.PREV_SQL_ID ,B.PREV_EXEC_START ,B.PLSQL_ENTRY_OBJECT_ID ,B.BLOCKING_SESSION ,B.EVENT ,C.SQL_PROFILE ,A.BINDS_XML ,A.SQL_TEXT ,C.SQL_FULLTEXT FROM GV$SQL_MONITOR A, GV$SESSION B, GV$SQL C WHERE 1 = 1 AND A.INST_ID = B.INST_ID AND A.SID = B.SID AND A.SESSION_SERIAL# = B.SERIAL# AND A.INST_ID = C.INST_ID(+) AND A.SQL_ID = C.SQL_ID(+) AND A.SQL_CHILD_ADDRESS = C.CHILD_ADDRESS(+) AND A.SQL_PLAN_HASH_VALUE = C.PLAN_HASH_VALUE(+) AND A.STATUS = 'EXECUTING';

2020-07-09 14:29:08
SQL 특정 모듈 혹은 DB 유저의 세션 정보 수집

--1.관련 테이블 생성 CREATE TABLE DB_MONITORING.DBAT_SESS_LOGGING AS SELECT INST_ID ,SAMPLE_TIME ,SESSION_ID ,SESSION_SERIAL# ,USER_ID ,SQL_ID ,TOP_LEVEL_SQL_ID ,FORCE_MATCHING_SIGNATURE ,SQL_OPNAME ,SQL_PLAN_HASH_VALUE ,SQL_PLAN_LINE_ID ,SQL_PLAN_OPERATION ,SQL_PLAN_OPTIONS ,SQL_EXEC_ID ,SQL_EXEC_START ,EVENT ,SESSION_STATE ,MACHINE ,PROGRAM ,PGA_ALLOCATED ,TEMP_SPACE_ALLOCATED FROM GV$ACTIVE_SESSION_HISTORY WHERE 1 = 0; --AND SAMPLE_TIME > SYSDATE - 1 / 60 / 24 --AND MODULE LIKE 'BIBusTKServerM@dwapp%' --AND USER_ID = (SELECT USER_ID -- FROM DBA_USERS -- WHERE USERNAME = 'OLAPIF') --ORDER BY SAMPLE_TIME DESC, INST_ID; CREATE INDEX DB_MONITORING.DBAT_SESS_LOGGING_IDX1 ON DB_MONITORING.DBAT_SESS_LOGGING (SAMPLE_TIME, SQL_ID, SQL_PLAN_HASH_VALUE); --DROP TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLSTAT; CREATE TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLSTAT AS SELECT INST_ID ,SQL_ID ,SHARABLE_MEM ,PERSISTENT_MEM ,RUNTIME_MEM ,SORTS ,FETCHES ,EXECUTIONS ,PX_SERVERS_EXECUTIONS ,END_OF_FETCH_COUNT ,USERS_EXECUTING ,FIRST_LOAD_TIME ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME ,CLUSTER_WAIT_TIME ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,ROWS_PROCESSED ,OPTIMIZER_MODE ,OPTIMIZER_COST ,PARSING_USER_ID ,PLAN_HASH_VALUE ,CHILD_NUMBER ,CPU_TIME ,ELAPSED_TIME ,SQL_PROFILE ,EXACT_MATCHING_SIGNATURE ,FORCE_MATCHING_SIGNATURE ,LAST_ACTIVE_TIME ,IO_INTERCONNECT_BYTES ,PHYSICAL_READ_REQUESTS ,PHYSICAL_READ_BYTES ,PHYSICAL_WRITE_REQUESTS ,PHYSICAL_WRITE_BYTES ,OPTIMIZED_PHY_READ_REQUESTS FROM GV$SQL WHERE 1 = 0; CREATE INDEX DB_MONITORING.DBAT_SESS_LOGGING_SQLSTAT_IDX1 ON DB_MONITORING.DBAT_SESS_LOGGING_SQLSTAT (SQL_ID); --DROP TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLPLAN; CREATE TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLPLAN AS SELECT INST_ID ,SQL_ID ,PLAN_HASH_VALUE ,CHILD_NUMBER ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT# ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,OBJECT_TYPE ,OPTIMIZER ,ID ,PARENT_ID ,DEPTH ,POSITION ,SEARCH_COLUMNS ,COST ,CARDINALITY ,BYTES ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME ,REMARKS FROM GV$SQL_PLAN WHERE 1 = 0; CREATE INDEX DB_MONITORING.DBAT_SESS_LOGGING_SQLPLAN_IDX1 ON DB_MONITORING.DBAT_SESS_LOGGING_SQLPLAN (SQL_ID, PLAN_HASH_VALUE); CREATE TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLTEXT ( SQL_ID VARCHAR2 (13) ,SQL_FULLTEXT CLOB ); CREATE UNIQUE INDEX DB_MONITORING.DBAT_SESS_LOGGING_SQLTEXT_PK ON DB_MONITORING.DBAT_SESS_LOGGING_SQLTEXT (SQL_ID); ALTER TABLE DB_MONITORING.DBAT_SESS_LOGGING_SQLTEXT ADD CONSTRAINT DBAT_SESS_LOGGING_SQLTEXT_PK PRIMARY KEY(SQL_ID) USING INDEX; CREATE TABLE DB_MONITORING.SESS_LOGGING_ACTION_LOG ( CUR_DATE timestamp , OPER_TYPE VARCHAR2(30) , START_END VARCHAR2(1) , SUCC_YN VARCHAR2(1) , OPER_COMMENTS VARCHAR2(400) ); --2.권한부여 grant select on sys.gv_$SQL_plan to db_monitoring; grant select on sys.gv_$sql to db_monitoring; grant select on dba_users to db_monitoring; grant select on gv_$active_session_history to db_monitoring; --'BIBusTKServerM@dwapp%' --3.프로시저 생성 CREATE OR REPLACE PROCEDURE DB_MONITORING.PROC_GATHER_SESS_LOGGING(P_MODULE IN VARCHAR2, P_USERNAME IN VARCHAR2) IS V_SQLFULLTEXT CLOB; P_SQLFULLTEXT CLOB; V_AMOUNT INTEGER; V_CNT NUMBER; -- v_MODULE VARCHAR2(200); -- V_USERNAME VARCHAR2(30); -- Sub Program 정의 PROCEDURE ACTION_LOGGING (P_OPER_TYPE IN VARCHAR2 --OPER_TYPE:INTERVAL_LOGGING , P_START_END IN VARCHAR2 --S:START, E:END , P_SUCC_YN IN VARCHAR2 --Y:SUCCESS, N:FAIL , P_COMMENT IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO SESS_LOGGING_ACTION_LOG (CUR_DATE, OPER_TYPE, START_END, SUCC_YN, OPER_COMMENTS) VALUES (SYSTIMESTAMP, P_OPER_TYPE, P_START_END, P_SUCC_YN, P_COMMENT); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END ACTION_LOGGING; BEGIN ACTION_LOGGING ('INTERVAL_LOGGING', 'S', '', 'Test'); INSERT INTO DB_MONITORING.DBAT_SESS_LOGGING SELECT DISTINCT INST_ID ,SAMPLE_TIME ,SESSION_ID ,SESSION_SERIAL# ,USER_ID ,SQL_ID ,TOP_LEVEL_SQL_ID ,FORCE_MATCHING_SIGNATURE ,SQL_OPNAME ,SQL_PLAN_HASH_VALUE ,SQL_PLAN_LINE_ID ,SQL_PLAN_OPERATION ,SQL_PLAN_OPTIONS ,SQL_EXEC_ID ,SQL_EXEC_START ,EVENT ,SESSION_STATE ,MACHINE ,PROGRAM ,PGA_ALLOCATED ,TEMP_SPACE_ALLOCATED FROM GV$ACTIVE_SESSION_HISTORY WHERE 1 = 1 AND SAMPLE_TIME > SYSDATE - 2 / 60 / 24 AND MODULE LIKE P_MODULE||'%' AND USER_ID = (SELECT USER_ID FROM DBA_USERS WHERE USERNAME = P_USERNAME) MINUS SELECT * FROM DB_MONITORING.DBAT_SESS_LOGGING WHERE 1 = 1 AND SAMPLE_TIME > SYSDATE - 3 / 60 / 24; COMMIT; FOR C1_REC IN (SELECT DISTINCT A.SQL_ID, A.SQL_PLAN_HASH_VALUE FROM (SELECT DISTINCT SQL_ID, SQL_PLAN_HASH_VALUE FROM DBAT_SESS_LOGGING WHERE SAMPLE_TIME > SYSDATE - 2 / 60 / 24) A ,DBAT_SESS_LOGGING_SQLSTAT B WHERE A.SQL_ID = B.SQL_ID(+) AND A.SQL_PLAN_HASH_VALUE = B.PLAN_HASH_VALUE(+) AND B.SQL_ID IS NULL) LOOP INSERT INTO DBAT_SESS_LOGGING_SQLSTAT SELECT INST_ID ,SQL_ID ,SHARABLE_MEM ,PERSISTENT_MEM ,RUNTIME_MEM ,SORTS ,FETCHES ,EXECUTIONS ,PX_SERVERS_EXECUTIONS ,END_OF_FETCH_COUNT ,USERS_EXECUTING ,FIRST_LOAD_TIME ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME ,CLUSTER_WAIT_TIME ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,ROWS_PROCESSED ,OPTIMIZER_MODE ,OPTIMIZER_COST ,PARSING_USER_ID ,PLAN_HASH_VALUE ,CHILD_NUMBER ,CPU_TIME ,ELAPSED_TIME ,SQL_PROFILE ,EXACT_MATCHING_SIGNATURE ,FORCE_MATCHING_SIGNATURE ,LAST_ACTIVE_TIME ,IO_INTERCONNECT_BYTES ,PHYSICAL_READ_REQUESTS ,PHYSICAL_READ_BYTES ,PHYSICAL_WRITE_REQUESTS ,PHYSICAL_WRITE_BYTES ,OPTIMIZED_PHY_READ_REQUESTS FROM GV$SQL WHERE SQL_ID = C1_REC.SQL_ID AND PLAN_HASH_VALUE = C1_REC.SQL_PLAN_HASH_VALUE AND EXECUTIONS > 0; COMMIT; INSERT INTO DBAT_SESS_LOGGING_SQLPLAN SELECT INST_ID ,SQL_ID ,PLAN_HASH_VALUE ,CHILD_NUMBER ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT# ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,OBJECT_TYPE ,OPTIMIZER ,ID ,PARENT_ID ,DEPTH ,POSITION ,SEARCH_COLUMNS ,COST ,CARDINALITY ,BYTES ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME ,REMARKS FROM GV$SQL_PLAN WHERE SQL_ID = C1_REC.SQL_ID AND PLAN_HASH_VALUE = C1_REC.SQL_PLAN_HASH_VALUE ORDER BY INST_ID, SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, ID; COMMIT; SELECT COUNT (*) INTO V_CNT FROM DBAT_SESS_LOGGING_SQLTEXT WHERE SQL_ID = C1_REC.SQL_ID; IF V_CNT = 0 THEN SELECT SQL_FULLTEXT INTO P_SQLFULLTEXT FROM GV$SQL WHERE SQL_ID = C1_REC.SQL_ID AND ROWNUM = 1; INSERT INTO DBAT_SESS_LOGGING_SQLTEXT VALUES (C1_REC.SQL_ID, EMPTY_CLOB ()) RETURNING SQL_FULLTEXT INTO V_SQLFULLTEXT; V_AMOUNT := DBMS_LOB.GETLENGTH (P_SQLFULLTEXT); DBMS_LOB.COPY (V_SQLFULLTEXT, P_SQLFULLTEXT, V_AMOUNT, 1, 1); COMMIT; END IF; END LOOP; ACTION_LOGGING ('INTERVAL_LOGGING', 'E', '', 'Test'); END; / EXEC PROC_GATHER_SESS_LOGGING('BIBusTKServerM@dwapp','OLAPIF') --4.Job Scheduler 등록 BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE (SCHEDULE_NAME => 'SCH_RUN_MINITELY' ,START_DATE => SYSTIMESTAMP ,REPEAT_INTERVAL => 'FREQ=MINUTELY; INTERVAL=1' ,COMMENTS => 'For Minutely Run Schedule'); END; / BEGIN DBMS_SCHEDULER.CREATE_PROGRAM (PROGRAM_NAME => 'PROG_GATHER_SESS_INFO' ,PROGRAM_TYPE => 'STORED_PROCEDURE' ,PROGRAM_ACTION => 'PROC_GATHER_SESS_LOGGING' ,NUMBER_OF_ARGUMENTS => 2 ,ENABLED => FALSE ,COMMENTS => 'Gather Session Info For Performance'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (PROGRAM_NAME => 'PROG_GATHER_SESS_INFO' ,ARGUMENT_POSITION => 1 ,ARGUMENT_NAME => 'V_MODULE' ,ARGUMENT_TYPE => 'VARCHAR2'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (PROGRAM_NAME => 'PROG_GATHER_SESS_INFO' ,ARGUMENT_POSITION => 2 ,ARGUMENT_NAME => 'V_USERNAME' ,ARGUMENT_TYPE => 'VARCHAR2'); DBMS_SCHEDULER.ENABLE('PROG_GATHER_SESS_INFO'); END; / BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'JOB_MINUTELY_GATHER_SESS_INFO' ,PROGRAM_NAME => 'PROG_GATHER_SESS_INFO' ,SCHEDULE_NAME => 'SCH_RUN_MINITELY' ,ENABLED => FALSE ,COMMENTS => 'Job For Gather Session Info For performance'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (JOB_NAME => 'JOB_MINUTELY_GATHER_SESS_INFO' ,ARGUMENT_NAME => 'V_MODULE' ,ARGUMENT_VALUE => 'BIBusTKServerM@dwapp'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (JOB_NAME => 'JOB_MINUTELY_GATHER_SESS_INFO', ARGUMENT_NAME => 'V_USERNAME', ARGUMENT_VALUE => 'OLAPIF'); DBMS_SCHEDULER.ENABLE ('JOB_MINUTELY_GATHER_SESS_INFO'); END; / --5.모니터링 SELECT * FROM DBA_SCHEDULER_JOBS WHERE OWNER = 'DB_MONITORING'; SELECT * FROM DBA_SCHEDULER_SCHEDULES WHERE OWNER = 'DB_MONITORING'; SELECT * FROM DBA_SCHEDULER_PROGRAMS WHERE OWNER = 'DB_MONITORING'; SELECT * FROM DBA_SCHEDULER_JOB_LOG WHERE 1 = 1 AND JOB_NAME = 'JOB_MINUTELY_GATHER_SESS_INFO' AND OWNER = 'DB_MONITORING' ORDER BY LOG_DATE DESC; SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE 1 = 1 AND JOB_NAME = 'JOB_MINUTELY_GATHER_SESS_INFO' AND OWNER = 'DB_MONITORING' ORDER BY LOG_DATE DESC; SELECT * FROM SESS_LOGGING_ACTION_LOG ORDER BY CUR_DATE DESC; SELECT * FROM DBAT_SESS_LOGGING ORDER BY SAMPLE_TIME DESC; SELECT * FROM DBAT_SESS_LOGGING_SQLSTAT; SELECT * FROM DBAT_SESS_LOGGING_SQLPLAN ORDER BY SQL_ID, CHILD_NUMBER, ID; SELECT * FROM DBAT_SESS_LOGGING_SQLTEXT;

2020-07-06 11:12:30

SQL Tuning Case

SQL Tuning Case

Article 번역

Article 번역

Book Study

Book Study

TroubleShooting Case

TroubleShooting Case

Development

Development

Book Scan

Book Scan

Entertainment

Entertainment

음악

음악

영상

영상