|
| Index I/O during inserts | 6 April 1999 |
| During inserts we see a 20:1 ratio of primary key index I/O to table I/O. We're puzzled as to what is causing the large amounts of I/O. | ||
 | Have you attempted a large delete from the table that was then rolled back? It sounds like there are a lot of blocks on the index freelist(s) that cannot be unlinked from the B*-tree structure. For each index insert requiring a new block to be linked into the tree, Oracle will attempt to reuse up to 5 blocks from the freelist(s) by default. However, because the transaction that put those blocks on the freelist may later have rolled back, Oracle does not unlink blocks from the B*-tree structure until they are reused. Unlinking requires access to both sibling leaves and the parent, which translates to a lot of I/O. |
|
| Which indexes to rebuild | 5 May 1999 |
| We do not have a window to run analyze index. Is there any other way to determine if any of my indexes needs to be rebuilt? | ||
|
What about taking a treedump of the indexes.
For Oracle7, first you need the decimal data block address of the root block for the index.
You can get that with DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS.
The root block is normally the block immediately following the segment header block.
Then use the following command:
alter session set events 'immediate trace name treedump level nnnnnnnn'; That will give you a trace file with the one line for each block in the B*-tree and a row count for each block. If there are large numbers of empty or nearly empty blocks, then the index is a good candidate for being rebuilt. For Oracle8, you need to use the OBJ# of the index segment as the level for the event. |
|
| Blocks in use by an index | 15 June 1999 |
| If I use DBMS_SPACE, it reports my index as having 6145 blocks and 3220 unused blocks => 2925 used blocks. If I analyze and validate the index, INDEX_STATS reports 2913 LF_BLOCKS and 11 BR_BLOCKS => 2924 blocks in total. Am I right in assuming there is one overhead block for an index that INDEX_STATS does not include? | ||
| Yes, there is always a segment header block, which accounts for the difference in this case. There can also be multiple freelist blocks if freelist groups have been specified, and extent map blocks if the segment has more extents than can be listed in the extent control table in the segment header block. |
|
| Right-hand indexes | 2 November 1999 |
| Is there any way to check for right-hand indexes? (indexes that get inserted into at the same point by many processes). | ||
| If you get buffer busy waits in the data block class that map to an index and it a primary key index based on a sequence number, then the chances are that it is the right-hand index problem. Failing that, you can check the number of transaction slots in the leaf blocks of such an index by taking blockdumps. That will tell you how many processes inserted concurrently into each block (allow one slot for the recursive transaction to split the block). If you are concerned that the index may be degraded due to deletion at the left side, then you can check that in any of the standard ways for checking index status. However, my choice in this case would be to take a treedump. |
|
| Bitmap index cardinality | 13 November 1999 |
| I have heard varying suggestions on the maximum number of distinct values for a column where bitmap indexes are still a good idea. I have heard anecdotal evidence that in Oracle8 (and by extension 8i) the use of a bitmap indexes for up to 99 distinct keys is still more efficient for both speed and storage than a simple index. | ||
| In a benchmark done by Cetin Ozbutun of the Oracle Support Center of Expertise in early 1997 it was found that bitmap indexes still outperformed simple indexes even at relatively high cardinalities. In one test, the break-even point was above 500,000 distinct values for a 1,000,000 row table. The normal caveats about storage and performance degradation due to DML still apply. |
|
| Index internals | 10 December 1999 |
| What happens when a brand new index is created? How many nodes does it have? If it has one root and one leaf node, what information is stored on the root node? Is there data on the branch nodes? Does an index expand horizontally or vertically? How much can it expand horizontally and how much vertically? When does an index block split? | ||
| If you mean a new index on an empty table, it has just one block which is a leaf block and serves as the root. As keys are inserted, this block will split. The split consists of distributing the keys to two new blocks, and changing the format of the existing block to the branch block format. Whenever the root block splits, two new blocks are allocated to hold its former keys, and the existing block is retained as the root. This ensures that the root block never moves. All other splits just result in the allocation a sibling block to take "half" the keys, and the insertion of a new key and pointer into the parent block (which can in turn cause that block to split). Index block splits happen in recursive transactions, and commit independent of the primary transaction. The branch blocks contain only a minimal distinct leading portion of each branch key. Indexes expand horizontally at first, and then vertically when necessary. There is no limit on the depth/height of an index, and the branching factor is determined by the key size and database block size. Blocks only split when they fail to accommodate a new key. |
|
| Indexes - balanced but skew | 27 December 1999 |
| Can index branch and leaf blocks get skewed if an index is created prior to loading the table? My theory is that if the index is created after all rows have been inserted, when the candidates values the for root block and branch blocks are available, then the index creation will be able to be organized in balanced manner, but not otherwise. Is this right? | ||
|
B*-tree indexes are balanced by definition - that is, all leaves are at the same level.
However, Oracle's implementation does not prune disused branch blocks.
The reason is that when keys are deleted, such that leaf blocks are emptied,
those leaf blocks cannot be unlinked from the B*-tree structure, lest the transaction need to rollback.
So emptied leaf blocks are placed on a transaction freelist for the index, and only unlinked from the B*-tree prior to reuse in another location,
and this algorithm is not applied recursively for branch blocks.
So although B*-trees are balanced by definition, the Oracle implementation does not cut out the dead wood,
and thus the distribution of the leaves can appear to be skewed.
This only happens because of key deletion.
The pattern of key insertion does not affect the distribution of leaves in any way.
However, it does affect their density. When a high-order key is inserted, such that the right-hand block needs to split, Oracle places only the new key value into the new leaf block. Thus when keys are inserted in ascending order, such during index (re)creation, the data density of the leaf blocks can be maximal. Whereas, if keys are inserted otherwise, the effect of block splits is such that leaf block data density will approach 75% of its theoretical maximum. That is, on average blocks will be half way between 50% for recently split blocks, and 100% for completely full blocks. |
|
| Index treedump | 23 January 2000 |
| Is it possible to find the real size of an index without using ANALYZE? I can find the table size using the ROWIDs, but is there something similar for indexes? | ||
|
You can take an index treedump as follows.
alter session set events 'immediate trace name treedump level nnnnn';This will give you one line in the trace file for each block in the index B*-tree indented to show its level. It also shows how many index entries are stored in each block. Under Oracle8, the level number is the OBJECT_ID of the index, as shown by DBA_OBJECTS. Under Oracle7, the level number is the decimal data block address of the root block. The root block is normally the first block after the segment header. You can calculate its decimal data block address from its file and block numbers using DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS. |
|
| Index block reuse | 14 March 2000 |
| Is it leaf or branch blocks in an index that don't get reused once all of the entries from the block have been deleted? Also, how many index entries fit in a block? How many sub-branches can a branch block refer to? And when do branches split? | ||
| It is branch blocks that don't get reused. The number of keys that fit in a leaf depends on the key size. Keys are compressed in branch blocks to a minimal distinct leading prefix; thus the branching factor can be very high. Branch blocks split when a leaf block split requires a new key to be inserted into a full branch block. |
| Copyright © Ixora Pty Ltd |
|