3. Undo Segment Summary
4. Undo Segment Stats
5. Latch Activity
Class Waits Total Wait Time (s) Avg Time (ms)
------------------ ----------- ------------------- --------------
data block 84,160,877 203,290 2
undo block 120,972 342 3
undo header 256,497 247 1
segment header 6,080 18 3
2nd level bmb 2,884 3 1
1st level bmb 47,533 3 0
extent map 1,494 2 1
3rd level bmb 257 1 3
-------------------------------------------------------------
| 항목 | 의미 | 관련 컬럼 |
|---|---|---|
| Class | 데이터 블록의 종류 | CLASS |
| Waits | 블록 경합이 발생한 건수 | WAIT_COUNT |
| Total Wait Time (s) | 블록 경합이 발생한 시간의 총합(단위:초) | TIME / 100 |
| Avg Time (ms) | 블록 경합 1회 발생 시 발생한 시간단위:1/1,000초) | 10 * (TIME/WAIT_COUNT) |
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TX-Transaction (row lock contention)
7,411,043 49,446 7,360,695 53,219 1,283,235 24,112.35
US-Undo Segment
8,354,307 8,354,307 0 4,218,599 2,120 .50
WF-AWR Flush
680 661 19 457 688 1,505.33
TX-Transaction (index contention)
165,928 164,107 0 159,883 400 2.50
SQ-Sequence Cache
136,633 136,633 0 32,313 331 10.24
TT-Tablespace
446,490 446,490 0 93,250 241 2.59
HW-Segment High Water Mark
924,231 924,219 7 65,092 125 1.91
.............................................................................
.............................................................................
.............................................................................
| 항목 | 의미 | 관련 컬럼 | ||
|---|---|---|---|---|
| Enqueue Type | 엔큐 타입과 엔큐 이름 | TYPE | '-' | NAME |
| Requests | 엔큐 락을 요청한 횟수 | TOTAL_REQ# | ||
| Succ Gets | 성공적으로 엔큐 락을 적용한 횟수 | SUCC_REQ# | ||
| Failed Gets | 엔큐 락 적용에 실패한 횟수 | FAILED_REQ# | ||
| Waits | 엔큐 락을 적용하기 위해 대기한 횟수 | TOTAL_WAIT# | ||
| Wt Time (s) | 엔큐 락을 적용하기 위해 대기한 시간(단위:초) | CUM_WAIT_TIME / 1,000 | ||
| Av Wt Time(ms) | 엔큐 락을 적용하기 위해 대기한 평균 시간(단위:1/1,000초) | CUM_WAIT_TIME / TOTAL_WAIT# |
SELECT SID,
OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
CHR(P1 / 256 / 256 / 256) || CHR(MOD(P1 / 256 / 256, 256)) TYPE,
DECODE(MOD(P1, 65536), 1, 'NONE',
2, 'RS',
3, 'RX',
4, 'S',
5, 'SRX',
6, 'X') REQUEST
FROM DBA_EXTENTS E,
V$SESSION_WAIT V
WHERE EVENT = ' enq: HW - contention'
AND FILE_ID = DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(P3)
AND DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(P3) BETWEEN BLOCK_ID
AND BLOCK_ID + BLOCKS - 1
;
DECLARE
v_time VARCHAR2(14);
v_num NUMBER := 0;
v_sqlcode NUMBER;
BEGIN
SELECT TO_CHAR(sysdate,'YYYYMMDDHH24MISS') INTO v_time FROM DUAL;
FOR i IN (
SELECT A.tree ,A.inst_id ,A.sid ,VS.serial# ,VS.module ,VS.machine ,GVP.spid
,VS.username ,VS.osuser ,A.hold ,A.request ,VS.status
,DECODE(rank,1,USR.name) OWNER
,DECODE(rank,1,OBJ.name) OBJECT_NAME
,VS.sql_id
,DECODE(VS.command, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER'
, 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX'
, 11,'ALTER INDEX', 12,'DROP TABLE', 13,'CREATE SEQUENCE', 14,'ALTER SEQUENCE'
, 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM'
, 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE'
, 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT'
, 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE'
, 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE'
, 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK'
, 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN'
, 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE'
, 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER'
, 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE'
, 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE',70,'ALTER RESOURCE COST'
, 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT'
, 76,'DROP SNAPSHOT', 79,'ALTER ROLE',85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW'
, 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE'
, 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY'
, 'OTHER COMMAND: '||command
) COMMAND
,SUBSTR(VS.module,1,15) ,SUBSTR(VS.machine,1,15) ,A.rank
FROM (
SELECT LPAD('-',1*(rank-1)) TREE ,sid ,id1 ,hold ,request ,typ ,inst_id ,rank ,cnt
FROM (
SELECT RANK() OVER (PARTITION BY VL.id1 ,VL.id2 ORDER BY VL.request ASC) RANK
,COUNT(*) OVER (PARTITION BY VL.id1 ) CNT
,VL.sid ,VL.type ,VL.id1 ,VL.type TYP ,inst_id
,DECODE(VL.lmode,1,'NONE',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') HOLD
,DECODE(VL.request,1,'NONE',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') REQUEST
FROM GV$LOCK VL
WHERE type = 'TM'
)
WHERE CNT > 1
) A
,GV$SESSION VS
,sys.OBJ$ OBJ
,sys.USER$ USR
,GV$PROCESS GVP
WHERE A.sid = VS.sid
AND A.inst_id = VS.inst_id
AND A.id1 = OBJ.obj#
AND USR.user# = OBJ.owner#
AND VS.inst_id = GVP.inst_id
AND VS.paddr = GVP.addr
ORDER BY A.id1 ,A.rank
)
LOOP
IF v_num=0 THEN
DBMS_OUTPUT.PUT_LINE('Instance Number: '||SYS_CONTEXT('userenv','instance'));
DBMS_OUTPUT.PUT_LINE('Time: '||SUBSTR(v_time,1,4)||'-'||SUBSTR(v_time,5,2)||'-'||SUBSTR(v_time,7,2)||' '
||SUBSTR(v_time,9,2)||':'||SUBSTR(v_time,11,2)||':'||SUBSTR(v_time,13,2));
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('TM Lock Status') ;
DBMS_OUTPUT.PUT_LINE('~~~~~~~~~~~~~~') ;
DBMS_OUTPUT.PUT_LINE('INST SID SERIAL# SPID USERNAME HOLD REQ STATUS OBJECT SQL_ID COMMAND MODULE MACHINE');
DBMS_OUTPUT.PUT_LINE('---- ------ ---------- ---------- ---------- ---- ---- -------- ------------------------- ------------- --------------- --------------- ---------------');
v_num := 6;
END IF;
DBMS_OUTPUT.PUT_LINE(
RPAD(TRIM(NVL(TO_CHAR(i.tree||i.inst_id,'99'), 'N/A')) ,4) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.sid, '999999'), 'N/A')), 6) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.serial#, '9999999999'), 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.spid, '9999999999'), 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.username, 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.hold, 'N/A')), 4) ||' '
||LPAD(TRIM(NVL(i.request, 'N/A')), 4) ||' '
||LPAD(TRIM(NVL(i.status, 'N/A')), 8) ||' '
||LPAD(TRIM(NVL(i.owner||'.'||i.object_name, 'N/A')), 25) ||' '
||LPAD(TRIM(NVL(i.sql_id, 'N/A')), 13) ||' '
||LPAD(TRIM(NVL(i.command, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.module, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.machine, 'N/A')), 15)
) ;
v_num := v_num+1;
END LOOP ;
IF v_num = 0 THEN
DBMS_OUTPUT.PUT_LINE('') ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE(' No data exists for this section of the report.') ;
END IF ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE('') ;
------- EXCEPTION --------
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Data Found!!');
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.PUT_LINE('SQL Error Code: '||v_sqlcode);
END;
/
TM Lock Status
~~~~~~~~~~~~~~
INST SID SERIAL# SPID USERNAME HOLD REQ STATUS OBJECT SQL_ID COMMAND MODULE MACHINE
---- ------ ---------- ---------- ---------- ---- ---- -------- ------------------------- ------------- --------------- --------------- ---------------
2 17534 8691 13938704 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A c4vyq4y9uapwx PL/SQL EXECUTE XXETF15110 GLERCSE1Q
2 19939 4029 6795342 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 17735 77 15368412 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q PL/SQL EXECUTE XXETF15110 GLERCSE1Q
2 17827 1320 14520474 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 17903 3953 13537482 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18159 446 8749254 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 18206 3649 15286510 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18247 44 13811762 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A N/A BACKGROUND XXETF15110 GLERCSE1Q
2 18346 64 12259534 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18403 174 8663118 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18448 3646 15913206 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 18652 906 12165254 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 18681 1001 15200452 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18690 76 8908858 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18730 1838 13045962 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18754 78 16027712 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 18977 55 14209196 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 19091 1299 7492188 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A 6vp4sww5nzb9q SELECT XXETF15110 GLERCSE1Q
2 19252 63 13869192 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A N/A BACKGROUND XXETF15110 GLERCSE1Q
2 19551 3324 13500560 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 19801 2123 5922878 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A faspa1g860nzw UPDATE XXETF15110 GLERCSE1Q
2 19834 73 15007890 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A N/A BACKGROUND XXETF15110 GLERCSE1Q
2 19890 2460 14643334 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A N/A BACKGROUND XXETF15110 GLERCSE1Q
2 17639 834 13385900 APPS RX N/A ACTIVE XXETF.XXETF_VAT_HEADERS_A N/A BACKGROUND XXETF15110 GLERCSE1Q
------------------------------
-- 시작 스냅샷 ID
var p_bsnap NUMBER
-- 종료 스냅샷 ID
var p_esnap NUMBER
EXEC :p_bsnap := 90505
EXEC :p_esnap := 90564
DECLARE
v_btime VARCHAR2(14);
v_etime VARCHAR2(14);
v_num NUMBER := 0;
v_sqlcode NUMBER;
v_sess NUMBER;
v_serial NUMBER;
BEGIN
SELECT TO_CHAR(END_INTERVAL_TIME,'YYYYMMDDHH24MISS') INTO v_btime
FROM DBA_HIST_SNAPSHOT
WHERE INSTANCE_NUMBER = SYS_CONTEXT('userenv','instance')
AND DBID = (SELECT DBID FROM V$DATABASE)
AND SNAP_ID = :p_bsnap;
SELECT TO_CHAR(END_INTERVAL_TIME,'YYYYMMDDHH24MISS') INTO v_etime
FROM DBA_HIST_SNAPSHOT
WHERE INSTANCE_NUMBER = SYS_CONTEXT('userenv','instance')
AND DBID = (SELECT DBID FROM V$DATABASE)
AND SNAP_ID = :p_esnap;
DBMS_OUTPUT.PUT_LINE('Instance Number: '||SYS_CONTEXT('userenv','instance'));
DBMS_OUTPUT.PUT_LINE('Snap ID: '||:p_bsnap||' ~ '||:p_esnap);
DBMS_OUTPUT.PUT_LINE('Time: '||SUBSTR(v_btime,1,4)||'-'||SUBSTR(v_btime,5,2)||'-'||SUBSTR(v_btime,7,2)||' '
||SUBSTR(v_btime,9,2)||':'||SUBSTR(v_btime,11,2)||':'||SUBSTR(v_btime,13,2)
||' ~ '
||SUBSTR(v_etime,1,4)||'-'||SUBSTR(v_etime,5,2)||'-'||SUBSTR(v_etime,7,2)||' '
||SUBSTR(v_etime,9,2)||':'||SUBSTR(v_etime,11,2)||':'||SUBSTR(v_etime,13,2)
);
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('TM Lock Status: WAITER') ;
DBMS_OUTPUT.PUT_LINE('~~~~~~~~~~~~~~~~~~~~~~') ;
FOR i IN (
SELECT A.inst ,A.sid ,A.serial#
,C.username ,A.type ,A.request
,B.owner OWNER
,B.object_name OBJECT_NAME
,A.sql_id
,DECODE(A.command, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER'
, 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX'
, 11,'ALTER INDEX', 12,'DROP TABLE', 13,'CREATE SEQUENCE', 14,'ALTER SEQUENCE'
, 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM'
, 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE'
, 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT'
, 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE'
, 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE'
, 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK'
, 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN'
, 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE'
, 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER'
, 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE'
, 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE',70,'ALTER RESOURCE COST'
, 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT'
, 76,'DROP SNAPSHOT', 79,'ALTER ROLE',85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW'
, 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE'
, 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY'
, 'OTHER COMMAND: '||command
) COMMAND
,SUBSTR(A.module,1,15) MODULE ,SUBSTR(A.program,1,15) PROGRAM
,A.BLOCKING_SESSION ,A.BLOCKING_SESSION_SERIAL# ,A.BLOCKING_SESSION_STATUS
FROM (
SELECT INSTANCE_NUMBER INST ,SESSION_ID SID ,SESSION_SERIAL# SERIAL#
,USER_ID ,SQL_ID
,SQL_OPCODE COMMAND
,CHR(P1/256/256/256)||CHR(MOD(P1/256/256,256)) TYPE
,DECODE(MOD(P1,65536),1,'NONE',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') REQUEST
,P2 ,PROGRAM ,MODULE
,BLOCKING_SESSION ,BLOCKING_SESSION_SERIAL# ,BLOCKING_SESSION_STATUS
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'enq: TM - contention'
AND SNAP_ID > :p_bsnap
AND SNAP_ID <= :p_esnap
GROUP BY INSTANCE_NUMBER ,SESSION_ID ,SESSION_SERIAL#
,USER_ID ,SQL_ID ,SQL_OPCODE
,CHR(P1/256/256/256)||CHR(MOD(P1/256/256,256))
,DECODE(MOD(P1,65536),1,'NONE',2,'RS',3,'RX',4,'S',5,'SRX',6,'X')
,P2 ,PROGRAM ,MODULE
,BLOCKING_SESSION ,BLOCKING_SESSION_SERIAL# ,BLOCKING_SESSION_STATUS
) A
,DBA_OBJECTS B
,DBA_USERS C
WHERE A.P2 = B.object_id (+)
AND A.user_id = C.user_id (+)
ORDER BY B.OBJECT_NAME ,A.BLOCKING_SESSION ,A.BLOCKING_SESSION_SERIAL# ,A.SID
)
LOOP
IF v_num=0 THEN
DBMS_OUTPUT.PUT_LINE('INST SID SERIAL# USERNAME REQUEST OBJECT SQL_ID COMMAND MODULE PROGRAM BLK_SID BLK_SERIAL#');
DBMS_OUTPUT.PUT_LINE('---- ------ ---------- ---------- ------- ------------------------- ------------- --------------- --------------- --------------- ------- ------------');
v_num := 6;
END IF;
DBMS_OUTPUT.PUT_LINE(
RPAD(TRIM(NVL(TO_CHAR(i.inst,'99'), 'N/A')) ,4) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.sid, '999999'), 'N/A')), 6) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.serial#, '9999999999'), 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.username, 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.type||'-'||i.request, 'N/A')), 7) ||' '
||LPAD(TRIM(NVL(i.owner||'.'||i.object_name, 'N/A')), 25) ||' '
||LPAD(TRIM(NVL(i.sql_id, 'N/A')), 13) ||' '
||LPAD(TRIM(NVL(i.command, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.module, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.program, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.BLOCKING_SESSION, '999999'), 'N/A')), 7) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.BLOCKING_SESSION_SERIAL#, '9999999999'), 'N/A')), 12)
) ;
v_num := v_num+1;
END LOOP ;
IF v_num = 0 THEN
DBMS_OUTPUT.PUT_LINE('') ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE(' No data exists for this section of the report.') ;
END IF ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE('') ;
v_num := 0;
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('~');
DBMS_OUTPUT.PUT_LINE('TM Lock Status: HOLDER') ;
DBMS_OUTPUT.PUT_LINE('~~~~~~~~~~~~~~~~~~~~~~') ;
FOR i IN (
SELECT D.INSTANCE_NUMBER inst ,D.SESSION_ID sid ,D.SESSION_SERIAL# serial#
,C.username
,D.sql_id
,DECODE(D.SQL_OPCODE, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER'
, 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX'
, 11,'ALTER INDEX', 12,'DROP TABLE', 13,'CREATE SEQUENCE', 14,'ALTER SEQUENCE'
, 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM'
, 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE'
, 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT'
, 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE'
, 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE'
, 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK'
, 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN'
, 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE'
, 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER'
, 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE'
, 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE',70,'ALTER RESOURCE COST'
, 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT'
, 76,'DROP SNAPSHOT', 79,'ALTER ROLE',85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW'
, 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE'
, 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY'
, 'OTHER COMMAND: '||SQL_OPCODE
) COMMAND
,SUBSTR(D.module,1,15) MODULE ,SUBSTR(D.program,1,15) PROGRAM
FROM (
SELECT snap_id, instance_number ,dbid
,BLOCKING_SESSION ,BLOCKING_SESSION_SERIAL#
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'enq: TM - contention'
AND SNAP_ID > :p_bsnap
AND SNAP_ID <= :p_esnap
GROUP BY snap_id ,instance_number ,dbid
,BLOCKING_SESSION ,BLOCKING_SESSION_SERIAL# ,BLOCKING_SESSION_STATUS
) A
,DBA_USERS C
,DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE A.dbid = D.dbid
AND A.INSTANCE_NUMBER = D.INSTANCE_NUMBER
AND A.snap_id = D.snap_id
AND A.BLOCKING_SESSION = D.SESSION_ID
AND A.BLOCKING_SESSION_SERIAL# = D.SESSION_SERIAL#
AND D.user_id = C.user_id (+)
ORDER BY D.SESSION_ID
)
LOOP
IF v_num=0 THEN
DBMS_OUTPUT.PUT_LINE('INST SID SERIAL# USERNAME SQL_ID COMMAND MODULE PROGRAM');
DBMS_OUTPUT.PUT_LINE('---- ------ ---------- ---------- ------------- --------------- --------------- ---------------');
v_num := 6;
END IF;
DBMS_OUTPUT.PUT_LINE(
RPAD(TRIM(NVL(TO_CHAR(i.inst,'99'), 'N/A')) ,4) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.sid, '999999'), 'N/A')), 6) ||' '
||LPAD(TRIM(NVL(TO_CHAR(i.serial#, '9999999999'), 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.username, 'N/A')), 10) ||' '
||LPAD(TRIM(NVL(i.sql_id, 'N/A')), 13) ||' '
||LPAD(TRIM(NVL(i.command, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.module, 'N/A')), 15) ||' '
||LPAD(TRIM(NVL(i.program, 'N/A')), 15)
) ;
v_num := v_num+1;
END LOOP ;
IF v_num = 0 THEN
DBMS_OUTPUT.PUT_LINE('') ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE(' No data exists for this section of the report.') ;
END IF ;
DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------',41)) ;
DBMS_OUTPUT.PUT_LINE('') ;
------- EXCEPTION --------
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Data Found!!');
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.PUT_LINE('SQL Error Code: '||v_sqlcode);
END;
/
Instance Number: 1
Snap ID: 90505 ~ 90564
Time: 2012-11-23 07:30:23 ~ 2012-11-24 13:00:00
~
TM Lock Status: WAITER
~~~~~~~~~~~~~~~~~~~~~~
INST SID SERIAL# USERNAME REQUEST OBJECT SQL_ID COMMAND MODULE PROGRAM BLK_SID BLK_SERIAL#
---- ------ ---------- ---------- ------- ------------------------- ------------- --------------- --------------- --------------- ------- ------------
4 15933 25696 APPS TM-SRX CLL.CLL_F189_INVOICE_LINE 0tsr3ztk4y1u2 DELETE XXIMPM_AUTO_NOT COMMON2_MGR 14943 46172
4 12620 36786 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 13204 57626 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 13204 57621 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 14436 63550 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 14583 18087 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 14583 18060 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 14943 46172 APPS TM-RS CLL.CLL_F189_INVOICE_LINE N/A BACKGROUND XXINVM8023 frmweb@LGEGLTAS 15933 25696
4 14992 22836 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 15250 23071 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 16304 23500 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 17820 34871 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 17841 62292 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 19006 26526 APPS TM-RS CLL.CLL_F189_INVOICE_LINE 75t1gf3du0ck6 INSERT CLLRIOIN CLLRIOIN_MGR 15933 25696
4 15933 25696 APPS TM-SRX CLL.CLL_F189_INVOICE_LINE 0tsr3ztk4y1u2 DELETE XXIMPM_AUTO_NOT COMMON2_MGR N/A N/A
4 16117 20474 APPS TM-S CLL.CLL_F189_INVOICE_LINE N/A BACKGROUND CLLRIFOE frmweb@GLTAS N/A N/A
2 17423 35703 APPS TM-S CLL.CLL_F189_INVOICE_LINE N/A BACKGROUND CLLRIFOE frmweb@GLTAS N/A N/A
.................................
.................................
.................................
-- 1. AWR에서는 데이터 없음(책 참조)
No data exists for this section of the report.
-------------------------------------------------------------
;
-- 2. 추출쿼리
SELECT UNDOTSN "Undo TS#",
SUM(UNDOBLKS) / 1000 "Num Undo Blocks (K)",
SUM(TXNCOUNT) "Number of Transactions",
MAX(MAXQUERYLEN) "Max Qry Len (s)",
MAX(MAXCONCURRENCY) "Max Tx Concurcy",
MIN(TUNED_UNDORETENTION) / 60 "Min TR (mins)",
MAX(TUNED_UNDORETENTION) / 60 "Max TR (mins)",
SUM(SSOLDERRCNT) || '/' || SUM(NOSPACEERRCNT) "STO/OOS",
SUM(UNXPSTEALCNT) || '/' || SUM(UNXPBLKRELCNT) || '/' ||
SUM(UNXPBLKREUCNT) || '/' || SUM(EXPSTEALCNT) || '/' ||
SUM(EXPBLKRELCNT) || '/' || SUM(EXPBLKREUCNT) "uS/uR/uU/eS/eR/eU"
FROM DBA_HIST_UNDOSTAT
GROUP BY UNDOTSN
;
| 항목 | 의미 | 관련 컬럼 |
|---|---|---|
| Undo TS# | 언두테이블 스페이스 ID | UNDOTSN |
| Num Undo Blocks (K) | 사용된 총 언두 블록 수 / 1,000 | SUM(UNDOBLKS) / 1000 |
| Number of Transactions | 수행된 총 트랜잭션 수 | SUM(TXNCOUNT) |
| Max Qry Len (s) | 최대 SQL 수행 시간(단위:초) | MAX(MAXQUERYLEN) |
| Max Tx Concurcy | 최대 동시 수행 트랜잭션 수 | MAX(MAXCONCURRENCY) |
| Min/Max TR (mins) | 자동 언드 유지 기능으로 오라클이 설정한 최소 및 최대 언두 유지 시간(단위:분) | MIN(TUNED_UNDORETENTION) / 60, MAX(TUNED_UNDORETENTION) / 60 |
| STO/OOS | . STO : ORA-01555 snapshot too old 에러 발생 횟수 . OOS : 언두 테이블스페이스 공간 부족 에러 발생 횟수 | SUM(SSOLDERRCNT) '/' SUM(NOSPACEERRCNT) |
| uS/uR/uU/eS/eR/eU | . uS : 익스텐트 훔치기(EXTENT Stealing)로 획득을 시도한 언두 블록 수 . uR : 익스텐트 훔치기(EXTENT Stealing)로 확보한 언두 블록 수 . uU : 다른 언두 세그먼트의 만료된 익스텐트 획득을 시도하여 확보할 언두 블록 수 . eS : 다른 언두 세그먼트의 만료된 익스텐트 획득을 시도하여 확보할 언두 블록 수 . eR : 다른 언두 세그먼트의 만료된 익스텐트 획득으로 확보한 언두 블록 수 . eU : 같은 언두 세그먼트의 만료된 익스텐트 획득으로 확보한 언두 블록 수 | SUM(UNXPSTEALCNT) '/' SUM(UNXPBLKRELCNT) '/' SUM(UNXPBLKREUCNT) '/' SUM(EXPSTEALCNT) '/' SUM(EXPBLKRELCNT) '/' SUM(EXPBLKREUCNT) |
-- 1. AWR에서는 데이터 없음(책 참조)
No data exists for this section of the report.
-------------------------------------------------------------
;
| 항목 | 의미 | 관련 컬럼 |
|---|---|---|
| End Time | 통계 수집 종료 시간 | TO_CHAR(END_TIME, 'DD-Mon HH24:MI') |
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 259,311 0.0 N/A 0 0 N/A
AW SGA latch 6 0.0 N/A 0 0 N/A
AWR Alerted Metric Eleme 6,710,819 0.0 N/A 0 0 N/A
Consistent RBA 4,154,238 0.1 0.0 0 0 N/A
FOB s.o list latch 67,622,077 0.8 0.0 0 0 N/A
JOX SGA heap latch 42,488 0.0 N/A 0 0 N/A
JS Sh mem access 2 0.0 N/A 0 0 N/A
JS broadcast add buf lat 18,709 0.0 N/A 0 0 N/A
.....................
.....................
.....................
| 항목 | 의미 | 관련 컬럼 |
|---|---|---|
| Latch Name | 래치 이름 | LATCH_NAME |
| Get Requests | 대기 가능 모드로 래치가 요청된 횟수 | GETS |
| Pct Get Miss | 대기 가능 모드로 래치 요청 시 슬립된 비율(%) | 100 * (MISSES / GETS) |
| Avg Slps / Miss | 대기 가능 모드로 래치 요청 시 대기 횟수당 평균 슬립 횟수 | SLEETPS / MISSES |
| Wait Time (s) | 대기 가능 모드로 래치 요청 시 대기한 시간(단위:초) | WAIT_TIME * 1,000,000 |
| NoWait Requests | 대기 불가능 모드로 래치가 요청된 횟수 | IMMEDIATE_GETS |
| Pct NoWait Miss | 대기 불가능 모드로 래치 요청 시 요청 실패 비율(%) | 100 * (IMMEDIATE_MISSES / IMMEDIATE_GETS) |