EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

随着xd的越来越普及,不少的企业使用了xd,但是不少企业因为资金有限,只有一台xd,但是为了实现数据的容灾,可能会使用一台非xd的机器来通过dataguard来实现容灾,但是因为xd的ehcc新特性,官方宣传是只在xd中支持,如果dg的备库不是xd。那么会怎么样,这里通过测试得出如下一些结论:xd与非xd可以构造dg,ehcc功能在xd上无法高效使用。对于这样的环境条件下,使用ORACLE自带压缩效率更高.针对ehcc压缩效率很低,个人猜测,是因为xd检查到备库是非xd环境,直接对ehcc进行了降级压缩处理,从而出现了ehcc的压缩效率比oltp还低(牺牲了xd的性能,确保了数据的安全,看来xd的设计还是考虑的比较全面)
xd基本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter clu;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;

OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ WRITE           PRIMARY          xxxxxx

SQL> !uname -a
Linux dm01db02 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

xd创建模拟表

SQL> create table t_FF_c compress as select  * from dba_objects;

Table created.

SQL> create table t_FF_c_o compress for oltp  as select  * from dba_objects;

Table created.

SQL> create table t_FF_q_l compress for query low as select  * from dba_objects;

Table created.

SQL> create table t_FF_q_h compress for query high  as select  * from dba_objects;

Table created.

SQL> create table t_FF_a_l compress for archive low  as select  * from dba_objects;

Table created.

SQL> create table t_FF_a_h compress for archive high as select * from dba_objects;

Table created.

SQL> create table t_ff as select * from dba_objects;

Table created.

xd查询模拟表

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

通过这里发现,带有非dg的xd使用ehcc压缩效率都低了很多

非xd备库基本信息

SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;

OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ ONLY WITH APPLY PHYSICAL STANDBY xxxxxx

SQL> show parameter clu;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string

SQL> !uname -a
Linux oradg 2.6.18-238.el5xen #1 SMP Sun Dec 19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

查询非xd dg备库

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

SQL> SELECT COUNT(*) FROM T_FF_Q_L;

  COUNT(*)
----------
     94709

SQL> SELECT COUNT(*) FROM T_FF_Q_H;

  COUNT(*)
----------
     94710

SQL> SELECT COUNT(*) FROM T_FF_C_O;

  COUNT(*)
----------
     94708

SQL> SELECT COUNT(*) FROM T_FF_C;

  COUNT(*)
----------
     94707

SQL> SELECT COUNT(*) FROM T_FF_A_L;

  COUNT(*)
----------
     94711

SQL> SELECT COUNT(*) FROM T_FF_A_H;

  COUNT(*)
----------
     94712

SQL> select count(*) from t_FF;

  COUNT(*)
----------
    94713

通过这里测试证明,对于非xd dg库,可以正常的查询xd上的ehcc相关表,而且相关大小也相同(物理dg当然相同了)

测试xd与非xd dg测试ehcc的dml操作

--xd 主库
SQL> update t_ff_a_h set owner='www.xifenfei.com';

94712 rows updated.

SQL> commit;

Commit complete.

SQL>  select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

7 rows selected.

SQL> alter system switch logfile;

System altered.

--非xd 备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

证明对于xd与非xd构成的dg环境,可以执行dml操作.

测试xd与非xd dg的ehcc的append操作

--xd准备三张测试空表
SQL> create table t_FF_a_l_1 compress for archive low as select * from t_FF  where 1=0;

Table created.

SQL> create table t_FF_a_h_1 compress for archive high  as select * from t_FF  where 1=0;

Table created.

SQL>truncate table t_FF;

Table truncated.

--插入数据(每个表执行5次)
SQL> insert /*+ APPEND */ into t_FF_a_l_1 select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.


SQL> insert /*+ APPEND */ into t_FF_h_l_1 select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t_FF select * from dba_objects; 

94714 rows created.

SQL> commit;

Commit complete.

--查看相关表数据量
SQL> select count(*) from t_FF_a_l_1;

  COUNT(*)
----------
    473570

SQL> select count(*) from t_FF_a_h_1;

  COUNT(*)
----------
    473570

SQL> select count(*) from t_FF;
  COUNT(*)
----------
    473570

--查看xd主库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW

--查看非xd备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%'; 

OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW

试验整体结论
1.xd可以与非xd机器构建dg容灾环境(不会因为非dg不支持ehcc而导致无法搭建他们之间的dg)
2.xd与非xd的dg,ehcc功能大大缩水,基本上和非压缩状态差不多,比OLTP低很多
3.xd与非xd的dg在备库中支持select,dml,hint append等操作,这些操作是因为ehcc表在xd端就进行了ehcc降级导致

此条目发表在 Data Guard, EXADATA 分类目录。将固定链接加入收藏夹。

EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试》有 1 条评论

  1. 311ybb 说:

    maa-wp-dr-dbm-130065.pdf 中对非Exdata的standby这样的描述:
    However, given that third-party storage is unable to support HCC, the following complications and restrictions arise:
    ● Active Data Guard cannot read HCC compressed tables on a third party storage. In read-only mode, any attempt to select from an HCC table at the standby will yield the following error: ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata, ZFSSA, or AXIOM storage
    ● Data Guard Snapshot Standby cannot read HCC compressed tables on third party storage

    To access HCC tables you must failover to the standby and decompress using ‘alter table move’ for each HCC table. During the alter table move operation the table will be locked. SQL> recover managed standby database finish; SQL> alter database commit to switchover to primary; SQL> alter database open;
    SQL> alter table move ;
    看楼主的测试貌似并没遇到白皮书里描述的这些问题?