11g Advance Compression for VB Tables.

[Former Member]

Hi,

We are planing to compress top table in our ECC 6.0 Production server.

Sap Note says:

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

"Migrate all SAP transparent tables (except queue type tables like VB or RFC tables) to oltp compressed tables."

Here (queue type tables)VB means all VB tables ???? or any other tables???

Can we compress VB* tables???

Please guide us.

[stefan_koehler]

Hello,

Sapnote #1431296 contains some detailed information.

1.4 Exception handling during table compression
-
We recommend that you do not compress the following tables in the Oracle system:
- Tables with more than 255 columns (Oracle requirement)
- SAP pool tables (for example, ATAB, UTAB)
- SAP cluster tables (for example, CDCLS, RFBLG)
- INDX-type tables (for example, BALDAT, SOC3)
- The ABAP source and ABAP load tables REPOSRC and REPOLOAD
- The update tables VBHDR, VBDATA, VBMOD, and VBERROR
- The RFC tables ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE, QRFCTRACE, and QRFCLOG

The BR*Tools already performs some checks and skip those tables.

Regards

Stefan

[Former Member]

Hi...Stefan....Thank U very much.

We are creating a case and we have listed Top - 101 Tables , and we listed in XL how many are havin LOB Segments,

But after looking the note you provided, it has given some more insights, We should also clasify these tables into "POOL" " CLUSTER" "INDX" " TRANSPARENT" tables before we initate for compressing.

We understood POOL & CLUSTER ...

But to identify a table , is it INDX-type table or not looking at Table characterstics , Where i will come to know to see this table belings to INDX type.

Please help & Thanks in advance.

Rgds

[stefan_koehler]

Hello,

you don't need to identify these tables manually - the BR*Tools include a logic to exclude those tables.

From Sapnote #1431296 again:

These recommendations are accommodated in BRSPACE as follows:
- Tables with more than 255 columns are automatically excluded from table compressions.
- Pool and cluster tables, INDX-type tables, ABAP source and ABAP load tables, and update tables are excluded using the special option "-SCT" (skip compressed table).
- RFC tables are excluded using the special parameter "_reorg_excl_tab", in which you can specify the tables explicitly.

Regards

Stefan

[Former Member]

Hi Stefan,

Before reading your recommendation I had compress the RFC table ARFCSDATA. Because, this is one of the topgrowth table in My SAP ECC system. Is there any negative impact to compress the RFC table ARFCSDATA? If yes, please tell me the procedure how can we undo compressed and  reorg?

regards.

[Former Member]

Hi Abishek,

First of all, complete and reorganize all TRFC and QRFC entries over the system, by using the programs, below;

TRFC reorganization: RSARFCER

QRFC reorganization: RSTRFCIDS

After the operation, be sure that the table does not have an entry. So you can move forward and rename the table by using statement, below;

ALTER TABLE <OWNER>.ARFCSDATA RENAME TO ARFCSDATA_ORIG;

I suggest you rename the table before you drop it, as a fallback method, against to a possible problem and be able to rollback the operation. After the you renamed the table respectfully, drop and create the table by using the statement, below;

DROP TABLE "<OWNER>"."ARFCSDATA";

CREATE TABLE "<OWNER>"."ARFCSDATA"                                            

( "ARFCIPID" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,                       

"ARFCPID" VARCHAR2(4) DEFAULT ' ' NOT NULL ENABLE,                            

"ARFCTIME" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,                           

"ARFCTIDCNT" VARCHAR2(4) DEFAULT ' ' NOT NULL ENABLE,                         

"ARFCDEST" VARCHAR2(32) DEFAULT ' ' NOT NULL ENABLE,                          

"ARFCLUWCNT" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,                         

"ARFCBLCNT" RAW(4) NOT NULL ENABLE,                                           

"ARFCDATA01" RAW(255),                                                        

"ARFCDATA02" RAW(255),                                                        

"ARFCDATA03" RAW(255),                                                        

"ARFCDATA04" RAW(255),                                                        

"ARFCDATA05" RAW(255),                                                        

"ARFCDATA06" RAW(255),                                                        

"ARFCDATA07" RAW(255)                                                         

) SEGMENT CREATION IMMEDIATE                                                

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING             

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645        

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                

TABLESPACE "<TABLESPACE_NAME>";

I strongly recommend you schedule a down-time and perform the operations during this timeline.

PS: You can dump the table content before the operation and import the entries, instead of reorganizing the TRFC and QRFC records, but I choose to reorganize the entries.

Best regards,

Orkun Gedik

[former_member197466]

Hi.

If you want to know more about the INDX type, please refer kba note 1696127 for more information.

As mentioned by Stefan, It is not necessary to list out the INDX-type tables. The BRSPACE special option "-SCT" will automatic to do this.

Cheers,

Ivy

[stefan_koehler]

Hello Abhishek,

you can decompress the table ARFCSDATA for sure.

The BR*Tools also have an option for that. Please check sapnote #1431296 (Point "1.3 Syntax of the options for table compression") and the corresponding option "dtab".

Regarding your question about the growth of table ARFCSDATA - please check sapnote #375566.

By the way - you also find an explanation in sapnote #1431296 - why you should not compress the corresponding kind of tables.

The majority of the tables mentioned above (pool, cluster, INDX-type, ABAP source, and ABAP loads) are already compressed in the SAP system. Therefore, a second compression in the Oracle system does not make sense.
A similar situation applies to tables that are used intensively in the  queuing mode (such as update tables and RFC tables). For RFC tables,  this applies only if the system is integrated in an SAP system group and  an intensive data exchange takes place between the systems.

Regards

Stefan

[Former Member]

Hi,

It means that only update tables not the tables such as VBRK.

Best regards,

Orkun Gedik