Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  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

-- ************************************************* 

/* 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


   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)



   perfstat.stats$sysstat MEMOLD,

   perfstat.stats$sysstat MEMNEW,

   perfstat.stats$sysstat DISKNEW,

   perfstat.stats$sysstat DISKOLD,

   perfstat.stats$snapshot sp


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


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.