oracle性能优化基础介绍

mrwhite717 贡献于2016-11-30

作者 fernanre  创建于2014-10-10 03:33:00   修改者DELL  修改于2016-12-01 02:33:00字数11494

文档摘要:
关键词:Process

 Oracle性能优化基础 目录 1. SQL性能分析和优化基本方法 2 1.1. SQL性能分析的量化指标 2 1.2. SQL量化分析和优化工具 3 1.3. SQL语句优化的过程 3 1.4. SQL语句执行计划 3 1.5. 索引的基本策略 8 1.6. 绑定变量的使用 17 1.7. 表连接 20 2. 优化器和统计信息采集 24 2.1. 优化器 24 2.2. 基于规则优化器RBO 24 2.3. 更先进的基于成本优化器CBO 25 2.4. 统计信息 25 3. 基于大批量数据处理相关技术 30 3.1. 位图索引 30 3.2. 物化视图 32 1. SQL性能分析和优化基本方法 1.1. SQL性能分析的量化指标 建议开发过程中,至少要对关键模块的SQL语句进行量化分析,其量化指标至少包括以下几个: Ø 消耗时间:包括Elapsed Time、CPU Time等指标; Ø 内存消耗:包括Buffer Gets、Consistant Gets等指标; Ø I/O消耗:包括Physical Reads、Physical Writes等指标; 第 页|共 36 页 Ø 语句分析次数:包括Parses、Hard Parses、Soft Parses等; 1.2. SQL量化分析和优化工具 为了满足对SQL语句进行量化分析和优化的需要,Oracle提供了大量的工具。例如:Auto*Trace、AWR、ADDM、ASH、Explain、 TKPROF、SQL*Trace、SQL*Profiling、SQL Access Advisor、SQL Tuning Advisor等。 还有一些第三方工具。例如:PL/SQL Developer等。 1.3. SQL语句优化的过程 Ø 定位有问题的语句; Ø 检查执行计划; Ø 检查执行过程中优化器的统计信息; Ø 分析有关表的记录数、索引的情况; Ø 改写SQL语句,使用HINT、调整索引、表分析; Ø 有些SQL语句不具备优化的可能,需要优化处理的方式; Ø 达到最佳执行计划; 1.4. SQL语句执行计划 一、 什么是SQL语句执行计划 SQL是声明型语言,她只说我要去哪里,但是很少告诉你到底如何去。 RDBMS所要做的是基于算法和现有统计信息计算最佳路径: Ø Access Path访问路径分析:访问数据时用TableScan还是index(FFS); Ø 对返回的结果集做进一步处理,以便返回行给客户端; SQL语句的执行最终会落实为Oracle执行步骤的组合,则是SQL执行计划。 第 页|共 36 页 二、 查看SQL语句执行计划 1、 使用Auto*Trace查看SQL语句的执行计划 在SQL/PLUS中执行执行set autotrace on;。如下图: 这样,在执行任何SQL语句都能看到该语句的执行结果、执行计划、统计信息。如下图所示: 调试过程中如果不想查看SQL语句的输出结果,只查看执行计划、统计信息,SQL/PLUS中执行set autotrace traceonly;。如下图所示: 第 页|共 36 页 关闭Autotrace,执行set autotrace off; 如果只查看SQL的执行时间,执行set timing on,关闭则执行set timing off;。如下图所示: 2、 PL/SQL Developer查看SQL语句的执行计划 在Explain Plan窗口中执行需要分析的SQL语句。如下图所示: 三、 SQL执行计划中的相关指标 第 页|共 36 页 Ø ID:分配给执行计划中每一个步骤的一个数字,每个步骤(执行计划中的行,或树中的节点)代表行源(row source); Ø Operation:该步骤实施的内容操作名; Ø Name:该步骤操作的表或索引名; Ø Rows :CBO基于统计信息估计该操作返回的行数; Ø Bytes :CBO基于统计信息估计该操作返回的字节数; Ø Cost:在默认启用CPU Costing的环境中Cost=IO Cost + CPU Cost; Ø %CPU:代表CPU Cost占总的Cost的比例,(Cost – Io Cost)/Cost; Ø Time:CBO评估该操作将要消耗的时间,单位为秒; Ø 与CBO相关的参数Cost、Rows、Bytes、Time等当使用RBO优化器时全部为NULL; Ø Pstart:访问多个分区时的起始分区; Ø Pstop:访问多个分区时的停止分区; 四、 SQL语句的执行计划树形图解析顺序 1) 从顶部开始; 2) 从树中向左下移,直至到达左节点(没有子节点的节点),首先执行此节点; 3) 查看此行源的同级行源。接下来执行这些行源; 4) 执行子行源后,接着执行父行源; 5) 完成此父行源及其子行源后,在树中向上退一级,查看相应父行源的同级行源和父行源。按前述方式执行。 6) 在树中不断上移动,直至用完所有行源为止。 0 1 2 3 4 5 9 6 7 8 第 页|共 36 页 例如上图,执行顺序3、6、8、7、5、9、4、2、1 A. 左下移动,3节点没有子节点,优先执行3 B. 遍历3的同级行源4,左下移动,执行6 C. 之后遍历6的统计行源7,执行8 D. 执行7 E. 执行5 F. 执行9 G. 执行4 H. 执行2 I. 执行1 五、 分析理解执行计划的作用 Ø 执行计划贯穿了Oracle调优的始终; Ø 了解执行计划的真实执行过程,将有助于优化; 第 页|共 36 页 Ø 对Oracle的原理理解有一定的帮助; Ø SQL调优的第一步; 1.5. 索引的基本策略 一、 ROWID 在数据库中,每条记录都有自己的物理地址,叫做ROWID,包括所属的数据文件号、数据块号,以及在该数据块中的具体位置等信息。如下图所示: 二、 Oracle的部分类似索引 三、 Oracle的索引到底是什么样的 第 页|共 36 页 Branch Leaf Index entry Index entry header Key column length Key column value ROWID Root Oracle各种索引都长得不太一样,下面介绍最常用的B*-Tree索引。如下图所示: 四、 针对B*树单字段索引的一些设计建议,也是索引设计的第一条规范 Ø 分析SQL语句中的约束条件字段; Ø (2)如果约束条件字段不固定,建议创建针对单字段的普通B*树索引; Ø (3)选择可选性最高的字段建立索引; Ø (4)如果多表连接SQL语句,注意被驱动表的连接字段是否需要创建索引; Ø (5)通过多种SQL分析工具,分析执行计划并以量化形式评估效果; 五、 如何避免索引被抑制 1、 不要轻易在字段前增加函数 如果建立几个索引就将性能大幅度提高,的确是件很开心、很有成就感的事情。但是在很多情况下,明明发现问题了却无从下手。因为开发人员把错误写在SQL语句中了,需要修改语句。例如,如下语句片段: 第 页|共 36 页 即便建了DJ_SZ_JDRQ字段的索引,该索引仍然无法启动,依然是全表扫描。因为索引树中记录的是DJ_SZ_JDRQ字段值,而不是to_char(DJ_SZ.JDRQ,‘YYYY.MM.D D’)函数值。因此DJ_SZ_JDRQ索引无法使用。上述语句片段应该修改成: 2、 避免出现索引列自动转换(隐式转换) 例如:表DJ_SZ的字段NUMBER类型为VARCHAR,如下语句片段是否会使用NUMBER字段上的索引? 3、 尽量不要将字段嵌入表达式中 Oracle其实挺笨的,如果这样写: Oracle也不会启动YXQ_Z字段上的索引,必须修改成这样: 4、 where子句中,如果索引列所对应的值的第一个字符由通配符开发,索引将不被使用。 例如以下语句,Oracle也不会使用索引,将使用全表扫描。 5、 通常要避免在索引列上使用NOT,NOT会产生和在索引列上使用函数相同的效果。Oracle当遇到NOT就是执行全表扫描。 例如: 第 页|共 36 页 不会使用索引: 使用索引 6、 避免在索引列上使用IS NULL和IS NOT NULL 在索引中使用任务可以为空的列,Oracle将无法使用该索引。对于单字段索引,如果列包含空值,索引中将不存在此记录;对于复合索引,如果每个列都包含空值,索引中同样不存在此记录,如果至少有一个列不为空,则记录存在于索引中。 注意:任何在where字据中使用is null 或 is not null的语句,优化器是不允许使用索引的。 7、 联接列 对于有联接的列,即使最后的联接值为一个静态值,优化器也是不会使用索引的。 系统优化器对于基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。 8、 带通配符(%)的like语句 第 页|共 36 页 通配符(%)在搜索词开头出现,Oracle系统不使用索引。 在很多情况下可能无法避免这种情况,但一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。如下面的语句: 六、 关于函数索引 针对前面的问题,为什么不可以直接使用Oracle函数索引?例如: 的确,只要数据库运行在基于成本的优化器(CBO)模式下,并且将参数query_rewrite_enabled设置为TRUE,就可以启动函数索引。 但是建议尽量少使用函数索引,而将函数进行转换,原因如下: Ø 函数索引是需要维护的,当数据库每次进行该表的DML(插、删、改)操作时,Oracle都需;要维护函数索引,也就是说需要进行一次计算,维护成本将高于普通索引; Ø 函数索引的计算值可能大于原字段值,将消耗更多的索引存储空间。 七、 复合索引 在大多数情况下,复合索引比单字段索引好。以某税务系统的SB_ZSXX(申报类_征收信息表为例),该表数据量很大,如果分别按纳税人识别号、税务机关代码、月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很多纳税记录,一个税务机关代码和同一个月份记录就更多了,所以3个字段合起来,“某个纳税人识别号+某个税务机关代码+某月”的记录就少多了。因此,复合索引比单子段索引效率高多了,很多时候复合索引使效率大幅度提高。但是,复合索引比单字段索引的内部原理复杂,而且复合索引有两个重要原则需要把握:前缀性和可选性。如果糊里糊涂地滥用复合索引,效果适得其反。 第 页|共 36 页 所以复合索引是把双刃剑,用好了极大提高系统性能,用不好,在数据库里面就是一堆垃圾。 例子:假设在一个员工表(emp)的(ENAME、JOB、MGR)3个字段上建了一个复合索引IDX_1,3个字段分别为员工姓名、工作、所属经理。请看下面这些语句会不会使用刚才建立的所有IDX_1。如图下图所示: 上述问题的答案:语句只要有ENAME=‘a’条件,就能用上索引IDX_1,而不是全表扫描。 1、复合索引的第一个原则——前缀性 例如前面建立了一个复合索引IDX_1 。请记住:Oracle索引,包括复合索引都是排序的。该复合索引在数据库索引树上是先按姓名排序,再按工作排序,最后按所属经理排序。 Oracle不是智能的,它只会按索引排序找,由于该索引结构是首先按姓名排序的,所以只要 给出姓名字段,就能使用索引。如果没有姓名字段,Oracle就成无头苍蝇,乱找,变成全表扫描。 但是有时候复合索引第一个字段没有在语句中出现,Oracle也会使用该索引。这就叫Oracle的skip scan index功能,Oracle9i才提供的。 第 页|共 36 页 ship scan index功能适合于什么情况?如果Oracle发现第一个字段值很少的情况下,例如员工表emp中有GENDER(性别)字段,所以为了提高索引利用率,Oracle可将这个索引拆成(‘男’,‘ENAME’,‘JOB’,‘MGR’),(‘女’,‘ENAME’,‘JOB’,‘MGR’)两个复合索引。这样即便没有GENDER条件,Oracle也会分别到男索引树和女索引树进行搜索。 但是,(GENDER、ENAME、JOB、MGR)索引本身设计是不合理的,它违背了复合索引的第二个原理:可选性。 2、复合索引的第二个原则——可选性 你可能会问:复合索引中如何排列字段顺序?这时就要用到复合索引的第二个原理:可选性规则。Oracle建议按字段可选性高低进行排列,即字段值多的拍在前面。例如,(ENAME,JOB,MGR,GENDER)。这是因为,字段值多,可选性越强,定位的记录越少,查询效率就越高。 3、 复合索引的设计建议 Ø 分析SQL语句中的约束条件字段; Ø 如果约束条件字段比较固定,则优先考虑创建针对多字段的普通B*树复合索引。例如同时涉及到月份、纳税人识别号、税务机关代码3个字段的条件,则可以考虑建立一个复合索引; Ø 如果单个字段是主键或唯一字段,或可选性非常高的字段,尽管约束条件字段比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销; Ø 在复合索引设计中,其次应考虑复合索引字段的排序。例如上述索引的字段排序顺序为:纳税人识别号、税务机关代码、月份; Ø 如果条件涉及的字段不固定,组合比较灵活,则分别为字段建立索引。 Ø 如果是多表连接SQL语句,注意是否可以在被驱动表的连接字段与该表的其他约束条件字段上创建复合索引。(该条将在表连接中介绍) 第 页|共 36 页 Ø 通过多种SQL分析工具,分析执行计划并以量化形式评估效果。 八、 如何进行索引监控分析和优化 如果发现你的一张表字段才30个,索引都50个了,你可能不了解业务逻辑,根本不敢去删,也不知道该删哪些,怎么办?建议综合以下两种策略: 1、根据原理去判断 这种情况肯定存在很多复合索引,你可根据复合索引的前缀性和可选性两大原理去分析这张表各字段 的记录分布情况,特别是这50个索引的具体自动情况,做出合并、整合判断。 2、利用Oracle索引监控特性 更保险的办法,Oracle9i开始就提供了索引监控特性,在某个典型业务周期开始之前,执行如下命令对你需要关注的索引启用监控功能: 在典型业务周期结束之后,执行如下命令,结束监控: 查询如下视图,你就知道在这个典型业务周期之内,这个索引到底有没有使用了。 九、 如何进行索引碎片分析和整理 频繁对索引字段进行DELETE、UPDATE操作,会对索引造成大量碎片,从而极大影响索引的使用效率,并造成索引I/O的增加。 1、索引碎片分析 执行如下语句可监控索引的碎片情况: 第 页|共 36 页 表中:索引碎片率(%) = (del_lf_rows_len/lf_rows_len)*100 。 如果索引碎片率超过20%,则Oracle认为索引碎片已经非常严重。 2、索引碎片整理 Oracle进行索引碎片的处理包括两种策略: (1) 重建索引(rebuild) (2) 压缩索引(Coalesce) 建议采取定期索引重建(rebuild)策略。索引碎片是在日常的运行过程中产生的,开发人员是无法在开发环境下进行索引碎片分析和整理的,该工作是需要DBA日常运行维护的。 1.6. 绑定变量的使用 假设在一个并发访问量很高的系统,需要查询不同员工信息,于是写以下这样的SQL语句: 第 页|共 36 页 上述语句在并发量非常大的整体情况下,会整体影响应用的性能。上述语句修改成这样: 其中X是个变量,在真正的执行过程中,分别为X赋值1234,3456,5678等,查询出需要的结果。 注意:不要小看这个问题,该问题在Oracle统计的十大影响性能的应用问题中,排名第三。 一、 SQL语句的执行过程 为了理解上面的常见错误原因所在,特别是在理解语句共享性原理,需要从解剖SQL语句执行过程开始。下图是SQL语句的主要执行过程: Open Close Parse Bind Execute Fetch SQL语句主要分为如下4个阶段: 1、 Parse阶段 改阶段Oracle将首先在SHARE POOL(共享池)中搜索该语句,即判断该语句是否已经被分析和执行过,如果没有发现该语句,则需要检查改语句的语法、语义及访问权限、对视图定义和子查询进行转换操作,并最终确定该语句最优的执行计划,这个完整的分析过程叫硬解析(Hard Parse)。如果在SHARE POOL中发现该语句,说明该语句已经被分析和执行过,则只需要检查该语句的语义及访问权限,而其他大部分工作则无需在进行,这个过程叫软解析( 第 页|共 36 页 Soft Parse)。可见硬解析比软解析的资源消耗要大得多。 2、 Bind阶段 在该阶段,当SQL语句含有变量(BIND变量)时,Oracle通过赋值或参参等方式为这些BIND变量赋值。 3、 Execute阶段 在该阶段,Oracle将实施Parse阶段确定的执行计划,开始执行DML语句,实施I/O及排序操作等。如果DDL、DML操作,完成此阶段则执行过程结束。 4、 Fetch阶段 在该阶段只适合Select操作,即进行查询记录的读取并对查询结果进行排序。为提高性能,Oracle建议以数组方式成批提取记录,降低服务器和客服端的网络传输次数。 可见,一个SQL语句在执行过程中要做很多事。在Oracle的优化原理和技术中,有两种策略,一个是尽量少做事,另一个是要做的事情一样都不少,但是让跟多的资源同时来做,加快完成的速度。语句共享性原理就是前一种策略,即尽量少做事,也是Oracle多种优化技术和产品所采取的策略。 二、 SQL语句共享性原理 在单笔事务的资源消耗小、并发量高的系统中,应以系统的响应速度作为首要的优化目标,由于单个语句的执行性能比较容易优化,因此从SQL语句的4个阶段分析,应该尽量减少的Parse次数。 在单笔事务的资源消耗大、并发量不高的系统中,应以系统整体吞吐量作作为优化目标,从SQL语句的4个阶段分析,资源消耗主要在Execute和Fetch阶段,因此应该保证这类应用语句的执行计划是最优的,从而达语句执行高效率。 如何实现语句共享化? 第 页|共 36 页 SQL语句共享性主要针对事务资源消耗小、并发量高的应用,由于在此类应用中语句本身相对比较简单,通过简单的索引策略,语句中含有Bind变量,一般都能保证语句的最最优化,但是这些语句并大量非常高,因此减少这些语句重复Parse次数,是保证应用性能的主要方面。在技术上称为提高SQL语句共享性。 可以采用的主要技术手段如下: 1、 应用级绑定变量处理 这样,针对不同的员工信息,Oracle就只对“select * from emp where empno=:x”做一次硬解析。 2、 系统级绑定变量 通过将Oracle初始化参数CURSOR_SHARING由系统的EXACT修改为SIMILAR或FORCE,将原有语句的常量修改为系统Bind变量SYS_B_0,SYS_B_1….,从而达到语句共享性的目的。例如,如果上述语句原来使用的是常量,如“select * from emp where empno=1234”,现在通过设置CURSOR_SHARING为SIMILAR或FORCE,则这个语句就变成了“select * from emp where empno=:SYS_B_0”。 1.7. 表连接 一、 最经典、最常用的表连接技术——嵌套循环 如下语句,查询所有员工所在部门的所有信息: 执行计划如下: 第 页|共 36 页 从执行计划上看,先循环查询DEPT,在按DEPT每条记录去查询EMP,找到DEPT对应部门的所有员工。 Oracle是以两层循环方式实现两个表的连接和检索,其中DEPT表是外循环,EMP表是内循环。Oracle嵌套循环连接技术是最经典,也仍然是最常用的表连接技术。把外循环表(DEPT)叫外表或驱动表,内循环表(EMP)叫内表或被驱动表。 二、 嵌套循环连接与索引 例如:查询员工编号为7499的员工信息和所在部门的信息,语句如下: 为提高查询效率,Oracle应该结合索引技术来实现上述操作。正确的查询方式应该是:先按照建立在empno字上的索引去EMP表查empno为7499的员工信息,在根据7499所在部门的编号deptno去DEPT表查询该部门的详细信息,而且DEPT表的deptno字段上也建立了索引。执行计划如下: PK_EMP EMP PK_DEPT DEPT 以图表如下: 第 页|共 36 页 所以现在可以理解在前面讲的“关于B*树索引设计建议”中的第4条。“如果是多表连接SQL语句,注意被驱动表的连接字段是否需要创建索引。” 其实在上例中,被驱动表是DEPT,DEPT表连接字段是deptno,而EMP的 deptno字段是可以不需要建立索引的。 继续举例:如果查询员工为7499的员工信息,并且部门在DALLAS的部门信息,语句如下: 执行计划如下: 正确的查询方式是:先按照建立在empno字段上的索引去EMP表查询empno为7499的员工信息,在根据7499所在的部门号deptno去DEPT表查询该部门详细信息。此时DEPT表还有一个条件:loc=‘DALLAS’,因此可考虑(deptno,loc)复合方式去查询DEPT表,效率更,即可建立( deptno,loc)字段上复合索引IDX_DEPT_2。 执行计划如下: 在上面的例子中,被驱动表是DEPT,DEPT表连接字段是deptno,而loc是其他约束条件,所以考虑创建(deptno,loc)字段上的复合索引。而EMP表deptno字段是可以不建立索引的。 第 页|共 36 页 关于复合索引设计建议第7条:如果是多表连接SQL语句,注意是否可以在被驱动表的连接字段与该表的其他约束条件字段上创建复合索引。 需要进一步说明的是,为了说明在表连接中建立复合索引的重要性,故意将建立在deptno字段上的DEPT表的主键PK_DEPT先删除掉。否则根据关于复合索引创建时的第3条建议:“如果单字段是主键或唯一字段,或者可选性非常高的字段,尽管约束条件字段比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销”。 其实本例子中是不需要建立复合索引(IDX_DEPT_2)的。 总之嵌套循环连接与索引,一般需要共同考虑和设计。 三、 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询。 低效: 高效: 2. 优化器和统计信息采集 2.1. 优化器 SQL优化器原理如下: 第 页|共 36 页 2.2. 基于规则优化器RBO Ø 一组简单的启发式规则和打分原则,来确定语句的执行过程和访问方式; Ø 与SQL语句编写的语法相关; Ø 不关心被访问对象的实际数据分布情况,索引效率等; Ø 简单粗放的执行机制; Ø 10g开始彻底不支持RBO; 规则如下: 第 页|共 36 页 2.3. 更先进的基于成本优化器CBO 依赖于数据对象的统计信息。例如表的记录数、消耗的数据块数、索引的可选性(不同的记录值数量)等。 精细的成本模型来评估SQL语句的执行性能。例如:CPU消耗、逻辑读写(内存消耗)、I/O读写、网络传输量的消耗分析等。 语句优化的整体质量高于RBO。 Oracle的大量新特性只能在CBO下运行。例如:HASH_JOIN操作、Bitmap索引、物化视图等。 2.4. 统计信息 数据库统计信息就是Oracle各种数据对象和运行环境的详细信息描述。 一、 统计信息类型 1、 表统计信息 表的记录数、物理块数、记录平均长度等; 第 页|共 36 页 字段的不同记录值数、空值(NULL)数、数据分布情况; 2、 索引统计信息 索引的叶节点块数、索引深度; 3、 系统统计信息 I/O存储设备指标、CPU指标、性能和利用率; 二、 统计信息的作用 依据精确反应实际数据和运行环境的信息,Oracle CBO优化器就有最大可能为每个SQL语句产生最优的、代价最低的执行路径,从而保证系统整体性能的最优。 三、 统计信息采集的挑战和难度 Ø 数据是处于动态变化之中的; Ø 统计信息采集本身是消耗系统资源的; Ø 如何在既不过度消耗系统资源,又能确保CBO优化器整体运行状况良好,即确保SQL语句最优化,是涉及运行维护、应用开发等各方面工作的重要挑战; 四、 统计信息采集基本策略 Ø 以应用SQL语句执行计划的最优化为最终目标; Ø 尽可能在较短的时间窗口内完成统计信息采集和更新,避免过度消耗系统资源; Ø 为简化管理,统计信息采集过程避免复杂化; 五、 手工采集相关脚本 Ø 数据库级统计信息采 Ø schema级统计信息采集 第 页|共 36 页 Ø 表级统计信息采集 Ø 分区级统计信息采集 六、 自动采集统计信息 Ø 为减轻DBA工作负担,Oracle从10g开始,通过自动调用GATHER_STATS _JOB作业来采集统计信息; Ø 自动监控DML操作涉及的数据对象和变更量; Ø 自动采集缺乏统计信息或过时统计信息的数据库对象; Ø 采用合适的采集样率; Ø 对数据分布不均匀的字段,进行数据分布分析(Histograms); Ø 自动设置并行度; Ø 对数据库对象根据修改和查询的频度进行优先级考虑; Ø GATHER_STATS_JOB自动在WEEKEND_WINDOW和WEEKNIGHT_WINDOW时间窗口运行。 七、 自动和手工结合进行统计信息的采集 保留10g统计信息采集缺省特性 第 页|共 36 页 Ø 对表的DML和Truncate操作进行自动监控,监控结果将保存在DBA_TAB_ MODIFICATIONS视图中; Ø 如果一个表的记录有10%以上记录被变更时,Oracle认为该表的统计信息为过期(STALE)数据; Ø 当手工通过GATHER_DATABASE_STATS、GATHER_SCHEMA_STATS进行统计信息采集,并且当OPTION参数设置为‘GATHER AUTO’或‘GATHER STALE’时,Oracle可自动根据DBA_TAB_MODIFICATIONS视图中信息,将存在过期(STALE)统计信息的表重新进行采集。; 八、 自动统计信息采集的局限 Ø 适合于数据规模较小、数据变化不大的系统 Ø 不太适合数据量浩大、数据变化复杂、业务复杂的系统 Ø 技术上的局限: a) 没有打开并行处理 b) 自动选择样本比例不合理 Ø Oracle再智能算法永远没有用户自己了解业务和数据变化情况 九、 关闭自动采集功能 十、 锁住统计信息采集 在某些交易系统中,某些表或schema的数据基本处于稳定状态,记录情况不会出现大起大落的情况。没有必要频繁对这些表或schema进行统计信息采集; 第 页|共 36 页 在采集了这些表或schema的典型业务数据信息,并且确保对这些表或schema的SQL语句执行计划为最优化之后,可通过10g的如下命令,锁住这些表或整个schema的统计信息: SQL> exec DBMS_STATS.LOCK_TABLE_STATS(‘owner name’,’table name’); SQL> exec DBMS_STATS.LOCK_SCHEMA_STATS(‘schema name’); 在自动采集统计信息或其它方式的统计信息采集中,这些表或schema的统计信息将不会被采集,将节省资源消耗和时间。 十一、 Pending统计信息 Ø 在修改了统计信息采集缺省参数,并进行统计信息采集之后,最好先放到Pending区域进行验证,然后再发布(Publish)到数据字典中 Ø 将统计信息不发布,而是保存在Pending区 Ø 收集该表统计信息 Ø 使用Pending区的统计信息 Ø 测试、验证 Ø 发布Pending区的统计信息 第 页|共 36 页 3. 基于大批量数据处理相关技术 3.1. 位图索引 一、 位图索引的特点 Ø 与常规B*树索引相比,位图索引的速度更快,使用更少的空间; Ø 每一个位图索引包含的存储块为位图; Ø 每一个位图包含一个特定的信息为每一个索引列的值; Ø 位图压缩并存储在一个B*树结构中; 二、 位图索引的结构 每个位置在一个位图存储特定的行信息。 三、 创建位图索引 第 页|共 36 页 四、 使用位图索引的查询 五、 何时使用位图索引 Ø 列基数较低; Ø 列中经常用到:复杂的WHERE子句的条件和组功能(如计算,总和) Ø 非常大的表; Ø DSS(决策支持系统)有许多特别的查询和并发DML变化很少; 第 页|共 36 页 3.2. 物化视图 如何降低各种统计运算语句的资源消耗,事实上Oracle提供了非常有针对性的技术,即物化视图和语句重写技术。 如下图: 通常是需要在各种交易明细数据的基础上进行统计运算的,这是非常消耗资源的。Oracle物化视图和语句重写的概念和思路如下: Ø 物化视图与视图类似,都保存了视图的定义。最大的区别是物化视图是一个实体,保存了从视图中产生的数据,尤其是汇总数据,需要消耗一定的存储资源,即遵循了以空间换取时间的基本原则。 Ø 将进行统计运算、多表连接和其他复杂计算的SQL语句的结果,直接生产到定义的物化视图中。 Ø 通过物化视图的各种数据刷新机制(COMPLETE、FAST、FORCE等)以及手工或自动方式,来保持基表与物化视图数据的一致性。 Ø Oracle的语句重写技术,能自动将原有SQL统计信息导向到相应的物化视图,一方面大大提高了统计运算速度,另一方面又保证了对应用的透明性。 Ø 在物化视图上,可以创建相应的索引,从而进一步提高访问速度。 一、 创建物化视图 第 页|共 36 页 二、 相关的系统设置 为了保证物化视图和语句重写功能的实现,需要预先进行如下一些系统设置。 (1)初始化参数的设置 (2)需要为创建物化视图的用户赋予如下权限: 三、 为什么没有实现语句重写 在通过物化视图技术过程中,最难的不是如何设计物化视图,而是发现统计运算语句还是查询交明细表,没有去查询物化视图。 如何分析为什么没有实现语句重写?过程如下: Ø 首先需要创建REWRITE_TABLE; 第 页|共 36 页 Ø 然后通过DNMS_MVIEW.EXPLAN_REWRITE去分析原因,并通过查询REWRITE_TABLE去获知是否实现语句重写及原因所在 执行以下命令可自动进行物化视图的刷新: 第 页|共 36 页

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档