stefan.koehler/blog blogged a year ago on "[Oracle] Index key compression" describing the technical underpinnings of a way to save space, and perhaps, get a little performance boost to boot. I found a system behavioral change, and want to tie this together back to a root cause. In his blog, Stefan asked "If someone has already implemented index key compression in his environment - please feel free to post your experiences."
Graph 1
This chart shows a dramatic performance improvement in one of our SAP databases. Initially, I saw the trend on monthly data from ST03, with March looking better than prior months, on both database time, and overall response time. Changes of 50 to 100 milliseconds are not uncommon, after configuration, support pack, code or patches, but this was over 200 milliseconds. Systems don't normally go from 1 second total resonse time to 0.8!
Often, when I am reviewing history, it is because something got worse, and either someone noticed, or we want to fix it before someone notices. Not only do systems rarely spontaneously improve, if we perform tuning miracles behind the scenes, no one notices. Or if they do, they ask how much better we can make it.
When I asked the lead DBA what changed recently, she reminded me they had done Oracle index key compressions on the weekend of March 15th. I shared the monthly chart and promised to look at the weekly data right away. The first graph has the results, clearly showing that database response time was cut just about in half after that date. The drop begins that week, which is a little strange, as it should have begun after that week, but the daily values are lost in the aggregate.
Graph 2
The second graph shows another SAP database, where similar index compression was done. In this case, no performance boost occurred; it even fluctuated up slightly late in March. Nothing alarming, just no gain like the first one.
I will be looking back at other indicators, especially the busiest SAP transactions and SQL statements, to see if I can show the correlation between the index changes and business productivity, and update the blog with my findings.
Graph 3
This graph illustrates the space recovered by using the compression feature on all indexes in the system, from the DB02 transaction. That savings is over 200GB if your scientific notation is a little rusty. You might also notice the inexorable rise in index space after the compression. The DBAs shrank the 12 largest indexes, meaning the next drop won't be so steep.
Graph 4
Finally, space saved on this database was also over 200GB, though growth continues after. While each system had about the same decrease of index size on disk, why did only one have a noticeable performance boost at the system level? We believe the reason is that the improved system has more ABAP joins in the code, though we don't want to state any theories before we collect more evidence.
Smaller space means faster backups, faster copybacks, quicker startup time, etc. While the primary reason for index compression would be to spend less money on disk space, we'll certainly take the dividend of better user experience.
References
SAP Note 1109743 - Use of Index Key Compression for Oracle Databases
ASUG Fall 2003 Conference Proceedings (Business Information, Technology & Infrastructure Forum) Paul Loos, Oracle Corporation, Oracle 9 I – New features: Focus on Compression (slides available on request)
UPDATE
A week later, I discovered more about the root cause of our database performance improvement. As it is tangential to index compression, I posted it separately.
4 Comments
