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


 

HTML Text

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

  
 

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.