SAP: Making Use of Oracle Advance Compression Option

Product: SAP, Oracle
Version: SAP 6.40 onward, Oracle 11g Release 1 (11.1), BR*Tools 7.20 onward

When planning for table compression, it is important to research and aware of following:

1. Oracle database compression restriction
2. Do not compress tables with frequent update
3. Do not compress tables that needs high performance throughput in INSERT
4. Do not compress tables that needs high performance throughput in UPDATE
5. Low space saving on high cardinality (less duplicate data). Uses Oracle Advanced Advisor PL/SQL to perform an estimate
6. Tables with more than 255 columns not supported
7. Tables with LONG columns not supported. Uses SAP BRSPACE (option long2lob) to migrate to LOB column type (recommend SecureFile LOB to use additional compression)
8. DELETE operation will runs 20% slower. If performance degrade more than 100%, search SAP for Oracle database patch with this known bug
9. BLOB is not compressed. Needs to convert to SecureFile in order to compress

SAP shipped with Oracle Enterprise Edition, which has license to use this feature. Anyone not using Enterprise Edition will find that compression does not work. On the other hand, anyone who has Enterprise edition not bundled with SAP, an extra license fee is require if you use it.

Normally people uses SAP brspace command to compress tables, which will SKIP these tables
1. SAP pool tables ATAB, UTAB. This is due to reason #2
2. SAP cluster tables CDCLS, RFBLG. Due to reason #2
3. INDX-type tables BALDAT, SOC3. Due to reason #3
4. ABAP source and load tables REPOSRC and REPOLOAD. Due to reason #4
5. Update tables VBHDR, VBDATA, VBMOD, VBERROR. Due to reason #3, #4
6. RFC tables ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE, QRFCTRACE, and QRFCLOG. Due to reason #3, #4

In ECC 6 system, there are 949 objects will be excluded when using Oracle Advance Compression Option

Following are my recommendation when decided to use Oracle Advance Compression Option for SAP

1. Convert LONG column type to LOB
brspace -f tbreorg -a long2lob -c ctablob -s PSAPOLD -t PSAPNEW -p 2
Above will convert those tables and move it to new tablespace PSAPNEW from PSAPOLD

2. Convert to new SecureFile LOB column type and compress
brspace -f tbreorg -a lob2lob -c ctablob -s PSAPOLD -t PSAPNEW
Above will convert those tables and move it to new tablespace PSAPNEW from PSAPOLD


3. Convert the rest of the tables
brspace -f tbreorg -a reorg -c ctab -s PSAPOLD -t PSAPNEW -p 8

Above will convert those tables and move it to new tablespace PSAPNEW from PSAPOLD. Compress 8 tables in parallel

Example of screenshot

Using Oracle Advanced Advisor
exec DBMS_COMP_ADVISOR.GetRatio ('SAPPRD', 'TEST_TABLE' , 'OLTP' , 10)

Note: For BR*Tools 7.x in Oracle 11g on UNIX (no need for AIX), needs to create additional soft link to make it work, unless the SAP kernel is version 7.20_EXT with BR*Tools 7.20

$ su - oracle
$ ln -s $ORACLE_HOME/lib/libnz11.so $ORACLE_HOME/lib/libnz10.so

Note: Following features are not feature of Oracle Advance Compression Option

1. Regular table compression
2. RMAN compression
3. Index compression

Note:
Use option "-i " if want to use different tablespace to store index. I do recommend this so that corruption of index tablespace or dbf file can be re-create from scratch.

Precaution
1. Verify there is no UNUSABLE index prior of compression
2. Verify there is no UNUSABLE index partition prior of compression
3. No Oracle SYS objects with status INVALID
4. PSAPTEMP has sufficient space to hold the largest table/index, with pre-allocated space, and don't rely on auto extend. If parallel compress, then increase to total size (performance reason)
5. Online redo log is properly size, or temporary add more. If parallel compress, increase more (performance reason)
6. Modify Oracle initialization file (spfile.ora) to has at least 1 GB for PGA (performance reason). Parameter PGA_AGGREGATE_TARGET
7. Increase DB_CACHE_SIZE to 1 GB in Oracle initialization file
8. If not using auto segment management, verify table and index initial extent (INITIAL) from DBA_SEGMENTS to ensure it will not over allocate the disk space. The compression will not free up the space if it sets larger than compressed data

Please use following PayPal donate if my post helped

SAP: Analyze Tablespace Growth

Product: SAP
Version: 4.x to 2010
Transaction Code: DB02
Type of database: Oracle 8.0 and above

Use this transaction (DB02) to analyze tablespace growth. It support raw device and Oracle ASM managed device as well.

However, for raw device, the size of the tablespace is fixed, so it can only determine the % used within the space allocated.

For beginner who does not understand Oracle tablespace, it is the visible usable space from SAP and database, which contain space logically assigned (total of all physical files). Under it, it consists of 1 or multiple files with physical space allocation, with extension dbf. DBA generally assign few MB to GB during tablespace creation, and allows them to auto grow (auto extend) if they hit the pre-allocated space. It is possible NOT to allow it to grow, regardless how much space left in the drive. Raw device and ASM is more complicated, so I won't explain here.

You must navigate to Space - Tablespace folder in order to analyze the tablespace usage. SAP keeps a 30 days history in "Detailed Analysis" folder

In order to determine the growth of each tablespace, the dbf data file has to:
1. shrink (or allocate) to its minimum size with as little free space as possible
2. minimum tablespace free space fragmentation (fragmented free space within the dbf file)
3. If there a a lot of free space fragmentation, then needs to re-organize the tablespace, which is time consuming, and requires downtime. For partition table, it is less downtime, but in all cases, there will certainly be performance impact, especially for data warehouse SAP BW

BusinessObject: Query Current and Archive Tables in Single Report

Product: BusinessObject
Module: Crystal Report designer

Consultant, including myself, often design archiving solution to move several years old OLTP data to archiving table, or even to different archiving database.

It becomes a challenge for report designer to pull the data from 2 different tables, or databases. This post will indicate how to design a single rpt report to get the data from 2 tables automatically

1. Create a sub-report to query archive table, which keeps data older than 1 year
2. Create a parameter called "dateQuery"
3. In main report, modify Conditional Suppression formula to hide this year's data if dateQuery is older than 1 year

If DateDiff ("m", {?dateQuery}, CurrentDate ) > 12 Then True

4. 3. In sub-report, modify Conditional Suppression formula to hide old data if dateQuery is this year

If DateDiff ("m", {?dateQuery}, CurrentDate ) <= 12 Then True

This is another solution instead of design another report to query old data. Nonetheless, it is not a bad idea to create separate report to prevent user abusing the report, such as extracting 5 years of data, which severe degrade database, hard disk, and or even congested the network

BusinessObject: Adjust Width of List Box Prompt

Product: BusinessObject
Module: Crystal Report designer

File: C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\Crystal Reports 2011\crystalreportviewers\prompting\css\promptengine2.css

In the prompt menu, all the List Box for filtering have fix width. It is possible to adjust this file to make it wider.

This is a global setting, so you can't control a specific List Box size in specific report. Moreover, if the width is too large (say 1920px), the right side of the screen will not be visible. There could be a "height: 100px" that can be added in, but I never try

.pePromptTextBox

{

    font-size: 8pt;

    background-color: #FFFFFF;

    font-family: Tahoma, verdana;

    width: 300px;

}

BusinessObject: Dynamic Timezone Conversion

Product: BusinessObject
Module: Crystal Report designer

I have see many people posting about timezone conversion related questions, and found many people are proposing a static timezone conversion, e.g. -1 hour for EST and CST.

This solution does not work for people in none USA timezone, for example, server is in USA, but user who view the report is in London, where both countries have different DST

Following solution auto detect server and user timezone, and adjust the time accordingly, instead of using hard coded time different. For illustration purpose, build-in function CurrentDateTime is used, but it can be replaced with column name to convert the time to correct timezone

1. Convert Crystal Report server timezone to user's timezone

ShiftDateTime ( CurrentDateTime, PrintTimeZone, CurrentCEUserTimeZone)

2. Convert UK timezone to Sweden's, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0,BST", "CET,-60,CEST")

3. Convert UTC time to server's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", PrintTimeZone)

4. Convert UTC time to user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0",  CurrentCEUserTimeZone )

5. Convert server's timezone to UTC time

ShiftDateTime ( CurrentDateTime, PrintTimeZone, "UTC,0")

6. Convert user's timezone to UTC time

ShiftDateTime ( CurrentDateTime, CurrentCEUserTimeZone, "UTC,0")

7. Convert US Eastern timezone to Pacific timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "EST,0,EDT", "PST,60,PDT")

8. Convert UTC time to Eastern timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", "PST,480,PDT")

9. Convert based on timezone defined in user table stored in database

This make use of Oracle functions instead of Crystal Report. So create a SQL Expression in Crystal Report with following value

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone TargetDataTimeZone As TzValue From Dual )


10. Same as above, but use client's timezone

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone SESSIONTIMEZONE As TzValue From Dual )



Note on Syntax:

ShiftDateTime ( [inputDateTime], [fromTimezone] , [toTimezone] )

where fromTimezone and toTimezone has following syntax

"Standard, Offset, DST, [offset in minute];[DST start date]/[and time],[DST end date]/[and time]"

"" (empty string) will use server's timezone defined in OS

CurrentCEUserTimeZone

Return the timezone (string) of the user viewing the report

PrintTimeZone

Return the timezone (string) of the BusinessObject server

The time zone string must be valid Crystal Report value. I recommend to use CurrentCEUserTimeZone and PrintTimeZone function to see the value on the report while designing the report, and change the OS's timezone to test it

SAP BusinessObject: Hide Group Header on 2nd Page Onward

Product: SAP BusinessObject
Module: Crystal Report designer

It is possible to hide the group header by using condition. Following step will configure the report so that in first page of report, it will display the group header, but on second page onward, it will hide it

1. Split the group header
2. On 1st group header, specify condition InRepeatedGroupHeader
3. On 2nd group header, specify condition NOT( InRepeatedGroupHeader )

End of post