Index dynamics
Today I will start with a series of 3 posts depicting the follow up of 4 index behavior on a Production database.
The purpose of this exercise is try to unveil the morphology these four indexes take during a given period of time, under what kind of load, model it in a graphical way. Since indexes are quite a black box, this exercises also proposes a complementary procedure for index quality measure.
Experiment subjects were selected using information on DBA_SEGMENTS+DBA_HIST_SEGMENTS, with focus on size and activity, indexes were choosen within the 400Mb-500Mb range thinking on fast ALTER INDEX REBUILD and fast ANALYZE VALIDATE STRUCTURE.
Our four candidates are (names were replaced in order to protect their identity)
OBJNAME BLOCKS BYTES HEIGHT PCTUSED -------------------- ---------- ---------- ------ ------- INDEX1 62464 511705088 4 69 INDEX2 60416 494927872 4 53 INDEX3 61056 500170752 4 45 INDEX4 54272 444596224 4 63
We have our “Before” snapshots, now we need to rebuild all four indexes and take “Initial” snapshots to start the experiment. After this operation our figures are:
OBJNAME BLOCKS BYTES HEIGHT PCT_USED -------------------- ---------- ---------- ------ -------- INDEX1 41600 340787200 4 95 INDEX2 34176 279969792 4 95 INDEX3 28032 229638144 3 95 INDEX4 37888 270445017 4 95
Note the amount of space “empty” the indexes had before rebuild, for INDEX2 and INDEX2 we may ask ourselves “How are the tables used in order to get index keys so dispersed?”. INDEX1 and INDEX4 seem within normal parameters. All four indexes now have the same initial condition on 5% free space.
On October 31th, Friday … Hallowen, we are going to see what has been happening with our indexes;and who knows, you may testify witchcraft… or, is it science? What do you think?
Keep in touch…
See next part of this story on Index Dynamics – Part II
1 comment so far
Leave a reply
[...] View starting post: Index Dynamics – Part 1 [...]