03 April 2006
Index snipets
B-TREEs
INDEX SCANS
INDEX FRAGMENTATION
REGULAR DBA CHECKS
PRO-ACTIVE ACTIONS:
- Oracle indexes are built using B*-trees
- They are always balanced
- Update is done through delete, then insert
- Each leaf node has a pointer to the next leaf node
- Each leaf node contains indexed value and corresponding ROWIDs
- Deleted blocks are re-used for inserts or eventually cleaned out and put back to the free list
- CF show you how many table reads have to be done for a full index scan
- The worst possible scenario would be #of rows
- The best scenario would be #of table blocks
- Rebuilding a table to be sorted like a index is the solution (if possible)
- Index pre-fetching is helpfull (_index_prefetch_factor and _db_file_noncontig_mblock_read_count hidden parameters)
- Oracle batches single-block reads
INDEX SCANS
- Index full scan will scan all leaf blocks
- Sequential read is used
- Corresponding wait is "db file sequential read", single block waits (p3=1)
- Index fast full scan will skip the B-tree and just scan the index segment
- Can use multiblock reads
- If FFS is slow, it is worth considering an index rebuild
INDEX FRAGMENTATION
- Happens for sparse deletes
- If PCTFREE is too high
- Sometimes for indexes with random inserts
- Index rebuilds give the best performance gains for FFS
REGULAR DBA CHECKS
- Indexes larger than tables
- De-fragmented indexes (some of them might be worth rebuilding)
- Indexes with low selectivity (selectivity: Distinct values#/all values#)
- Foreign keys without indexes
- Tables with no indexes
- Overlapping indexes
- ANALYZE INDEX
VALIDATE STRUCTURE; - Check INDEX_STATS view
PRO-ACTIVE ACTIONS:
- Monitoring indexes.
- ALTER INDEX
MONITORING USAGE ; - Check v$object_usage afterwards
