Script:
prompt
spool waits.rpt
col p1text for a30
col p2text for a30
col p3text for a30
col program for a40
col sid for 9999
col event for a57
col wait_class for a15
col "OS User" for a9
col "User" for a20
col "Status" for a10
col "Program" for a40
col "Module" for a38
col "Terminal" for a15
col "Client Info" for a15
col machine for a25
col "SID/Serial" for a15
set feed off
accept x_user prompt 'User : '
accept x_sid prompt 'SID : '
accept x_NoSqlNet prompt 'Ignore SQL*Net client waits? [Y] ' default 'Y'
prompt
prompt Current sessions by username
prompt
select i.instance_name "instance",
s.osuser "OS User",
s.sid || ',' || s.serial# "SID/Serial",
r.spid "OS PID",
s.username "User",
s.status "Status",
s.logon_time "Logon",
s.machine "Machine",
s.program "Program",
s.module "Module",
s.client_info "Client Info"
from gv$session s,
gv$process r,
gv$instance i
where s.username like upper( '%' || '&&x_user' || '%' )
and s.sid = nvl( '&x_sid', s.sid )
and i.inst_id = s.inst_id
and r.inst_id = s.inst_id
and r.addr (+)= s.paddr
order by s.username, s.terminal, s.program, s.logon_time;
prompt
prompt By event, username, inst_id, wait_class, sid
prompt
select b.event,
a.username, a.inst_id,
c.wait_class, b.sid, c.total_waits, c.time_waited,
b.seconds_in_wait,
decode( b.wait_time,
0, 'Waiting',
-1, 'Last Wait minimal',
-2, 'Waited Unknown',
decode( sign( b.wait_time ),
-1, 'Wrapped',
1, 'Last Wait',
'Unknown'
)
) "Wait status",
a.osuser, a.program,
b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw,
b.seq#
from gv$session a,
gv$session_wait b,
gv$session_wait_class c
where a.username like upper( '%&x_user%' )
and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' )
and b.wait_time > -2
and a.sid = b.sid
and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y'
or instr( b.event, 'SQL*Net message from client' ) = 0
)
and b.inst_id = a.inst_id
and c.inst_id = a.inst_id
and c.sid = a.sid
and c.serial# = a.serial#
order by b.event, username, inst_id, wait_class, sid;
prompt
prompt By seconds_in_wait, total_waits
prompt
select b.seconds_in_wait, c.total_waits,
a.inst_id, a.username, b.sid,
c.wait_class, c.time_waited,
decode( b.wait_time,
0, 'Waiting',
-1, 'Last Wait minimal',
-2, 'Waited Unknown',
decode( sign( b.wait_time ),
-1, 'Wrapped',
1, 'Last Wait',
'Unknown'
)
) "Wait status",
b.event,
a.osuser, a.program,
b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw,
b.seq#
from gv$session a,
gv$session_wait b,
gv$session_wait_class c
where a.username like upper( '%&x_user%' )
and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' )
and b.wait_time > -2
and a.sid = b.sid
and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y'
or instr( b.event, 'SQL*Net message from client' ) = 0
)
and b.inst_id = a.inst_id
and c.inst_id = a.inst_id
and c.sid = a.sid
and c.serial# = a.serial#
order by b.seconds_in_wait, c.total_waits;
select event, inst_id, count(*)
from gv$session_wait
group by event, inst_id
order by event, inst_id;
prompt
prompt ITL waits
prompt
col statistic_name for a15
select a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
a1.value,
a1.subobject_name,
a1.inst_id,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
from gv$segment_statistics a1
where statistic_name = 'ITL waits'
and owner = nvl( upper( '&&x_user' ), owner )
and value > 0
order by value desc;
prompt
prompt ITL waits: summary
prompt
select a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
sum( a1.value ) "Combined Value",
a1.subobject_name,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
from gv$segment_statistics a1
where statistic_name = 'ITL waits'
and owner = nvl( upper( '&&x_user' ), owner )
and value > 0
group by a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
a1.subobject_name,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
order by "Combined Value";
spool off
_______________________________________________________________
EJEMPLO DE SALIDA.
User :
SID :
Ignore SQL*Net client waits? [Y]
Current sessions by username
instance OS User SID/Serial OS PID User Status Logon Machine Program Module Client Info
---------------- --------- --------------- ------------------------ -------------------- ---------- ------------------- ------------------------- ---------------------------------------- -------------------------------------- ---------------
FERD oracle 753,12935 16291 APEX_PUBLIC_USER INACTIVE 30/09/2015 14:32:45 axdevap05 httpd.worker@xxdevjj05 (TNS V1-V3)
FERD oracle 502,1 24866 DBSNMP INACTIVE 25/09/2015 19:48:40 blx24ax01 JDBC Thin Client JDBC Thin Client
FERD oracle 352,2133 32351 DBSNMP INACTIVE 30/09/2015 11:38:45 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 552,1905 18784 DBSNMP INACTIVE 30/09/2015 14:18:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 6,883 17550 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 302,3427 17532 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD peterm 702,18145 24137 UTILITIES_DEV01 ACTIVE 30/09/2015 13:52:28 M10000_HG98A sqlplusw.exe SQL*Plus
By event, username, inst_id, wait_class, sid
EVENT USERNAME INST_ID WAIT_CLASS SID TOTAL_WAITS TIME_WAITED SECONDS_IN_WAIT Wait status OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ#
--------------------------------------------------------- ------------------------------ ---------- --------------- ----- ----------- ----------- --------------- ----------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ----------
SQL*Net message to client UTILITIES_DEV01 1 Idle 702 51 351732 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 Network 702 52 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 System I/O 702 14 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 User I/O 702 25 8 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
By seconds_in_wait, total_waits
SECONDS_IN_WAIT TOTAL_WAITS INST_ID USERNAME SID WAIT_CLASS TIME_WAITED Wait status EVENT OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ#
--------------- ----------- ---------- ------------------------------ ----- --------------- ----------- ----------------- --------------------------------------------------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ----------
0 14 1 UTILITIES_DEV01 702 System I/O 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 25 1 UTILITIES_DEV01 702 User I/O 8 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 54 1 UTILITIES_DEV01 702 Idle 351737 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 55 1 UTILITIES_DEV01 702 Network 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
EVENT INST_ID COUNT(*)
--------------------------------------------------------- ---------- ----------
DIAG idle wait 1 2
EMON slave idle wait 1 5
SQL*Net message from client 1 6
SQL*Net message to client 1 1
Space Manager: slave idle wait 1 1
Streams AQ: emn coordinator idle wait 1 1
Streams AQ: qmn coordinator idle wait 1 1
Streams AQ: qmn slave idle wait 1 1
Streams AQ: waiting for time management or cleanup tasks 1 1
VKTM Logical Idle Wait 1 1
pmon timer 1 1
rdbms ipc message 1 13
smon timer 1 1
ITL waits
STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME VALUE SUBOBJECT_NAME INST_ID TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC#
--------------- -------------------- ------------------ ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ---------- ---------- ----------
ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST ########## 1 SYSAUX 1 500 500 15
ITL waits: summary
STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME Combined Value SUBOBJECT_NAME TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC#
--------------- -------------------- ------------------ ------------------------------ -------------- ------------------------------ ------------------------------ ---------- ---------- ---------- ----------
ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 1 SYSAUX 1 500 500 15
No hay comentarios:
Publicar un comentario