Measuring Performance with ASO Fragmentation

It's true that ASO fragmentation takes place when performing physical partial clears of data. For example, I loaded some data into ASOSamp and the ess00001.dat file for the database is 562,036,736 bytes. If I clear out Prev Year data then the ess00001.dat file increases to 847,249,408 bytes. A restructure of the outline shrinks it back down to 285,212,672 bytes. Note: Only certain restructures will clear up the fragmentation issue. For example, adding a member to a dynamic dimension will but adding a member to a stored dimension will not. Click here to see the various levels of ASO outline restructuring.

What I'd like to do today is measure the retrieval performance associated with fragmentation. I will use the data I mentioned above for four tests. The elapsed time is recorded next to each scenario in parenthesis.

Test 1: Run simple query with no aggregate views. Record elapsed time for each scenario.

Scenario 1: Load just Curr Year data. (0.68s)
Scenario 2: Load data in Curr Year and Prev Year but then clear out Prev Year. (0.68s)

Test 2: Run zoom-in query with no aggregate views. Record elapsed time for each scenario.

Scenario 1: Load just Curr Year data. (3.548s)
Scenario 2: Load data in Curr Year and Prev Year but then clear out Prev Year. (3.56s)

Test 3: Create 50% aggregate views. Record elapsed time for each scenario.

Scenario 1: Load just Curr Year data. (33.71s)
Scenario 2: Load data in Curr Year and Prev Year but then clear out Prev Year. (32.13s)

Test 4: Export data.

Scenario 1: Load just Curr Year data. (19.632s)
Scenario 2: Load data in Curr Year and Prev Year but then clear out Prev Year. (19.714s)

Summary

Well that's not very exciting. But it is good news -- fragmentation doesn't seem to impede performance. I feel like I need to run these tests again with much larger ASO cubes to see what happens. Perhaps with a 20 or 50 GB it might make a difference.

Epilogue

The most interesting result is that in Test 3, 50 views were created which increased the ess00001.dat file to 411,041,792 bytes in Scenario 1 but in Scenario 2 the ess00001.dat file does not increase past 847,249,408 bytes. It seems to be using the empty space in the file to store the aggregate views. I don't think it's any coincidence that the size of the file increases by exactly the size of the size of the Curr Year data when the Prev Year data is cleared. So I'm not sure I'd call ASO fragmentation a true fragmentation. Fragmentation implies that there are lots of many empty spaces all over the files and it seems like data is still contiguous in the ess*.dat file. It would be interesting to hear from an Oracle developer how the clear command impacts the files on disk.