Search This Blog

Total Pageviews

Tuesday 22 August 2017

Oracle Process Info ...

Oracle Process information ..


col kill       for a15
col evnt   for a30
col usr    for a10
col osuser for a14
col mach   for a8
col prog   for a20
col sid    for 999
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,osuser,substr(a.username,1,10) usr,substr(a.program,1,20) prog,to_char(logon_time,'dd/mm hh24:mm:ss') logon,sql_id,prev_sql_id,substr(event,1,30) evnt,pga_alloc_mem pga
,decode(a.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
from gv$session a,gv$process b 
where a.paddr=b.addr 
and a.inst_id=b.inst_id
and b.username is not null 
order by 9 desc ;


KILL            OSUSER         USR        PROG                 LOGON          SQL_ID        PREV_SQL_ID   EVNT                                  PGA COMMAND
--------------- -------------- ---------- -------------------- -------------- ------------- ------------- ------------------------------ ---------- --------
'370,58214,@2'  oracle                    oracle@*******.int.s 25/07 13:07:02               6hnhqahphpk8n rdbms ipc message               165162756 UNKNOWN
'370,2076,@1'   oracle                    oracle@*******.int.s 25/07 13:07:51               6hnhqahphpk8n rdbms ipc message               163327748 UNKNOWN
'981,14255,@1'  oracle                    oracle@*******.int.s 25/07 13:07:53                             wait for unread message on bro   64040708 UNKNOWN

===



SET PAGESIZE 500 linesize 500

COLUMN instance_name     FORMAT a9         HEADING 'Instance'
COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right
COLUMN session_machine   FORMAT a22        HEADING 'Machine'         JUSTIFY right
col kill for a17
prompt
prompt +----------------------------------------------------+
prompt | User Sessions (All)                                |
prompt +----------------------------------------------------+

BREAK ON instance_name SKIP PAGE

SELECT
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
   -- i.instance_name      instance_name
 -- , s.sid                sid
 -- , s.serial#            serial_id
   , sql_id
   ,PREV_SQL_ID
  , lpad(s.status,9)     session_status
  , lpad(s.username,14)  oracle_username
  , lpad(s.osuser,12)    os_username
  , lpad(p.spid,7)       os_pid
 -- , s.program            session_program
  , lpad(s.terminal,10)  session_terminal
  , lpad(s.machine,19)   session_machine
  , s.event
,FINAL_BLOCKING_SESSION_STATUS
,FINAL_BLOCKING_SESSION
,p1
,p2
 p3
FROM   gv$session  s
    INNER JOIN gv$process  p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
    INNER  JOIN gv$instance i ON (p.inst_id = i.inst_id)
    AND s.TYPE != 'BACKGROUND'
and s.status='ACTIVE'
ORDER BY
    i.instance_name
  , s.sid
/

      



define 1='XXXX' ---- Change user name !!!!
col u_username head USERNAME for a23
col u_sid head SID for a14 
col u_audsid head AUDSID for 9999999999
col u_osuser head OSUSER for a16
col u_machine head MACHINE for a25 truncate
col u_program head PROGRAM for a20

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill ,s.username||CASE WHEN s.sid = SYS_CONTEXT('userenv','sid') THEN ' (me)' WHEN s.type = 'BACKGROUND' THEN ' (bg)' END u_username,  
       p.spid, 
       s.sql_id, 
       s.audsid u_audsid,
       s.osuser u_osuser, 
       substr(s.machine,instr(s.machine,'\')) u_machine,
       CASE WHEN s.type = 'BACKGROUND' OR REGEXP_LIKE(s.program, '.*\([PJ]\d+\)') THEN
          REGEXP_REPLACE(SUBSTR(s.program,INSTR(s.program,'(')), '\d', 'n')
       ELSE
          '('||REGEXP_REPLACE(REGEXP_REPLACE(s.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
       END u_program,
       --substr(s.program,instr(s.program,'(')) u_program, 
       -- s.sql_address, 
       s.last_call_et lastcall, 
       s.status, 
       --, s.logon_time
	   s.event 
from 
    gv$session s,
    gv$process p
where 1=1
  and   s.paddr=p.addr
  and   s.inst_id=p.inst_id
--and s.type!='BACKGROUND'
and (lower(s.username) like lower('&1') or (nvl(s.username,'%') = '%' and s.type = 'USER'))
and s.status='ACTIVE'
order by s.inst_id,s.last_call_et desc
/







set linesize 300
COLUMN Block                            HEADING "Block"                      FORMAT a10
COLUMN blocking_instance                HEADING "Blocking|I#"                FORMAT 999999999
COLUMN blocking_instance                HEADING "Blocking|I#"                FORMAT 999999999
COLUMN blocking_session_status          HEADING "Blocking|SID Status"        FORMAT a10
COLUMN final_blocking_instance          HEADING "Final|Blocking|I#"          FORMAT 999999999
COLUMN final_blocking_session           HEADING "Final|Blocking|SID"         FORMAT 999999999
COLUMN final_blocking_session_status    HEADING "Final|Blocking|SID Status"  FORMAT a10
COLUMN inst_id                          HEADING "I#"                         FORMAT 99
COLUMN username                         HEADING "DBUser"                     FORMAT a15
COLUMN osuser                           HEADING "OSUser"                     FORMAT a15
COLUMN status                           HEADING "Status"                     FORMAT a10
COLUMN state                            HEADING "State"                      FORMAT a10
COLUMN logon_time                       HEADING "LogonTime"                  FORMAT a18
COLUMN service_name                     HEADING "ServiceName"                FORMAT a20
col EVENT for a30
col kill for a15
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.sql_id,PREV_SQL_ID,event,'Blocked By' Block
     , s.blocking_instance
     , s.blocking_session
     , s.blocking_session_status
     , s.final_blocking_instance
     , s.final_blocking_session
     , s.final_blocking_session_status
                         FROM gv$session s
                        where 1=1
                  -- and s.sid = :SID
                       -- AND   s.inst_id = :INST_ID
                      --  and s.blocking_session is NOT NULL
                        order by 1;



set linesize 1000 pagesize 500
col kill for a17

col PLSQL_ENTRY_OBJECT       for a20                                                                                                        
col PLSQL_ENTRY_SUBPROGRAM for a20 
col PLSQL_ENTRY_SUBPROGRAM for a20 
col  PLSQL_ENTRY_OBJECT for a20 

col CLIENT_IDENTIFIER for a20

select 
--s.ECID ,s.inst_id, s.SID,s.SERIAL#,con_id,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill,s.con_id,
--s.ECID ,
p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
, ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
--, 'alter system kill session ' || '''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session
from gv$session s ,gv$process p
where 1=1
-- and s.process='5'  --forms OS process ID
--s.program like '%xxx%'
and p.addr=s.paddr
and p.inst_id = s.inst_id
and s.status!='INACTIVE'
;



  
  
set linesize 500 pagesize 400
col osuser 		for a16
col program 	for a10 trunc
col Locked 		for a6
col status 		for a1 trunc print
col "hh:mm:ss" 	for a8
col SQL_ID 		for a15
col seq# 		for 99990 --Sequence number that uniquely identifies the wait. Incremented for each wait
col module 		for a25
col ACTION 		for a20
col kill 		for a19
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col username    for a25
col MACHINE for a37
select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill
,STATUS
,MACHINE 
--,con_id --12c
, username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser
, substr(program,instr(program,'/',-1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , sql_id,prev_sql_id, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module,final_blocking_session_status,sql_exec_start
-- substr(action,1,20) action
from GV$SESSION
where 1=1
--and USERNAME like 'DBFS%' 
--and USERNAME not in ('SYS','SYSRAC','DBSNMP' )
--and  type = 'USER'
--and STATUS!='INACTIVE'
--and audsid != 0 -- to exclude internal processess
--and SQL_ID='cabcn90dg7ttu'
--and event not like 'SQL*Net message from%'
--and osuser='_USER'
--and con_id =5
--and sid=4605
--and program like 'oracle%'
--and SERVICE_NAME in ('p_p')
 and PADDR = (select ADDR from V$PROCESS where SPID = 22289)
order by inst_id,  last_call_et desc, sid
/




SET ECHO  OFF FEEDBACK  6 HEADING  ON LINESIZE 500 PAGESIZE 50000 TERMOUT ON TIMING  OFF TRIMOUT  ON TRIMSPOOL  ON VERIFY  OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN session_status    FORMAT a9         HEADING 'Status'
COLUMN oracle_username   FORMAT a18        HEADING 'Oracle User'
COLUMN os_username       FORMAT a18        HEADING 'O/S User'
COLUMN os_pid            FORMAT a8         HEADING 'O/S PID'
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'         TRUNC
COLUMN session_machine   FORMAT a30        HEADING 'Machine'          TRUNC
COLUMN session_program   FORMAT a40        HEADING 'Session Program'  TRUNC
col kill for a23 
col EVENT for a20 TRUNC
BREAK ON instance_name SKIP PAGE

SELECT
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill
--,s.con_id,
,'kill -9 '||spid PKill
  , s.status
  , s.sql_id
 ,prev_sql_id
 ,event
  , s.username          oracle_username
  , s.osuser            os_username
  , p.spid              os_pid
  , s.terminal          session_terminal
  , s.machine           session_machine
  , s.program           session_program
FROM          gv$session  s
  INNER JOIN gv$process  p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
WHERE 1=1
    -- and   s.status = 'ACTIVE'
  AND s.username IS NOT null
ORDER BY  1
  
  

set linesize 300
col PROGRAM  for a20
col MACHINE for a20
col SCHEMANAME for a18
col kill for a17
select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill ,sid,serial#,username,sql_id,schemaname,osuser,process,machine,port,program,type,paddr
from gv$session
where PADDR = (select ADDR from V$PROCESS where SPID = &spid)




Oracle Session Wait Info ..

Oracle Session wait info .. 

session_waits.sql


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

Oracle DBA

anuj blog Archive