Oracle Session wait info ..
session_waits.sql
col username format a20
col event format a30
col wait_class format a15
col kill for a15
col STATE for a12
SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.con_id ,
NVL(s.username, '(oracle)') AS username,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state,
s.sql_id ,
PREV_SQL_ID,
s.p1,
s.p2,
s.p3
-- FINAL_BLOCKING_SESSION_STATUS
FROM gv$session_wait sw, gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
-- and event='enq: TX - row lock contention'
and s.state='WAITING'
--and USERNAME!='SYS'
and sql_id is not null
--and s.sql_id='15hgdm2v0vaj0'
ORDER BY sw.seconds_in_wait DESC;
col username format a12
col sid format 9999
col state format a25
col event format a50
col wait_time format 99999999
col command format a8
col kill format a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username, se.event, se.state,
-- sql_id,
-- se.wait_time
se.seconds_in_wait
,decode(s.command,0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
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 OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
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',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
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',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
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',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
305,'ALTER PUBLIC DATABASE LINK') command,
s.blocking_session,
s.blocking_session_status,
s.status,
'select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' for_plan
from gv$session s, gv$session_wait se
where 1=1
and s.sid=se.sid
and s.inst_id=se.inst_id
-- and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
and SQL_ID is not null
order by se.wait_time;
KILL USERNAME EVENT STATE SECONDS_IN_WAIT COMMAND BLOCKING_SESSION BLOCKING_SE STATUS FOR_PLAN
--------------- ------------ -------------------------------------------------- ------------------------- --------------- -------- ---------------- ----------- -------- --------------------------------------------------------------------------------------------------------------------
'985,59968,@1' SYS PX Deq: Execution Msg WAITED SHORT TIME 0 SELECT NOT IN WAIT ACTIVE select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
'1714,14669,@2' SYS PX Deq: Execution Msg WAITED SHORT TIME 0 SELECT NOT IN WAIT ACTIVE select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
'859,23018,@1' SYS PX Deq: Execute Reply WAITING 0 SELECT UNKNOWN ACTIVE select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
| select RANK, | |
| WAIT_EVENT, | |
| lpad(TO_CHAR(PCTTOT, '990D99'), 6) || '% waits with avg.du =' || | |
| TO_CHAR(AVERAGE_WAIT_MS, '9999990D99') || ' ms' as EVENT_VALUES | |
| from (select DENSE_RANK() OVER(order by sum(time_waited) desc) as RANK, | |
| event as WAIT_EVENT, | |
| round(RATIO_TO_REPORT(sum(time_waited)) OVER() * 100, 2) AS PCTTOT, | |
| round(avg(average_wait) * 10, 2) as AVERAGE_WAIT_MS | |
| from (select se.SID, | |
| se.INST_ID, | |
| se.EVENT, | |
| se.TIME_WAITED, | |
| se.AVERAGE_WAIT | |
| from gv$session_event se | |
| where se.WAIT_CLASS not in ('Idle') | |
| union | |
| select ss.SID, | |
| ss.INST_ID, | |
| sn.NAME as EVENT, | |
| ss.VALUE as TIME_WAITED, | |
| 0 as AVERAGE_WAIT | |
| from gv$sesstat ss, v$statname sn | |
| where ss."STATISTIC#" = sn."STATISTIC#" | |
| and sn.NAME in ('CPU used when call started')) | |
| where (sid, inst_id) in | |
| (select sid, inst_id | |
| from gv$session | |
| where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND')) | |
| group by event | |
| order by PCTTOT desc) we | |
| where RANK <= 10 | |
| / RANK WAIT_EVENT EVENT_VALUES ---------- ---------------------------------------------------------------- ----------------------------------------------------------- 1 CPU used when call started 29.9% waits with avg.du = 0.00 ms 2 db file sequential read 21.8% waits with avg.du = 0.58 ms 3 gc buffer busy acquire 21.4% waits with avg.du = 0.80 ms 4 read by other session 9.4% waits with avg.du = 0.33 ms 5 SQL*Net break/reset to client 8.6% waits with avg.du = 0.05 ms 6 gc cr disk read 8.0% waits with avg.du = 0.23 ms 7 acknowledge over PGA limit 0.3% waits with avg.du = 8.35 ms 8 SQL*Net message to client 0.1% waits with avg.du = 0.00 ms 9 events in waitclass Other 0.0% waits with avg.du = 0.00 ms 10 gc cr block 2-way 0.0% waits with avg.du = 0.22 ms =========== define v_event_filter='library cache lock' ----<<<< set echo off heading on feedback on set linesize 200 trimspool on set pagesize 60 set tab off col blocking_sql_id format a12 head 'BLOCKING|SQL_ID' col session_id format 999999 head 'SID' col event format a40 head 'EVENT' col session_state format a12 head 'SESSION|STATE' col time_waited format 999,999.99 head 'TIME|WAITED|SECONDS' col sample_time format a25 head 'SAMPLE TIME' with blocked as ( select distinct con_id, h.inst_id, h.sample_time, h.sample_id, h.session_id, h.session_serial#, h.blocking_session, h.blocking_session_serial#, h.sql_id, h.event, h.session_state from gv$active_session_history h where h.blocking_session is not null --and h.event like '&v_event_filter' and sample_time > sysdate - interval '5' minute ), blockers as ( select distinct max(b.sample_id) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_id , max(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_time , count(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) event_count , b.sql_id , b.inst_id , b.session_id , b.blocking_session , b.event , b.session_serial# , b.session_state ,b.con_id -- NULL for top level blockers --, b.time_waited from blocked b left outer join gv$active_session_history bl on bl.sample_id = b.sample_id and bl.inst_id = b.inst_id and bl.con_id = b.con_id and bl.sql_id = b.sql_id and bl.blocking_session = b.session_id and bl.blocking_session_serial# = b.blocking_session_serial# ) select --sample_id con_id, sample_time , sql_id , session_id , blocking_session , inst_id , event_count , session_state , event from blockers order by sample_time, session_id / SESSION CON_ID SAMPLE TIME SQL_ID SID BLOCKING_SESSION INST_ID EVENT_COUNT STATE EVENT ---------- ------------------------- ------------- ------- ---------------- ---------- ----------- ------------ ---------------------------------------- 5 16-MAY-24 06.44.51.752 AM 1m8z8cvabtqyf 3241 1523 1 1 WAITING library cache lock col sid format 99999 col username format a15 col event format a30 col p1text format a15 col p1 format 999999999999999 col p2text format a12 col p2 format 99999999999 col wait_time format 999999 head 'WAIT|TIME' col seconds_in_wait format 999999 head 'SECONDS|IN|WAIT' col state format a20 col seq format 999999 head 'SEQ' col blocking_session format a8 head 'BLKING|INSTANCE|SESSION' col wait_time_micro format 999,999,999 head 'WAIT TIME|MICROSECONDS' col wait_time_seconds format 999999.99 head 'WAIT TIME|SECONDS' col kill for a17 set linesize 250 pagesize 60 select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.con_id, s.username username, e.event event, s.sid, s.sql_id, e.p1text, e.p1, e.p2text, e.p2, e.seq# seq, e.state, e.wait_time, -- time of current or most recent wait in microseconds --e.wait_time_micro, e.wait_time_micro / 1000000 wait_time_seconds, --e.seconds_in_wait, -- ??? decode(s.blocking_session, null,'',s.blocking_instance || ':' || s.blocking_session) blocking_session from gv$session s, gv$session_wait e where s.username is not null and s.sid = e.sid and s.inst_id = e.inst_id -- skip sqlnet idle session messages --and s.module like 'Gathering Stats :EMT%' and s.event not in ('SQL*Net message from client','SQL*Net more data from client','REPL Capture/Apply: messages','rdbms ipc message') order by s.username, upper(e.event) / |
No comments:
Post a Comment