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

nullnullnullOracle数据库高级技术交流 ---大批量数据处理技术Oracle(中国)顾问咨询部 罗 敏 资深技术顾问 电话：13321161702 eMail: Michael.luo@oracle.com交流内容交流内容分区技术 报表优化技术 并行处理应用经验 nullOracle的分区技术 分区技术内容什么是分区? 分区的好处? 如何实施分区? 如何评估分区的效果? Oracle的分区技术基本原理分而治之 2003200420052006分区概述分区概述大数据对象 (表, 索引)被分成小物理段 当分区表建立时，记录基于分区字段值被存储到相应分区。 分区字段值可以修改。(row movement enabled) 分区可以存储在不同的表空间 分区可以有不同的物理存储参数 分区支持IOT表，对象表，LOB字段，varrays等分区技术的效益和目标分区技术的效益和目标性能 Select和DML操作只访问指定分区 并行DML操作 Partition-wise Join 可管理性：数据删除，数据备份 历史数据清除 提高备份性能 指定分区的数据维护操作 可用性 将故障局限在分区中 缩短恢复时间 分区目标优先级 高性能 –>数据维护能力->实施难度 –> 高可用性（故障屏蔽能力） 分区方法分区方法分区方法: 范围 --- 8 Hash --- 8i 列表 --- 9i 组合 --- 8iRange partitioningHash partitioning范围分区例123CREATE TABLE sales (acct_no NUMBER(5), person VARCHAR2(30), sales_amount NUMBER(8), week_no NUMBER(2)) PARTITION BY RANGE (week_no) (PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0, PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1, ...… PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 );分区字段：week_no. VALUES LESS THAN 必须是确定值 每个分区可以单独指定物理属性123范围分区例范围分区特点最早、最经典的分区算法 Range分区通过对分区字段值的范围进行分区 Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。 数据管理能力强 数据迁移 数据备份 数据交换 范围分区的数据可能不均匀 范围分区与记录值相关，实施难度和可维护性相对较差范围分区特点Hash分区例Hash分区例create table CUSTOMERS (... column definitions ...) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by hash(customer_no) partitions 8 store in (cust_data01,cust_data02) create table CUSTOMERS (... column definitions ...) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by hash(customer_no) (partition cust_p01 tablespace cust_data01 ,partition cust_p02 tablespace cust_data02 ,partition cust_p03 tablespace cust_data03 ,partition cust_p04 tablespace cust_data04 ,partition cust_p05 tablespace cust_data05 ,partition cust_p06 tablespace cust_data06 ,partition cust_p07 tablespace cust_data07 ,partition cust_p08 tablespace cust_data08) nullHash分区特点基于分区字段的HASH值，自动将记录插入到指定分区。 分区数一般是2的幂 易于实施 总体性能最佳 适合于静态数据 HASH分区适合于数据的均匀存储 HASH分区特别适合于PDML和partition-wise joins。 支持 (hash) local indexes 9i 不支持 (hash) global indexes 10g 支持(hash) global indexes HASH分区 数据管理能力弱 HASH分区对数据值无法控制列表分区例列表分区例create table addresses (... column definitions ...) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by list(city_name) (partition addr_p01 values ('WELLINGTON') tablespace addr_data01 ,partition addr_p02 values ('CHRISTCHURCH') tablespace addr_data02 ,partition addr_p03 values ('DUNEDIN','INVERCARGILL') tablespace addr_data03 ,partition addr_p04 values ('AUCKLAND') tablespace addr_data04 ,partition addr_p05 values ('HAMILTON','ROTORUA','TAURANGA') tablespace addr_data05) null列表分区特点List分区通过对分区字段的离散值进行分区。 List分区是不排序的，而且分区之间没有关联关系 List分区适合于对数据离散值进行控制。 List分区只支持单个字段。 List分区具有与范围分区相似的优缺点 数据管理能力强 List分区的数据可能不均匀 List分区与记录值相关，实施难度和可维护性相对较差 复合分区例复合分区例create table daily_trans_data (...column definitions ...) partition by range(trans_datetime) subpartition by hash(customer_no) subpartitions 8 store in (dtd_data01,dtd_data02) (partition dtd_20010620 values less than (to_date('21-jun-2001','dd-mon-yyyy')) (subpartition dtd_20010620_s01 ,subpartition dtd_20010620_s02 ,subpartition dtd_20010620_s03 tablespace dtd_data03 ,subpartition dtd_20010620_s04 tablespace dtd_data04 ,subpartition dtd_20010620_s05 tablespace dtd_data05 ,subpartition dtd_20010620_s06 tablespace dtd_data06 ,subpartition dtd_20010620_s07 tablespace dtd_data07 ,subpartition dtd_20010620_s08 tablespace dtd_data08 ) ,partition dtd_20010621 values less than (to_date('22-jun-2001','dd-mon-yyyy')) ,partition dtd_20010622 values less than (to_date('23-jun-2001','dd-mon-yyyy')) subpartitions 4 ) 复合分区图示复合分区图示复合分区特点复合分区特点Oracle支持的Composite分区： Range-Hash,Range-List 既适合于历史数据，又适合于数据均匀分布 与范围分区一样提供高可用性和管理性 更好的PDML和partition-wise joins性能 实现粒度更细的操作 支持复合 local indexes 不支持复合composite global indexes？分区索引分区索引表索引√√√√不同的分区索引Local partitioned indexGlobal Partitioned Index不同的分区索引绍兴杭州温州分区索引分区索引分区表索引的分类： Local Prefixed index Local Non-prefiexed index Global Prefixed index Non Partition Index Global索引的分区不同与表分区 Local索引的分区与表分区相同 An index is prefixed if it is partitioned on a left prefix of the index columns. 分区表上的非分区索引等同于Global索引分区索引分区索引Global索引必须是范围分区 --- 9i之前 Global索引可以是HASH分区 -- 10g新特性 Global索引不支持Bitmap索引 Unique索引必须是prefixed，或者包含分区字段 Local索引（non-prefixed, non-unique）可以不包含分区字段分区索引举例create index cust_idx1 on customers(customer_name) global partition by range (customer_name) (partition cust_p01 values less than (‘H’) tablespace cust_index01 ,partition cust_p02 values less than (‘N’) tablespace cust_index02 ,partition cust_p03 values less than (‘T’) tablespace cust_index03 ,partition cust_p04 values less than (MAXVALUE) tablespace cust_index04) create index cust_idx2 on customers(customer_no) local (partition cust_idx_p01 tablespace cust_index01 ,partition cust_idx_p02 tablespace cust_index02 ,partition cust_idx_p03 tablespace cust_index03 ,partition cust_idx_p04 tablespace cust_index04 ,partition cust_idx_p05 tablespace cust_index05 ,partition cust_idx_p06 tablespace cust_index06 ,partition cust_idx_p07 tablespace cust_index07 ,partition cust_idx_p08 tablespace cust_index08) create index cust_idx3 on customers(customer_type) local;分区索引举例null分区表索引的使用OLTP系统中的建议 Global和unique local index性能优于nonunique local index Local index提供了更好的可用性 数据仓库系统中的建议 Local index更适合于数据装载和分区维护 在大量数据统计时，能充分利用Local index并行查询能力 在性能、高可用性和可管理性之间进行平衡分区索引选择策略分区索引选择策略分区裁剪功能分区裁剪功能Partition pruning: Only the relevant partitions are accessed.99-May99-Apr99-Feb99-Jan99-Mar99-JunsalesSQL> SELECT SUM(sales_amount) 2 FROM sales 3 WHERE sales_date BETWEEN 4 TO_DATE(‘01-MAR-1999’, 5 ‘DD-MON-YYYY’) AND 6 TO_DATE(‘31-MAY-1999’, 7 ‘DD-MON-YYYY’);分区裁剪举例分区裁剪举例1 select * from daily_trans_summ 2* where trans_datetime between to_date('25-jun-2001 08','DD-mon-yyyy hh24') and to_date('28-jun-2001 18','DD-mon-yyyy hh24') Partition Partition Operation Options Object Name Start Stop -------------------- ------------ --------------------- ------------ ------------ SELECT STATEMENT PARTITION RANGE ITERATOR 231 234 TABLE ACCESS FULL DAILY_TRANS_SUMM 231 234 1 select * from daily_trans_summ 2* where trans_datetime in ('25-jun-2001','28-jun-2001') Partition Partition Operation Options Object Name Start Stop -------------------- ------------ --------------------- ------------ ------------ SELECT STATEMENT PARTITION RANGE INLIST KEY(INLIST) KEY(INLIST) TABLE ACCESS FULL DAILY_TRANS_SUMM KEY(INLIST) KEY(INLIST) nullNonpartition-wise joinFull partition-wise joinPartial partition-wise joinQuery slavePartitionPartitioned table123Partition-Wise JoinPartition-wise JoinsPartition-wise JoinsTables and indexes that are partitioned identically are equi-partitioned. A full partition-wise join occurs when joining two equi-partitioned tables that are partitioned on the join key. Oracle splits the join into joins of pairs of partitions. A partial partition-wise join occurs when only one of the tables is partitioned on the join key. Partition-wise joins occur when joining a hash partition table to a composite partition table if the hash partitioning and sub-partitioning is on the join key. Oracle assigns parallel query slaves to process the partition joins.Partition-wise Joins举例Partition-wise Joins举例1 select /*+ full(c) */ c.customer_no, count(*) 2 from customers c, daily_trans_data d 3 where c.customer_no = d.customer_no 4 and d.trans_datetime between to_date('25-jun-2001','dd-mon-yyyy') 5 and to_date('28-jun-2001','dd-mon-yyyy') 6* group by c.customer_no Partition Partition Operation Options Object Name Start Stop ------------------------- ------------ ---------------- ------------ ------------ SELECT STATEMENT PARTITION HASH ALL 1 8 SORT GROUP BY HASH JOIN PARTITION RANGE ITERATOR 50 53 TABLE ACCESS FULL DAILY_TRANS_DATA 393 424 TABLE ACCESS FULL CUSTOMERS 1 8 null分区表设计原则表的大小：当表的大小超过1.5GB－2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。 数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。 数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建立对应关系。 只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。 并行数据操作：对于经常执行并行操作（如Parallel Insert,Parallel Update等）的表应考虑进行分区。 表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。 null分区表的管理功能分区的增加（ADD） 分区的删除（DROP） 分区的合并（MERGE） 分区的清空（TRUNCATE） 分区的交换（EXCHANGE） 分区的压缩（COALESE） 分区的移动（MOVE） 分区的分离（SPLIT） 修改分区的Default Attribute 分区的更名（RENAME）null分区索引的管理功能分区索引的删除（DROP） 分区索引的修改（MODIFY） 分区索引Default Attribute的修改 分区索引的重建（REBUILD） 分区索引的更名（RENAME） 分区索引的分离（SPLIT） 分区索引的Unusable “滚动窗口”操作 - 大量数据高速装载分区表和Local索引AUG 2002新月份数据的加载和索引的维护...“滚动窗口”操作 - 大量数据高速装载“滚动窗口”操作 - 大量数据高速装载删除或归档最老月份的数据新月份数据的加载和索引的维护...分区表和Local索引“滚动窗口”操作 - 大量数据高速装载null分区交换功能通过交换数据段，实现分区和非分区表的数据交换。以及子分区和分区表的数据交换 非常快捷的数据移动方式。特别是没有validation和索引维护操作时 Local 索引自动维护 Global索引必须重建 null分区交换的应用--- 全文检索12：00分区 BF_DXX_stage中间表（1）1：00数据的加载 （2）建立context 索引 （3）partition的exchangeBF_DXX表* 初始化工作* 整理工作… …2：00分区1：00分区 null分区交换的应用--- 全文检索第一步：1：00数据的加载 insert into BF_DXX_stage(SJ,TEXT3) values(to_date('2004.03.02','YYYY.MM.DD'),'大撒反对撒'); 第二步：建立context 索引 CREATE INDEX IDX_ BF_DXX _STAGE ON BF_DXX_stage(text3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER MYLEXER STORAGE MYSTORE FILTER CTXSYS.NULL_FILTER MEMORY 100M') parallel 4; 第三步：partition的交换 alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes; 迁移表空间(Transportable Tablespace)技术简介 迁移表空间(Transportable Tablespace)技术简介 第一步：exp transport_tablespace=yes 第二步：FTP 数据文件和dmp文件 第三步：imp transport_tablespace=yes 迁移表空间技术的作用迁移表空间技术的作用业务系统数据向数据仓库系统的迁移 对业务系统和数据仓库系统的数据进行定期归档 数据仓库向数据集市的数据迁移 数据对外发布 按表空间进行时间点的数据恢复(TSPITR) 迁移表空间技术的优点迁移表空间技术的优点性能大大高于export/import或PL/SQL编写的程序 由于Dmp文件只包含表空间的结构信息，因此该技术的真正开销在于数据文件的传输。 对源系统的影响非常小 只需要将被迁移的表空间设置为只读方式 可同时传输索引数据，避免在目的数据库中重建索引 null分区交换的应用--- ETL在源系统中，将需要抽取的数据以如下语句形式，抽取到建立在单独表空间上的中间表中： CREATE TABLE ... AS SELECT … INSERT /*+ APPEND */ AS SELECT … 以TTS方式将中间表的表空间传输到数据仓库之中。 exp transportable_tablespace=Yes … FTP 中间表表空间的数据文件 imp transportable_tablespace=Yes … 在数据仓库中对中间表进行各种数据归并等清洗工作，并建立需要的各种索引。 通过exchange技术，将中间表数据及索引直接交换到分区表中。 Alter table <分区表> exchange partition <分区名> with table <中间表> including indexes;   null分区交换的应用---重复记录删除问题描述： 在使用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> alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606; alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 * ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found null分区交换的应用---重复记录删除在试图删除该分区的重复记录时，又出现如下错误： SQL> delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) where rowid not in (select min(rowid) from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN); * ORA-01502: index 'GYFX.IDX_SAACNAMT_C_1' or partition of such index is in unusable state null分区交换的应用---重复记录删除简单办法是彻底删除这两个唯一索引，重新创建。 数据量大，时间太长。 影响系统的可用性。 更完备的解决方式 创建一个与sor_acct_dcc_saamt_c结构一样的临时表test。 SQL> create table test as select * from sor_acct_dcc_saamt_c where 1=2; 将sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606数据交换到临时表test。 SQL> alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test; null分区交换的应用---重复记录删除更完备的解决方式 删除test中的重复记录 delete from test where rowid not in (select min(rowid) from test group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN); 因为test表没有任何索引，可避免上述ORA-01502错误。 将临时表test数据交换回sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606 。 alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test; null分区交换的应用---重复记录删除更完备的解决方式 重新创建创建该分区索引IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2 alter index IDX_SAACNAMT_C_1 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606; alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606; 此时重复记录已经删除，可避免上述ORA-01452错误 null现有系统实施分区的经验分区对象的确定：存储空间最大的前20个表 Select * from (Select * from dba_segments order by bytes desc) where rownum <= 20; 分析大表的操作行为 Select * from (Select sql_text,executions from v$sqlarea where upper(sql_text) like ‘%SB_ZSXX%’ order by executions desc) where rownum <= 20; 综合其它分区因素的考虑：性能，数据迁移，备份，高可用性，可维护性 null分区的评估性能方面 相应速度 资源消耗（CPU、内存、I/O） 性能分析工具的使用：Oracle Trace, Autotrace, TKPROF 其它方面 数据迁移能力 数据备份和恢复 数据扩展性（Add, Drop, Exchange, Merge, …） 数据高可用性 nullOracle报表优化技术报表处理问题报表处理问题报表处理是大部分IT系统是最耗时、最消耗资源的模块 报表处理，主要通过Formula One、BO等报表处理工具实现 SQL语句基本都是各种统计运算语句。…SUM… GROUP BY 各种报表的表格单元都是统计运算语句生成。统计运算语句量非常大 统计运算语句，基本都是从一些交易明细表或基表，直接进行汇总运算操作汇总数据管理 - 物化视图 Materialized View其他汇总数据按月汇总的 逾期未归还贷款本金的统计查询汇总数据管理 - 物化视图 Materialized View 辽宁省和四川省逾期91－180天未归还贷款本金的贷款用户总数 ？按地区汇总的 逾期未归还贷款本金的统计物化视图物化视图SQL查询的实例化 物化视图可以设置成查询重写功能 刷新类型: Complete or Fast Force or Never 刷新模式: Manual Automated (同步或异步)查询重写概述查询重写概述查询物化视图，而不是基表，将极大提高查询统计性能。 查询重写功能对应用透明。不需要特殊权限。 查询重写 与大小写无关 不支持子查询物化视图创建例物化视图创建例SQL> CREATE MATERIALIZED VIEW sales_summary 2 TABLESPACE users 3 PARALLEL (DEGREE 4) 4 BUILD IMMEDIATE 5 ENABLE QUERY REWRITE 6 AS 7 SELECT p.prod_name, 8 SUM (s.quantity_sold), 8 SUM (s.amount_sold) 9 FROM sales s, products p 10 WHERE s.prod_id = p.prod_id 11 GROUP BY p.prod_name; 物化视图创建和查询重写例物化视图创建和查询重写例SQL> SELECT p.prod_name,SUM (s.quantity_sold), 2 SUM (s.amount_sold) 3 FROM sales s, products p 4 WHERE s.prod_id = p.prod_id 5 GROUP BY p.prod_name; SQL> select operation, object_name 2 from v$sql_plan 3 where object_name like 'SALES%'; OPERATION NAME ---------------------- ----------------- SELECT STATEMENT TABLE ACCESS SALES_SUMMARY报表优化的基本思路 报表优化的基本思路 以报表为单位，分析现有报表的SQL语句。总结统计运算SQL的共同规律，作为设计物化视图的考虑对象。 定义相应的物化视图。包括SQL查询语句的编写，刷新方式的确定等 评价原有统计运算SQL语句的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 ，是否被Oracle查询重写到相应的物化视图 评价如何在物化视图上创建索引 评估所有物化视图数据和索引的空间消耗情况，从而确定物化视图数据和索引表空间的容量 报表优化示例 报表优化示例 现有统计运算语句 SELECT ts_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 ))/100 FROM ts_stab.VIP_ORG_STAFF, ts_dyna.VIP_ACCT_ALL WHERE (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' )) GROUP BY ts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME, ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME 报表优化示例 报表优化示例 物化视图定义 CREATE MATERIALIZED VIEW MV_01 TABLESPACE TS_TAB_DYN PARALLEL (DEGREE 2) BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT ts_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) FROM ts_stab.VIP_ORG_STAFF, ts_dyna.VIP_ACCT_ALL WHERE ( ts_dyna.VIP_ACCT_ALL.STAFF_ID=ts_stab.VIP_ORG_STAFF.STAFF_ID ) GROUP BY ts_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 报表优化示例 报表优化示例 TABLESPACE TS_TAB_DYN：物化视图MV_01存放的表空间。建议为物化视图单独设立表空间 PARALLEL (DEGREE 2)：并行度设计为2（与目前的CPU个数相等） BUILD IMMEDIATE：创建MV_01的同时，产生汇总数据 REFRESH COMPLETE ON DEMAND：完全刷新 ENABLE QUERY REWRITE：启动查询语句重写功能 报表优化示例 报表优化示例 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), 报表优化示例 报表优化示例 创建物化视图上的索引 create index idx_MV_01_01 on MV_01(VIP_ORGAN3) tablespace TS_IND_DYNA; 物化视图上的刷新 定期执行如下命令，可自动进行物化视图的刷新 exec dbms_mview.refresh('TS_DYNA.MV_01'); 报表优化示例 报表优化示例 初始化参数的设置 query_rewrite_enabled：TRUE query_rewrite_integrity：STALE_TOLERATED 权限方面的设置 grant CREATE ANY MATERIALIZED VIEW to TS_DYNA; grant GLOBAL QUERY REWRITE to TS_DYNA; null并行处理经验介绍并行处理概述 并行处理概述 Oracle并行处理技术无处不在！ 只要是大批量数据处理，就要考虑并行处理 并行处理的先决条件 大批量数据处理 多CPU（SMP），CPU利用率不高 足够的内存 I/O负载不高 并发用户少并行SQL语句举例 并行SQL语句举例 insert into SUM_ACCT_DCC_SAACN_P SELECT '20050930', a.sa_acct_no, nvl(b.sa_curr_cod, '01'), nvl(b.sa_curr_iden, '0'), …. … FROM sor_acct_dcc_saacn_p_20050930 a left join sor_acct_dcc_saamt_p_20050930 b on a.sa_acct_no = b.sa_acct_no and a.etl_load_date = b.etl_load_date left join sor_acct_dcc_crcrdcrd c on a.sa_card_no = c.cr_crd_no where a.etl_load_date = '20050930' 1小时30分钟 A: 10,657,379 B: 10,548,397 C: 9,142,893 并行SQL语句举例 并行SQL语句举例 alter session enable parallel dml ; insert /*+ append parallel(d,12) */ into SUM_ACCT_DCC_SAACN_P d nologging SELECT /*+ parallel(a,12) parallel(b,12) parallel(c,12) */ '20050930', a.sa_acct_no, nvl(b.sa_curr_cod, '01'), nvl(b.sa_curr_iden, '0'), … … FROM sor_acct_dcc_saacn_p_20050930 a left join sor_acct_dcc_saamt_p_20050930 b on a.sa_acct_no = b.sa_acct_no and a.etl_load_date = b.etl_load_date left join sor_acct_dcc_crcrdcrd c on a.sa_card_no = c.cr_crd_no where a.etl_load_date = '20050930' 16分钟! 并行SQL语句举例 并行SQL语句举例 direct path insert、nologging 并行处理技术 数据加载中的并行处理 数据加载中的并行处理 在控制文件（.ctl）中进行参数设置 direct path load DIRECT = TRUE 并行处理技术 PARALLEL = TRUE 统计信息采集效率的提高 统计信息采集效率的提高 dbms_stats.gather_table_stats(ownname=>’OSS_SA_91’,tabname=>’TF_B_WORKFLOW’,estimate_percent=>50,degree=>4,cascade=>TRUE); /* 单表统计信息采集 */ 统计信息采集的建议 统计信息采集的建议 设置并行度 只对最消耗资源SQL语句（Top-Session）所涉及的表进行统计信息采集 设定采样率参数 只对当前分区数据进行采集 考虑block_sample=>TRUE，method_opt=>‘FOR ALL INDEXED COLUMNS ’等参数 编写统计信息采集工作的脚本，并在非生产时间的夜间定期执行，同时与其它后台处理错开时间 nullAnull