Compression of LOB segment.

[Former Member]

Hi,

I have a LOB segment which is 126 GB in size. The total size of my db is 294GB which means that the LOB segment occupies almost 42% of the total size.

Is there any way of compressing it? If so, what are the analysis to be done before proceeding with the compression.

The SAP system is a PI system and oracle version being used is 11gR2. OS: Sun solaris.

Regards,

Varsha Bellary.

[Former Member · Accepted solution]

Hi Varsha,

Please find the how to information in the document, below;

Note 1431296 - LOB conversion and table compression with BRSPACE 7.20

By the way, I assume that you have a problem with the payload tables such as SXMSCLUP and SXMSCLUR. Belong to the PI system, I suggest you activate the switch procedure first . Then, archive the payload tables, periodically. Please find the related documents, below;

Archive procedure:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e0afdd69-a8a7-2d10-7e9a-cbf6fa21c...

Switch procedure:

http://wiki.sdn.sap.com/wiki/display/XI/Overview+of+the+Switch+Deletion+Procedure

By doing so, table reogranization will be done by the system automatically, when the threshold level exceeded.

On the other hand, you can reorganize the tables manually, in order to reclaim the free space to the storage.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Gedik,

i am performing table compression one of PROD system , i have sort out the all tables using the scripts as per note 1438410.

i have a small doubt in LOB  list , what are the  differences between S1,B1,B2 and B3.

is there any latest note for SQL scripts .. can you please help me.

regards,

Balaram

[Former Member · Accepted solution]

Hi Balaram,

>> i have a small doubt in LOB  list , what are the  differences between S1,B1,B2 and B3.

Could you clarify what script you are running?

>> is there any latest note for SQL scripts .. can you please help me

OSS notes have been updating, regularly. So, you'll be find latest version when you browse

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

as per note 1438410, i am using below scpits..

Space_LargestTables_11g+.txt --- to find largest tables
Segments_Tables_CompressionExceptions.txt

if any latest notes , can you please provide me.

regards,

balaram

[Former Member · Accepted solution]

>> S1,B1,B2 and B3

Ok, but what do you mean with these values?

As I mentioned in my previous message that the note contains latest scripts.

Best regards,

Orkun Gedik

[Former Member]

Hm, this sounds familiar. Theoretically yes you can try to compress the lob segment. Things that can spoil your efforts:

- lobs can have inline (in the table block) storage for objects < 4kb

- lob data often is already compressed

- you don't have a securefile lob already (mainly objects created prior 11g)

Check notes:
LOB conversion and table compression with BRSPACE 7.20

Oracle 11g Advanced Compression for SAP Systems

Oracle 11g: SecureFiles - The new way to store LOB data

Now to the PI specifics. I am guessing the lob segment is a column in table SXMSCLUP, which is one of the main tables that hold message data.

If that is true, then i have to ask if you are regularly deleting data from that table? You either have to delete old messages or apply a archiving strategy to prevent SXMSCLUP from growing endlessly.

Cheers Michael

PS: just that you know, we have an issue where the table's lobsegment was still growing even when we properly delete old data from it (PI 7.3 / Oracle 11.2.0.2)