Oracle Object Fragmentation Test
Fragmentation of tables and indexes may reduce performance, depending on the way data is accessed. Fragmentation also leads to greater overall storage space usage.
Table fragmentation will result in longer query times when a full table scan is performed. Since data is not as evenly packed in the data blocks, many blocks may have to be read during a scan to satisfy the query. These blocks may be distributed on various extents. In this case, Oracle must issue recursive calls to locate the address of the next extent in the table to scan.
Index fragmentation may bring a higher penalty to application performance. When accessing data through an index and an index range scan, Oracle must read each block in the specified range to retrieve the indexed values. If the index is highly fragmented, Oracle may have to search many more blocks, and possibly levels, to get this information, thus delaying query processing and degrading overall performance.
The first step to resolving the performance threat posed by fragmentation is to identify which objects (tables, indexes, or both) are fragmented. The Oracle Object Fragmentation test helps in this regard. This test scans a pre-configured object sample for high and very high levels of fragmentation, and reports the count of fragmented objects. Using the detailed diagnosis capability of the test, you can also quickly drill down to the specific objects that have been fragmented. You can thus proceed to rebuild the fragmented objects to reduce disk I/O.
This test is disabled by default. To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Oracle Database as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.
Target of the test : An Oracle server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for every DisplayName configured for the object name parameter of this test.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Highly fragmented Oracle objects: |
Indicates the number of highly fragmented objects of this type. |
Number |
If 30% - 49% of an object is found to be fragmented, then such an object is counted as a highly fragmented object. Table Fragmentation occurs when we update/delete data in table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reused ever). This leaves behind holes in the table, which results in table fragmentation. When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses. Index fragmentation is characterized by splitting and spawning. Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally. Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes. Both these phenomenon are key performance degraders. This is why, a high value of this measure, if left unchecked, can cause disk I/O to mount, queries to run for long periods, and the overall performance of the database server to deteriorate. Use the detailed diagnosis of this measure to identify the highly fragmented objects and the percentage fragmentation of each object, so that you can understand how badly that object is fragmented and can proceed to rebuild it. |
Very highly fragmented Oracle objects: |
Indicates the number of objects of this type that are very highly fragmented. |
Number |
If 50% or more of an object is found to be fragmented, then such an object is counted as a very highly fragmented object. Table Fragmentation occurs when we update/delete data in table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reused ever). This leaves behind holes in the table, which results in table fragmentation. When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses. Fragmentation is characterized by splitting and spawning. Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally. Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes. Both these phenomenon are key performance degraders. This is why, a high value of this measure, if left unchecked, can cause disk I/O to mount, queries to run for long periods, and the overall performance of the database server to deteriorate. |