statspack中相關效能指標的計算方法

Buffer Cache Hit Ratio

100*( 1 -  ( valdiff( stats$sysstat, 'physical reads' )
-  valdiff( stats$sysstat, 'physical reads direct' )
-  valdiff( stats$sysstat, 'physical reads direct (lob)' )
) / valdiff( stats$sysstat, 'session logical reads' )
);
Sorts in Memory
100*( valdiff( stats$sysstat, 'sorts (memory)' )
/ ( valdiff( stats$sysstat, 'sorts (memory)' )
+ valdiff( stats$sysstat, 'sorts (disk)' )
)
)
Shared Pool Free
select bytes from stats$sgastat
where snap_id = endsnap
and pool = 'shared pool'
and name = 'free memory'
Shared Pool Reloads
100*( valdiff( stats$librarycache, reloads)
/ valdiff( stats$librarycache, pins)
)
Library Cache Get Hit Ratio
100*( valdiff( stats$librarycache, gethits)
/ valdiff( stats$librarycache, gets)
)
Library Cache Pin Hit Ratio
100*( valdiff( stats$librarycache, pinhits)
/ valdiff( stats$librarycache, pins)
Buffer Nowait Percentage
100*( 1 - ( valdiff( stats$waitstat, wait_count )
/ valdiff( stats$sysstat, 'session logical reads' )
)
)
Redo Nowait Percentage
100*( 1 - ( valdiff( stats$sysstat, 'redo log space requests' )
/ valdiff( stats$sysstat, 'redo entries' )
)
)
Non-Parse CPU
100*( 1 - ( valdiff( stats$sysstat, 'parse time cpu' )
/ valdiff( stats$sysstat, 'CPU used by this session' )
)
)
Soft Parse
100*(1 - ( valdiff( stats$sysstat, 'parse count (hard)' )
/ valdiff( stats$sysstat, 'parse count (total)' )
)
)
Execute to Parse
100*( 1 - ( valdiff( stats$sysstat, 'parse count (total)' )
/ valdiff( stats$sysstat, 'execute count' )
)
)
Parse CPU to Parse Elapsed
100*( valdiff( stats$sysstat, 'parse time cpu' )
/ valdiff( stats$sysstat, 'parse time elapsed' )
)
Willing to Wait Latch Gets
100*(1 - ( valdiff( stats$latch, misses )
/ valdiff( stats$latch, gets )
)
)
Immediate Latch Gets
100*(1 - ( valdiff( stats$latch, immediate_misses )
/ valdiff( stats$latch, immediate_gets )
)
)
Rollback Segment Contention
100*( valdiff( stats$rollstat, waits )
/ valdiff( stats$rollstat, gets )
)
Total Transactions over interval
valdiff( stats$sysstat, 'user rollbacks' )
+
valdiff( stats$sysstat, 'user commits' )
Transactions per Second
( valdiff( stats$sysstat, 'user rollbacks' )
+
valdiff( stats$sysstat, 'user commits' )
)
/
interval seconds
Average Open Cursors
stats$syssstat 'opened cursors current'
/ stats$syssstat 'logons current'
Rollbacks
valdiff( stats$sysstat, 'user rollbacks' )
Commits
valdiff( stats$sysstat, 'user commits' )
/ trancount

valdiff( stats$sysstat, 'user commits' )
/ interval seconds
Redo Size
valdiff( stats$sysstat, 'redo size' )
/ trancount

valdiff( stats$sysstat, 'redo size' )
/ interval seconds
Logical Reads
valdiff( stats$sysstat, 'session logical reads' )
/ trancount

valdiff( stats$sysstat, 'session logical reads' )
/ interval seconds
Block Changes
valdiff( stats$sysstat, 'db block changes' )
/ trancount

valdiff( stats$sysstat, 'db block changes' )
/ interval seconds
Physical Reads
valdiff( stats$sysstat, 'physical reads' ) * blocksize / 1024
/ trancount

valdiff( stats$sysstat, 'physical reads' ) * blocksize / 1024
/ interval seconds
Physical Writes
valdiff( stats$sysstat, 'physical writes' ) * blocksize / 1024
/ trancount

valdiff( stats$sysstat, 'physical writes' ) * blocksize / 1024
/ interval seconds
User Calls
valdiff( stats$sysstat, 'user calls' )
/ trancount

valdiff( stats$sysstat, 'user calls' )
/ interval seconds
Parse Count
valdiff( stats$sysstat, 'parse count (total)' )
/ trancount

valdiff( stats$sysstat, 'parse count (total)' )
/ interval seconds
Hard Parse Count
valdiff( stats$sysstat, 'parse count (hard)' )
/ trancount

valdiff( stats$sysstat, 'parse count (hard)' )
/ interval seconds
Sort Count
(
valdiff( stats$sysstat, 'sorts (memory)' )
+
valdiff( stats$sysstat, 'sorts (disk)' )
)
/ trancount

(
valdiff( stats$sysstat, 'sorts (memory)' )
+
valdiff( stats$sysstat, 'sorts (disk)' )
)
/ interval seconds
Logons Count
valdiff( stats$sysstat, 'logons cumulative' )
/ trancount

valdiff( stats$sysstat, 'logons cumulative' )
/ interval seconds
Executes
valdiff( stats$sysstat, 'execute count' )
/ trancount

valdiff( stats$sysstat, 'execute count' )
/ interval seconds
Reloads per Second
valdiff( stats$librarycache, sum(reloads) )
/ trancount

valdiff( stats$librarycache, sum(reloads) )
/ interval seconds
Data Size
select sum(bytes) from sys.dba_data_files
+
select sum(bytes) from sys.dba_temp_files
-
select sum(bytes) from sys.dba_free_space
Free Space Size
select sum(bytes) from sys.dba_free_space

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23577591/viewspace-687780/，如需轉載，請註明出處，否則將追究法律責任。