技术头条 - 一个快速在微博传播文章的方式     
您现在的位置：首页 --> Oracle --> EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

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

浏览:3860次  出处信息

随着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* fromv$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database11g 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 forLinux: 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> SELECTOPEN_MODE, DATABASE_ROLE ,NAMEFROMV$DATABASE;
 
OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READWRITE           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> createtablet_FF_c compress asselect  * fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_FF_c_o compress foroltp  asselect  * fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_FF_q_l compress forquery low asselect  * fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_FF_q_h compress forquery high  asselect  * fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_FF_a_l compress forarchive low  asselect  * fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_FF_a_h compress forarchive high asselect* fromdba_objects;
 
Tablecreated.
 
SQL> createtablet_ff asselect* fromdba_objects;
 
Tablecreated.

xd查询模拟表

SQL> selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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> SELECTOPEN_MODE, DATABASE_ROLE ,NAMEFROMV$DATABASE;
 
OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READONLYWITHAPPLY 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 Dec19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

查询非xd dg备库

SQL> selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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> SELECTCOUNT(*) FROMT_FF_Q_L;
 
  COUNT(*)
----------
     94709
 
SQL> SELECTCOUNT(*) FROMT_FF_Q_H;
 
  COUNT(*)
----------
     94710
 
SQL> SELECTCOUNT(*) FROMT_FF_C_O;
 
  COUNT(*)
----------
     94708
 
SQL> SELECTCOUNT(*) FROMT_FF_C;
 
  COUNT(*)
----------
     94707
 
SQL> SELECTCOUNT(*) FROMT_FF_A_L;
 
  COUNT(*)
----------
     94711
 
SQL> SELECTCOUNT(*) FROMT_FF_A_H;
 
  COUNT(*)
----------
     94712
 
SQL> selectcount(*) fromt_FF;
 
  COUNT(*)
----------
    94713

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

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

--xd 主库
SQL> updatet_ff_a_h setowner='www.xifenfei.com';
 
94712 rowsupdated.
 
SQL> commit;
 
Commitcomplete.
 
SQL>  selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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 rowsselected.
 
SQL> altersystem switch logfile;
 
System altered.
 
--非xd 备库
SQL> selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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> createtablet_FF_a_l_1 compress forarchive low asselect* fromt_FF  where1=0;
 
Tablecreated.
 
SQL> createtablet_FF_a_h_1 compress forarchive high  asselect* fromt_FF  where1=0;
 
Tablecreated.
 
SQL>truncatetablet_FF;
 
Tabletruncated.
 
--插入数据(每个表执行5次)
SQL> insert/*+ APPEND */ intot_FF_a_l_1 select* fromdba_objects; 
 
94714 rowscreated.
 
SQL> commit;
 
Commitcomplete.
 
SQL> insert/*+ APPEND */ intot_FF_h_l_1 select* fromdba_objects; 
 
94714 rowscreated.
 
SQL> commit;
 
Commitcomplete.
 
SQL> insert/*+ APPEND */ intot_FF select* fromdba_objects; 
 
94714 rowscreated.
 
SQL> commit;
 
Commitcomplete.
 
--查看相关表数据量
SQL> selectcount(*) fromt_FF_a_l_1;
 
  COUNT(*)
----------
    473570
 
SQL> selectcount(*) fromt_FF_a_h_1;
 
  COUNT(*)
----------
    473570
 
SQL> selectcount(*) fromt_FF;
  COUNT(*)
----------
    473570
 
--查看xd主库
SQL> selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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> selects.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
fromdba_segments s,dba_tables t
wheres.owner=t.owner andt.table_name=s.segment_name  andt.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降级导致

建议继续学习：

  1. 关于Exadata    (阅读：2023)
  2. Exadata：存储节点上所有监控指标与其监控概览    (阅读：1754)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：了解Database Replay Capture内部原理
后一篇：ORACLE 12C可以通过expdp导出view数据 >>
文章信息
© 2009 - 2025 by blogread.cn 微博：@IT技术博客大学习

京ICP备15002552号-1