All About Oracle Database Fragmentation

All About Oracle Database Fragmentation

All About Oracle Database Fragmentation
Craig A. Shallahamer OraPub, Inc.

1. Abstract
There are many types of Oracle database fragmentation. Some are harmful and some are not. An active Oracle database will naturally foster harmful database fragmentation unless the DBA takes proactive application design and space management steps. This paperaddresses tablespace freespace, segment, data block, index leaf block, and row fragmentation. Each fragmentation type is described in detail along with how to detect the fragmentation, how to quantify its intensity, what its performance and administration affects are, and how to resolve only the harmful fragmentation. Various scripts are used throughout the paper and references are made to publiclyavailable tools. The most updated version of this paper is available for free at OraPub's web-site, www.orapub.com .

2. Introduction
Whether you like it or not, database fragmentation continues to be of interest to the Oracle community. Maybe it's because it's fun to share what is happening with your database, maybe it's challenging to overcome some of the harmful affects of database fragmentation,maybe it gives DBAs a sense of job security, maybe vendors instill fear to create a demand for their products, or maybe it's just plain fun to look and see how an application is chopping up your database. Whatever the case, the topic of database fragmentation continues to be of interest. One would think with all the talk, technical papers, vendor products, and Oracle's slow but steady pacetowards a "zero fragmented database," fragmentation would no longer be an issue. But the fact is, fragmentation does not need to be an issue if the DBA proactively and properly manages their database. Very few applications corner the DBA with regards to harmful fragmentation. This leaves one with a basic choice; either proactively manage your database or purchase a product to do it for you. Managingfragmentation yourself is not always the best answer. It depends… It depends on how many excess CPU cycles, how much extra disk space, how much slower performance your users can endure, and possibly how much available database down time is available. If you have plenty of these things, then by all means, purchase a product or use the new on-line defragmentation features Oracle is slowly but steadilybuilding into the kernel. In all seriousness, you do have choice. And you should seriously consider whether it is worth your time to proactively manage harmful fragmentation or let a product/tool do it for you. As you might expect, I believe the answer lies somewhere in between. With this said, one might be wondering why I'm taking the time to write this paper. Well the fact is, people areinterested in database fragmentation and there are many, many sources of partial and misleading information. This is having a harmful impact on databases and wasting DBA's time. There are many types of Oracle database fragmentation. And DBAs need to know that some are harmful and some are not. As you will come to understand, an active Oracle database will naturally foster harmful database fragmentationunless the DBA takes proactive application design and space management steps. That's just the way it is and there is no getting around it. This paper addresses the most important types of database fragmentation. Tablespace freespace, segment, data block, index leaf block, and row fragmentation are covered. Each fragmentation type is described in detail along with how to detect the fragmentation,how to quantify its intensity, what the performance and administration affects are, and how to resolve only the harmful fragmentation.

Craig A. Shallahamer Understanding fragmentation definitions is not enough. Once must understand how to easily detect the fragmentation and then quantify its intensity. Without the ability to quantify, one is in a weakened position to determine the appropriate...