Oracle Compression using BRSPACE

[Former Member]

All,

We've just started compression activity on our systems, using below OSS Note, SAP ECC 6.0 SAP_BASIS 23, and oracle 11 ( upgraded from 10 ).

SAP Note 1431296 - LOB conversion and table compression with BRSPACE 7.20.

The idea is to move all the tables to new tablespaces ( disks ) and then remove the old disks.

1. Create new tablespace ( Compressed ) ( PSAPPRD700Z)

2. Create _reorg_excl_tab

3. Run below brspace commands

a. brspace -u / -f tbreorg -a long2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -lc medium -SCT ( No long row tables so wasn't executed )

b. brspace -u / -f tbreorg -a long2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

c. brspace -u / -f tbreorg -a lob2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -lc medium -SCT

d. brspace -u / -f tbreorg -a lob2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

e. brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -SCT

f.  brspace -u / -f tbreorg -a reorg -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

The tables are being moved to new tablespace but there is no change in the size of the tables in new tablespace.

Earlier when I did table compression using "SAP Note 1436352 - Oracle 11g Advanced Compression for SAP Systems", enable compression and then reorg the result was 50-60% reduction is table size.

Is there any thing else that I should be doing.

Thanks,

Abhishek

[Former Member · Accepted solution]

Hi Abhisek,

Check the tables are compressed by the statements, below;

SQL>SELECT table_name, compression, compress_for FROM user_tables;

And could you provide how did you created the new tablespace?

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

BRTOOLS is being used ( 7.20 pack latest patch).

Here are the results from select queries.

SQL> SELECT table_name, compression, compress_for FROM user_tables where compression='DISABLED';

934 rows selected.

SQL> SELECT table_name, compression, compress_for FROM user_tables where compression='ENABLED';

no rows selected

Regards,

Abhishek

[Former Member · Accepted solution]

To be sure, could you query a table that you reorganized and provide the output by executing statement, below;

SQL>SELECT COMPRESSION, COMPRESS_FOR FROM DBA_TABLES WHERE TABLE_NAME='<TABLE_NAME>';

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Add "-CBD" option into the brspace command line.

Read the "SAP note 1289494 - FAQ: Oracle compression" and search for "14. How do I activate table compression?"

Find the sample scenario, below;

Uncompressed tablespace=PSAPSR3

Compressed tablespace=PSAPTAB_NEUD

SQL> SELECT COMPRESSION, COMPRESS_FOR,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='ZT000';

COMPRESS COMPRESS_FOR

-------- ------------

DISABLED

CMD> brspace -u / -f tbreorg -o SAPSR3 -s PSAPSR3 -t ZT000 -n PSAPTAB_NEUD -CBD

SQL> SELECT COMPRESSION, COMPRESS_FOR,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='ZT000';

COMPRESS COMPRESS_FOR

-------- ------------

ENABLED  BASIC

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

This notes takes back to 1431296 which I've mentioned in my first message.

About the query, below are the results.

SQL> SELECT COMPRESSION, COMPRESS_FOR FROM DBA_TABLES WHERE TABLE_NAME='DD03L';

COMPRESS COMPRESS_FOR

-------- ------------

ENABLED  OLTP

Size before reorg -> 705000 MB

Size after reorg -> 705000 MB

I've just compared the number of tables in target and there is a big mismatch.

Earlier there were 796 total segments and now it's 665 in new tablespace.

Source tablespace is empty.

Regards,

Abhishek

[Former Member · Accepted solution]

Did you updated the statistics? Do you comparing the results on brtools?

I created a test scenario on my environment. Find the scenario, below;

  CREATE TABLE "SAPSR3"."ZTADIR"
   (    "PGMID" VARCHAR2(12) DEFAULT ' ' NOT NULL ENABLE,
        "OBJECT" VARCHAR2(12) DEFAULT ' ' NOT NULL ENABLE,
        "OBJ_NAME" VARCHAR2(120) DEFAULT ' ' NOT NULL ENABLE,
        "KORRNUM" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
        "SRCSYSTEM" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
        "AUTHOR" VARCHAR2(36) DEFAULT ' ' NOT NULL ENABLE,
        "SRCDEP" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "DEVCLASS" VARCHAR2(90) DEFAULT ' ' NOT NULL ENABLE,
        "GENFLAG" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "EDTFLAG" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "CPROJECT" VARCHAR2(24) DEFAULT ' ' NOT NULL ENABLE,
        "MASTERLANG" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "VERSID" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,
        "PAKNOCHECK" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "OBJSTABLTY" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "COMPONENT" VARCHAR2(90) DEFAULT ' ' NOT NULL ENABLE,
        "CRELEASE" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
        "DELFLAG" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "TRANSLTTXT" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "CREATED_ON" VARCHAR2(24) DEFAULT '00000000' NOT NULL ENABLE,
        "CHECK_DATE" VARCHAR2(24) DEFAULT '00000000' NOT NULL ENABLE,
        "CHECK_CFG" VARCHAR2(90) DEFAULT ' ' NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 81190912 NEXT 2621440 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "PSAPSR3"
/

INSERT INTO SAPSR3.ZTADIR SELECT * FROM SAPSR3.TADIR;
COMMIT;

CMD> brspace -u / -p initSPQ.sap -s 20 -l E -f dbshow -c tbinfo -t "ZTADIR"

Result: Allocated space in KB (space) ....... 151552

CMD> brspace -u / -f tbreorg -o SAPSR3 -s PSAPSR3 -t ZTADIR -n PSAPTAB_NEUD -CBD

CMD> brspace -u / -p initSPQ.sap -s 20 -l E -f dbshow -c tbinfo -t "ZTADIR"

Result: Allocated space in KB (space) ....... 81920

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Yes I did and i'm checking via sql query and through standard db02 result.

Example below.

SQL> select * from dba_segments where segment_name='DBMAPS';

no rows selected

Table DBMAPS was there in the source tablespace and as per the logs it was reorganized but now i dont' see the table.

Regards,

Abhishek

[Former Member · Accepted solution]

Execute the statement, below;

select segment_name from dba_segments where segment_name like '%DBMAPS%';

However, I queried three SAP ECC 6.0 system in my environment that the table is empty.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

Here are the results.

SQL> select segment_name from dba_segments where segment_name like '%DBMAPS%';

no rows selected

Other missing entries in target are below.

DDXTF	TABLE	49,000
DDXTF_CONV_UC	TABLE	0,063
DDXTF_CONV_UC~0	INDEX	0,063
DDXTF~0	INDEX	24,000
DDXTT	TABLE	3,000
DDXTT_CONV_UC	TABLE	0,063
DDXTT_CONV_UC~0	INDEX	0,063
DDXTT~0	INDEX	2,000

Regards,

Abhishek

[Former Member · Accepted solution]

You do not see table segments in dba_segments if deferred segment creation is used. For more info see Note 1583303 - Deferred Segment Creation

Regards

Roman

[Former Member · Accepted solution]

Hi Roman,

Thanks for the reply, however if I understand the note correctly what it means is that if the table is empty then it falls into deferred category and won't be created until the first row is inserted.

In my scenario missing tables are not empty and for some their earlier size was in few 100Mbs too.

So I think Deferred Segment Creation may not be the problem.

Regards,

Abhishek

[Former Member · Accepted solution]

As Roman referred those tables does not exist because of the didn't used. By the Oracle 11g, the initial extent does not created unless a record inserted into the table. So, because of this you can't see these objects on the "dba_segments".

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Ok. Execute the following statement from sqlplus:

select * from dba_tables where table_name like '%DBMAPS%'

Regards

Roman

[Former Member · Accepted solution]

Hi Thanks,

I just did the comparison again table by table for some 30-40 odd missing tables and yes they didn't have any rows even though the size was more than 20 MB.

For the next reorg I'll pull the table names from ALL_TABLES to compare.

There is however 3 more problems.

1. For below kind of segments the names have changed, is this ok?

LOBINDEX

LOBSEGMENT

2. Tables show compression enabled but are not compressed as far as the size is concerned.

3. Indexes doesn't have compression enabled after reorg in target tablespace, I know there is a separate note for Index compression but I had in my mind that indexes would also be compressed when whole table space is compression enabled.

Regards,

Abhishek

[Former Member · Accepted solution]

1. For below kind of segments the names have changed, is this ok?
LOBINDEX
LOBSEGMENT

Names for LOBINDEX and LOBSEGMENT types of segments are generated by oracle. After reorganization it seems to be ok if they changed.

2. Tables show compression enabled but are not compressed as far as the size is concerned.

Need more investigation.

3. Indexes doesn't have compression enabled after reorg in target tablespace, I know there is a separate note for Index compression but I had in my mind that indexes would also be compressed when whole table space is compression enabled.

I think index compression is another task than table compression. For more info see

Note 1289494 - FAQ: Oracle compression

and

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

Regards

Roman

[Former Member · Accepted solution]

>> I think index compression is another task than table compression. For more info see

Yes, index compression is not part of ACU. So, even if the index stored in the tablespace with compressed attribute, it will not be compressed. In short, you need to compress the index, manually.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

I'm yet to find the root cause for tables not getting compressed even after they have compression flag enabled in new tablespace.

About Indexes I don't even see the compression flag in new tablespace.

Regards,

Abhishek

[Former Member · Accepted solution]

Hi Abhishek,

You can idenfity the compressed index by looking "COMPRESSION" column on the "DBA_INDEXES" table.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

Thanks for the reply.

None of the indexes in the new tablespace have compression enabled, example.

Type

Tablespace

Size [MB]

Compressed

Compress For

Logging

INDEX	PSAPPRD700Z	0,125	DISABLED		YES
INDEX	PSAPPRD700Z	0,125	DISABLED		YES
INDEX	PSAPPRD700Z	35,000	DISABLED		YES
INDEX	PSAPPRD700Z	0,313	DISABLED		YES
INDEX	PSAPPRD700Z	7,000	DISABLED		YES
INDEX	PSAPPRD700Z	6,000	DISABLED		YES
INDEX	PSAPPRD700Z	1.727,000	DISABLED		YES
INDEX	PSAPPRD700Z	1.728,000	DISABLED		YES

Regards,

Abhishek

[Former Member · Accepted solution]

I'm shure you have not performed index compression. If not please post actions you did to perform index compresion

Regards

Roman

[Former Member · Accepted solution]

Hi,

Yes I didn't do anything additional to compress the indexes, I ran steps 1 - 3 as per my first message.

Regards,

Abhishek

[Former Member · Accepted solution]

Abhishek,

When you compress the index, you will see the "COMPRESS" column "ENABLED". Check the note "1289494 - FAQ: Oracle compression", and search for "Activating compression" string. It is very well explained in the document.

ALTER INDEX "<index_name>" REBUILD ONLINE COMPRESS 2;

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Fully agree with Orkun Gedik. Please read carefully note 1289494 according to activating index compression (p.8)

Regards

Roman

[Former Member · Accepted solution]

Hi Roman/Orkun,

Thanks for the reply.

Does it mean that after I've done the reorg from old tablespace to new tablespace ( as per my first message ), I'll have to do index compression one by one again?

Regards,

Abhishek

[Former Member · Accepted solution]

Yes, you should compress the index. This is because index compression is regardless from tablespace and table compression. You can create a script or use brtools.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Yes, you should compress the index. This is because index compression is regardless from tablespace and table compression. You can create a script or use brtools.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Please read the following documents:

SQL Commands for Lock, Fragmentation and Index Compression Analysis

and

[Oracle] Index key compression

in addition to notes mentioned above

Regards

Roman

[Former Member · Accepted solution]

Hi,

An update on this.

I've  postponed the investigation on indexes for a later date.

For now I've started the Reorg for key tablespace ( psapprd ) from old lob to new lob, lob2lob.

It's running at a very slow speed since last 1 hour it's reorganizing a table of 15 GB APQD.

Any ideas on this?

Regards,

Abhishek

[Former Member · Accepted solution]

Hi Abhishek,

I suggest you reorganize the batch inputs before the table reorganization. You can find the further information in the note 36781 - Table APQD is very large

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi,

Thanks I'll make a note of this when I do this in PRD.

For now I've increased the parallel threads from 1 to 5 and triggered the next compression.

* Converting the tables with old LOB fields that are compressed in the SAP

system:

Regards,

Abhishek

[volker_borowski2 · Accepted solution]

3. Indexes doesn't have compression enabled after reorg in target tablespace, I know there is a separate note for Index compression but I had in my mind that indexes would also be compressed when whole table space is compression enabled.

Well,

there is the suggestion, that you apply index compression first (sapnote for that) and move to new TS after this later (the way as you did) but you CAN do in one step as well. Checkout "brspace -help" for the options to apply compression. There are i.e. -ctabind and -clobind available.

Volker

[Former Member]

Guys,

This activity was completed on Production, thanks to everyone for the feed back and help.

The system was running at 1500 GB and after reorg and compression new DB size is 637 GB.

Significant Performance improvement.

Indexes were compressed prior to reorg.

It took around 10-12 hours to complete the whole activity.

Regards,

Abhishek