X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

Critical Statistics for OLTP Table Compression

Guest Author

There are various OLTP Table Compression session level statistics available to help identify space savings with this feature. These statistics are very important to figure out the benefits of compression, and if compression is indeed helping in your environment. Since the statistic definitions didn't make it into the Reference manual, I thought it was a good idea to list few of the critical ones for OLTP Compression that can help you get an idea of space savings, amount and kind of DML activity in your environment.

Note that OLTP Table Compression got renamed to Advanced Row Compression in Oracle 12c and is part of Advanced Compression Option.

Statistic Name

Description

HSC Compressed Segment
Block Changes

Total number of block
changes to Tables/Heaps Segments(Compressed only).

HSC Heap Segment Block
Changes

Total number of block
changes to Tables/Heaps Segments (Compressed or Non-Compressed).

HSC IDL Compressed
Blocks

Number of blocks
compressed during Insert Direct Load  - actual number of blocks.

HSC OLTP Compressed
Blocks

Number of blocks
compressed during DML (inserts and updates) activity - actual number of
blocks.

HSC OLTP Non
Compressible Blocks

Blocks marked as Final
(Not to be compressed again unless substantial changes to the data in the
block).

HSC OLTP Space Saving

Number of bytes saved
in total using OLTP Table Compression. Take the delta on every compression and adds.

HSC OLTP inline
compression

Number of Inline Compressions. Inline Compression is compression of a block inline in the user transaction. Space released is associated (held reserved) with the user transaction and can only be used by this transaction till the user transaction commits. Once the user transaction commits, the space is released for other transactions.

Total number of block compressions  = Inline + Recursive +
IDL Compressions

HSC OLTP recursive
compression

Number of Recursive Compressions. Recursive Compression is compression of a block in a recursive transaction which is immediately committed. Space released is not associated (not reserved) with the user transaction triggering compression and can be used by any transaction immediately.

Total number of block
compressions  = Inline + Recursive + IDL Compressions

HSC OLTP positive
compression

Number of times
compression was beneficial (post compression block had more free space).

HSC OLTP negative
compression

Number of times
compression was negative (post compression block had less free space) and was reverted back.

HSC OLTP Drop Column

Number of compression attempts due to drop column.

HSC OLTP Compression skipped rows

Number of rows that
are skipped for compression (could be deleted or non-compressible etc.).

Heap Segment Array
Inserts

Number of array
inserts into Heap Segments.

Heap Segment Array
Updates

Number of array
updates into Heap segments.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.