Ver este artículo en EspañolView starting post: Index Dynamics - Part 1
As I've promised, today will share with you mid-term results for my index observations.
First we may see a graph of Used Space, as reported by column PCT_USED of table INDEX_STATS (right after an ANALYZE over each index). This percentage accounts the space allocated to the B-Tree that is used.
There is one line for every index we are considering, and note the legend on the graph showing the index name and the number of columns inside parenthesis.
What can be observed in this chart?
1) After rebuild (done on saturday), every index starts with 95% used space.
2) First day is sunday, the system had almost 0 activity, therefore our indexes' space usage show slight changes.
3) Starting on monday, INDEX1 reported wrong data for PCT_USED and the other indexes began their "decay" trend.
4) After a whole week of activity, indexes gained free space, some of them faster than others (for instance INDEX4 went from 95% to 84% used space, that is 11% on 5 days).
Point 3 raised a service request with Oracle.
Point 4 may be explained in terms of:
a) Table transactionality (how many insertions/deletes/updates it had)
b) Index type, if unique or non-unique.
c) Number of columns conforming the index.
d) Type of every indexed column.
I may propose the following hypothesis: the index used space decay rate is directly proportional to the table's transactionality, to the number of columns and types of them, and inversely proportional to the type of the index (Unique or non-Unique) and block size.
As you already know, B-Tree indexes have two types of nodes: called Branches and Leafs.
Now, let's see where is that space allocated, look this chart that shows increment or delta on daily samples taken from column LF_BLKS.
You may see a great saving due to rebuild, however that saving fades slowly during the following days. Next chart makes a zoom, in order to watch closely the variation rate experimented during those days.
Keep in mind the rate at which leaf blocks are incremented, later you'll see how it's related to new key insertions (transactionality).
What about the branches? ... that's what we're going to see on next chart: the behavior of those indexes for branch blocks. I've taken the sampled value for BR_BLKS and got the variation rate versus the previous day.
Please observe, the stepped peak for INDEX2 and INDEX4, during the first day of activity the number of branch blocks almost doubled, that means an intense reorganization within the index. That may be caused due to the nature of these indexes (all are UNIQUE) and a high transactionality rate.
For the INDEX3, we observe that the increase is splited between 2 days, Sunday and Monday. This table presented activity the day after the rebuild was done.
We have to ignore the INDEX4, cause their figures are not reliable.
Why is there a higher increase on the number of branch blocks? We may answer that question recalling the percentage of free space (5%) we had after the rebuild, that is a very small margin for a leaf block, and the chances of split increase if we have a UNIQUE index. We must remember that a leaf block split, may involve a branch block split.
Our partial conclusions may be stated as follow:
1) Depending on the percentage of free space, after rebuilding indexes, their state becomes "less" stable.
2) Indexes tend to take a "stable" form, with the pass of time.
3) The branch blocks are the stressed part of the B-Tree right after the rebuild.
I will finish this experiment next Saturday, and share with you all remaining findings next Tuesday; yes, seven days from now... or eight days? anyway...
I hope to get near a mathematical model for the Index Decay Rate, cross fingers.
Thank you for reading, keep in touch!
Follow to next post Index Dynamics - Part 3
Subscribe to Oracle Database Disected by Email
Bookmark this on Delicious
No comments:
Post a Comment