Oracle compression

[Former Member]

Dear Expert,

I have done oracle upgrade to 11G from 10G ,,,but now client is looking for Oracle Compression also ...But i have never done this before ...i am going to do this on the Quality first ...please help me to find out the steps or any guide regarding the activity ....

Thanks

Rableen

[Former Member]

Hi Rableen,

Please help me what is procedure you follow for compression for below table space (any document)

PSAPSR3

PSAPSR3700

PSAPSR3FACT

PSAPSR3ODS

PSAPSR3USR

how long time is required ?

what is your tabe-space size?

after compression what is your table-space size?

please help.

Thanks,

Manas

[Former Member]

Hi Manas,

Just check my last update (on Rableen's) to get clarification.

It can be done online, we got 3x compression ratio (Our database size got reduced from 3TB to 1TB).

Regards,

Nick Loy

[Former Member]

Hi Nick,

Thanks for your reply which process you done below activity.

4) Migrated data from old to new tablespace by giving -C (Compression)

Kindly help.

Thanks,

MAnas

[Former Member]

Using brspace (tbreorg), you can achieve this.

This is what where you perform the actual compression, I gave my source and target within the command line which migrated my data from old TS to new TS with compression option ON.

Ex. Command: brspace -u / -f tbreorg -a long2lob -s <Source TS> -t allsel -n <Target TS> -I <Target Index TS> -c ctablobind -lc medium -ic sap_proc -l 2 -p 16 -SCT -KST

Regards,

Nick Loy

[Former Member]

Hi Nick,

Thanks for your reply.

Ex. Command: brspace -u / -f tbreorg -a long2lob -s <Source TS> -t allsel -n <Target TS> -I <Target Index TS> -c ctablobind -lc medium -ic sap_proc -l 2 -p 16 -SCT -KST

Above one command we covert all data from PSAPSR3 to New PSAPSR3 but note

sapnote_0001431296 shows many of command i.e.

brspace -u / -f tbreorg -a long2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -lc medium -SCT -CBD

brspace -u / -f tbreorg -a long2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

brspace -u / -f tbreorg -a lob2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -lc medium -SCT -CBD

brspace -u / -f tbreorg -a lob2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -SCT -CBD

brspace -u / -f tbreorg -a reorg -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

its correct or i added anything. Please help.

Thanks

Manas

[Former Member]

Hi Manas,

First you should understand how the conversion works!

Read the command line and the explanations of each command.

brspace -u / -f tbreorg -a long2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -lc medium -SCT -CBD

It is Long2lob conversion (-a long2lob) and for uncompressed tables (-SCT, skip compressed tables)

brspace -u / -f tbreorg -a long2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

It is long2lob for all remaining tables

brspace -u / -f tbreorg -a lob2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -lc medium -SCT -CBD

It is lob2lob conversion (-a lob2lob) and for uncompressed tables (-SCT, skip compressed tables)

brspace -u / -f tbreorg -a lob2lob -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

It is lob2lob conversion (-a lob2lob) for all remaining tables

brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -c ctablob -SCT -CBD

It is for reorganization (-a reorg) for all uncompressed tables (-SCT, skip compressed tables)

brspace -u / -f tbreorg -a reorg -o sapsr3 -s PSAPSR3 -t allsel -n PSAPSR3CP -CBD

It is for reorganization (-a reorg) for all remaining tables

You should run all of the above for each and every tablespace.

Regards,

Nick Loy

[Former Member]

Hi Nick,

Thanks. I asking i can convert all the data from old tablespace to new tablespace using below command

Ex. Command: brspace -u / -f tbreorg -a long2lob -s <Source TS> -t allsel -n <Target TS> -I <Target Index TS> -c ctablobind -lc medium -ic sap_proc -l 2 -p 16 -SCT -KST

if yes then any compress table what is the status after transfer?

I think many compress table present in SAP.

Anther question if database size is 7 TB how long time required from transfer data from old to new tablespace?

I can perform all the task at online ?

Below sentence i can not understand...

2) Prepared system for ACO enablement (We took 2hrs of downtime to increase the shared pool size and for other ACO prerequisites, downtime may not be required in your/other case)

Kindly help.

Regards,

Manas

[Former Member]

Hi Manas,

Above example command can do the long2lob conversion and the migration from old to new TS.

This command converts only uncompressed tables of given tablespace.

You should execute next command (Given below) to migrate the data of remaining tables (compressed)

brspace -u / -f tbreorg -a long2lob -s <Source TS> -t allsel -n <Target TS> -I <Target Index TS> -c ctablobind -lc medium -ic sap_proc -l 2 -p 16  -KST

As I mentioned in my last update, you should run all of the above commands in sequence to migrate the whole data to new tablespace, so that each command can migrate selective data from old to new in phases.

Since it's an online activity, you should not bother about the execution time.

Run this migration with minimum additional load so that migration cannot hamper your system performance.

Anther question if database size is 7 TB how long time required from transfer data from old to new tablespace?

Execution time depends on the resources that you have on system. As I said, you should not bother about the execution time.

Prepared system for ACO enablement

There are some pre-requisites, please check the notes mentioned in below blogs.

Ex: Shared pool size, latest SBP etc.

http://scn.sap.com/community/oracle/blog/2013/04/16/cut-down-your-storage-cost-with-oracle-aco-advan...

http://scn.sap.com/community/oracle/blog/2014/01/30/get-more-benefited-from-aco

Regards,

Nick Loy

[Former Member]

Thanks Nick. Ok fine. Another one is

If I first compress the large table using the below command.

alter table sapsr3.EDI40 move;

ALTER TABLE SAPSR3.EDI40 COMPRESS FOR OLTP;

then after i transfer the data from old to new tablespace if there any problem ?

If no can you guide me after large table compress how we get the space from OS lavel?

( Database space it getting but OS directory space )

At the below process we can apply in BI server ?

(those you  reply on 4th Feb 10:24 am )

Nick Loy     Feb 4, 2014 10:24 AM  (in response to Manas .)

Please suggest.

Thanks

Manas

[Former Member]

Hi Manas,

Alter command just enables the OLTP compression!

You can use -t <Table name> in brspace command to compress and migrate the data from old to new TS.

Above commands will convert, compress, reorg and migrate the data from old to new TS.

Post completion you can see the free space at OS.

ACO is Oracle's feature and there is no such application restriction (It's a database feature), you can implement this on BI as well.

Regards,

Nick Loy

[Former Member]

Dear All,

I have reorganize the table ..

++++++++++++++++++++++++++++++++++++++

PSAPSR3

  PSAPSR3700

          PSAPSR3FACT

          PSAPSR3ODS

          PSAPSR3USR

++++++++++++++++++++++++++++++++++++++

Now how to proceed with the tablespace

PSAPTEMP

          PSAPUNDO

          SYSAUX

          SYSTEM

please let us know on how to proceed

Rableen

[Former Member]

Hi Rableen,

The best approach (we followed for our environment) to get storage benefit out of this Oracle compression is -

1) Allocated 60% of additional storage and created new sapdata FSs ( We had sapdata1-60 so created new FSs from 61 to 70)

2) Prepared system for ACO enablement (We took 2hrs of downtime to increase the shared pool size and for other ACO prerequisites, downtime may not be required in your/other case)

3) Created new TSs in new sapdata file systems (Ex:SAPSR3_NEW for SAPSR3)

4) Migrated data from old to new tablespace by giving -C (Compression)

5) Swapped TS names once the data is compressed and migrated to new TS

6) Deleted old tablespace (which is 0 in size)

7) Took a downtime and move SYSTEM, SYSAUX and PSAPUNDO to new sapdata files (Minimum downtime is required)

😎 Dropped all old sapdata file systems (sapdata1 to 60)

9) Released the entire storage (Free LUNs) back

Benefits of above process:

* It require minimum downtimes

* It migrates the TS structure from DMTS to LMTS by default (If you have any DMTS TS)

* It gives the storage back to your environment

Regards,

Nick Loy

[Former Member]

Dear All,

Thanks for all you helping me in the activity ......

But my question still remains that how to proceed with the activity ,,,,

PSAPSR3

  PSAPSR3700

          PSAPSR3FACT

          PSAPSR3ODS

          PSAPSR3USR

          PSAPTEMP

          PSAPUNDO

          SYSAUX

          SYSTEM

From the above given table ...if i am creating the tablespace

PSAPSR3Z

PSAPSR3700Z

          PSAPSR3FACTZ

          PSAPSR3ODSZ

          PSAPSR3USRZ

Then how to move the

PSAPTEMP

          PSAPUNDO

          SYSAUX

          SYSTEM

Tablespace to the newly created datafiles......

Please help me more for the activity

Rableen

[Former Member]

Hi  Rableen,

You don't have to move them, actually it's not mandatory to move the compressed tables to a new Tablespace, it's just a way to reclaim the space.

Ragards

[Former Member]

Hi Jose,

Thanks for your reply i have completed the activity .....there is no inconsistency .....everything is fine ....

but the things we are analyzing  that the performance is slow down after the activity ...is there any thing extra need to done..

Rableen

[Former Member]

Hi Rableen,

As Alex has already mentioned in one of the replies, Oracle compression has a higher resource requirement regarding CPU, above all for wirite sentences (insert and update).

In addition, please check the sap note Note 1289494 - FAQ: Oracle compression there are some references to performance issues because of Oracle bugs:

That's why, in my eyes, it makes no sense to compress all tables in a tablespace. For me the approach should be compress the tables with highest compression rates and table to tables testing the performance before and after compression.

Cheers

[Former Member]

Dear Jose,,

We are on latest of 11.2.0.3 .......and we have compressed only below table....

PSAPSR3

  PSAPSR3700

Rableen

[Former Member]

Hi Rableen,

In addition to the technical details, bear in mind that Oracle Compression requires an additional license if you Oracle License was not acquired throwgh SAP.

If your customer bought the Oracle Licenses directly to Oracle He has to check the license agreement regarding compression.

In this thread you can find detailed information:

http://scn.sap.com/thread/1923823

Regards

[Former Member]

Dear All,

Thanks for your reply i have got the confusions .....I am following brspace procedure.

In the note mentioned you have to create 30% of size of table space

PSAPSR3

          PSAPSR3700

          PSAPSR3FACT

          PSAPSR3ODS

          PSAPSR3USR

          PSAPTEMP

          PSAPUNDO

          SYSAUX

          SYSTEM

and having sapdata1 to sapdata9

We have the above table space existing so we have create table space for each of the above in new datafile as

PSAPSR3Z

          PSAPSR3700Z

          PSAPSR3FACTZ

          PSAPSR3ODSZ

          PSAPSR3USRZ

          PSAPTEMPZ

          PSAPUNDOZ

          SYSAUXZ

          SYSTEMZ

and then run the commands.

Rableen

[Former Member]

Hi Rableen,

Regarding your question about about the new tablespace ....

Usually the compression is activated only for some tables, the bigger tables and the tables that have a better compression ratio. The approach of the note to compress and/or move all the tables from one TS to another one and after that remove the old one to reclaim the space.

In this case you need to create a new TS for each TS that you want to move. And the new TS should be at least a 30% of the original.

In addition, as per you TS information, your system is a SAP BW, in this case, please have a loo to this note:

https://service.sap.com/sap/support/notes/701235

[alexander_brocke]

Hello Rableen,

At least there are two ways getting Oracle Compression implemented:

1. SAP System Export/Import method using R3load. The compression itself is then done during System IMPORT. Please be Aware of a longer System Import time, because the CPU needs to calculate the compression [In SAPinst/SWPM you get then the Point "OLTP Compression if you choose the exper Settings for Oracle DB :-)] This is probably the best way, because R3load "knows" the exceptions from some tables etc. .

2. Via BR*TOOLS. Is in General supported by SAP, but you must know the exceptions and implement them on you own into the command line Statement which will be created by BR*TOOLS

The mentioned notes from Eduardo should also help you - Read them carefully before you start 🙂

Good luck ! 🙂

Regards,

Alex

[former_member184473]

Hello Rableen,

Check the following notes:

1818320	Oracle 11g Advanced Compression for LONG data restriction
1436352	Oracle 11g Advanced Compression for SAP Systems
1289494	FAQ: Oracle compression
1847870	Oracle 11g: Advanced Compression - Known issues and solution

Regards,

Eduardo Rezende