RE: ** identify overloaded file system - and balance the load

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 5 Jan 2009 11:13:37 -0500
Message-ID: <DD1BE620275A413E8DC73172706D02F3_at_rsiz.com>

Without knowing whether your file system to device mapping is SAME, BORING, or Haphazard it is difficult to know what you would do with the information, but you can sum the file io grouping by substring of the datafile name that is the filesystem name.  

If your filesystem names are irregular, just make a table containing file# and filesystem name. Even if your filesystem naming convention is irregular you should be able to automate loading the table.  

If your layout of filesystems to devices is Haphazard, it will still only be good luck to be able to determine a file to move that will result in less device contention or to add a new file that will be on more lightly loaded devices.  

If your layout is SAME, then i/o is likely already reasonably flattened across devices, though throughput might not be maximized.

If your layout is BORING, then your mapping exercise will be trivial, you should be able to tell whether you should move some files, and you'll have a clear idea where you should add new files.  

Something like this query from 1994 from Rightsizing Inc.'s snapstat suite (you may have to adjust depending on release) is probably what you want to collect values  

select

      file#,

      SYSDATE,       phyrds,

      phywrts,

      phyblkrd,

      phyblkwrt,

      readtim,

      writetim

   from sys.v_$filestat;  

Then do the join and group by to get your numbers with a subtraction between various SYSDATE endpoints. Totally illegitimate across instance restarts, by the way. Its up to you do pick good intervals for collections. You might be able to assemble what you want from Oracle's statspack or AWR as well. Usually load texture by device varies wildly by time of day,day of week, day of month, and whether it is a quarter end or year end unless you're SAME.  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Joshi
Sent: Saturday, January 03, 2009 1:01 PM To: oracle-l_at_freelists.org
Subject: ** identify overloaded file system - and balance the load  

Hi,
  For : OLTP database
Oracle version : 10G
OS : SunSolaris

 I am trying to identify file systems with heavy usage. So that I can move the load out to file systems with less load. By moving database files or creating new heavy usage database files to light load file systems. I have sar reports. It has the devices. Next step for me is to map to the file systems. Or is there a way/command to get the sar to identify per file system instead of devices. I am also trying to use statspack but that also goes to the level of files/ tablespaces. Some tablespaces have files spread out over different file systems. This is my approach. There are over 200 files in the database and very difficult to sum them up. Instead if there is something at unix level to say which file system is overloaded and which is under loaded : that would help to balance it. Thanks for help. If someone has done similar work and has better suggestions : please let me know. Thanks  

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 05 2009 - 10:13:37 CST