 |
|
Oracle Tips by Burleson |
Using STATSPACK data for
trend analysis of buffer cache ratios
This data could be easily imported into a
spreadsheet and used to make graphs and charts for clearer
analysis. In addition, there are a number of utilities that provide
a graphical front-end for the STATSPACK data.
Another script, sp_sorts_ratio_trend.sql,
shows the percentage of sorts that take place in memory compared to
on disk. When too many sorts happen on disk, a bottleneck can be
introduced. This can cause additional disk I/O. This additional I/O
could result in run speed reduction for both the sorting session as
well as any other session that has to wait longer for disk access.
* sp_sorts_ratio_trend.sql
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial
purposes
-- with no warranties. Use at your own
risk.
--
-- To license this script for a commercial
purpose,
-- contact info@rampant.cc
--
*************************************************
/* sp_sorts_ratio_trend.sql */
column SNAP_DATE heading "Year Mo Day Hour"
format a16
column MEMORY_SORTS
format 999,999,999
column DISK_SORTS
format 999,999,999
column "DISK/MEMORY RATIO"
format 999.99
select
to_char(snap_time, 'yyyy-mm-dd HH24')
SNAP_DATE,
memnew.value-memold.value MEMORY_SORTS,
disknew.value-diskold.value DISK_SORTS,
(((disknew.value-diskold.value) /
(memnew.value-memold.value)) * 100)
"DISK/MEMORY RATIO"
from
perfstat.stats$sysstat MEMOLD,
perfstat.stats$sysstat MEMNEW,
perfstat.stats$sysstat DISKNEW,
perfstat.stats$sysstat DISKOLD,
perfstat.stats$snapshot sp
where:
See Code Depot
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
