OLTP compression

[Roshan]

Hi Erman,

I am using OLTP compression for my tables in a datawarehouse test enviornment.

Please check link below
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

Kindly advise if it is good to implement it. In terms of storage it is ok. But performance and other factors?

[ErmanArslansOracleBlog]

I will directly reply to this by using Oracle Support Note named : Master Note for OLTP Compression (Doc ID 1223705.1)

One thing that I can say directly is;

The amount of undo created when updating a table set for OLTP compression will be greater than the amount of undo created for a non-compressed table. Likewise, whatever is written to undo is also written to the redo logs so there is a corresponding increase in redo when OLTP compression is used.

Another thing;

Performance is expected to improve for queries and deletes due to the reduction in physical reads and having no additional overhead for those operations. Concurrency may suffer due to increased rows per block and so should be monitored and addressed if needed. Performance for updates can benefit from fewer physical reads but there is occasional overhead when blocks must be compressed. Performance for inserts has the occasional overhead of block compression as well so it is important to use direct path when possible and otherwise to minimize the number of times a given block is compressed.

Read "Master Note for OLTP Compression (Doc ID 1223705.1)" for more.

[Roshan]

Hi Erman,

I have reviewed the doc ID.

"Concurrency may suffer due to increased rows per block and so should be monitored and addressed if needed."

How do I monitor it? AWR report?

[ErmanArslansOracleBlog]

You can use Enteprise manager in the first place.

Here is a reference for you - >

https://docs.oracle.com/database/121/TDPPT/tdppt_realtime.htm#TDPPT033