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.
My Big Data World 
You must be logged in to post a comment.