Skip to Main Content
  • Questions

  • Space thresholds for tablespace and OS

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jon.

Asked: September 17, 2008 - 11:37 am UTC

Last updated: September 17, 2008 - 7:20 pm UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hi Tom

I am currently struggling with a situation where we are under new management which has no experience with open systems and Oracle and databases in general. We are being questioned as to "why" for various things and the standard comment is "find best practices" which means, we don't really value the knowledge or experience of the staff, and we want to check and make sure your story is correct. We're going through a periond where our storage footprint is being highly scrutinized. Are you aware of any best practices for overall space thresholds in regards to Oracle i.e. how much free space should have have available in your tablespaces, and how much free space should you have available on the OS (we're running oracle on AIX 5.3). I realize each database is different and each one's "milieage may vary" but unfortunately we've been beaten down to a one-size-fits-all approach.

Sorry for the editorial comments, I'm looking for any help in regards to space management best practices.

Thanks!

and Tom said...

... how much free space should have have available in your tablespaces ...

I like zero. I like autoextend. Others will say 10% (whatever that means). Others yet will say ......

... and how much free space should you have available on the OS ...

that would be a function of your growth - do you have historical numbers (there are dba views in 10g with the performance/diagnostic packs that can provide some of this). If you never grow, zero percent free would be ok. If you grow at the rate of 100gb per month, you better have, well, at least 100gb free right now and a purchase order open for more.


There is no one size fits all - it really does depend. If you are a warehouse that is basically rebuilt every week - you need no "free space" - just enough space. If you are a transactional system that has sort of hit steady state - not really growing anymore - deletes+inserts - ditto. If you are a transactional system that is growing by leaps and bounds, or a warehouse that is in accumulate mode (growing) the story is very different.


Basically, you need to do some capacity planning - how fast are you growing, if at all, storage wise.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

So best practice?

Jon Gill, September 17, 2008 - 3:16 pm UTC

So are you saying the best practice is to utilize autoextend? and just manage the OS space and go from there? Is this supported as a generally accepted approach/methodology. This is not me asking, but questions I will get asked. I absolutely agree, you can only plan based on historic information. But to beter understand the OS space threshold question, is there a %used for particular types of filesystems that could cause performance degradation and you would want to provide a slight overhead for?
Tom Kyte
September 17, 2008 - 7:20 pm UTC

I do not believe in best practices actually - if there were a universal "this is best", why would there be other ways?

I like autoextend.
Some people *hate* it.

Is it generally accepted - sure, is it also generally sometimes frowned on - sure, is it a good idea - sure, is it....

This will be a battle you cannot win on this one - there are two diametrically opposed opinions here. I like autoextend with a reasonable maxsize on the datafiles in general (raw would be the exception, although ASM brings it back into being again since ASM makes raw look "not raw"). I prefer my files to grow extent by extent and just keep reserve capacity in the file system. That way you need less overall reserve capacity. If each tablespace had to run at 10% free - and you have tablespaces that grow at different rates (they typically do), you cannot "share" the free space across them. If you let the file system on the other hand run 10% free - any datafile from any tablespace can use that free space.




There is no % "threshold" I am aware of - like I said, it totally depends on your rate of growth.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.