|
Get Complete OSM Toolkit (free) |
|
ashclp.sql
ashclpct.sql
ashp.sql
ashpctcpu.sql
ashpcte.sql
ashpctecl.sql
ashpctes.sql
ashpcts.sql
ashrt.sql
ashrt_old.sql
ashsp.sql
ashspct.sql
ashsqlcl.sql
ashsqle.sql
ashsqlpctcl.sql
ashsqlpctcpu.sql
ashsqlpcte.sql
ashsqlpcts.sql
ashsqlpctt.sql
ashsqls.sql
avgexplore.sql
bc.sql
bc7.sql
bcmap.sql
bcmap7.sql
bcobjfb.sql
chr.sql
clone.sql
cr_rtsp.sql
cu.sql
cycledb6
cycledb9
dboc.sql
delete.me
dev_list.txt
dfio.sql
dfl.sql
diag.sql
ds.sql
ds7.sql
dsn.sql
eval1.sql
event_type.sql
event_type_nc.sql
fgidx.sql
fgtbl.sql
hashchk.sql
idx.sql
iosum.sql
iosum9.sql
iosum9x.sql
iosumx.sql
ip.sql
ipcbc.sql
ipx.sql
irtviews.sql
istat.sql
latch.sql
latch8.sql
latch_old.sql
latchchild.sql
latchclass.sql
latchx.sql
lc.sql
lock.sql
loghist8.sql
lruno.sql
mkodo.sql
mkodo_ouch.sql
mts.sql
mysess.sql
objfb.sql
objloc.sql
ogbigld.sql
ogcrobj.sql
ogdoit.sql
ogobjlst.sql
ogobjset.sql
oracpu.sql
oracputext.sql
oracpux.sql
oracpux1.sql
oracpux2.sql
oscpux.sql
oscpux1.sql
oscpux10.sql
oscpux2.sql
oscpux210.sql
osmclear.sql
osmi.sql
osmprep.sql
osmtitle.sql
osmtitlel.sql
osmtitlell.sql
osmtitlem.sql
osmtitles.sql
rbs.sql
rdohist.sql
readme.txt
rlog.sql
rtc.sql
rtcx.sql
rtio.sql
rtow.sql
rtpctx.sql
rtsess.sql
rtsess9.sql
rtsessx.sql
rtsum.sql
rtsys.sql
rtsysx.sql
rtsysx8.sql
sessinfo.sql
sessinfo9.sql
sesstat.sql
sga.sql
simsql1.sql
simsql2.sql
snap_stats.sql
spspinfo.sql
sqls1.sql
sqls18.sql
sqls19.sql
sqls2.sql
sqls3.sql
sqls4.sql
stu.sql
supers.sql
swenq.sql
swenqc.sql
swenqnew.sql
swenqx.sql
swhist.sql
swhist.sql.sav
swhistx.sql
swhistx.sql.sav
swpct.sql
swpctidle.sql
swpctx.sql
swpctxidle.sql
swsessid.sql
swsid.sql
swsw.sql
swswc.sql
swswp.sql
swsys.sql
sysstat.sql
tbfsum.sql
tcsizing.sql
tcstats.sql
test.sql
timechk.sql
topcpu
topdml.sql
tp.sql
tp9.sql
tsmap.sql
tss.sql
undo.sql
users.sql
-- ********************************************************************
-- * Copyright Notice : (c)2000-2004 OraPub, Inc.
-- * Filename : tp.sql
-- * Author : Craig A. Shallahamer
-- * Original : 17-NOV-00
-- * Last Update : 13-mar-06
-- * Description : Top Oracle Processes
-- * Usage : start tp.sql
-- ********************************************************************
set feedback off head on echo off
-- RSS not picking up in 9.2 or below
--prompt To get RSS, init.ora os_timed_statistics must be › 0
--prompt
accept zsid prompt "Enter a session ID (sid) :"
accept zcpu prompt "Enter the minimum session cpu time (sec) :"
accept zpio prompt "Enter the minimum session physical I/O (K gets):"
accept zlio prompt "Enter the minimum session logical I/O (M gets):"
accept zrdo prompt "Enter the minimum session redo (kb) :"
accept qpio prompt "Enter the minimum SQL stmt physical I/O (K gets):"
accept qlio prompt "Enter the minimum SQL stmt logical I/O (M gets):"
--accept qrbs prompt "Enter the minimum rollback amount (kb) :"
def osm_prog = 'tp.sql'
def osm_title = 'Top Oracle Processes (by QPIO*10+QLIO desc)'
start osmtitlem
set verify off feedback off head on echo off
col sidx format 9999 heading 'SID'
col uname format a8 heading 'Oracle|User Name' trunc
col timex format 9999 heading 'STime|(min)'
col scpu format 9990 heading 'SCPU|(sec)'
col pio format 9990 heading 'SPIO|(k)'
col lio format 9990 heading 'SLIO|(m)'
col rs format 9990 heading 'SRedo|(MB)'
col rss format 9990 heading 'SRSS|(MB)'
col sa format a16 heading 'SQL Address'
col dr format 9990 heading 'PIO|(k)'
col bg format 9990 heading 'LIO|(m)'
col rowx format 9990 heading 'Rows|(k)'
col load noprint
col pio_time heading 'Sec/|PIO' format 90.00
col lio_time heading 'Sec/|LIO' format 90.00
col exe_time heading 'Sec/|EXE' format 990.00
col cpu heading 'CPU|(min)' format 990
col etime heading 'ETime|(min)' format 990
col stype heading 'Stmt|Type' format a4
col exe heading 'Exes' format 99990
-- t4.value/1024/1024 rss,
select
s.sid sidx,
s.username uname,
(sysdate-s.logon_time)*24*60 timex,
(t2.value)/100 scpu,
sio.physical_reads/1000 pio,
(sio.block_gets+sio.consistent_gets)/1000/1000 lio,
t1.value/1024/1024 rs,
s.sql_address sa,
q.disk_reads/1000 dr,
q.buffer_gets/1000/1000 bg,
q.rows_processed/1000 rowx,
q.cpu_time/1000000/60 cpu,
q.elapsed_time/1000000/60 etime,
(q.elapsed_time/1000000)/(q.disk_reads+0.00000001) pio_time,
(q.elapsed_time/1000000)/(q.buffer_gets+0.00000001) lio_time,
q.executions exe,
substr(q.sql_text,1,4) stype,
q.disk_reads*10+q.buffer_gets load
from
v$session s,
v$sqlarea q,
v$sesstat t1,
v$sesstat t2,
v$sesstat t4,
v$sess_io sio
--v$transaction r,
--( select value blksize from v$parameter where name = 'db_block_size' ) x
where
s.sql_address = q.address and
s.sid = t1.sid and
s.sid = t2.sid and
s.sid = t4.sid and
t1.statistic# = 133 and -- redo size (10g only, 9i is 115)
t2.statistic# = 12 and -- CPU used by this session
t4.statistic# = 318 and -- OS Maximum resident set size
s.sid = sio.sid and
s.sid like '&zsid%' and
t2.value ›= nvl('&zcpu',0) and
sio.physical_reads/1024 ›= nvl('&zpio',0) and
(sio.block_gets+sio.consistent_gets)/1024/1024 ›= nvl('&zlio',0) and
q.disk_reads/1024 ›= nvl('&qpio',0) and
q.buffer_gets/1024/1024 ›= nvl('&qlio',0) and
t1.value/1024 ›= nvl('&zrdo',0)
order by
load desc
/
--r.used_ublk*x.blksize/1024 rb,
--s.taddr = r.addr and
--r.used_ublk*x.blksize ›= nvl('&qrbs',0)*1024
start osmclear
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
Craig Shallahamer's Blog