《oracle开发技术交流.ppt》由会员分享，可在线阅读，更多相关《oracle开发技术交流.ppt（89页珍藏版）》请在金锄头文库上搜索。
1、WANGZT 深圳市新宇龙信息科技有限公司 2018年10月18日星期四,ORACLE开发技术交流 (侧重性能优化),交流内容,性能优化总体概述,SQL语句优化,表分区及索引,ORACLE性能分析,分析函数的使用,DTV报表程序分析,数据库优化调整级别,第一级：操作系统级包括硬件平台 第二级：ORACLE RDBMS级的调整 第三级：数据库设计级的调整 第四级：SQL语句级,系统优化涉及的角色,在优化过程中，系统涉及到下面角色,高级管理人员:负责制定并考察商业规划和流程，从而为应用设计提供一种清晰而适当的模型。 应用设计人员：必须饶过潜在的系统瓶颈进行设计，还应当与系统设计人员进行交流，从而得
2、到每个人都可以理解应用模型。 应用开发人员：必须与其所选择的实现策略进行充分的交互，使得在进行语句优化的时候，可以顺利地较快确定模块和SQL语句。 数据库管理员：必须仔细地监视系统的活动，并将其归档，以此来识别和修正异常的系统性能。,何时优化效率最高,下图是应用程序生命周期内不同阶段的优化代价，由此可见为了达到最佳的效果，优化工作应当从设计阶段进行,优化的优先步骤,下面是对基于ORACLE应用的优化的推荐方法，它分为10个步骤。按照投资回报减少的顺序给出优化过程步骤，对性能影响最大就越靠前：,理解ORACLE性能优化,响应时间与吞吐量的折衷 响应时间=服务时间+等待时间；系统吞吐量指在给定的时
3、间内所完成的工作量； 在线事务处理（OLTP）应用程序把吞吐量定义为性能指标； 决策支持系统（DSS）把响应时间定义为性能指标。 临界资源 诸如 CPU、内存、I/O容量、网络带宽等资源，是减少时间的要素 过度请求的影响 响应时间增长，降低吞吐量 调整以解决问题 调整单元消费量：使事务在执行时占用更少的资源减少服务时间； 调整功能请求：通过重新规划或重新分配工作； 调整容量：通过增加或重新分配资源。,优化数据模型设计,规范化 数据库设计阶段通常要经历规范化阶段，此时需要对数据进行分析，以降低数据冗余，除了主键外，任何数据元素都应当在数据库中只能出现一次； 数据规范化的优点是减少了数据冗余，节约
4、了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。 反规范化 在数据库的设计过程中有时故意保留非规范化约束，或者规范化以后又反规范，这样做通常是为了改进数据库的查询性能，加快数据库系统的响应速度。 如一些汇总数据。 设计优化策略 将规范化的表作为逻辑数据库设计的基础 再根据整个应用系统的需要，物理地非规范化数据 规范与反规范都是建立在实际的操作基础之上的约束，脱离了实际两者都没有意义。只有把两者合理地结合在一起，才能相互补充，发挥各自的优点。,数据库表设计,合理设计表及索引,建立分区表 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样
5、，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间 。 合理建立索引 索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址 。 建表参数 在建立表时，应正确设置参数pctfree和pctused，尽量减少数据库中出现行链接和行迁移。 在建表时设置并行参数,以便使用并行处理.,分区表设计原则,表的大小：当表的大小超过1.5GB2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无
6、关数据查询的特性。 数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。 数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建立对应关系。 只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。 并行数据操作：对于经常执行并行操作（如Parallel Insert等）的表应考虑进行分区。 表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。,建立索引原则,何时创建B*树索引 ？ 经常在where 后首先用 column_name=xx; 经常在两个
7、以上表进行连接条件的; 在几个表中选择合适的字段作为 主键和外部键； 何时创建逆关键字索引 ？ 用户经常操作的记录中，对最近的记录操作次数更多 ； 被索引的列顺序是逆序递减的数值。 位图索引一般只用户数据仓库 何时不要建索引 ？ 索引列的宽度过宽； 在应用中根本就很少用作为查询条件； 该列如果成为索引可能消耗很大的空间。,索引列和表达式的选择,WHERE 从句频繁使用的关键字； SQL语句中频繁用于进行表连接的关键字； 可选择性高的关键字，可选择性值在另外章节介绍； 对于取值较少的关键字或表达式，不要采用标准的B*树索引。可以考虑建立位图索引； 不要将那些频繁修改的列作为索引列； 不要使用包含
8、函数或操作符的WHERE从句中的关键字作为索引。如果存在这样的需要的话，可以考虑建立函数索引; 如果大量的并发INSERT、UPDATE、DELETE语句访问了父表或子表，则考虑使用完整性约束的外部键作为索引； 在选择所有列，还要考虑该索引所引起的INSERT、UPDATE、DELETE操作是否值得。,优化访问路径,为了确保数据库访问的效率，需要优化访问路径： 考虑使用簇、哈希簇 ； B*树索引、位图索引 ； 优化程序提示； 对表进行分析；,ORACLE并行执行,设置会话级的并行: alter session enable parallel dml; alter session force p
9、arallel query parallel n; 好处:使用比较简单有效; 设置语句级并行: 通过提示设置,如:Select /*+ parallel(a,n) */ 好处:可以比较灵活地设置; 语句级并行度设置优先级高于会话级; 并行注意事项: parallel是相对于多CPU而言的，对于单CPU，反而会更慢！ 建表时要设置并行度,或之后用alter table * parallel n来设置; 系统的并行参数要设置正确。 并行查询一般用在数据仓库项目或报表查询系统中, OLTP系统一般不建议用parallel;,定期对表进行分析,对表进行分析的作用 目前基本都是使用基于代价的优化器,表的
10、统计分析资料对ORACLE生成最优的执行计划十分有用. 分析语句示例 DBMS_STATS.GATHER_TABLE_STATS(ownname=DGDM_MK,tabname=TB_MK_LS_CALL_2_DTAL,estimate_percent=1,cascade=TRUE,degree=4); 上面语句中, DGDM_MK 是表属主名字, TB_MK_LS_CALL_2_DTAL是表名;4是并行度; analyze table ACCT_ITEM estimate statistics sample 10 percent;,A,问题讨论,交流内容,性能优化总体概述,SQL语句优化,表
11、分区及索引,ORACLE性能分析,分析函数的使用,DTV报表程序分析,SQL语句性能分析,SQL语句性能分析,使用AUTOTRACE set autotrace on; (打开自动分析统计，并显示SQL语句的运行结果） set autotrace traceonly; (打开自动分析统计，不显示SQL语句的运行结果）。 set autotrace on exp; (仅显示EXPLAIN PLAN) set autotrace on stat;(仅显示统计信息) 使用EXPLAIN PLAN 使用开发工具提供的查看执行计划的功能 Explain plan set statement_id = (
12、语句标记名称) for 从PLAN_TABLE 中选择输出 两者区别 AUTOTRACE 实际执行后的统计结果； EXPLAIN PLAN是执行计划，不真正执行SQL语句,SQL语句性能分析,执行计划的解读,通过分析执行计划，可以知道Oracle是怎么样连接表，使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。 需要按照从里到外，从上到下的次序解读分析的结果 EXPLAIN PLAN分析的结果是用缩进的格式排列的， 最内部的操作将被最先解读，如果两个操作处于同一层中，带有最小操作号的将被首先执行。 常用的工具软件PL/SQL DEVELOPER以及Enterprise Manage
13、r Console中的执行计划生成及展示功能都可以按顺序解读执行计划,特别是Enterprise Manager Console 中解读的比较详细.,ORACLE的优化模式,CHOOSE: 如果数据字典中有访问表的统计信息,将基于代价的优化方法,并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息,将基于规则的优化方法; RULE: 基于规则的优化是基于固定的语法规则及可用索引的优化,与数据量大小没有关系. ALL_ROWS: 是基于代价的优化方法;它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑. FIRST_ROWS: 是基于代价的优化方法;能够获得最佳响应时
14、间;会选择一个最快速度返回结果集第一条记录,而不管获取结果集的所有记录的执行成本是不是最优.,ORACLE表的连接方式,嵌套循环：NESTED LOOP JOIN 排序合并连接：SORT MERGE JOIN 哈希连接：HASH JOIN 索引连接(INDEX JOIN) 迪卡尔连接：CARTESIAN JOIN 群集连接 (CLUSTER JOIN),嵌套循环连接,Oracle 优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表，将另外一个表指定为内部表 从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。 读取外部
15、表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。 重复上述步骤，直到外部表中的所有纪录全部处理完，最后产生满足要求的结果集。 使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法 在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的 如果内部行源表连接的列上不包含索引, 嵌套循环连接效率是很低的 可以通过在SQL语句中添加HINTS(/*+ use_nl(a b) */ ),强制ORACLE优化器产生嵌套循环连接的执行计划,排序合并连接,ORACLE分别将第一个源表，第二个源表按他
16、们各自要连接的列排序，然后将两个已经排序的源表合并，如果找到匹配的数据，就放在结果集中。 在缺乏数据的选择性或者可用的索引时,或者两个源表都比较庞大时,排序合并连接将比嵌套循环连更加高效。 排列合并连接需要比较大的临时内存块,以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。 可以通过在SQL语句中添加HINTS（/*+ use_merge(a,b) ）,强制ORACLE优化器产生排序合并连接的执行计划。,哈希连接,当内存能够提供足够的空间时，哈希(HASH)连接时ORACLE优化器的通常选择。 ORACLE访问一张表（通常是较小的表），在内存中建立一张基于连接键的哈希表，在另外一个表做排序合并。然后它扫描连接中的其它表（通常是较大的表），并根据哈希表检测是否有匹配的记录。 返回较多记录，无可用索引，两张表都比较大。 小表建立hash table后扫描大表。 可以通过在SQL语句中添加HINTS（/*+ use_hash(a,b) ）,强制ORACLE优化器产生hash连接的执行计划。,
