Compression Methods on Exadata: Create compression table using ALTER TABLE MOVE – Part 2 of 6

moveData
In the last post, it shows the examples to create compression tables using CREATE TABLE clause. In reality, majority of the time, we have existing tables in place and we just need to change it to a compression table. It is important to know when the compression happens. For example, if uses ALTER TABLE … COMPRESS FOR QUERY HIGH command, there is NO change to the existing data in the current table. Only after you uses MOVE keyword to rebuild the segment, it can really do the compression work. As usual, I shows different ways to use MOVE to compress tables as follows. Of course, in my examples, I do not have a plan to use a dolly to move data. But if you have multiple petabyte or even exabyte of data, dolly from a truck may be the faster way to move compared with slow network transfer via WAN across the country.

BASIC Compression:

WZHOU@dbm1> create table movetab_basic as select * from testcp;

Table created.

Elapsed: 00:00:09.38

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_BASIC';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_BASIC              YES DISABLED

Elapsed: 00:00:00.01

WZHOU@dbm1> ALTER TABLE movetab_basic MOVE COMPRESS;

Table altered.

Elapsed: 00:00:29.78

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables  2  where table_name='MOVETAB_BASIC';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_BASIC              YES ENABLED  BASIC

Elapsed: 00:00:00.01

OLTP Compression:


WZHOU@dbm1> create table movetab_oltp as select * from testcp;

Table created.

Elapsed: 00:00:08.30

WZHOU@dbm1> ALTER TABLE movetab_oltp MOVE COMPRESS FOR oltp;

Table altered.

Elapsed: 00:00:29.03

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
   where table_name='MOVETAB_OLTP';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_OLTP               YES ENABLED  OLTP

Elapsed: 00:00:00.00

Query Low Compression:

WZHOU@dbm1> create table movetab_query_low as select * from testcp;

Table created.

Elapsed: 00:00:08.82

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_QUERY_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_LOW          YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_query_low MOVE COMPRESS for query low;

Table altered.

Elapsed: 00:00:15.70

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_QUERY_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_LOW          YES ENABLED  QUERY LOW

Elapsed: 00:00:00.01

Query High Compression:


WZHOU@dbm1> create table movetab_query_high as select * from testcp;

Table created.

Elapsed: 00:00:08.21

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_QUERY_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_HIGH         YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_query_high MOVE COMPRESS for query high;

Table altered.

Elapsed: 00:00:27.60

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_QUERY_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_HIGH         YES ENABLED  QUERY HIGH

Elapsed: 00:00:00.00

Archive Low Compression:


WZHOU@dbm1> create table movetab_archive_low as select * from testcp;

Table created.

Elapsed: 00:00:08.74

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_ARCHIVE_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_LOW        YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_archive_low MOVE COMPRESS for archive low;

Table altered.

Elapsed: 00:00:28.26

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_ARCHIVE_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_LOW        YES ENABLED  ARCHIVE LOW

Elapsed: 00:00:00.00

Archive High Compression:

WZHOU@dbm1> create table movetab_archive_high as select * from testcp;

Table created.

Elapsed: 00:00:08.23

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_ARCHIVE_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_HIGH           YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_archive_high MOVE COMPRESS for archive high;

Table altered.

Elapsed: 00:02:32.26
WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_ARCHIVE_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_HIGH           YES ENABLED  ARCHIVE HIGH

Elapsed: 00:00:00.00

Remember from last post, the uncompressed table size is 1,152 MB. You can see the sizes of the tables after compression are quite similar to the approaches using CREATE TABLE in the last post.

WZHOU@dbm1> @table_size
Enter value for owner: WZHOU
Enter value for table_name: MOVE%
Enter value for type:

OWNER   SEGMENT_NAME           TYPE    TOTALSIZE_MEGS TABLESPACE_NAME
------- ------------------------------ ---------- --------------
WZHOU   MOVETAB_ARCHIVE_HIGH   TABLE      48.0 USERS
WZHOU   MOVETAB_ARCHIVE_LOW    TABLE      58.0 USERS
WZHOU   MOVETAB_QUERY_HIGH     TABLE      59.0 USERS
WZHOU   MOVETAB_QUERY_LOW      TABLE     160.0 USERS
WZHOU   MOVETAB_BASIC          TABLE     384.0 USERS
WZHOU   MOVETAB_OLTP           TABLE     424.0 USERS
                                       --------------
sum                                    1,133.0

6 rows selected.

[update 1]
So what’s happened if I need to access this table during the move, can I still do the select or other DML operation against this table during the move? With this question in mind, I did a few tests to see how it behaves during the MOVE operation. Make the Move operation longer, I use ARCHIVE HIGH compression method to see the difference. I also opened 5 more db sessions to do SELECT, INSERT, UPDATE, DELETE and monitor operations.

Session 1:

WZHOU@dbm1> create table movetab_chkop as select * from testcp;
Table created.
Elapsed: 00:00:08.32

Then in the same session, run the compression for archive high. During the compression process, run other operations in different sessions as follows:

ALTER TABLE movetab_chkop MOVE COMPRESS for archive high;

Session 2 (check SELECT):
select id, object_name from movetab_chkop where id = 10;
select count(*) from movetab_chkop;

Session 3 (check INSERT):
insert into movetab_chkop select * from testcp where id = 1;

Session 4 (check UPDATE):
update movetab_chkop set id = 1001 where id = 10000;

Session 5 (check DELETE):
delete from movetab_chkop where id = 5000;

Session 6 (Monitor the other sessions)
run as.sql script to check out the active session.
as.sql

The script to check who is blocking who

select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

The result are listed as follows:

Session 1 (Do archive high compression)

WZHOU@dbm1> ALTER TABLE movetab_chkop MOVE COMPRESS for archive high;
Table altered.
Elapsed: 00:02:32.15

Session 2 (check SELECT)

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 10;

	ID OBJECT_NAME
---------- ------------------------------
	10 I_OBJ5

Elapsed: 00:00:00.70

WZHOU@dbm1> select count(*) from movetab_chkop;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:00.65

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 10000;

	ID OBJECT_NAME
---------- ------------------------------
     10000 KU$_M_VIEW_SCM_T

Elapsed: 00:00:00.04

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 5000;

	ID OBJECT_NAME
---------- ------------------------------
      5000 UTL_RECOMP_ALL_OBJECTS
Elapsed: 00:00:00.02

Session 3 (check INSERT)

WZHOU@dbm1> insert into movetab_chkop select * from testcp where id = 1;

1 row created.
Elapsed: 00:02:09.70

Session 4 (check UPDATE)

WZHOU@dbm1> update movetab_chkop set id = 1001 where id = 10000;

1 row updated.
Elapsed: 00:02:04.50

Session 5 (check DELETE)

WZHOU@dbm1> delete from movetab_chkop where id = 5000;

1 row deleted.
Elapsed: 00:01:59.17

Session 6 (Monitor the other sessions)

WZHOU@dbm1> @as

  SID	 SERIAL# PROG	    SQL_ID	   CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME OFF SQL_TEXT
----- ---------- ---------- ------------- ------ --------------- ---------- ----------- --- -----------------------------------------
 1167	     587 sqlplus@en 1vz2hfh9wccwd      0      3566611749	  0	    .00 No  insert into movetab_chkop select * from t
 1945	      15 sqlplus@en 8856nfxk46wv8      0       900447529	  0	    .00 No  delete from movetab_chkop where id = 5000
  976	     669 sqlplus@en 9qta3dvp1jp5f      0       143915756	  0	  67.22 Yes ALTER TABLE movetab_chkop MOVE COMPRESS f
 1751	      15 sqlplus@en b2qff928t8ca7      1      2858995896	  8	    .35 No  select sid, serial#,substr(program,1,19)
 1557	     341 sqlplus@en f23grns86fvmn      0      3466372318	  0	    .01 No  update movetab_chkop set id = 1001 where

Elapsed: 00:00:00.45

WZHOU@dbm1> select l1.sid, ' IS BLOCKING ', l2.sid
  from v$lock l1, v$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
        and l1.id2=l2.id2;

  SID 'ISBLOCKING'    SID
----- ------------- -----
  976  IS BLOCKING   1167
  976  IS BLOCKING   1557
  976  IS BLOCKING   1945

Elapsed: 00:00:00.08

After the compression completes, all DML operations are unblocked.

WZHOU@dbm1> @as

  SID	 SERIAL# PROG	    SQL_ID	   CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME OFF SQL_TEXT
----- ---------- ---------- ------------- ------ --------------- ---------- ----------- --- -----------------------------------------
 1751	      15 sqlplus@en b2qff928t8ca7      1      2858995896	  9	    .36 No  select sid, serial#,substr(program,1,19)

Elapsed: 00:00:00.44

WZHOU@dbm1> select l1.sid, ' IS BLOCKING ', l2.sid
  from v$lock l1, v$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
        and l1.id2=l2.id2;

no rows selected

From the above results, we can see the MOVE operation is blocking all DML operations while select operation is not blocked. So if you have a big table to be compressed, there will be a period all DML operations will be blocked during the move.

Advertisements

One thought on “Compression Methods on Exadata: Create compression table using ALTER TABLE MOVE – Part 2 of 6

  1. Pingback: Compression Methods on Exadata: Use parallel to speed up compression – Part 3 of 6 | My Big Data World

Leave a Reply

Please log in using one of these methods to post your comment:

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