Oracle Scratchpad

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

Introduction

The implication of the word “fragmentation” is that something is broken into pieces, but it also carries an emotional overtone that suggests  it’s lots of little pieces. In an Oracle context you need to consider what you mean by “pieces”, the granularity of the pieces, and the possible impact on performance. Since it’s possible to talk about fragmentation at the (logical) disk level, the file level, the tablespace level, the segment level, the extent level, and the block level, it’s necessary to think very clearly about what you’re trying to  say when you make a comment like “my tablespace is fragmented” or “my index is fragmented”.

Let’s start with an example: I have created a new tablespace and moved a table into it. When I check dba_extents the table has 100 extents. Clearly it is “fragmented” in the basic sense of the word since it is made of 100 different pieces. On the other hand, because the table was the first thing I created in the tablespace, I can see that all the extents are adjacent – so you could say the table is “logically fragmented” but “physically contiguous”.

Does this example of fragmentation have any impact on the performance your system ? Since most I/O done by Oracle operates at the block level (we read data blocks into the db cache, we write data blocks to files), and the location of the block within any particularly extent is irrelevant, the answer is probably no. But there are times when we try to read multiple adjacent blocks with a single read request (tablescans and index fast full scans); does it matter that our “physically contiguous” table is “logically fragmented” into lots of extents ?

What if the extents are (say) only 64KB each, does this limit the size of the “db file multiblock read” requests that we will be making or can  those reads cross extent boundaries ? What if the tablespace is made up of two (or more) files so that the extents generally “round-robin” between files – does this affect the way the reads can operate ? What if we try to do a parallel tablescan -are the restrictions on “direct path reads” different ? If you’re running a datawarehouse that spends a lot of its time doing this type of operation then these are just some of the questions you need to answer. (See, for example, a note I wrote three years ago about some of the anomalies of I/O sizes when running parallel query, and a related enhancement in 11g described by Christian Antognini a couple of years later.)

It’s only after you start to think clearly about what you mean by “fragmentation” that you can begin to understand the possible problems that it can cause and the reasons why it may, or may not, have an impact on your system. In part two I’ll make some comments about the way you should think about fragmentation at the disk level and the tablespace level.

[Further reading on Fragmentation]

6 Comments »

  1. [...] 8-Introduction to Fragmentation in Oracle Jonathan Lewis-Fragmentation-1 [...]

    Pingback by Blogroll Report 09/07/2010 – 16/07/2010 « Coskan’s Approach to Oracle — July 16, 2010 @ 4:32 pm BST Jul 16,2010 | Reply

  2. [...] Lewis introducing a new series about fragmentation. In this post he defines what he means by fragmentation. Alex is looking forward to reading the next bits. Charles Hooper shows how to present performance [...]

    Pingback by Log Buffer #195, A Carnival of The Vanities for DBAs | The Pythian Blog — July 16, 2010 @ 5:18 pm BST Jul 16,2010 | Reply

  3. [...] 介绍 By Jonathan Lewis Translated By Jametong [...]

    Pingback by 碎片(Fragmentation)–介绍 « a db thinker's home — July 27, 2010 @ 7:18 am BST Jul 27,2010 | Reply

  4. [...] Introduction – with links to parts 2 – 4 [...]

    Pingback by Fragmentation 4 « Oracle Scratchpad — September 6, 2010 @ 12:53 pm BST Sep 6,2010 | Reply

  5. [...] Introduction – with links to parts 2 – 4 [...]

    Pingback by Fragmentation 3 « Oracle Scratchpad — September 6, 2010 @ 12:57 pm BST Sep 6,2010 | Reply

  6. [...] Introduction – with links to parts 2 – 4 [...]

    Pingback by Fragmentation 2 « Oracle Scratchpad — September 6, 2010 @ 12:58 pm BST Sep 6,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers