Oracle Tips and Tricks — David Fitzjarrell

November 9, 2013

“Compressing” Matters

Filed under: General — dfitzjarrell @ 14:55

Compression is often mentioned in reverent tones as the cure for database space problems, and properly used it can be just that. However simply throwing compression at a table without careful consideration of what can be accomplished or what behavior to expect afterwards can leave you scratching your head in confusion. Let’s look at the compression options available in Oracle 11.2, noting what happens after the initial compression is completed and data is modified.

Oracle 11.2.0.x provides, out of the box, two types of table compression, BASIC and OLTP. BASIC is exactly what its name says, compressed with a basic compression algorithm. Let’s see what space savings BASIC can provide:

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
SALGRADE                                  65536

SQL> alter table emp move compress;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   184549376
SALGRADE                                  65536

SQL>

So far, so good. Let’s now update some data in the EMP table and see what happens to the consumed space:

SQL> update emp set sal=5001 where sal=5000;

1048576 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   234881024
SALGRADE                                  65536

SQL> 

The update caused Oracle to uncompress the rows being updated and, thus, increase the space consumed by the table. You can re-compress the table after updates but that could get to be a daunting task, requiring a stored procedure for inserts, updates and deletes to the affected table or tables. A second option is a scheduled job run late at night to re-compress the compressed tables.

OLTP compression works a bit differently, as it compresses the data but will, when affected data blocks are marked as full, re-compress the block automatically. Let’s look at the same table using OLTP compression. First let’s uncompress the table:

SQL> alter table emp move nocompress;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   645922816
SALGRADE                                  65536

SQL> 

It’s interesting to note that the uncompressed table size has decreased slightly from its original value, possibly because unnecessary NULL bytes have been removed (this is a guess). Compressing for OLTP and updating the same rows produces these results:

SQL> alter table emp move compress for oltp;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   184549376
SALGRADE                                  65536

SQL> update emp set sal=5000 where sal=5001;

1048576 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   201326592
SALGRADE                                  65536

SQL>

Notice that the compressed size of the OLTP table is initially the same as it was for BASIC compression; the difference is in the compressed size after the update. It’s the OLTP compression mechanism that does this, as some of the updated blocks are now full and triggered the automatic re-compression of the data. Of course these are not the only two compression options, but the next set of compression types, using the Hybrid Columnar Compression (HCC) algorithm, require licensing the Advanced Compression feature.

HCC works differently than Basic or OLTP compression as it re-organizes data into Compression Units (CU). Note that there are two types of HCC compression, QUERY and ARCHIVE, with two levels of LOW and HIGH for each. Using the same examples from a previous article let’s look at these compression types in action. First in line is QUERY:

SQL> 
SQL> --
SQL> -- Current storage for the EMP table
SQL> -- (this is simply a test table for this example)
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.82
SQL> 
SQL> --
SQL> -- Compress the table for query high (use HCC)
SQL> --
SQL> -- Note elapsed time to compress
SQL> --
SQL> alter table emp move compress for query high;

Table altered.

Elapsed: 00:00:35.65
SQL> 
SQL> --
SQL> -- Index is now invalid
SQL> --
SQL> -- Must rebuild to make it usable
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:01:13.70
SQL> 
SQL> --
SQL> -- Current compression type, storage for table/index
SQL> -- initially after compression is enabled
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  QUERY HIGH

Elapsed: 00:00:00.20
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     8388608
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.03
SQL>

The resulting size is much smaller than either of the Basic or OLTP compression options. Even at the Query Low compression rate the size is still less than OLTP compression can provide:

SQL> 
SQL> --
SQL> -- Initial storage
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.25
SQL> 
SQL> --
SQL> -- Compress for QUERY LOW
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter table emp move compress for query low;

Table altered.

Elapsed: 00:00:16.16
SQL> 
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:43.08
SQL> 
SQL> --
SQL> -- These figures are the same as those generated
SQL> -- AFTER the HCC compressed data was updated the first time
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  QUERY LOW

Elapsed: 00:00:00.02
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                    14680064
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL>

The QUERY compression type is definitely a space saver, but note that after updates to a table compressed with either QUERY LOW or QUERY HIGH the compression behavior reverts to the OLTP re-compression mechanism.

The HCC compression type ARCHIVE is definitely the most aggressive in terms of space savings, but it’s also intended for data that is designated read-only and is or will be archived for occasional use:

SQL> --
SQL> -- Current storage for the EMP table
SQL> -- (this is simply a test table for this example)
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL> 
SQL> --
SQL> -- Compress the table for archive high
SQL> --
SQL> -- Note elapsed time to compress
SQL> --
SQL> alter table emp move compress for archive high;

Table altered.

Elapsed: 00:00:38.55
SQL> 
SQL> --
SQL> -- Index is now invalid
SQL> --
SQL> -- Must rebuild to make it usable
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:39.45
SQL> 
SQL> --
SQL> -- Current compression type, storage for table/index
SQL> -- initially after compression is enabled
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;



TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  ARCHIVE HIGH

Elapsed: 00:00:00.02
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     4194304
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.01
SQL>

The space savings are substantial, taking the table from its original size of 680 megabytes down to 4 megabytes, a savings of 99.41 percent. Using ARCHIVE LOW instead of ARCHIVE HIGH still produces impressive results:

SQL> --
SQL> -- Initial storage
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:01.31
SQL> 
SQL> --
SQL> -- Compress for ARCHIVE LOW
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter table emp move compress for archive low;

Table altered.

Elapsed: 00:00:34.16
SQL> 
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:48.44
SQL> 
SQL> --
SQL> -- These figures are the same as those generated
SQL> -- AFTER the HCC compressed data was updated the first time
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  ARCHIVE LOW

Elapsed: 00:00:00.03
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     8388608
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL>

Using ARCHIVE LOW the table ends up twice as large as it did using ARCHIVE HIGH but the table is 98.82 percent smaller than it was before compression. This is the same level of compression afforded by QUERY HIGH. The same caveat for QUERY level compression also applies to ARCHIVE; updates to a table compressed either ARCHIVE LOW or ARCHIVE HIGH cause Oracle to revert to the OLTP re-compression mechanism. Also note that the reported compression type in the data dictionary does NOT change when this occurs.

Compression can be an excellent tool to save database storage, but you need to be aware that the compression levels can change for updated tables when running Exadata and using any of the HCC compression types. Also it’s good to be aware that BASIC compression won’t automatically re-compress updated data; maintaing a reasonable compression ratio will require some sort of manual or scheduled intervention, so using OLTP compression on active tables is likely the best choice if you are planning on using table compression.

Compression: saving space, one byte at a time.

About these ads

1 Comment »

  1. Excellent example.

    Comment by dikpater.werk@gmail.com — November 11, 2013 @ 10:47 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 675 other followers

%d bloggers like this: