EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试
浏览:3423次 出处信息
随着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降级导致
建议继续学习:
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
后一篇:ORACLE 12C可以通过expdp导出view数据 >>
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: DATAGURAD EHCC EXADATA
- 发布时间:2012-12-18 23:15:28
近3天十大热文
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [53] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Oracle MTS模式下 进程地址与会话信
- [49] Go Reflect 性能
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [36] 程序员技术练级攻略
- [27] 视觉调整-设计师 vs. 逻辑