Exadata Storage Index Aging – Part 3 – Analysis on Test 2B

Continued from previous post.

Based on the observations below, it looks as if there are more mechanisms for offloading than a max of 8 columns in the storage index array. It seems that the 8 columns parameter is a minimum parameter for retention (_cell_storage_index_columns = 8). If columns 1-10 have all been offloaded, but a post test still shows a similar, but different set of columns (look closely at 1, 19, 20 and 21) retained then the total number of columns must have risen above the 8 columns in the storage index array. I’d just attribute this to the fact that the array items are only created for 1Mb sections of each segment storage region, but the same percentage of offload occurs on the In Line (IL) tests as previously tested on an earlier IL test.

This is good news for the end users meaning there there is some elastic capacity to the storage index array above the 8 columns specified. It seems that although more than these specific columns were offloaded, but once activity died down only 8 remained active. So 8 seems to be a minimum and not a maximum.

But, still need to understand why 1, 21, 20, and 19 seem to persist longer? Was it previous activity or something else in the data? Other possibilities?

—————————————————————————————————————————

Observations
Looking at the data from Test 2B here are some summary observations of the changes:

Baseline Test:
8 offloaded columns retained from previous actions. Columns (in order of testing): 1, 7, 8, 9, 10, 19, 20, 21

Test Columns 1-10:
All ten columns get storage index offloads, but column 1 is offloaded on first try and has not been aged out of the storage index array.

IL Test – All Columns – After test on columns 1-10
Offloads not aged out in order of column testing: 10, 9, 8, 7, 1, 21, 20, 19

Test Columns 2-11:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 2-11
Offloads not aged out in order of column testing: 11, 10, 9, 8, 1, 21, 20, 19

Test Columns 3-12:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 3-12
Offloads not aged out in order of column testing: 12, 11, 10, 9, 1, 21, 20, 19

Test Columns 4-13:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 4-13
Offloads not aged out in order of column testing: 13, 12, 11, 10, 1, 21, 20, 19

Test Columns 5-14:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 5-14
Offloads not aged out in order of column testing: 14, 13, 12, 11, 1, 21, 20, 19

Test Columns 6-15:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 6-15
Offloads not aged out in order of column testing: 15, 14, 13, 12, 1, 21, 20, 19

Test Columns 7-16:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 7-16
Offloads not aged out in order of column testing: 16, 15, 14, 13, 1, 21, 20, 19

Test Columns 8-17:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 8-17
Offloads not aged out in order of column testing: 17, 16, 15, 14, 1, 21, 20, 19

Test Columns 9-18:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 9-18
Offloads not aged out in order of column testing: 18, 17, 16, 15, 1, 21, 20, 19

Test Columns 10-19:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19

IL Test – All Columns – After test on columns 10-19
Offloads not aged out in order of column testing: 19, 18, 17, 16, 15, 1, 21, 20

Test Columns 11-20:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19, and 20

IL Test – All Columns – After test on columns 11-20
Offloads not aged out in order of column testing: 20, 19, 18, 17, 16, 15, 1, 21

Test Columns 12-21:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19, 20, and 21

IL Test – All Columns – After test on columns 12-21
Offloads not aged out in order of column testing: 21, 20, 19, 18, 17, 16, 15, 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s