Oracle EBS Advanced Row Compression

Oracle E-Business Suite Release 12.2 with Oracle Database 12c Release 1 Advanced Row Compression (KB443050).

We should avoid compression on these Objects
—————————————————
1. SYSTEM / CORE DATABASE OBJECTS
SYSTEM
SYSAUX
UNDO
TEMP

###########################################################################################

Following objects can be compressed
====
1. Interface tables (BEST candidates)
—————————————–

Following are the temporary tables:

AP_*_INTERFACE
AR_*_INTERFACE
GL_INTERFACE
MTL_*_INTERFACE
PO_INTERFACE_ERRORS

#############################################################################################
Never compress the below:

These are always HOT:
———————-

MTL_ONHAND_QUANTITIES
MTL_MATERIAL_TRANSACTIONS
AP_INVOICES_ALL (current data)
AR_* active tables
PO_* active procurement
OE_* order processing
FND_* system tables
WF_* active workflow

1. LONG (legacy datatype)
—————————
Oracle 19c does not support compression

2. RAW / LONG RAW (large binary legacy)
—————————————-
Direct OLTP compression does not apply, mostly migration-restricted types

 

Oracle Advance Compression
————————–

NOTE: First check top ten heavy sized tables in database which are needed to be compressed.

SELECT
segment_name AS table_name,
owner,
ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_in_gb
FROM
dba_segments
WHERE
segment_type = ‘TABLE’
AND owner NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY
segment_name, owner
ORDER BY
size_in_gb DESC
FETCH FIRST 50 ROWS ONLY;

output
——-

TABLE_NAME OWNER SIZE_IN_GB
—————————————- —————————————- ———-
FND_LOG_MESSAGES APPLSYS 231.85 —
ZX_LINES ZX 228.16
AR_DISTRIBUTIONS_ALL AR 224.15
ZX_LINES_DET_FACTORS ZX 217.92
MTL_MATERIAL_TRANSACTIONS INV 189.5
MTL_TRANSACTION_ACCOUNTS INV 157.23
RA_CUSTOMER_TRX_LINES_ALL AR 148.93 —
GL_IMPORT_REFERENCES GL 111.57
MLOG$_CHASE_GP_REPORT APPS 102.85
MLOG$_STOCK_REPORT_FOR_ALL APPS 97.85
XLA_DIAG_SOURCES XLA 84.18 —
MTL_CST_ACTUAL_COST_DETAILS INV 73.02 —
RA_CUST_TRX_LINE_GL_DIST_ALL AR 72.85
AR_TRX_LINES_GT_BKP APPS 68.63 —–
X_SALE_BATCH_LINES EBSINT 52.18 —–
DOC_ITEM_BKP MUHAMMADSAAD 52 —–
XLA_AE_SEGMENT_VALUES XLA 48.92
MTL_SYSTEM_ITEMS_B INV 48.02
WF_ITEM_ATTRIBUTE_VALUES APPLSYS 39.53 —
ICX_CAT_ITEMS_CTX_DTLS_TLP ICX 38.09
Z_TRX_DETAILS_T APPS 36.74 ——
DOC_ITEM EBSINT 33.62 ——
PWC_MTL_SYSTEM_ITEMS_KFV APPS 33.17 ——
CHASE_GP_REPORT APPS 31.19 ——
RPRO_SALES_DATA_T APPS 28.93 ——
ZX_REP_TRX_DETAIL_T ZX 27.83 —
CST_PERIOD_CLOSE_SUMMARY BOM 26.73 — –first
PO_ATTRIBUTE_VALUES PO 20.28
DOC_ITEM_RP_DATA EBSINT 19.6 —–
RCV_TRANSACTIONS PO 19.11
XXCU_RP_SALES_INFO_TEST1 APPS 18.24 ——
PO_ATTRIBUTE_VALUES_TLP PO 15.9
ICX_CAT_ATTRIBUTE_VALUES ICX 15.04
STOCK_REPORT_FOR_ALL_EBS_D APPS 13.95 —
ICX_CAT_ATTRIBUTE_VALUES_TLP ICX 12.16
MTL_TRANSACION_BCK APPS 12.06 —–
MTL_MAT_TBCK11 APPS 11.89 ——-
STOCK_REPORT_FOR_ALL APPS 11.6 —
AP_INVOICE_DISTRIBUTIONS_ALL AP 10.18
Z_TRX_DETAILS_TF APPS 9.8 —–
ZX_REC_NREC_DIST ZX 8.95
MRP_RELIEF_INTERFACE MRP 8.63
HXC_MOB_LOG HXC 8.49 —
ENI_OLTP_ITEM_STAR ENI 8.38 —
MONTH_WISE_STOCK APPS 8.26 —
AP_PAYMENT_HIST_DISTS AP 8.19
ICX_CAT_ITEMS_CTX_HDRS_TLP ICX 8.18
XXCU_RP_SALES_INFO_TEST APPS 7.72 —–
WF_ITEM_ACTIVITY_STATUSES APPLSYS 7.61 —
AUD$_20240801 AUD_BACKUP 7.12 —

 

NOTE: As par oracle recommendation oracle suggested the highlighted tables above for compression.

SAFE ALSO MARKED ABOVE
————————

FND_LOG_MESSAGES 🔥 (best candidate)
GL_IMPORT_REFERENCES
XLA_DIAG_SOURCES
MTL_CST_ACTUAL_COST_DETAILS
WF_ITEM_ATTRIBUTE_VALUES
WF_ITEM_ACTIVITY_STATUSES
HXC_MOB_LOG
ENI_OLTP_ITEM_STAR
AUD$_20240801
ZX_REP_TRX_DETAIL_T
CST_PERIOD_CLOSE_SUMMARY
STOCK_REPORT_FOR_ALL
STOCK_REPORT_FOR_ALL_EBS_D
MONTH_WISE_STOCK

CUSTOM ALSO SAFE BUT MUST VERIFY ALSO MARKED ABOVE
—————————————————

AR_TRX_LINES_GT_BKP
DOC_ITEM_BKP
DOC_ITEM
DOC_ITEM_RP_DATA
Z_TRX_DETAILS_T
Z_TRX_DETAILS_TF
X_SALE_BATCH_LINES
RPRO_SALES_DATA_T
XXCU_RP_SALES_INFO_TEST
XXCU_RP_SALES_INFO_TEST1
MTL_TRANSACION_BCK
MTL_MAT_TBCK11
CHASE_GP_REPORT
PWC_MTL_SYSTEM_ITEMS_KFV

#########################################################################################################

1: First Compressed table
2: Second Rebuild there Indexes
3: Third run Gather Schema on that table which you have compressed.

EXAMPLE
——–

1: ALTER TABLE APPS.AP_INVOICES_ALL MOVE COMPRESS FOR OLTP;
2: ALTER INDEX APPS.<index_name> REBUILD COMPRESS;
3: BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘APPS’,
tabname => ‘AP_INVOICES_ALL’,
cascade => TRUE
);
END;
/

#########################################################################################################

FOR TESTING (CST_PERIOD_CLOSE_SUMMARY) <– i take this object for testing
———–

Better to copy the original table and then apply compression:

First take out the relevant objects of this table. (Like synonym , index and other objects)
———————————————————————————————-

select * from dba_objects where object_name =’CST_PERIOD_CLOSE_SUMMARY’ <– owner is BOM

Before compression size check the size
————————————
SELECT segment_name, bytes/1024/1024/1024 GB
FROM dba_segments
WHERE segment_name = ‘CST_PERIOD_CLOSE_SUMMARY’; <—- 26.73156..

Now take out the list of invalid object from the dba_objects
————————————————————-

select object_name,status from dba_objects where status=’INVALID’;

Before taking backup must check tablespace have space to take backup or not.
—————————————————————————–

SELECT a.tablespace_name,
ROUND (((c.BYTES – NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
c.BYTES / 1024 / 1024 space_allocated,
ROUND (c.BYTES / 1024 / 1024 – NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
c.DATAFILES
FROM dba_tablespaces a,
( SELECT tablespace_name,
SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name
) b,
( SELECT COUNT (1) DATAFILES,
SUM (BYTES) BYTES,
tablespace_name
FROM dba_data_files
GROUP BY tablespace_name
) c
WHERE b.tablespace_name(+) = a.tablespace_name
AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES – NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

check my table belongs to which tablespace
——————————————–
SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name = ‘CST_PERIOD_CLOSE_SUMMARY’; <— This object is in “APPS_TS_SUMMARY” tablespace

Also check default tablespace of BOM which is owner it will take backup on that tablespace which is default so you have to mention the correct tablespace
while creating backup otherwise it will take backup in default tablespace.
————————————————————————————————————-
SELECT default_tablespace
FROM dba_users
WHERE username = ‘BOM’;

Before taking backup must check there is enough space in os /u01 mount point then add datafile in “APPS_TS_SUMMARY”
——————————————————————————————————————

df -h

alter tablespace APPS_TS_SUMMARY add datafile ‘/u01/../../../../APPS_TS_SUMMARY’ size 30Gb;

Now first create a copy the table and apply compression on that copy for testing before applying on original table
———————————————————————————————————————

CREATE TABLE BOM.CST_PERIOD_CLOSE_SUMMARY_bkp AS SELECT * FROM BOM.CST_PERIOD_CLOSE_SUMMARY WHERE ROWNUM < 50000; <– for long tables

CREATE TABLE BOM.CST_PERIOD_CLOSE_SUMMARY_bkp AS SELECT * FROM BOM.CST_PERIOD_CLOSE_SUMMARY <– for normal tables

Note: Check the size of backup table is it as same as original table size.

take backup like this in actual right tablespace
————————————————–

CREATE TABLE BOM.CST_PERIOD_CLOSE_SUMMARY_bkp
TABLESPACE APPS_TS_SUMMARY
AS
SELECT * FROM BOM.CST_PERIOD_CLOSE_SUMMARY;

check backup table size
————————

SELECT segment_name, bytes/1024/1024/1024 GB
FROM dba_segments
WHERE segment_name = ‘CST_PERIOD_CLOSE_SUMMARY_bkp’;

Note: the size of bkp table is 11gb and original table is 26gb so dont worry it remove the fragmentation from that table.

 

Now must check the datatype of the table check it all columns are valid for compression of the table or not
———————————————————————————————————-

desc CST_PERIOD_CLOSE_SUMMARY_bkp

 

Check the indexes first before compress
—————————————-

SELECT *
FROM dba_indexes
WHERE table_name = ‘CST_PERIOD_CLOSE_SUMMARY’;

INDEX_NAME
1 CST_PERIOD_CLOSE_SUMMARY_C1
2 CST_PERIOD_CLOSE_SUMMARY_N1
3 CST_PERIOD_CLOSE_SUMMARY_N2

check indexes are already valid or not before compress on table
—————————————————————–

select owner,status,object_name from dba_objects where object_name in (‘CST_PERIOD_CLOSE_SUMMARY_C1′,’CST_PERIOD_CLOSE_SUMMARY_N1′,’CST_PERIOD_CLOSE_SUMMARY_N2’);

STATUS OBJECT_NAME
1 VALID CST_PERIOD_CLOSE_SUMMARY_N2
2 VALID CST_PERIOD_CLOSE_SUMMARY_N1
3 VALID CST_PERIOD_CLOSE_SUMMARY_C1

 

Now start the Compression: (Required extra space of same table size before compression in tablespace but temporary after whole activity done shrink the datafiles)
———————————————————————————————————————————————-

SQL> ALTER TABLE BOM.CST_PERIOD_CLOSE_SUMMARY_bkp MOVE ROW STORE COMPRESS ADVANCED;

Successfully compressed

——————————————————————————————

Check compression is enabled on table or not.
———————————————-

SELECT table_name, compression, compress_for
FROM dba_tables
WHERE table_name = ‘CST_PERIOD_CLOSE_SUMMARY’

TABLE_NAME COMPRESSION COMPRESS_FOR
CST_PERIOD_CLOSE_SUMMARY ENABLED ADVANCED

Compare the size after compression and before compression
———————————————————-

SELECT segment_name, bytes/1024/1024/1024 GB
FROM user_segments
WHERE segment_name = ‘CST_PERIOD_CLOSE_SUMMARY_bkp’;

Compare with original → % saving note karo

Index rebuild karo
———————

ALTER INDEX index_name REBUILD;

ALTER INDEX BOM.CST_PERIOD_CLOSE_SUMMARY_C1 REBUILD;
ALTER INDEX BOM.CST_PERIOD_CLOSE_SUMMARY_N1 REBUILD;
ALTER INDEX BOM.CST_PERIOD_CLOSE_SUMMARY_N2 REBUILD;

Shrink datafile if you have add any one temporary for this activity
———————————————————————–

ALTER DATABASE DATAFILE ‘/path/file.dbf’ RESIZE 1G;

SELECT
df.file_name,
df.tablespace_name,
df.bytes/1024/1024/1024 AS total_gb,
(df.bytes – NVL(fs.free_bytes,0))/1024/1024/1024 AS used_gb,
NVL(fs.free_bytes,0)/1024/1024/1024 AS free_gb
FROM dba_data_files df
LEFT JOIN (
SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space
GROUP BY file_id
) fs
ON df.file_id = fs.file_id;

————————————————————————-

Run Gather Stats on the table

 

###############################################################################################################

SQL> ALTER TABLE XX_TEST_COMP ROW STORE COMPRESS ADVANCED;

SQL> ALTER TABLE XX_TEST_COMP MOVE ROW STORE COMPRESS ADVANCED;

During the compression you may read on the Table but not Write on it…

###############################################################################################

Reducing the Oracle E-Business Suite Data Footprint (KA1128)

References :

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html
https://blogs.oracle.com/ebstech/using-advanced-compression-with-e-business-suite-databases