One of the most useful tools in order to improve a SAP systems with Oracle Database is Advance Compression. I don’t usually see much Basis Teams using this feature of Oracle in order to tune up a little bit their databases saving space and improving performance. I want to give you some information about how useful it is and I’m going to use this and this written by Steve Somah in the SAP SCN. He performed a database compression on a SAP ECC 6.0 EHP3 and these were the results:
- The data was reduced by about 46% less:
- The dialog response time improved around 38%. The database time improved by more than 50%:
- The overall response time improved by 55%:
Those are impressing numbers, right? Before you jump up to compress your whole database I would like to give you some tips so you won’t mess it up.
How to start
The main SAP Notes about Oracle Advance Compression are:
- 1436352 – Oracle Database 11g Advanced Compression for SAP Systems.
- 2138262 – Oracle Database 12c Advanced Compression for SAP Systems
Those notes explain a lot of concepts about data compression on Oracle database, different types of compression and how to do it. If you read the note for your Oracle version you’ll see that requirements are something like this:
- To have an Oracle Database with version 220.127.116.11 or higher.
- Have the Advanced Compression Option (ACO) purchased from Oracle. It is usually included in the license agreement for Oracle Database on SAP Systems but you better check this before starting.
- SAP Kernel 6.40 or higher.
- SAP BRSPACE 7.20 or higher.
As you can see the requirements aren’t difficult to achieve. You can compress using the BRTOOLS if you execute the following command with the ORASID user and follow the menus:
brspace -u / -f tbreorg -t TABLE
It is not difficult, just follow the BRSPACE menus and the SAP Note. If you have any question before or after the table compression check the SAP Note 1847870 – Oracle 11g: Advanced Compression – Known issues and solution. One last thing to keep in mind: Don’t use the OLTP compression on a SAP BW database. For BW systems you can check SAP Note 701235 – Usage of Oracle compression and BW, it is quite old but it will work.
Before starting there is a big question you should ask yourself…
What should I compress? I want to compress the whole world!
The most important question before starting is what we want to compress. It is completely unnecessary and not recommended to compress the whole database since we won’t benefit from the Oracle compression if we do so. For deciding which tables we want to compress we will look for the following characteristics:
- Tables with a compression ratio above 2.0. For doing to I wrote a small SQL script you can execute and it will tell you the compression ratio for a specific table. The SQL code is not my creation and I don’t remember where I found it, probable on Julian Dontcheff’s Blog:
set serveroutput on
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('PSAPSR3', 'SAPSR3', 'BALHDR', '',DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||round(blkcnt_uncmp/blkcnt_cmp,2)||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
On the line DBMS_COMPRESSION you have to write the tablespace, user that owns the table and the table name. The output will be something like this:
If the compression ratio is above 2 then the table is an interesting one to compress in the database.
- Tables high a high number of Logical Reads, Physical Reads and UnOptimized reads. We can get this information on transaction ST04 using the AWR Report in Workload Reporting:
Over there you will find some data like this one:
Just check the top tables in each section.
Little example for great people
Using the last screenshot as example we can see that the BALHDR table has a high UnOptimized Reads (about 15% of the whole database). If we execute the script from the last point we will see that the ratio is 3,2 so it makes this table an interesting option in order to compress it. Keep in mind that the CPU use by the database will be higher when working with this table after the compression but the database response time will be lower.
In the screenshot I posted about the script output I executed it for the BSIS table. The compression ratio was 6.47 which was great and the table appeared on the UnOptimized Reads as a LOB Segment instead with a table name. If you “convert” that object name from LOB to table name you will see that the table name is the BSIS. Another good option for compressing!
As you can see table compression on Oracle is quite easy to do and it can improve a lot your databases. People usually don’t keep in mind this feature and they just focus on improving the queries, memory, buffer, etc. on the database in order to improve the performance. Remember to use all the tools available when fighting against a performance issue. Check your database to see if there is any interesting table to compress!