|
|
 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Mike Ault's Blog
|
|
| MikeA |
Wednesday, April 04, 2007 9:15 AM |
In my last blog entry we looked at sizing tables in 10g, of course the other side of that coin is the sizing of indexes. As with tables the Oracle8 manuals had an explanation of how to size indexes, but manuals since then have been strangely silent on the subject. In this blog I will address the sizing of normal, b-tree indexes since they are still the in the majority as far as type of index used in most applications.
In indexes you have root, branch and leaf blocks. Generally speaking the number of leaf blocks will far out-number the quantity of root and branch blocks for any given index. Usually you can disregard the size requirements of the root and branch blocks since they will form less than 1 percent of a large indexes size.
The general formula for index sizes in Oracle10g is based on the amount of space left in a block after allowing for block overhead:
Max Space=(blocksize*(1-pctfree/100))-(block overhead+row overhead)
Where:
Blocksize is the size of a given block in bytes
Block overhead is the overhead associated with each block (about 113 bytes) that oracle uses for tracking the block components
Row overhead is the overhead associated with each row, usually the length of a ROWID (10 bytes) plus 1 byte for each column in the index, plus 2 (I have no idea what the 2 is for, but if you don’t add it you come out with the wrong answer.) You also allow for PCTFREE
Once you have the maximum space available for index entries you multiple the average row length in bytes times the expected number of rows and then divide by the maximum available space per block to get the expected number of blocks for the index.
Of course it is usually easier to add in the row overhead to the average row length then just subtract the constants from the blocksize and do the math to get the blocks. As usual I have created a spreadsheet to the drudge work away.
|
Oracle10g INDEX Size Calculation Spread Sheet
|
|
|
|
|
|
|
|
|
|
|
Table:
|
|
Index:
|
|
Percent Filled (non-unique):
|
100
|
|
|
Blocksize:
|
4096
|
Pctfree:
|
10
|
Initrans:
|
2
|
1
|
|
|
# of Rows:
|
600572
|
ROWID Len
|
10
|
|
2
|
|
|
|
|
|
|
|
|
|
|
|
|
Column Name
|
Size
|
Corrected Size
|
|
|
|
|
|
Test1
|
|
4
|
5
|
ARL:
|
27
|
|
|
|
Test2
|
|
4
|
5
|
AS:
|
3526
|
|
|
|
Test3
|
|
4
|
5
|
RPB:
|
131
|
|
|
|
|
|
0
|
0
|
# Blocks:
|
4585
|
|
|
|
|
|
0
|
0
|
Size:
|
18.81
|
M
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
Not Required
|
|
|
|
|
|
0
|
0
|
|
Required
|
|
|
|
|
|
0
|
0
|
|
Calculated or Derived
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
0
|
|
|
|
|
|
|
|
0
|
27
|
|
|
|
|
In testing various indexes using this sizing formula and varying the blocksize between 2 to 16K I got the following results:
Calculated verses Actual Index Size 600K Entries
|
BS
|
Cal Blk
|
Act Blk
|
Cal Meg
|
Act Meg
|
Diff
|
|
16384
|
1153
|
1152
|
18.015625
|
18
|
1.00
|
|
8192
|
2328
|
2304
|
18.1875
|
18
|
1.01
|
|
4096
|
4767
|
4864
|
18.621094
|
19
|
0.98
|
|
2048
|
9846
|
10240
|
19.230469
|
20
|
0.96
|
Table Showing Results For 600,572 Entries
|
BS
|
Act Blk
|
Toad Meg
|
Act Meg
|
Diff
|
|
16384
|
1152
|
16.19
|
18
|
0.8994
|
|
8192
|
2304
|
16.37
|
18
|
0.9094
|
|
4096
|
4864
|
16.75
|
19
|
0.8816
|
|
2048
|
10240
|
17.59
|
20
|
0.8795
|
Table Showing Toad Results For 600,572 Entries
A larger index containing 6,001,215 entries was also checked using the formula for different blocksizes, the results paralleled those at the smaller size index:
Table Showing Results For 6,001,215 Entries
|
Blocksize
|
Cal Blks
|
Act Blks
|
Cal Meg
|
Act Meg
|
Diff
|
|
16384
|
11931
|
11776
|
186.4219
|
184
|
1.013162
|
|
8192
|
24102
|
23552
|
188.2969
|
184
|
1.023353
|
|
4096
|
49191
|
49152
|
192.1523
|
192
|
1.000793
|
|
2048
|
101716
|
102400
|
198.6641
|
200
|
0.99332
|
Table of Results for 6,001,215 Entries
|
Blocksize
|
Act Blks
|
Toad Meg
|
Act Meg
|
Diff
|
|
16384
|
11776
|
162
|
184
|
0.880435
|
|
8192
|
23552
|
164
|
184
|
0.891304
|
|
4096
|
49152
|
167
|
192
|
0.869792
|
|
2048
|
102400
|
176
|
200
|
0.88
|
Toad of Toad results for 6,001,215 Entries
As you can see for a larger index the results actually came closer percentage wise in size than for a smaller index.
So again we see that we are within 12 percent using Toad and using manual calculations, for the indexes tested, we are within 4 percent of actual size for indexes.
So with last months entry and this one, you should be armed with the knowledge to size standard Oracle tables and indexes in Oracle10g. In next weeks entry we will look at sizing a table that may have a lob entry or entries either stored inline or out of line.
|
| Copyright ©2007 Quest Software Inc. |
| Permalink |
Trackback |
|
|
|
|
|
|
|
|
|