88 lines
3.9 KiB
SQL
88 lines
3.9 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tablespace IO statistics from statspack data
|
|
* To Do : 1 - Ajust script for
|
|
* Arguments: 1 - Tablespace Name, you can password wild characters like %
|
|
* 2 - Number of days of data to consider
|
|
* 3 - DBID
|
|
* 4 - instance_number
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Jan-09 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
|
|
define tablespace_name="&&1"
|
|
define days="&&2"
|
|
define dbid="&&3"
|
|
define instance_number="&&4"
|
|
|
|
|
|
WITH filestatxs
|
|
as
|
|
(select f.dbid, f.instance_number, f.snap_id, s.snap_time
|
|
, f.tsname, f.file#, f.filename
|
|
, DECODE( f.singleblkrds - LAG (f.singleblkrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
|
|
, 0, 0
|
|
, ( ( f.singleblkrdtim - LAG (f.singleblkrdtim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
/ ( f.singleblkrds - LAG (f.singleblkrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
) * 10
|
|
) singleblkreadtime
|
|
, DECODE( f.phyrds - LAG (f.phyrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
|
|
, 0, 0
|
|
, ( ( f.readtim - LAG (f.readtim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
/ ( f.phyrds - LAG (f.phyrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
) * 10
|
|
) readtime
|
|
, DECODE( f.phywrts - LAG (f.phywrts ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
|
|
, 0, 0
|
|
, ( ( f.writetim - LAG (f.writetim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
/ ( f.phywrts - LAG (f.phywrts ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
|
|
) * 10
|
|
) writetime
|
|
, f.singleblkrds - LAG (f.singleblkrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) singleblkreads
|
|
, f.phyrds - LAG (f.phyrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) reads
|
|
, f.phywrts - LAG (f.phywrts ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) writes
|
|
from stats$database_instance i
|
|
, stats$snapshot s
|
|
, stats$filestatxs f
|
|
where (i.dbid, i.instance_number,i.startup_time) in (select max(dbid), instance_number, max(startup_time)
|
|
from stats$database_instance i2
|
|
where i2.dbid = &dbid
|
|
and i2.instance_number = &instance_number
|
|
group by instance_name, instance_number
|
|
)
|
|
and s.dbid = i.dbid
|
|
and s.instance_number = i.instance_number
|
|
and f.dbid = s.dbid
|
|
and f.instance_number = s.instance_number
|
|
and f.snap_id = s.snap_id
|
|
and s.dbid = &dbid
|
|
and s.instance_number = &instance_number
|
|
and s.snap_time > sysdate - &days
|
|
and f.tsname like '&tablespace_name' )
|
|
select f2.snap_time
|
|
, f2.tsname
|
|
, ROUND(avg(singleblkreadtime),2) "AvgSingleBlockRead(ms)"
|
|
, ROUND(avg(readtime),2) "AvgRead(ms)"
|
|
, ROUND(avg(writetime),2) "AvgWrite(ms)"
|
|
, SUM(singleblkreads) tot_singleblkreads
|
|
, SUM(reads) - SUM(singleblkreads) tot_multiblkreads
|
|
, SUM(reads) tot_reads
|
|
, SUM(writes) tot_writes
|
|
from filestatxs f2
|
|
group by f2.snap_time, f2.tsname
|
|
order by f2.snap_time desc
|
|
;
|
|
|
|
|
|
@@footer
|