随着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降级导致
加我QQ(107644445)
