Oracle Scratchpad

July 16, 2010

Fragmentation 2

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 6:51 pm BST Jul 16,2010

This note is part two of a four-part series, and covers Disk and Tablespace fragmentation. The whole series is as follows

  1. Introduction – with links to parts 2 – 4
  2. Disk and Tablespace Fragmentation – this bit
  3. Table Fragmentation
  4. Index Fragmentation

2.1 Disk “fragmentation”.

Tablespaces are made up of files, and files are stored on discs – which are often “logical volumes” rather than real devices. When you issue a (real) disc read request, the largest amount of data you can get off a (real, physical) disc in a single physical action is something like 300KB to 500KB – the content of a single circular track on a single platter of a disc.


July 13, 2010

Fragmentation 1

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 8:33 pm BST Jul 13,2010

This note started life as a nutshell until I realised that it was going to be more of a coconut than a hazel nut and decided to turn it into a short series instead. I should manage to  post  four parts over the next two weeks:

  1. Introduction (this bit)
  2. Disk and Tablespace Fragmentation
  3. Table Fragmentation
  4. Index Fragmentation

And a few extra links related to attempts to measure or reclaim “fragmented space”.

  1. An alcohol-inspired analogy for “defragmentation”
  2. A little warning about LOBs and commonly used space calculations
  3. Another example of “special” features messing up traditional space calculations
  4. Yet anpther example of looking at what you’ve got before you apply the usual arithmetic
  5. Fooled by how LOB space is reported
  6. Thoughts on preparing to reduce tablespace (file) sizes
  7. Moving objects to release space from a tablespace needs a little planning
  8. Why system-managed extent allocation makes releasing space from tablespaces a little more awkward


February 6, 2010

Shrink Tablespace

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 5:31 pm GMT Feb 6,2010

Here’s an example of a theme that appears on the OTN database forum from time to time (I haven’t included a link to it because it’s just one example of many similar questions):

“I have a user tablespace allocated for 3-4 schemas. As I urgently needed space on hard disk I had to remove one of the schema(drop user). Now this tablespace is shown as 70% filled. I want to reduce allocated space to it.”


January 30, 2010

Free Space

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 10:37 am GMT Jan 30,2010

I’ve just seen the following question on OTN:

I had a log table which was about 30G in Production, since it was growing in size we decided to drop it. After dropping it we can’t see the space being freed. Even at OS level we can’t see any reduction in filesize.Can you please explain and/or help in this?

This prompted me to come up with the following analogy. (more…)

August 7, 2009

Index Fragmentation

Filed under: fragmentation,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 6:07 pm BST Aug 7,2009

Here’s a thought for the weekend:

When people talk about “index fragmentation”, what do they mean, and why do they care ?

I often see email or forum posts from people claiming that their indexes are fragmented and need to be rebuilt – but they rarely explain (even when asked) what they mean by “fragmented”, and how they have measured the “fragmentation”, and why they think they have evidence that the index needs to be rebuilt.

So if you are accustomed to talking about indexes being “fragmented”, would you let me know what you mean, and how you measure “fragmentation”. (I can think of three or four interpretations for the term – but I’m interested to hear from people who actually use it.)

July 1, 2008

Ancient History

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 9:23 pm BST Jul 1,2008

I’ve just been browsing through a subdirectory on my laptop that has survived many migrations from machine to machine over the years – even though some of the material needs programs that probably don’t exist any more.

This slide, from a presentation I gave at the UKOUG annual conference in 1996, raised a wry smile. The title of the presentation was “Fact, Folklore, or Fairy-table” – possibly the first ever “Mythbuster” presentation on the Oracle scene. The title of each slide declared a popular belief, and the rest of the slide commented on the sense or (usually) lack thereof in the belief.


« Previous Page

Website Powered by