Oracle Advance Compress的使用與限制
Oracle Advanced Compression的使用與限制
--查詢資料insert之後的佔用的空間，發現以sys這個user將資料insert到另一SCHEMA
apps的三個table，有設定壓縮項目的table還是有壓縮的效果。
【前言】
最近客戶詢問了為何在Oracle以database user sys使用者與其它一般使用者對table進行壓縮時，兩者的壓縮比不一樣?
利用Google大神與Oracle官方網站找到的資料測試後，分享處理客戶詢問問題的心得。
【Oracle Advanced Compression功能】
使用Oracle Advanced Compression功能，藉由減少資料庫block重覆的值來壓縮資料，可大大減少資料的儲存空間。可以用來壓縮的資料庫物件包含了tables, materialized views與partitioned
tables(部份或全部)。
Oracle Advanced Compression應用於CREATE table時的子句，以下所列會因Oracle資料庫版本而略有差異，應用時請注意各版本之間引用子句的差異。
NOCOMPRESS
COMPRESS
COMPRESS FOR
DIRECT_LOAD OPERATIONS
COMPRESS FOR ALL
OPERATIONS
COMPRESS FOR OLTP
Oracle Advanced Compression於新增資料到table時要注意需符合下列條件才會進行資料壓縮。
Direct path
SQL*Loader
CREATE TABLE and
AS SELECT statements
Parallel INSERT (or
serial INSERT with an APPEND hint) statements
Single-row or
array inserts
Single-row or
array updates
【測試情境如下】
1.分別以兩個db user sys與apps各新增三個table，此步驟新增壓縮選項為BASIC, OLTP與無壓縮的table。
以db user sys登入資料庫後，在自己的tablespace 新增可壓縮的table
create table
test_compress_oltp COMPRESS For OLTP as select * from all_objects WHERE rownum
= 0;
create table
test_compress_basic COMPRESS BASIC as select * from all_objects WHERE rownum =
0;
create table
test_nocompress as select * from all_objects WHERE rownum = 0;
以db user apps登入資料庫後，在自己的tablespace 新增可壓縮的table
create table
test_compress_oltp COMPRESS For OLTP as select * from all_objects WHERE rownum
= 0;
create table
test_compress_basic COMPRESS BASIC as select * from all_objects WHERE rownum =
0;
create table
test_nocompress as select * from all_objects WHERE rownum = 0;
2. 查看目前各table設定的壓縮選項
Select OWNER,TABLESPACE_NAME,TABLE_NAME,COMPRESSION,COMPRESS_FOR from
dba_tables where OWNER IN('APPS','SYS') AND TABLE_NAME LIKE 'TEST_%' ORDER BY
OWNER;
3. 以db user sys進行insert data 到剛剛新增的三個table
(以insert指令需加入Hint APPEND才會套用壓縮。)
insert /*+ append
*/ into test_nocompress select * from all_objects;
commit;
insert /*+ append
*/ into test_compress_oltp select * from all_objects;
commit;
insert /*+ append
*/ into test_compress_basic select * from all_objects;
commit;
4. 以db user apps進行insert data 到剛剛新增的三個table
(以insert指令需加入Hint APPEND才會套用壓縮。)
insert /*+ append
*/ into test_nocompress select * from all_objects;
commit;
insert /*+ append
*/ into test_compress_oltp select * from all_objects;
commit;
insert /*+ append
*/ into test_compress_basic select * from all_objects;
commit;
5.查詢資料insert之後的佔用的空間
select OWNER,SEGMENT_NAME, sum(EXTENT_ID)
EXTENTS,sum(BLOCKS) BLOCKS, sum(BYTES)/1024/1024 MB from dba_extents where
OWNER IN('APPS','SYS') AND SEGMENT_NAME
LIKE 'TEST_%' group by OWNER,SEGMENT_NAME ORDER BY OWNER;
由上表可發現以APPS執行insert data於有壓縮選項的table都有明顯壓縮比例，而SYS這個user執行insert data幾乎沒有壓縮比可言。
6.接著以db user APPS刪除並新增原本位於SCHEMA APPS的三個table，此次改以db user sys 新增資料到SCHEMA APPS的三個table。
--Login as apps
--drop table
drop table test_compress_oltp;
drop table test_compress_basic;
drop table test_nocompress;
--create table
create table
test_compress_oltp COMPRESS For OLTP as select * from all_objects WHERE rownum
= 0;
create table
test_compress_basic COMPRESS BASIC as select * from all_objects WHERE rownum =
0;
create table
test_nocompress as select * from all_objects WHERE rownum = 0;
--Login as sys，並將在SCHEMA sys下的all_objects資料insert到SCHEMA
apps下的三個table
insert /*+ append
*/ into apps.test_nocompress select * from all_objects;
COMMIT;
insert /*+ append
*/ into apps.test_compress_oltp select * from all_objects;
COMMIT;
insert /*+ append
*/ into apps.test_compress_basic select * from all_objects
COMMIT;
7.以執行計劃比較有壓縮和沒有壓縮的表格於查詢資訊時的CPU或I/O執行成本差異。
由下方進行兩個有壓縮和沒有壓縮的表格查詢，可發現有壓縮的table的CPU執行成本還更低。
a)查詢有壓縮的table apps.test_compress_oltp
set autotrace on
explain
col object_name
format a50
select count(*)
from test_compress_oltp;
b)查詢無壓縮的table apps.test_nocompress
set autotrace on
explain
col object_name
format a40
select count(*)
from test_nocompress;
【結論】
Oracle Advanced Compression功能於create table時可加入COMPRESS設定表格壓縮功能，但有其限制，無法在SCHEMA SYS與SYSTEM Tablespace的table上進行有效的壓縮，請查看下方參考資料於應用此功能時，了解應用上的限制，避免誤用此功能。
【參考資料】
Oracle 11g
1.請查詢Doc ID 882712.1關於啟用Oracle Advanced Compression功能及其限制的詳細說明。
2.Oracle的官方文件參考網站
上述網頁提到
You cannot specify any type of table compression for tables
with LONG or LONG RAW columns, tables that are owned by the SYS schema and reside in the
SYSTEM tablespace, or tables with ROWDEPENDENCIES enabled.
Oracle 12c
上述網頁提到
You cannot specify any type of table compression for tables
with LONG or LONG RAW columns, tables that are owned by the SYS schema and reside in the
SYSTEM tablespace, or tables with ROWDEPENDENCIES enabled.
Oracle Advanced Compression
回覆刪除