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

Exadata Storage Index Aging – Part 2B

This is the third blog in a series looking at Storage Cell Storage Index Predicate Offloads. Continued from previous post. And there’s more here: Analysis for Test 2B click here.

For Test 2B – This corrects the columns with very little offload so that they get between 60-100 percent offload from the storage index. See the previous 2 blogs for more background information.

Results:
Now we have offload for each test segment and 8 columns offloaded for each In Line (IL) test as expected. We can even see a pattern on when columns are released (aged out) after repeated queries on the 8 columns.

The pattern I see is that after 10 queries columns 1, and 7-21 seemed to keep their offload status without aging out. But what is going on with column 1 that keeps it from aging out for the entire test. And what about 2-6 that seem to age out fairly fast after their query run with a storage index offload.

I see some patterns developing, but may need more eyes info or tests to figure this out. Will continue when I can.

Let me hear your ideas.

Here’s the test sequence again:

Test 2 - Test Plan

Test 2 – Test Plan

Here’s the full output in excel format; this time run to completion:
dcox_exadata_storage_index_aging_test2b