cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle comression in SAP environments

Former Member
0 Kudos

Hi,

I have came accross OSS Note 1109743 - Use of Index Key Compression for Oracle Databases.

This seems as quite interesting feature for me to be applied on few of my I/O bound systems (CPU is not an issue on database servers). Most of the CPU on my central instance servers as per ST06 is spent in IO wait %.

My systems are of SAP version Netweaver 2004s with Oracle 10.2.0.2 as a database. HW is HP UX 11.11 PA RISK.

I have red carefully through 1109743 note, but I would like to know more about the feature from someone who had implemented it.

- Is there any watchouts?

- how can I estimate benefit of doing index key compression and see the benefit?

- What would be the best approach to start with (choose biggest tables or biggest indexes, or choose SQL statements with most disk reads and compress indexes that are used in these?)

- How much impact it will have on CPU? Can it be somehow estimated?

- what would happen if already compressed index is rebuild via Brspace? is compression going to be gone if we use brspace with default parameters?

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

>

> Hi,

> I have came accross OSS Note 1109743 - Use of Index Key Compression for Oracle Databases.

Hi Andrija!

Reading the correct note/documentation first - very good!

> This seems as quite interesting feature for me to be applied on few of my I/O bound systems (CPU is not an issue on database servers). Most of the CPU on my central instance servers as per ST06 is spent in IO wait %.

> My systems are of SAP version Netweaver 2004s with Oracle 10.2.0.2 as a database. HW is HP UX 11.11 PA RISK.

> I have red carefully through 1109743 note, but I would like to know more about the feature from someone who had implemented it.

> - Is there any watchouts?

If there would be anything that is already known to be an issue - the Oracle collegues would had been so kind to mention it in the note

> - how can I estimate benefit of doing index key compression and see the benefit?

Nope - you just have to test it. For each and every index you're going to compress seperately.

The effect or benefit totally depends on what data you have and what you do with it.

Therefore a general estimation cannot be given.

If you want't to measure it:

- take the business processes that use the indexes you want to compress. Define a testscenario you can repeat as you like.

Make sure the testscenario includes loading data, changing data, deleting data and selecting data.

- run the testscenario with the uncompressed indexes a few times (to get an average of the "performance")

- perform the compression - make sure to measure the resources that are important you your business (your time, performance degration of the system by the compression).

- run the testscenario again a few times

- compare the effort you had with the difference of the performance with compressed/uncompressed indexes.

> - What would be the best approach to start with (choose biggest tables or biggest indexes, or choose SQL statements with most disk reads and compress indexes that are used in these?)

Usally it would be a good idea to try to find a transaction that is most important to your business and then go "down" to it's use of indexes. But this can be quite difficult.

So probably a better choice would be to find the indexes that cause most of the logical I/O. To find out you can check the v$segstat:


select * from (
select o.object_name, o.object_type, st.statistic_name, st.value
from
   dba_objects o
   , v$segstat st
where
   o.object_id = st.obj#
and o.data_object_id = st.dataobj#
and o.object_type='INDEX'
and st.statistic_name='logical reads'
order by value desc)
where rownum <=10;

This snippet would give you the 10 indexes that get he most logical reads.

> - How much impact it will have on CPU? Can it be somehow estimated?

Measure the CPU load while the testscenarios are running. (ps, sar, vmstat, whatever you like)

> - what would happen if already compressed index is rebuild via Brspace? is compression going to be gone if we use brspace with default parameters?

BRSPACE uses the ALTER INDEX ... REBUILD [ONLINE]command to rebuild indexes. As long as you don't specify anything different, no changes to the storage parameters (including the compression flag) will be made.

Hope that helps a bit.

KR Lars

Former Member
0 Kudos

Hi Andrija, Lars

There is not much more to say, after Lars's answer. Nevertheless i did a small test on a test system and here are the results. I looked out for an sql having a lot of buffer gets resulting from an index range scan. These obviously will benefit most when doing a compression.

If found this statement with a range scan on the ~3 index:

SELECT DISTINCT "MANDT" , "QNAME" , "DEST" FROM "TRFCQOUT"                                       
WHERE "MANDT" = :A0 AND "DEST" = :A1 AND "NOSEND" = :A2

----------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    50 |  2800 |    17   (6)|
|   1 |  SORT UNIQUE NOSORT|            |    50 |  2800 |    17   (6)|
|*  2 |   INDEX RANGE SCAN | TRFCQOUT~3 |  2212 |   120K|    16   (0)|
----------------------------------------------------------------------

Facts before compression:

- size of TRFCQOUT~3: 256 blocks

- buffer gets per execution: 110

After compression (i did a compress 4):

- size of TRFCQOUT~3: 64 blocks

- buffer gets per execution: 44

This means the execution time will be roughly half than without compression. For larger objects (a 256 blocks index will probably always fit into the buffer cache) and if you are io bound and have plenty of cpu left, the benefit might be even better.

Obviously this is only a small and very specific example. I hope there are no major mistakes, anyhow feedback, questions or further discussion is welcome.

Best regards

Michael

lbreddemann
Active Contributor
0 Kudos

Hi Michael,

I don't see any mistakes here, but just want to point out, that the RFC* indexes are quite special ones in SAP setups.

Due to the extreme way data is loaded and removed into the indexes (removing from the outer left edge, inserting to the most right) these indexes gets special made up CBO statistics and are often rebuild (sometime even regularly).

So, my point here is: better look for another index to try this out. One that is more "usual".

KR Lars

Answers (4)

Answers (4)

Former Member
0 Kudos

A semi-automatic check based on VALIDATE STRUCTURE (attention: locks!) could be:

DROP TABLE ZINDEX_COMP;

CREATE TABLE ZINDEX_COMP

( NAME VARCHAR2(30),

LF_BLKS NUMBER,

OPT_CMPR_COUNT NUMBER,

OPT_CMPR_PCTSAVE NUMBER,

OPT_SAVED_MEM_MB NUMBER);

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SET ECHO OFF

SET LINESIZE 120

TRUNCATE TABLE ZINDEX_COMP;

SPOOL index_comp.sql

SELECT

'ANALYZE INDEX "' || INDEX_NAME || '" VALIDATE STRUCTURE;' ,

'INSERT INTO ZINDEX_COMP -

SELECT NAME, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE, -

ROUND(OPT_CMPR_PCTSAVE / 100 * LF_BLKS * 8192 / 1024 / 1024) -

FROM INDEX_STATS;'

FROM

DBA_INDEXES

WHERE

OWNER = USER;

SPOOL OFF

@index_comp.sql

SET LINESIZE 120

SELECT * FROM

( SELECT

*

FROM

ZINDEX_COMP

ORDER BY

OPT_SAVED_MEM_MB DESC )

WHERE ROWNUM <= 100;

Regards

Martin

Former Member
0 Kudos

Smaller corrections were done in the note (e.g. restricted support section removed, warning of UNIFORM tablespaces with large extent size added). As long as the change is not double checked (4 eyes principle) the note is not visible for customers - even not the previous version. I assume that the not will be checked and released again in the next days.

Regards

Martin

former_member204746
Active Contributor
0 Kudos

The SAP note is available again.

thanks to Martin and Stefan for their feedback.

former_member204746
Active Contributor
0 Kudos

on a DEV system, I fetched back 15% of DB size. not bad on a system that does not have a lot of transactional data. It got 75% back on index D010TAB~0.

I noted that in some cases, compressing indexes took more DB space than its original sizes! The worst was an increase of 3.5% for index S278~0.

So, I will compress indexes that have at least 25% disk space saved and ignore others.

0 Kudos

Hello Eric,

Compression is good when the fields on the index have "repeated" values.

I assume that your index S278~0 do not have many.

Perhaps you can take a look Stefan [blog |https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/9361] [original link is broken] [original link is broken] [original link is broken];

He talks about it:

Many unique value combinations - bad compression (and maybe more space is needed)

former_member204746
Active Contributor
0 Kudos

Thanks Fidel,

I already read Stefan's blog, it is great information.

I was just sharing information from my own experience. I will do the same as soon as I finish the same work on a PRD copy.

former_member204746
Active Contributor
0 Kudos

Ok, I completed the compression of selected indexes on an R/3 4.7 system.

My database is sized at 850GB. I select indexes of all tables that were over 100MB and ran exec ind_comp.get_column('TABLE_NAME') for each of them. I saved 140GB of tablespace space. This means that a full tablespace reorg would free up 140GB of disk space.

note that I needed to increase PSAPTEMP to accommodate compression for indexes from table MSEG.

I compressed indexes at a rate of about 55%. If indexes did not have a compression rate over 25% or better, I would uncompress it back to normal.

A few tables which I did not compress its indexes:

DD03L

MARI

TTREE

PA2002

CKMLKALNR

CMKI1

FUNCT

FUPARAREF

Best compression rate was achieved on many LIS tables (Sxxx tables) with rated of 70% or better:

BDCPS

COEP

AUUMI

GLPCA

HUMSEG

BSIS

ACCTIT

LTAP

BSAS

WLK1

BKPF

Former Member
0 Kudos

Hi Eric

Thanks for sharing your experience. I have two questions after reading your latest post:

- Are you monitoring cpu and phyisical reads (disc io) before and after the compression? Would be interesting to know, if they change significantly.

- As every index rebuild saves a lot of space short time, some amount of saved space might be due to that, and the indexes might again grow a bit in the near future. Or did you rebuild the index, compress and compare these numbers?

Regards

Michael

lbreddemann
Active Contributor
0 Kudos

Hi alltogether,

reading this thread shows that you all took a big effort in trying out and setting up index compression.

Although I'm always happy as well to try out new features, I'm wondering wether index compression bring an economic benefit in it's current state.

As I see it, still a lot of manual work, try and error is involved to reduce the size of indexes considerable.

This work costs a lot of money as it can not be done by low paid staff that works down checklists but only by highly trained and thus expensive database professionals.

On the other hand, storage is getting cheaper and cheaper all the time. Is saving on it really paying off?

So the questions are:

- how much faster are the transaction now, that use the index?

- how much more business transactions can now be performed per unit of time?

- does the business benefit that comes from using compressed indexes outweight the costs of the implementation?

Perhabs it would be a good thing, if there would be a kind of automatic advisory (like the segment advisor) that creates a list of indexes that would benefit from beeing compressed. Could be done e.g. as a special stats-run with the brtools.

KR Lars

former_member204746
Active Contributor
0 Kudos

you monitoring cpu and phyisical reads (disc io) before and after the compression? Would be interesting to know, if they change significantly.

  • I have not done that yet. unfortunately, this is a QAS system and nobody does real work on it.

As every index rebuild saves a lot of space short time, some amount of saved space might be due to that, and the indexes might again grow a bit in the near future. Or did you rebuild the index, compress and compare these numbers?

  • I rebuild indexes online and it compresses it at the same time

On the other hand, storage is getting cheaper and cheaper all the time. Is saving on it really paying off?

  • I believe so. database is smaller, backups will take less time, so more uptime when doing offline backups. Yes, storage is getting cheaper and cheaper, but there is still money to be saved. I plan on saving roughly 15,000$ and this work does not cost 15,000$ in time. And, in theory, you get a 15% performance increase because database buffers will contain more index information because it is also compressed in it.

  • Martin, you provided a good tool. For my part, I took a snapshot of indexes to be compressed using DB02 "Detail analysis" and exported the results in Excel... I did the same thing after the compression so I could compare.

  • 1 more thing, In my system indexes of table MSEG are all stored in its own tablespace PSAPMSEGI. I changed the compress/rebuild command to move indexes in a temporary tablespace named PSAPMSEG2I... I then drop PSAPMSEGI and renamed PSAPMSEG2I to PSAPMSEGI. This way, I really save disks space and it does not involve that much work.

example:

alter index "MSEG~0" rebuild online compress 3 parallel tablespace PSAPMSEG2I;
alter index "MSEG~0" noparallel;

  • if using the old tablespace layout PSAPBTABI or the new one PSAPSR3, in both cases, you will not really save disk space because the tablespaces are usually huge and a full reorg is hard to accomplish.. you will only save space in the long run because your DB won't increase for a while because it will use "free" space created by the compression.

lbreddemann
Active Contributor
0 Kudos

On the other hand, storage is getting cheaper and cheaper all the time. Is saving on it really paying off?

  • I believe so. database is smaller, backups will take less time, so more uptime when doing offline backups. Yes, storage is getting cheaper and cheaper, but there is still money to be saved. I plan on saving roughly 15,000$ and this work does not cost 15,000$ in time. And, in theory, you get a 15% performance increase because database buffers will contain more index information because it is also compressed in it.

.

Hmm... offline backups shouldn't really be an argument nowadays. Databases should be backed up online (RMAN),

How do you get the 15.000$ numbers?

Concerning the 15% performance increase: how did you estimate this?

What about parallel updates on data that is now (due to compression) located in the same block? By reducing the number of blocks you increase the level of parallelity per block and thus increasing the expected number of lock situations.

Of course you can always half automate things like Martins script does - but the core question still is unanswered: does it pay business wise?

best regards,

Lars

former_member204746
Active Contributor
0 Kudos

Hmm... offline backups shouldn't really be an argument nowadays. Databases should be backed up online (RMAN),

How do you get the 15.000$ numbers?

  • When saving space on my PRD server, I will also save disk space after system refreshes on QAS and TST. I plan on saving roughly 500GB and it costs around 12,000$ from the info I got. I will also save on fewer tapes for my backups.

Concerning the 15% performance increase: how did you estimate this?

  • I did not, well, not yet, I referred to the SAP note. I explained this earlier,. The reorg was made on a QAS system with not much activity.

What about parallel updates on data that is now (due to compression) located in the same block? By reducing the number of blocks you increase the level of parallelity per block and thus increasing the expected number of lock situations.

  • I can't say. I'm not that good on Oracle to answer. Maybe Stefan can help!

Of course you can always half automate things like Martins script does - but the core question still is unanswered: does it pay business wise?

  • I made some testing in QAS which is a copy of PRD. I will compress the same indexes and do about the same steps, all these steps were scripted and should be easy to accomplish. The same work would not be interesting in our DEV system which is smaller but involves about the same work.

stefan_koehler
Active Contributor
0 Kudos

Hello Eric,

>What about parallel updates on data that is now (due to compression) located in the same block? By reducing the number of blocks you increase the level of parallelity per block and thus increasing the expected number of lock situations.

> * I can't say. I'm not that good on Oracle to answer. Maybe Stefan can help!

Lars is correct with his exception.

If you compress your index you can have more ITL (=Interested Transaction Lists) waits on the index.

Now it depends on your oracle version and on the object (in which version the object was created) to avoid or to run into ITL waits. If you are using Oracle 10gR2 the MAXTRANS paramter is ignored, if you specify it at object creation (take a look at the link below). Now oracle allocates new ITL slots up top PCTFREE, so this is the limit (in sap environment i think PCTFREE is 10)

You can check this by running a select on the segment statistics:

select OBJECT_NAME, STATISTIC_NAME, VALUE  
from V$SEGMENT_STATISTICS 
where OBJECT_NAME = '<INDEX>' and STATISTIC_NAME = 'ITL waits';

=> MAXTRANS: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses007.htm#g1058547

I hope, that i hit the point of Lars )

Regards

Stefan

former_member204746
Active Contributor
0 Kudos

I am using Oracle 10.2.0.2, so, guess I would hit that issue.

I am not sure I fully understand the problem. What is the big impact of more ITL? I guess this means more wait, but how long could these wait time be like? 1ms ? 1s? 1 minute?

Edited by: Eric Brunelle on May 2, 2008 2:27 PM

stefan_koehler
Active Contributor
0 Kudos

Hello Eric,

> I am using Oracle 10.2.0.2, so, guess I would hit that issue.

You would hit this issue also with oracle 9i, the difference between 9i and 10gR2 is that 10gR2 is ignoring the MAXTRANS option and set it to 255 every time.

>I am not sure I fully understand the problem. What is the big impact of more ITL? I guess this means more wait, but how long could these wait time be like? 1ms ? 1s? 1 minute?

hmm ok to understand the problem we have to go a little bit deeper. How does a DML works internally?

If you change (insert / update / delete) data in an oracle block you need an entry in the Interested Transaction Lists.

Before you can change the data, every session needs such an entry.

So let's say you have created a database object with an intial ITL of 2 (INITRANS) and a maximum ITL of 3 (MAXTRANS).

Now initial 2 ITL entries are reserved at object creation and 2 session can manipulate different data (=rows) in the same block at the same time guaranteed. If a third session want to change data in the same block, it needs a new ITL entry (up to MAXTRANS or PCTFREE), if the ITL "table" can not be extended, the third session has to wait for a free ITL slot and this is shown as an ITL wait. (wait event enq: TX - allocate ITL entry)

For more information about the wait event "enq: TX - allocate ITL entry" take a look at the following link (point 10.3.7.2.4 TX enqueue):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i18202

So if you use a compressed index and have many parallel DML statements you can hit more ITL waits as before.

The session is waiting until a ITL slot is free.

Regards

Stefan

lbreddemann
Active Contributor
0 Kudos

>

> Hello Eric,

> >What about parallel updates on data that is now (due to compression) located in the same block? By reducing the number of blocks you increase the level of parallelity per block and thus increasing the expected number of lock situations.

> > * I can't say. I'm not that good on Oracle to answer. Maybe Stefan can help!

>

> Lars is correct with his exception.

> If you compress your index you can have more ITL (=Interested Transaction Lists) waits on the index.

Actually I did not mean the ITL issues (which do appear every now and than, no question).

What I meant was cache buffer chain latches. Think of heavy parallel accesses to nearby indexed values. (e.g. NRIV).

Well - anyhow - my point was not to discuss possible performance issues on indexes, but to show, that although index compression is a nice feature, it's monetary benefit needs to be shown.

It sounds all nice - no question. And I don't know a single case yet, where index compression had caused problems.

It's just the question if it really pays off to do the extra work.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

I don't think that index compression will negatively impact "latch: cache buffers chains" waits. Just take into account that the index root block has to be read during each index access - independent of activated compression. It is quite unlikely that any other index block will be accessed as frequently as the root block. If significant "latch: cache buffers chains" waits exist, they are typically related to the root block in the first place.

Kind regards

Martin

lbreddemann
Active Contributor
0 Kudos

Hi Martin,

that explanation seems reasonable (as usual).

thanks and best regards,

Lars

former_member204746
Active Contributor
0 Kudos

I'm sorry for hijacking this topic, I was about to start testing this aggressively on our QAS server. So, I thought about going to get the latest version of this SAP note.

This note completely disappeared from the SAP note system!

when trying to retrieve SAP note 1109743, I get:

An SAP note with the number requested could not be found.

anybody knows what happened to that note?

stefan_koehler
Active Contributor
0 Kudos

Hello Eric,

that is quite interesting, because i have researched this topic too and also write Jan Klokkers and Christian Graf an email about my intention. I also received an answer from Jan.

As i wrote a blog about that topic this week the note was also valid (21. April), so that is really strange.

Regards

Stefan

former_member204746
Active Contributor
0 Kudos

Thanks Stefan, your blog entry is very interesting.

if you still have Jan Klokkers and Christian Graf's email address, can you contact them? perhaps they are aware of this?

thanks.

stefan_koehler
Active Contributor
0 Kudos

Hi Eric,

i will write them an email tomorrow to get more information and will inform you.

This feature was very nice and useful in some of our cases.. i hope they support this furthermore.

Regards

Stefan

stefan_koehler
Active Contributor
0 Kudos

Hello Andrija,

just to add some additional information.

If you want to know the benefit or maybe the optimal compression length.

You can execute a "validate index <INDEX>" and after that you can query the view INDEX_STATS.

=> http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4216.htm#sthref2013

- OPT_CMPR_COUNT (Optimal key compression length)

- OPT_CMPR_PCTSAVE (Corresponding space savings after an ANALYZE)

If you want to know more about compressed indexes with some nice proofs and internals, go to Richard Footes Blog: http://richardfoote.wordpress.com/?s=compression&searchbutton=go%21

He is "famous" for his index internal knowledge.

Regards

Stefan

Former Member
0 Kudos

Hi Stefan!

How can I look at view index_stats within SAP.

RSORAANA makes a validate structure and return some fields of iondex_stats, but not the 2 ones you mentioned here. Of course I could make a copy of RSORAANA and modify it to get these fields.

But is there another standard way?

ST04 and/or RSORAVDV do not help either, becuase with them index_stats is not accessible.

Thanx in advance!

Regards,

Volker