SAP Basis Tech Supports

Symptom

Database is using too much disk space for indexes

Other terms

disk space, performance, compression, disk I/O

Reason and Prerequisites

Index Key Compression is supported in SAP environments from Oracle Release 10.2 on.Index Key Compression is only available for B*Tree indexes and not for Bitmap Indexes.Please make sure that your PSAPTEMP Temporary Tablespace is at least as big as the biggest index to rebuild. Without sufficient temporary tablespace the index rebuild may failPlease check the free space of the tablespace containing the index to be rebuilt. As a rule of thumb you should have enough free space in the tablespace to store the index to be rebuilt twice.

Solution

Use Index Key Compression by rebuilding existing B*Tree indexes in the database to store B*Tree indexes as space efficient as possible.
What is key compression?
Key compression allows repeating values for columns in a B*Tree index to be replaced by shorter tokens in the index blocks. Key compression looks at the values of the leading columns of the index and replaces repeating values by shorter tokens. To get the maximum benefit out of key compression you need to compute the optimal mix of the length of the repeating values in the leading columns of an index and the selectivity of these repeating values in the leading part of an index.
What are the advantages and disadvantages of key compression?
Saves disk space for indexes and reduces total database size on disk
Customer experiences show that up to 75% less disk space is needed for key compressed indexes. Even after index reorganisations have taken place an additional up to 20% total disk space reduction for the whole database can be achieved using index compression. Without any reorganizations done before the total space savings for the complete database may be higher than 20% using index compression as index compression implicitly reorganizes any index.
Real world example:
The size of the of index ‘GLPCA~1′ index was reduced from 18GB to 4.5GB.Reduces physical disk I/O and logical buffer cache I/O improving buffer cache qualityHigher CPU consumption
Every compression technique comes with higher CPU consumption. The higher CPU consumption is more than compensated by doing less logical I/O for index blocks in the database buffer cache.Improved overall database throughput
Early customer experiences have shown a 10-20% better database throughput for an SAP system by using index key compression in a non CPU bound environment.

To make index key compression work two steps need to be done:
Step 1:Compute the number of the leading columns of an index to
deliver the highest compression rate
Step 2:Rebuild the index with the optimal number of the leading
columns to be compressed.
There is alternative to step 1 for ERP/ECC customers. Based on experience from many customers two sample scripts have been created which do contain the SQL statements to compress the typical large indexes of an ERP/ECC system.
The one script “IX_COMP_TYPICAL_ONLINE.txt” may be used while the SAP system is up. The other script “IX_COMP_TYPICAL_OFFLINE.txt” can only be used when the SAP system is down. The offline version rebuilds indexes faster and requires less temp space in PSAPTEMP.
Please rename “IX_COMP_TYPICAL_ONLINE.txt” to “IX_COMP_TYPICAL_ONLINE.sql” or “IX_COMP_TYPICAL_OFFLINE.txt” to “IX_COMP_TYPICAL_OFFLINE.sql” and execute the script by calling sqlplus.
Example:
sqlplus sapsr3/sap
start IX_COMP_TYPICAL_ONLINE
For non-ERP/ECC systems or for compressing additional indexes the following steps need to be executed.
A PL/SQL package “ind_comp.sql” is provided as an attachment to this note which performs both steps.

Installation of PL/SQL package “ind_comp.sql”
Step 1:
Select the Output Directory and create it in Oracle:
sqlplus
connect / as sysdba
create directory “~IND_COMP_DIR” as ‘‘;
grant read, write on directory “~IND_COMP_DIR” to ;
Step 2.
Rename attached file from “ind_comp.txt” to “ind_comp.sql”.
Step 3.
Login into the Oracle database with the sapsid user and compile the
PL/SQL Package.
Examples:
sqlplus sapsr3/sap
start ind_comp

How to use PL/SQL package “ind_comp” ?
The function get_column of the PL/SQL package accepts three parameters
ind_comp.get_column (table info, space, opmode);
table info: You can specify either a table name or number of tables
space: Default value is FALSE not to generate space queries before and after the index is changed
opmode: Default value is ONLINE generating only INDEX REBUILD
commands therefore ignoring all partitioned indexes. When specifying the value OFFLINE all partitioned indexes will be dropped and created. Using OFFLINE requires downtime of the SAP system for the index creation phase.

Examples:
exec ind_comp.get_column(’GLPCA’);
This computes the optimal compression factor for all indexes of
the table GLPCA.
exec ind_comp.get_column(’GLPCA’, true);
This computes the optimal compression factor for all indexes of
the table GLPCA. For each index of the table GLPCA the size
before and after the compression is generated.
exec ind_comp.get_column(10);
This computes the optimal compression factor for all
non-partitioned indexes of the 10 largest tables.
exec ind_comp.get_column(’/BIC/B0000980000′, opmode => ‘OFFLINE’);
This computes the optimal compression factor for all partitioned indexes on the table /BIC/B0000980000.
exec ind_comp.get_column(10, opmode => ‘OFFLINE’);
This computes the optimal compression factor for all partitioned
and non-partitioned indexes of the 10 largest tables.
This scenario should be used for SAP BW systems.
exec ind_comp.get_column(10, true, ‘OFFLINE’);
This computes the optimal compression factor for all
partitioned and non-partitioned indexes of the 10 largest tables.
Additional queries for and after the compression are being generated.

Example:
sqlplus sapsr3/sap
set serveroutput on size 1000000
exec ind_comp.get_column(’RFBLG’);
Processing table RFBLG
“RFBLG~0″ compress 2;
An additional output is written to an ASCII file in the /tmp directory on UNIX systems. On Windows it is the directory C:\TEMP. The file has the name of the table name specified as input parameter to the get_column function of the ind_comp package. In the above example you will find the ASCII file “GLPCA_IX_COMP.sql” in the /tmp directory.
This file can be used in a seperate sqlplus session to rebuild the index with the optimal compression factor. Never rebuild an index during high load times of the system.
Example:
sqlplus sapsr3/sap
start GLPCA_IX_COMP

In addition the package “ind_comp” creates a summary file “IX_COMP.sql” in the /tmp directory where all index rebuilds and index
create statements for all partitioned and non-partitioned tables are stored.
CAUTION:
Partitioned indexes cannot be rebuild and must be recreated. Therefore the output file “IX_COMP.sql” created by the “ind_comp” package in ‘OFFLINE’ mode can only be executed during the downtime of the SAP system !
The execution of the PL/SQL package to compute the optimal index compression factor can be done at any time and requires no downtime i.e. you can run ind_comp.get_column while the SAP system is up.
It is advised not to run the package during peak times of the SAP system !
Possible reasons, why compression does not show any or small space savings
High UNIFORM value for locally managed tablespaces. If UNIFORM is used then a value of 10MB is sufficient. As an alternative do not use UNIFORM but instead use AUTOALLOCATE.High INITIAL, NEXT and PCTINCREASE values for dictionary managed tablespaces. Adjust accordingly.High INITIAL_EXTENT value for index to be compressed. Previous index reorganisations can create the situation that an index has only one large initial extent. In this case the initial extent size should be changed by adding the storage clause to the rebuild index statement. Example: alter index “RFBLG~0″ rebuild online compress 2 parallel 4 pctfree 1 storage (initial 1m);