col username form a10 heading "DB User"
col osuser form a10 heading "OS User"
col name form a20 heading "Type Of Sort"
col tablespace form a10
col machine for a25
col program for a25
col module for a22
col logtime for a25
col name for a22
SET TIME ON
prompt
prompt **Top 5 by Buffer Gets:
prompt
set linesize 2000
set pagesize 100
set TIMI ON
select d.sql_text sql,'''', x.sid,'''',x.serial#, '''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value from (SELECT * FROM
(SELECT s.sid,s.serial#, s.username,s.machine, a.hash_value, a.address, s.module, s.program, a.buffer_gets FROM
V$SQLAREA a, v$session s
WHERE a.buffer_gets > 10000 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY buffer_gets DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.buffer_gets desc, x.hash_value, d.piece;
prompt
prompt *** Top 5 by Physical Reads:
prompt
set TIMI ON
set linesize 2000
set pagesize 2000
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value from (SELECT * FROM
(SELECT s.sid, s.serial#, s.username,s.machine, a.hash_value, a.address, s.module, s.program, a.disk_reads FROM
V$SQLAREA a, v$session s
WHERE disk_reads > 1000 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY disk_reads DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.disk_reads desc, x.hash_value, d.piece;
prompt
prompt *** Top 5 by Executions:
prompt
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value from (SELECT * FROM
(SELECT s.sid,s.serial#,s.username,s.machine, a.hash_value,a.address, s.module, s.program,a.executions FROM
V$SQLAREA a, v$session s
WHERE executions > 100 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY executions DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.executions desc, x.hash_value, d.piece;
prompt
prompt *** Top 5 by Parse Calls:
prompt
set TIMI ON
set linesize 2000
set pagesize 2000
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value from (SELECT * FROM
(SELECT s.sid,s.serial#,s.username,s.machine, a.hash_value,a.address, s.module, s.program, a.parse_calls FROM
V$SQLAREA a, v$session s
WHERE parse_calls > 1000 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY parse_calls DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.parse_calls desc, x.hash_value, d.piece;
prompt
prompt ***Top 5 by Sharable Memory:
prompt
set TIMI ON
set linesize 2000
set pagesize 100
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value from (SELECT * FROM
(SELECT s.sid,s.serial#,s.username,s.machine, a.hash_value, a.address, s.module, s.program, a.sharable_mem FROM
V$SQLAREA a, v$session s
WHERE sharable_mem > 1048576 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY sharable_mem DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.sharable_mem DESC, x.hash_value, d.piece;
prompt
prompt ***Top 5 long running operations:
prompt
set TIMI ON
set linesize 2000
set pagesize 100
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.sql_hash_value,'''',
X.ELAPSED_SECONDS,'''', X.TIME_REMAINING,'''',X.TOTALWORK, '''',X.sofar, '''',x.logtime
from (SELECT * FROM
(SELECT s.sid,s.serial#,s.username,s.machine, a.sql_hash_value, a.sql_address, s.module, s.program,to_char(s.logon_time,'dd-mon-yyyy hh:mi:ss am') logtime,
a.ELAPSED_SECONDS, a.TIME_REMAINING, a.TOTALWORK, a.sofar FROM
V$session_longops a, v$session s
WHERE a.sid = s.sid and a.serial#=s.serial# and a.SQL_ADDRESS=s.sql_address and a.SQL_HASH_VALUE=s.SQL_HASH_VALUE
ORDER BY ELAPSED_SECONDS DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.sql_hash_value=d.hash_value and x.sql_address=d.address order by x.sql_hash_value,d.piece,
x.sid,x.ELAPSED_SECONDS DESC;
prompt
prompt ***Sort Usage Report
prompt
select d.sql_text sql,'''', x.sid,'''',x.serial#,'''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.sql_hash_value,'''',
x.name ,'''',x.value from (SELECT * FROM
(SELECT s.sid ,s.sql_hash_value, s.serial#, s.sql_address, s.username , s.machine, s.module, s.program, vsn.name ,vss.value
FROM v$session s,
v$sesstat vss,
v$statname vsn
WHERE vss.statistic#=vsn.statistic#
AND s.sid = vss.sid
AND vsn.name like '%sort%'
AND s.username is not null and s.sid !=(select distinct sid from v$mystat)
ORDER BY s.username) )x, v$sqltext_with_newlines d where x.sql_hash_value=d.hash_value and x.sql_address=d.address order by x.sql_hash_value,
d.piece, x.sid, x.username DESC;
No comments:
Post a Comment