2014年数据库技术大会_DTCC2014_Exadata在运营商数据仓库系统中的运用-王科

OracleExadata在数据仓库系统中的运用从认识开始8年ORACLE从业经验开发、集成、运维、架构多岗位经验现任职于国内最大的数据库服务厂商上海新炬DTCC2013分享主题：OracleTimesTen企业级应用实践DTCC2014分享主题：Exadata在数据仓库系统中的运用王科([e-mail])13632429857业务系统向云端转移对技术架构与运维的关注度远高于某个产品基础设施外包，应用向套装或自行开发转移大家畅想一下5年后我们这个行业会怎么样？世界级Oracle专家JonathanLewis：我很为DBA们的未来担心（图灵访谈2013/11）Exadata的发展史，硬件能力不断提升Connectivity(Gb/s)Memory(GB)25616X850XV12008Flash(TB)CPU(Cores)Storage(TB)1684X33657624V220095.364241841841152X220105045.3962048400X3201250422.41288X3X0644096400X4201367244.8192注：数据来源于官方宣传文档。Exadata—分享大纲1.混合列压缩2.数据处理3.索引使用4.存储使用5.优化案例6.EM12C监控有一辆好车，就能跑起来了么？EHCC方式/速度压缩比QUERYLOWLZO6xQUERYHIGHZLIB10xARCHIVELOWZLIB12xARCHIVEHIGHBZ216x•逻辑压缩单元−CU是跨多个数据库块的逻辑结构−CU大小由数据库自动确定−在加载数据时按列组织数据−每一列都分别进行压缩−支持智能扫描•适合相对静止的数据−更新期间CU内的所有行会被锁定−更新会导致重组整个CU，压缩降级或不压缩CompressionUnitCREATE…asSELECTAPPENDINSERTIMPDPALTERTABLEMOVE节约存储成本减少I/O扫描(1/6)混合列压缩：数据仓库中不得不谈的核心功能!table_compression::=注：以上压缩比来自某数据仓库的实测值70%QueryHigh：41TB=HCC33TB，7.5x30%ArchiveHigh：8TB，13xA域CDR数据9TBB域CDR数据3TB(9+3)x30=360TB每天入库每月总量(1/6)混合列压缩：案例分享及压缩方式建议某运营商数据仓库混合列压缩案例(X2，HalfRack)：−CREATETABLEBIGTABLE……NOCOMPRESS;−SQL*Loader/IMPDP/APPENDINSERT−ALTERTABLEBIGTABLECOMPRESSFORQUERYHIGH;−or:ALTERTABLEBIGTABLEMOVEPARTITIONP1COMPRESSFORQUERYHIGH;−or:ALTERTABLEBIGTABLEMOVEPARTITIONPnCOMPRESSFORARCHIVEHIGH;不同分区，可使用不同的压缩类型常用的两种压缩方式：方式一：预定义−CREATETABLEBIGTABLE……COMPRESSFORQUERYHIGH;−SQL*Loader/IMPDP/APPENDINSERT方式二：后压缩(2/6)数据处理：集合数据处理DECLARECURREC_CUR;RECTYPE_TABLE%ROWTYPE;BEGINOPENCURFORSELECT*FROMTABLE1;LOOPFETCHCURINTO…IFCONDITION(REC)THENINSERTINTOTABLE2…ELSEINSERTINTOTABLE3…ENDIF;COMMIT;ENDLOOP;END;先来看一个简单的例子：逐行的数据处理方式，在大数据集合中，是否还合适？INSERT/*+append*/INTOTABLE2SELECT*FROMTABLE1WHERECONDITION…INSERT/*+append*/INTOTABLE3SELECT*FROMTABLE1WHERENOTCONDITION…INSERT/*+append*/FIRSTWHENCONDITIONTHENINTOTABLE2VALUES…ELSEINTOTABLE3VALUES…SELECT*FROMTABLE1;尽量简洁，SQL应该告诉数据库做什么而不是怎么做集合数据处理应有效的使用CPU和IO等资源也可以这样实现：(2/6)数据处理：集合数据处理集合数据处理的实现方式：(有哪些好处？)CREATETABLEtable1_newNOLOGGINGPARALLELCOMPRESSFORALLoperationsasSELECT*FROMtable1WHERElocal!=‘BEIJING'/ALTERTABLEtable1RENAMETOtable1_old/ALTERTABLEtable1_newRENAMETOtable1/ALTERSESSIONENABLEPARALLELDML/DELETEFROMtable1WHERElocal=‘BEIJING'/COMMIT/保留的记录(2/6)数据处理：转变思维，DML重写(DELETE)常见的DML场景：delete如果是大量数据的delete，可能会出现什么问题？ALTERSESSIONENABLEPARALLELDML/UPDATEtable1setprice=12WHEREprice=10andsdate'10-Apr-14'/COMMIT/(2/6)数据处理：转变思维，DML重写(UPDATE)常见的DML场景：update如果是大量数据的update，可能会出现什么问题？CREATETABLEtable1_newNOLOGGINGPARALLELCOMPRESSFORALLoperationsasSELECT…,caseprice=10andsdate‘10-Apr-14’then12elsepriceend,…FROMtable1/ALTERTABLEtable1RENAMETOtable1_old/ALTERTABLEtable1_newRENAMETOtable1/影响因素转换方式修改方式•压缩没有影响压缩效果可能下降因压缩可能引起性能问题•碎片没有碎片、行迁移都很有可能发生•LoggingandUNDO没有、或很少较多，影响性能•索引需要重建索引自动维护产生索引碎片位图索引需重建•元数据表权限等需要重新定义没有影响•空间需求2倍的数据空间UNDOandLogging•代码需要重写代码传统代码不变，但可能遭遇性能问题•其它第三方ETL工具可能不支持(2/6)数据处理：数据转换与修改两种方式的比较大量数据的修改，建议通过转换和表重定义而不是传统的OLTPDML方式这样才能发挥硬件和并行处理能力最小化数据碎片问题同时最大化压缩的效果最小化日志和数据恢复问题在Exadata环境中，索引使用成为一个有争议且值得探讨的问题在OLTP系统中，索引经常是提升性能的利器在大数据集合下，索引维护成本很高(碎片、重建…)索引对批量数据的DML操作，影响性能索引扫描，通常会产生随机的IO读取操作(不可预估，与查询条件有关)。假设I/O带宽从200MB/S提升到40GB/S，你是否依然坚定的选择索引扫描？(3/6)索引使用：这是一个值得探讨的问题|1|SORTAGGREGATE||1|8||||*2|INDEXRANGESCAN|IDX_BUFBSG|12M|93M|32621(1)|00:06:32|----------------------------------------------------------------------------------------Statistics----------------------------------------------------------48203consistentgets48203physicalreadsSQLaltersessionset_serial_direct_read=true;SQLselect/*+full(BUFBSG)*/count(*)fromBUFBSGwheredatatime=to_date('2014-03-2203:30:00');---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|8|740K(1)|02:28:06||1|SORTAGGREGATE||1|8||||*2|TABLEACCESSSTORAGEFULL|BUFBSG|12M|93M|740K(1)|02:28:06|---------------------------------------------------------------------------------------------Statistics----------------------------------------------------------2725896consistentgets2725865physicalreads(3/6)索引使用：真的需要使用索引吗？我们来看一个真实的案例：(注：已采集相应表的最新统计信息)SQLselectcount(*)fromBUFBSGwheredatatime=to_date('2014-03-2203:30:00');----------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|8|32621(1)|00:06:32|CBO聪明的选择了索引扫描，从执行计划来看，成本更低。42591.6406474.50348742591.6406474.503487(3/6)索引使用：真的需要使用索引吗？但事实却是这样的：SQLselectcount(*)fromBUFBSGwheredatatime=to_date('2014-03-2203:30:00');Elapsed:00:00:22.01SQLaltersessionset_serial_direct_read=true;SQLselect/*+full(BUFBSG)*/count(*)fromBUFBSGwheredatatime=to_date('2014-03-2203:30:00');Elapsed:00:00:03.49但实际情况，全表扫描更快。Exadata智能扫描，过滤掉40多GB的数据，实际传到DB节点的数据只有474MB，再加上InfiniBand网络的优势，全表扫描完胜索引扫描。SQLSELECTIO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024,IO_INTERCONNECT_BYTES/1024/