Oracle Session Wait Info
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;
set pagesize 100 linesize 500
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)
/
break on day
set pagesize 1000 linesize 500
col day for a11
col metric_name for a18
select
to_char(begin_time,'DD-Mon') Day,
A.METRIC_NAME,
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'00',maxval,0)),'999999999999999') "00",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'01',maxval,0)),'999999999999999') "01",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'02',maxval,0)),'999999999999999') "02",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'03',maxval,0)),'999999999999999') "03",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'04',maxval,0)),'999999999999999') "04",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'05',maxval,0)),'999999999999999') "05",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'06',maxval,0)),'999999999999999') "06",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'07',maxval,0)),'999999999999999') "07",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'08',maxval,0)),'999999999999999') "08",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'09',maxval,0)),'999999999999999') "09",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'10',maxval,0)),'999999999999999') "10",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'11',maxval,0)),'999999999999999') "11",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'12',maxval,0)),'999999999999999') "12",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'13',maxval,0)),'999999999999999') "13",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'14',maxval,0)),'999999999999999') "14",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'15',maxval,0)),'999999999999999') "15",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'16',maxval,0)),'999999999999999') "16",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'17',maxval,0)),'999999999999999') "17",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'18',maxval,0)),'999999999999999') "18",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'19',maxval,0)),'999999999999999') "19",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'20',maxval,0)),'999999999999999') "20",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'21',maxval,0)),'999999999999999') "21",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'22',maxval,0)),'999999999999999') "22",
to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'23',maxval,0)),'999999999999999') "23"
from dba_hist_sysmetric_summary A
where
A.METRIC_NAME in
('Host CPU Utilization (%)',
'Average Active Sessions',
'Session Count',
'SQL Service Response Time',
'User Transaction Per Sec',
'Temp Space Used',
'Total PGA Allocated'
)
and BEGIN_TIME > trunc(sysdate) - 7
group by A.METRIC_NAME, to_char(begin_time,'DD-Mon')
order by to_char(begin_time,'DD-Mon'),a.metric_name ;
Tuesday, 22 August 2017
Oracle Session Wait Info ..
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment