Oracle RAC Index Fragmentation Test
Indexes are Oracle database objects that provide a fast, efficient method of retrieving data from database tables. The physical addresses of required rows can be retrieved from indexes much more efficiently than by reading the entire table. Effective indexing usually results in significant improvements to SQL performance.
Oracle’s default index structure is B*-tree, which stands for “Balanced tree.” It has a hierarchical tree structure. At the top is the header. This block contains pointers to the appropriate branch block for any given range of key values. The branch block points either to another branch block, if the index is big, or to an appropriate leaf block. Finally, the leaf block contains a list of key values and physical addresses (ROWIDs) of rows in the database. Theoretically, any row in a table, even a big one, could be retrieved in a maximum of three or four I/Os (input/output operations): one header block, one or two branch block(s), and one leaf block.
The advantages of indexing do not come without a cost. As database objects, indexes are created for tables only and they must be in sync with them: indexes must be updated by the database with every data manipulation language (q) operation - INSERT, DELETE, or UPDATE. Where there are a large number of tables with dynamic data, too many INSERTs, DELETEs, and UPDATEs on the tables can over time, fragment the index. When indexes are fragmented, queries take longer to pull out rows from tables, thereby significantly increasing disk I/O. This adversely impacts overall SQL performance.
The first step to resolving the performance threat posed by fragmented indexes is to identify which indexes are fragmented. The Oracle RAC Index Fragmentation test helps in this regard. This test scans a pre-configured index sample for high and very high levels of fragmentation, and reports the count of fragmented indexes. Using the detailed diagnosis capability of the test, you can also quickly drill down to the specific indexes that have been fragmented. You can thus proceed to rebuild the fragmented indexes 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 the Oracle Cluster as the desired Component type, set Performance as the Test type, choose the 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 : Oracle Cluster
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 indexes: |
Indicates the number of highly fragmented indexes.
|
|
If 30% - 49% of an index is found to be fragmented, then such an index is counted as a highly fragmented index. Ideally, the value of this measure should be 0. A high value indicates high index fragmentation. 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 highly fragmented indexes, and proceed to rebuild them. |
Very highly fragmented Oracle indexes: |
Indicates the number of indexes that are very highly fragmented. |
Number |
If 50% or more of an index is found to be fragmented, then such an index is counted as a very highly fragmented index. Ideally, the value of this measure should be 0. A high value indicates very high index fragmentation. 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 all performance of the database server to deteriorate. Use the detailed diagnosis of this measure to identify very highly fragmented indexes, and proceed to rebuild them. |