oracle数据库高级技术交流计划-大批量数据处理技术.ppt

Oracle数据库高级技术交流 大批量数据处理技术 Oracle 中国 顾问咨询部罗敏资深技术顾问电话Mail Michael luo 交流内容 分区技术报表优化技术并行处理应用经验 Oracle的分区技术 分区技术内容 什么是分区 分区的好处 如何实施分区 如何评估分区的效果 Oracle的分区技术基本原理 分而治之 2003 2004 2005 2006 分区概述 大数据对象 表 索引 被分成小物理段当分区表建立时 记录基于分区字段值被存储到相应分区 分区字段值可以修改 rowmovementenabled 分区可以存储在不同的表空间分区可以有不同的物理存储参数分区支持IOT表 对象表 LOB字段 varrays等 分区技术的效益和目标 性能Select和DML操作只访问指定分区并行DML操作Partition wiseJoin可管理性 数据删除 数据备份历史数据清除提高备份性能指定分区的数据维护操作可用性将故障局限在分区中缩短恢复时间分区目标优先级高性能 数据维护能力 实施难度 高可用性 故障屏蔽能力 分区方法 分区方法 范围 8Hash 8i列表 9i组合 8i Rangepartitioning Hashpartitioning 1 2 3 CREATETABLEsales acct noNUMBER 5 personVARCHAR2 30 sales amountNUMBER 8 week noNUMBER 2 PARTITIONBYRANGE week no PARTITIONP1VALUESLESSTHAN 4 TABLESPACEdata0 PARTITIONP2VALUESLESSTHAN 8 TABLESPACEdata1 PARTITIONP13VALUESLESSTHAN 53 TABLESPACEdata12 分区字段 week no VALUESLESSTHAN必须是确定值每个分区可以单独指定物理属性 1 2 3 范围分区例 最早 最经典的分区算法Range分区通过对分区字段值的范围进行分区Range分区特别适合于按时间周期进行数据的存储 日 周 月 年等 数据管理能力强数据迁移数据备份数据交换范围分区的数据可能不均匀范围分区与记录值相关 实施难度和可维护性相对较差 范围分区特点 Hash分区例 createtableCUSTOMERS columndefinitions pctfree0nologgingstorage initial40mnext40mpctincrease0 partitionbyhash customer no partitions8storein cust data01 cust data02 createtableCUSTOMERS columndefinitions pctfree0nologgingstorage initial40mnext40mpctincrease0 partitionbyhash customer no partitioncust p01tablespacecust data01 partitioncust p02tablespacecust data02 partitioncust p03tablespacecust data03 partitioncust p04tablespacecust data04 partitioncust p05tablespacecust data05 partitioncust p06tablespacecust data06 partitioncust p07tablespacecust data07 partitioncust p08tablespacecust data08 Hash分区特点 基于分区字段的HASH值 自动将记录插入到指定分区 分区数一般是2的幂易于实施总体性能最佳适合于静态数据HASH分区适合于数据的均匀存储HASH分区特别适合于PDML和partition wisejoins 支持 hash localindexes9i不支持 hash globalindexes10g支持 hash globalindexesHASH分区数据管理能力弱HASH分区对数据值无法控制 列表分区例 createtableaddresses columndefinitions pctfree0nologgingstorage initial40mnext40mpctincrease0 partitionbylist city name partitionaddr p01values WELLINGTON tablespaceaddr data01 partitionaddr p02values CHRISTCHURCH tablespaceaddr data02 partitionaddr p03values DUNEDIN INVERCARGILL tablespaceaddr data03 partitionaddr p04values AUCKLAND tablespaceaddr data04 partitionaddr p05values HAMILTON ROTORUA TAURANGA tablespaceaddr data05 列表分区特点 List分区通过对分区字段的离散值进行分区 List分区是不排序的 而且分区之间没有关联关系List分区适合于对数据离散值进行控制 List分区只支持单个字段 List分区具有与范围分区相似的优缺点数据管理能力强List分区的数据可能不均匀List分区与记录值相关 实施难度和可维护性相对较差 复合分区例 createtabledaily trans data columndefinitions partitionbyrange trans datetime subpartitionbyhash customer no subpartitions8storein dtd data01 dtd data02 partitiondtd 20010620valueslessthan to date 21 jun 2001 dd mon yyyy subpartitiondtd 20010620 s01 subpartitiondtd 20010620 s02 subpartitiondtd 20010620 s03tablespacedtd data03 subpartitiondtd 20010620 s04tablespacedtd data04 subpartitiondtd 20010620 s05tablespacedtd data05 subpartitiondtd 20010620 s06tablespacedtd data06 subpartitiondtd 20010620 s07tablespacedtd data07 subpartitiondtd 20010620 s08tablespacedtd data08 partitiondtd 20010621valueslessthan to date 22 jun 2001 dd mon yyyy partitiondtd 20010622valueslessthan to date 23 jun 2001 dd mon yyyy subpartitions4 复合分区图示 复合分区特点 Oracle支持的Composite分区 Range Hash Range List既适合于历史数据 又适合于数据均匀分布与范围分区一样提供高可用性和管理性更好的PDML和partition wisejoins性能实现粒度更细的操作支持复合localindexes不支持复合compositeglobalindexes 分区索引 表 索引 Localpartitionedindex GlobalPartitionedIndex 不同的分区索引 绍兴 杭州 温州 分区索引 分区表索引的分类 LocalPrefixedindexLocalNon prefiexedindexGlobalPrefixedindexNonPartitionIndexGlobal索引的分区不同与表分区Local索引的分区与表分区相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns 分区表上的非分区索引等同于Global索引 分区索引 Global索引必须是范围分区 9i之前Global索引可以是HASH分区 10g新特性Global索引不支持Bitmap索引Unique索引必须是prefixed 或者包含分区字段Local索引 non prefixed non unique 可以不包含分区字段 createindexcust idx1oncustomers customer name globalpartitionbyrange customer name partitioncust p01valueslessthan H tablespacecust index01 partitioncust p02valueslessthan N tablespacecust index02 partitioncust p03valueslessthan T tablespacecust index03 partitioncust p04valueslessthan MAXVALUE tablespacecust index04 createindexcust idx2oncustomers customer no local partitioncust idx p01tablespacecust index01 partitioncust idx p02tablespacecust index02 partitioncust idx p03tablespacecust index03 partitioncust idx p04tablespacecust index04 partitioncust idx p05tablespacecust index05 partitioncust idx p06tablespacecust index06 partitioncust idx p07tablespacecust index07 partitioncust idx p08tablespacecust index08 createindexcust idx3oncustomers customer type local 分区索引举例 分区表索引的使用 OLTP系统中的建议Global和uniquelocalindex性能优于nonuniquelocalindexLocalindex提供了更好的可用性数据仓库系统中的建议Localindex更适合于数据装载和分区维护在大量数据统计时 能充分利用Localindex并行查询能力在性能 高可用性和可管理性之间进行平衡 分区索引选择策略 分区裁剪功能 Partitionpruning Onlytherelevantpartitionsareaccessed 99 May 99 Apr 99 Feb 99 Jan 99 Mar 99 Jun sales SQL SELECTSUM sales amount 2FROMsales3WHEREsales dateBETWEEN4TO DATE 01 MAR 1999 5 DD MON YYYY AND6TO DATE 31 MAY 1999 7 DD MON YYYY 分区裁剪举例 1select fromdaily trans summ2 wheretrans datetimebetweento date 25 jun 200108 DD mon yyyyhh24 andto date 28 jun 200118 DD mon yyyyhh24 PartitionPartitionOperationOptionsObjectNameStartStop SELECTSTATEMENTPARTITIONRANGEITERATOR231234TABLEACCESSFULLDAILY TRANS SUMM2312341select fromdaily trans summ2 wheretrans datetimein 25 jun 2001 28 jun 2001 PartitionPartitionOperationOptionsObjectNameStartStop SELECTSTATEMENTPARTITIONRANGEINLISTKEY INLIST KEY INLIST TABLEACCESSFULLDAILY TRANS SUMMKEY INLIST KEY INLIST Nonpartition wisejoin Fullpartition wisejoin Partialpartition wisejoin Queryslave Partition Partitionedtable 1 2 3 Partition WiseJoin Partition wiseJoins Tablesandindexesthatarepartitionedidenticallyareequi partitioned Afullpartition wisejoinoccurswhenjoiningtwoequi partitionedtablesthatarepartitionedonthejoinkey Oraclesplitsthejoinintojoinsofpairsofpartitions Apartialpartition wisejoinoccurswhenonlyoneofthetablesispartitionedonthejoinkey Partition wisejoinsoccurwhenjoiningahashpartitiontabletoacompositepartitiontableifthehashpartitioningandsub partitioningisonthejoinkey Oracleassignsparallelqueryslavestoprocessthepartitionjoins Partition wiseJoins举例 1select full c c customer no count 2fromcustomersc daily trans datad3wherec customer no d customer no4andd trans datetimebetweento date 25 jun 2001 dd mon yyyy 5andto date 28 jun 2001 dd mon yyyy 6 groupbyc customer noPartitionPartitionOperationOptionsObjectNameStartStop SELECTSTATEMENTPARTITIONHASHALL18SORTGROUPBYHASHJOINPARTITIONRANGEITERATOR5053TABLEACCESSFULLDAILY TRANS DATA393424TABLEACCESSFULLCUSTOMERS18 分区表设计原则 表的大小 当表的大小超过1 5GB 2GB 或对于OLTP系统 表的记录超过1000万 都应考虑对表进行分区 数据访问特性 基于表的大部分查询应用 只访问表中少量的数据 对于这样表进行分区 可充分利用分区排除无关数据查询的特性 数据维护 按时间段删除成批的数据 例如按月删除历史数据 对于这样的表需要考虑进行分区 以满足维护的需要 数据备份和恢复 按时间周期进行表空间的备份时 将分区与表空间建立对应关系 只读数据 如果一个表中大部分数据都是只读数据 通过对表进行分区 可将只读数据存储在只读表空间中 对于数据库的备份是非常有益的 并行数据操作 对于经常执行并行操作 如ParallelInsert ParallelUpdate等 的表应考虑进行分区 表的可用性 当对表的部分数据可用性要求很高时 应考虑进行表分区 分区表的管理功能 分区的增加 ADD 分区的删除 DROP 分区的合并 MERGE 分区的清空 TRUNCATE 分区的交换 EXCHANGE 分区的压缩 COALESE 分区的移动 MOVE 分区的分离 SPLIT 修改分区的DefaultAttribute分区的更名 RENAME 分区索引的管理功能 分区索引的删除 DROP 分区索引的修改 MODIFY 分区索引DefaultAttribute的修改分区索引的重建 REBUILD 分区索引的更名 RENAME 分区索引的分离 SPLIT 分区索引的Unusable 分区表和Local索引 AUG2002 新月份数据的加载和索引的维护 滚动窗口 操作 大量数据高速装载 删除或归档最老月份的数据 新月份数据的加载和索引的维护 分区表和Local索引 滚动窗口 操作 大量数据高速装载 分区交换功能 通过交换数据段 实现分区和非分区表的数据交换 以及子分区和分区表的数据交换非常快捷的数据移动方式 特别是没有validation和索引维护操作时Local索引自动维护Global索引必须重建 分区交换的应用 全文检索 12 00分区 BF DXX stage中间表 1 1 00数据的加载 2 建立context索引 3 partition的exchange BF DXX表 初始化工作 整理工作 2 00分区 1 00分区 分区交换的应用 全文检索 第一步 1 00数据的加载insertintoBF DXX stage SJ TEXT3 values to date 2004 03 02 YYYY MM DD 大撒反对撒 第二步 建立context索引CREATEINDEXIDX BF DXX STAGEONBF DXX stage text3 INDEXTYPEISCTXSYS CONTEXTPARAMETERS LEXERMYLEXERSTORAGEMYSTOREFILTERCTXSYS NULL FILTERMEMORY100M parallel4 第三步 partition的交换altertableBF DXXexchangepartitionp2withtableBF DXX stageincludingindexes 迁移表空间 TransportableTablespace 技术简介 第一步 exptransport tablespace yes第二步 FTP数据文件和dmp文件第三步 imptransport tablespace yes 迁移表空间技术的作用 业务系统数据向数据仓库系统的迁移对业务系统和数据仓库系统的数据进行定期归档数据仓库向数据集市的数据迁移数据对外发布按表空间进行时间点的数据恢复 TSPITR 迁移表空间技术的优点 性能大大高于export import或PL SQL编写的程序由于Dmp文件只包含表空间的结构信息 因此该技术的真正开销在于数据文件的传输 对源系统的影响非常小只需要将被迁移的表空间设置为只读方式可同时传输索引数据 避免在目的数据库中重建索引 分区交换的应用 ETL 在源系统中 将需要抽取的数据以如下语句形式 抽取到建立在单独表空间上的中间表中 CREATETABLE ASSELECT INSERT APPEND ASSELECT 以TTS方式将中间表的表空间传输到数据仓库之中 exptransportable tablespace Yes FTP中间表表空间的数据文件imptransportable tablespace Yes 在数据仓库中对中间表进行各种数据归并等清洗工作 并建立需要的各种索引 通过exchange技术 将中间表数据及索引直接交换到分区表中 Altertableexchangepartitionwithtableincludingindexes 分区交换的应用 重复记录删除 问题描述 在使用SQL Loader进行数据加载sor acct dcc saamt c表时 由于操作失误 重复加载 导致分区ETL LOAD DATE 0606出现重复记录 也使得两个唯一索引 IDX SAACNAMT C 1 IDX SAACNAMT C 2的ETL LOAD DATE 0606分区不可用 UNUSABLE 用户在试图重新创建该分区索引时 出现如下错误 SQL alterindexIDX SAACNAMT C 2rebuildpartitionETL LOAD DATE 0606 alterindexIDX SAACNAMT C 2rebuildpartitionETL LOAD DATE 0606 ORA 01452 cannotCREATEUNIQUEINDEX duplicatekeysfound 分区交换的应用 重复记录删除 在试图删除该分区的重复记录时 又出现如下错误 SQL deletefromsor acct dcc saamt cpartition ETL LOAD DATE 0606 whererowidnotin selectmin rowid fromsor acct dcc saamt cpartition ETL LOAD DATE 0606 groupbyETL LOAD DATE CUST ACCT NO SA CURR COD SA CURR IDEN ORA 01502 index GYFX IDX SAACNAMT C 1 orpartitionofsuchindexisinunusablestate 分区交换的应用 重复记录删除 简单办法是彻底删除这两个唯一索引 重新创建 数据量大 时间太长 影响系统的可用性 更完备的解决方式创建一个与sor acct dcc saamt c结构一样的临时表test SQL createtabletestasselect fromsor acct dcc saamt cwhere1 2 将sor acct dcc saamt c表分区ETL LOAD DATE 0606数据交换到临时表test SQL altertablesor acct dcc saamt cexchangepartitionETL LOAD DATE 0606withtabletest 分区交换的应用 重复记录删除 更完备的解决方式删除test中的重复记录deletefromtestwhererowidnotin selectmin rowid fromtestgroupbyETL LOAD DATE CUST ACCT NO SA CURR COD SA CURR IDEN 因为test表没有任何索引 可避免上述ORA 01502错误 将临时表test数据交换回sor acct dcc saamt c表分区ETL LOAD DATE 0606 altertablesor acct dcc saamt cexchangepartitionETL LOAD DATE 0606withtabletest 分区交换的应用 重复记录删除 更完备的解决方式重新创建创建该分区索引IDX SAACNAMT C 1 IDX SAACNAMT C 2alterindexIDX SAACNAMT C 1rebuildpartitionETL LOAD DATE 0606tablespaceETL0 R LOAD IDX 200606 alterindexIDX SAACNAMT C 2rebuildpartitionETL LOAD DATE 0606tablespaceETL0 R LOAD IDX 200606 此时重复记录已经删除 可避免上述ORA 01452错误 现有系统实施分区的经验 分区对象的确定 存储空间最大的前20个表Select from Select fromdba segmentsorderbybytesdesc whererownum 20 分析大表的操作行为Select from Selectsql text executionsfromv sqlareawhereupper sql text like SB ZSXX orderbyexecutionsdesc whererownum 20 综合其它分区因素的考虑 性能 数据迁移 备份 高可用性 可维护性 分区的评估 性能方面相应速度资源消耗 CPU 内存 I O 性能分析工具的使用 OracleTrace Autotrace TKPROF其它方面数据迁移能力数据备份和恢复数据扩展性 Add Drop Exchange Merge 数据高可用性 Oracle报表优化技术 报表处理问题 报表处理是大部分IT系统是最耗时 最消耗资源的模块报表处理 主要通过FormulaOne BO等报表处理工具实现SQL语句基本都是各种统计运算语句 SUM GROUPBY各种报表的表格单元都是统计运算语句生成 统计运算语句量非常大统计运算语句 基本都是从一些交易明细表或基表 直接进行汇总运算操作 其他汇总数据 按月汇总的逾期未归还贷款本金的统计 查询 汇总数据管理 物化视图MaterializedView 辽宁省和四川省逾期91 180天未归还贷款本金的贷款用户总数 按地区汇总的逾期未归还贷款本金的统计 物化视图 SQL查询的实例化物化视图可以设置成查询重写功能刷新类型 CompleteorFastForceorNever刷新模式 ManualAutomated 同步或异步 查询重写概述 查询物化视图 而不是基表 将极大提高查询统计性能 查询重写功能对应用透明 不需要特殊权限 查询重写与大小写无关不支持子查询 物化视图创建例 SQL CREATEMATERIALIZEDVIEWsales summary2TABLESPACEusers3PARALLEL DEGREE4 4BUILDIMMEDIATE5ENABLEQUERYREWRITE6AS7SELECTp prod name 8SUM s quantity sold 8SUM s amount sold 9FROMsaless productsp10WHEREs prod id p prod id11GROUPBYp prod name 物化视图创建和查询重写例 SQL SELECTp prod name SUM s quantity sold 2SUM s amount sold 3FROMsaless productsp4WHEREs prod id p prod id5GROUPBYp prod name SQL selectoperation object name2fromv sql plan3whereobject namelike SALES OPERATIONNAME SELECTSTATEMENTTABLEACCESSSALES SUMMARY 报表优化的基本思路 以报表为单位 分析现有报表的SQL语句 总结统计运算SQL的共同规律 作为设计物化视图的考虑对象 定义相应的物化视图 包括SQL查询语句的编写 刷新方式的确定等评价原有统计运算SQL语句的执行计划 是否被Oracle查询重写到相应的物化视图评价如何在物化视图上创建索引评估所有物化视图数据和索引的空间消耗情况 从而确定物化视图数据和索引表空间的容量 报表优化示例 现有统计运算语句SELECTts stab VIP ORG STAFF VIP ORGAN3 NAME ts stab VIP ORG STAFF VIP ORGAN2 NAME sum ts dyna VIP ACCT ALL T1 100 sum ts dyna VIP ACCT ALL T29 100 100FROMts stab VIP ORG STAFF ts dyna VIP ACCT ALLWHERE ts dyna VIP ACCT ALL STAFF ID ts stab VIP ORG STAFF STAFF ID AND ts stab VIP ORG STAFF VIP ORGAN3 org2 OR ALL org2 AND ts stab VIP ORG STAFF VIP ORGAN2 ALL OR ALL ALL AND ts dyna VIP ACCT ALL ACCOUNT DATE 200401 AND ts dyna VIP ACCT ALL ACCOUNT DATE 200401 GROUPBYts stab VIP ORG STAFF VIP ORGAN3 NAME ts stab VIP ORG STAFF VIP ORGAN2 NAME 报表优化示例 物化视图定义CREATEMATERIALIZEDVIEWMV 01TABLESPACETS TAB DYNPARALLEL DEGREE2 BUILDIMMEDIATEREFRESHCOMPLETEONDEMANDENABLEQUERYREWRITEASSELECTts stab VIP ORG STAFF VIP ORGAN3 NAME ts stab VIP ORG STAFF VIP ORGAN2 NAME ts stab VIP ORG STAFF VIP ORGAN3 ts stab VIP ORG STAFF VIP ORGAN2 ts dyna VIP ACCT ALL ACCOUNT DATE sum ts dyna VIP ACCT ALL T1 sum ts dyna VIP ACCT ALL T29 FROMts stab VIP ORG STAFF ts dyna VIP ACCT ALLWHERE ts dyna VIP ACCT ALL STAFF ID ts stab VIP ORG STAFF STAFF ID GROUPBYts stab VIP ORG STAFF VIP ORGAN3 NAME ts stab VIP ORG STAFF VIP ORGAN2 NAME ts stab VIP ORG STAFF VIP ORGAN3 ts stab VIP ORG STAFF VIP ORGAN2 ts dyna VIP ACCT ALL ACCOUNT DATE 报表优化示例 TABLESPACETS TAB DYN 物化视图MV 01存放的表空间 建议为物化视图单独设立表空间PARALLEL DEGREE2 并行度设计为2 与目前的CPU个数相等 BUILDIMMEDIATE 创建MV 01的同时 产生汇总数据REFRESHCOMPLETEONDEMAND 完全刷新ENABLEQUERYREWRITE 启动查询语句重写功能 报表优化示例 SELECT短语应包含的字段原SELECT语句的所有非统计运算字段 ts stab VIP ORG STAFF VIP ORGAN3 NAME ts stab VIP ORG STAFF VIP ORGAN2 NAME 原SELECT语句中所有查询条件涉及到的字段 如 ts stab VIP ORG STAFF VIP ORGAN3 ts stab VIP ORG STAFF VIP ORGAN2 ts dyna VIP ACCT ALL ACCOUNT DATE 原子化 Primitive 的统计运算字段 即去掉表达式的统计运算字段sum ts dyna VIP ACCT ALL T1 sum ts dyna VIP ACCT ALL T29 报表优化示例 创建物化视图上的索引createindexidx MV 01 01onMV 01 VIP ORGAN3 tablespaceTS IND DYNA 物化视图上的刷新定期执行如下命令 可自动进行物化视图的刷新execdbms mview refresh TS DYNA MV 0