Skip to Content

Stefan Koehle 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

image

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

image

 

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

image

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

 

image

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Stefan Koehler
    Hello Jim,
    thanks for your update on this topic.
    I have introduced the index key compression in our productive logistic system a while ago, but didn’t write a blog about my experience (shame on me).

    I saved (without the normal reorg benefit) round about 520 GB disk space. In my analysis i don’t only look at the biggest indexes, i also identified the most used indexes and some custom ones, that i thought of.

    My experience shows that the index key compression improves the performance on archiving strcutures (ZARIX*) and FI indexes (like GLPCA) drastically.

    The other indexes doesn’t impact the performance very much… it only saves disks space.

    Just my experience with it.

    Regards
    Stefan

    (0) 
  2. Fidel Vales
    Nice to hear some good and “final” experiences of the index compression.

    May be you could take a look at the AWR data (if still available)

    I “assume” that the improvement is due to the different use of the indexes in both systems, may be on the first you compressed the most used indexes.

    (0) 
  3. Iyyappan MR
    Hi,

    It was quite interesting analysis. Post implementation of Database flashback (hope you  have seen my blog on db flashback), i was looking for implementing this.

    Looks you left the CPU consumption anlaysis. I read some of the oracle documents that stats the CPU bottleneck would be there due to the compression.

    Did you notice this?

    Shall test this and revert with my anlaysis too.

    Regards,
    Iyyappan MR

    (0) 
    1. Jim Spath Post author
      Iyyappan: I have not looked at CPU consumption per statement.  As the overall database time decreased on one system, and was stable in the other, it doesn’t appear that CPU performance has degraded.  See my followup Indexes lose weight, but is that the answer? for a deeper dive into the root cause of database performance improvements.

      Martin Frauendorfer from SAP pointed me to SAP notes 853576 and 1257075 regarding the Automated Workload Repository. That may tell you more specific CPU metrics than the ST03 values I typically review.

      Jim

      (0) 

Leave a Reply