ORACLE SQL性能调优.doc

ORACLE SQL 性能调优性能调优 1序言序言 2 2影响影响 SQL PERFORMANCE 的关键因素和配置 的关键因素和配置 2 2 1关于执行计划 2 2 2ORACLE 优化器 2 2 2 1ORACLE优化器的优化方式 2 2 2 2优化器的优化模式 Optermizer Mode 3 2 2 3Optimizer mode优化模式级别的设定 3 2 2 4查看对象统计信息 object statistics 3 2 3结合 BENQ ORACLE ERP 4 2 3 1优化模式 Optermizer Mode 4 2 3 2关于 Gather 5 2 4跟踪 SQL 实际运行的 COST 5 2 5小结 5 3SQL 语句的语句的 TUNING 经验分享 经验分享 6 3 1绝大多数情况下NOT EXISTS比NOT IN 效率高 6 3 2UNION ALL 效率比 UNION 高很多 6 3 3一些很耗资源的 SQL 操作 在不必要的情况下不要使用 6 3 4通常联接查询比子查询的效率要高很多 7 3 5用TABLE 索引 INDEX 栏位去做 TABLE间的关联 可避免费时的全表扫描 7 3 6在 VIEW中尽量不要使用 PACKAGE FUNCTION 来得到栏位值 8 3 7通过 ROWID 访问表 9 3 8必要时 可在 ORACLE STANDARD TABLE上加索引 9 3 9合理排列 WHERE 子句中的连接顺序 9 3 10用 WHERE子句替换 HAVING 子句 9 3 11关于使用索引 INDEX 的一些注意点 10 3 11 1 NOT操作将不使用索引 10 3 11 2 是字符连接函数 就象其它函数那样 停用了索引 10 3 11 3相同的索引列不能互相比较 这将会启用全表扫描 10 3 11 4避免在索引列上使用计算 11 3 11 5基于成本的优化器 CBO 会对索引的选择性进行判断 来决定是否使用索引 11 3 11 6Index信息的重新统计 11 3 12识别 低效运行 的 SQL 语句 11 学习报告学习报告 1序言序言 BenQ Sale Office自Oracle ERP上线后 随着资料量的日益加大 目前BQE BQC BQP这几个Site的 Performance的问题都表现得越来越明显 Tunning得工作量也明显增加 考虑到影响Oracle SQL Performance的有很多方面 我们这次研究的Tunning先包括两个大方向 Oracle DB Configuration和SQL Statement Tuning 这份文档主要将影响SQL Performance的因素做个基础的阐述 再将大家在平时写SQL积累的经验做个总结 2影响影响 SQL Performance 的关键因素和配置 的关键因素和配置 Oracle Database上的设置对Performance的影响很大 如Shared Pool Size Buffer Cache Size SGA Structures Database I O Configuration Rollback Segments等等 这些是DBA要更据实际状况取 Tunning的部分 我们暂不详细讨论 DBA可对这块进行补充 这份文档我们将重点放在影响SQL执行效率的一些关键因素和设置上 2 1关于执行计划关于执行计划 我们在平时工作中用到大量的View View中SQL的写法对效率的影 响很大 首先有必要了解一条SQL语句是如何被执行的 当SQL语句 进入Oracle的缓存后 在该语句准备执行之前 DBMS将执行下列步 骤 1 SQL 语法检查 检查 SQL 语句拼写是否正确和词序 2 SQL 语义分析 核实所有的与数据字典不一致的表和列的名字 3 生成执行计划 使用优化规则和数据字典中的统计表来决定最佳执行计 划 4 建立可执行的二进制代码 基于执行计划 Oracle 生成二进制执行代码 5 抓取并返回需要的数据 其中第三步生成执行计划非常关键 所谓执行计划 就是对一个查询任务 做出一份怎样去完成任务的详细方案 对于查询而言 我们提交的SQL仅仅是描述出了我们的目的 但Oracle内部怎么去得到这些数据 是由数据库 DBMS来决定的 所以执行计划产生的好坏直接影响SQL 运行的Performance 我们平时对SQL做一些Tuning 为了得到相同的 数据而去尝试用不同的SQL写法 目的就是能让Oracle更据你的语句产生一个更好的执行计划 从而得到更好的 效率 2 2ORACLE优化器优化器 在不同的情况下 同一条SQL可能有多种执行计划 但理论上在某一时点 一定只有一种执行计划是最优的 花费时间是最 少的 执行计划的工作是由优化器 Optimizer 来完成的 那优化器是依据什么讯息去创建出最合理的执行计划 回答这个 问题前先要了解一下ORACLE的优化器 2 2 1ORACLE优化器的优化方式优化器的优化方式 学习报告学习报告 ORACLE优化器的优化方式有两大类 即基于规则的优化方式 Rule Based Optimization 简称为RBO 和基于代价的优化方 式 Cost Based Optimization 简称为CBO A RBO方式 优化器在分析SQL语句时 更据数据库中表和索引等定义信息 遵循的是Oracle内部预定的一些规则 比如 我们常见的 当一个where子句中的一列有索引时去走索引而不走全表扫描 B CBO方式 依词义可知 它是看语句的代价 Cost 了 基于代价的查询 数据库根据搜集的表和索引的数据的统计信息 统计信息通过analyze 命令或者使用dbms stats包来搜集 综合来决定选取一个数据库认为最优的执行计划 实际 上不一定最优 统计信息给出表的大小 有多少行 每行的长度等信息 注意 这些统计信息起初在库内是没有的 是根据 analyze 命令或者dbms stats包来定期搜集后才出现的 所以很多 的时侯过期统计信息会令优化器做出一个错误的执行计划 因些我们应及时更新这些信息 为了使用基于成本的优化器 CBO 你必须经常运行analyze或dbms stats命令 以增加数据库中的对象统计信息 object statistics 的准确性 在Oracle8及以后的版本 Oracle强列推荐用CBO的方式 2 2 2优化器的优化模式优化器的优化模式 Optermizer Mode 优化模式包括Rule Choose First rows All rows这四种方式 先解释一下 1 Rule 即走基于规则的方式 2 First Rows 基于成本的方式 指执行计划采用最少资源尽快的返回部分结果给客户端 它将是以最快的方式返回查询 的最先的几行 从总体上减少了响应时间 对于排序分页页显示这种查询尤其适用 3 All Rows 基于成本的方式 当一个表有统计信息时 它将以最快的方式返回表的所有的行 从总体上提高查询的吞吐 量 没有统计信息则走基于规则的方式 4 Choose 这是我们应关注的 默认的情况下 Oracle 用的便是这种方式 指的是当一个表或索引有统计信息 指运行过 analyze 命令或者使用过 dbms stats 包来搜集 则走 CBO 的方式 在 CHOOSE 模式下 ORACLE 采用的是 FIRST ROWS 如果表或索引没统计信息 那么走 RBO 的方式 注 Oracle ERP 11i 之前的版本 默认用 RULE Oracle ERP 11i 之后的版本 默认用 CHOOSE 2 2 3Optimizer mode优化模式级别的设定 优化模式级别的设定 A Instance 级别 我们可以通过在 ora 文件中设定 OPTIMIZER MODE 去选用 B Sessions 级别 通过 SQL ALTER SESSION SET OPTIMIZER MODE 来设定 C 语句级别 这些需要用到 Hint 比如 SELECT rule ordh order number ordl ordered item FROM apps oe order headers all ordh apps oe order lines all ordl WHERE ordh header id ordl header id 2 2 4查看对象统计信息查看对象统计信息 object statistics 对 CBO 模式 对象统计信息至关重要 如何查看对象统计信息 object statistics Oracle 中关于表的统计信息是在数据字 学习报告学习报告 典中的 可以下 SQL 查询到 eg SELECT table name num rows blocks empty blocks AS empty avg space chain cnt avg row len FROM dba tables WHERE owner ONT AND table name OE ORDER LINES ALL TABLE NAMENUM ROWSBLOCK S EMPTYAVG SPAC E CHAIN CNTAVG ROW LE N OE ORDER LINES ALL5344505500441 可以看到数据字典中统计到的该表有 5344 笔记录 我们下 SQL 验证一下 select count from apps OE ORDER LINES ALL 发现返回是 16518 笔记录 可见这个表的统计信息是比较陈旧的 真实数据与统计到的数据有较大的差别 在这种情况下 如果某个 View 用到此 Table 且系统使用 CBO 的方式 则可能导致 Oracle 的 optimizer 给出效率低下的执行计划 此时可以用 ANALYZE 去重新统计 OE ORDER LINES ALL 这个表 可以下 SQL ANALYZE TABLE ONT OE ORDER LINES ALL COMPUTE STATISTICS 再次 Query 数据字典 TABLE NAMENUM ROWSBLOCK S EMPTYAVG SPAC E CHAIN CNTAVG ROW LE N OE ORDER LINES ALL1651815301035865257643 发现此时的信息已是最新的了 有了比较正确的统计信息 optimizer 才能给出高效的执行计划 2 3结合结合BenQ Oracle ERP 2 3 1优化模式优化模式 Optermizer Mode 我们再来看一下我们 ERP 的 DB 的设置情况 以下是从我们 ERP 中 BQE Production 环境取到的一些设置 disk BQE bqedb 9 2 0 dbs ifilecbo ora optimizer mode Prior to 11i optimizer mode was always set to rule For 11i choose is mandatory Although Applications modules will set the optimizer mode to either first rows or all rows depending on whether online or batch an Applications database MUST BE STARTED with the optimizer mode set to CHOOSE Many of the system dictionary views in particular export still require the RBO In general the profile options will ensure that on line users use first rows batch jobs use all rows 学习报告学习报告 IMPORTANT The CBO requires accurate table and index statistics and FND STATS should be run regularly See the FND documentation for further details optimizer mode choose 可以看到默认 Oracle ERP11i 用的 optimizer mode 是 choose 且 Oracle 强烈建议要定期运行 FND STATS 2 3 2关于关于 Gather 说到定期运行 FND STATS 不知大家是否会联想到 Oracle ERP 中的一个 Request Gather Oracle ERP 中有几个与 Gather 有关的标准 Request Gather All Column Statistics FND STATS GATHER ALL COLUMN STATS Gather Column Statistics FND STATS GATHER COLUMN STATS Gather Schema Statistics FND STATS GATHER SCHEMA STATS Gather Table Statistics FND STATS GATHER TABLE STATS 查看 FND STATS 这个 Package 的写法 其实它就是在调用 Oracle DB 中 Standard 的 Package dbms stats 中的某 些 Function Oracle DB 中常用的 Gather 有以下一些 DBA 也可以直接在 Database 级别上定期 Run 这些 Function 以便能让 Oracle 统计到最新的数据库状况 dbms stats gather database stats dbms stats gather schema stats dbms stats gather table stats dbms stats gather index stats 2 4跟踪跟踪 SQL实际运行的实际运行的Cost 执行计划是 Oracle 更据一些统计信息去 估计 出各个步骤所耗的 Cost 与实际的执行过程所耗 Cost 不见得一样 实际 执行过程耗的 CPU Disk IO 等资源的数量可以通过 sql trace 统计出来 所以 Tuning SQL 不仅要看 执行计划 有时 还必须结合 trace 的 Log 去分析 For example 若我要查某段程序运行过程的所有SQL的Performance情况 可以程序逻辑开始前加上sql trace true 结束前结束sql trace false即可 alter session set sql trace true 程序逻辑here alter session set sql trace false 然后去OS上去找出这个trace file 用tkprof 去转换 然后再看Log的详细内容 DEV2 disk DEV2 dev2db 9 2 0 admin DEV2 udump tkprof dev2 ora 13148 trc log txt 附档是转出来的例子 记得 CPU DISK 用的比较少的 就会比较好 学习报告学习报告 2 5小结小结 更据以上一些理论和我们 ERP 上的实际状况 我们可以得到一些建议 1 因为在 Instance Level 我们的 optimizer mode choose 所以定期运行 ANALYZE 或 dbms stats 非常重要 尤其是当上次统计后 数据量已发生较大变化之后 注意 统计操作是很耗资源的动作 要在系统 Loading 小的时候进行 2 因为 optimizer mode 优化模式可以设定 Sessions 级别和语句级别 所以必要时可以通过改 optimizer mode 的方 式让提高 Performance 例如 某报表的 View 是 EIS 类型的 需要一次抓得所有资料 则可以使用 Hint 的方式使该 SQL 的 optimizer mode ALL ROWS 让 Oracle 优化器产生更好的执行计划 学习报告学习报告 3SQL 语句的语句的 Tuning 经验分享 经验分享 3 1绝大多数情况下绝大多数情况下not exists比比not in 效率高效率高 低效 DEV2 5秒 SELECT ordl ordered item FROM apps oe order lines all ordl WHERE ordl header id not IN SELECT header id FROM apps oe order headers all ordh WHERE ordh flow status code CLOSED 高效 DEV2 1秒 SELECT ordl ordered item FROM apps oe order lines all ordl WHERE not EXISTS SELECT 1 FROM apps oe order headers all ordh WHERE ordh flow status code CLOSED AND ordh header id ordl header id 注 exists和 in 相比 效率有高有低 没有明显的差别 3 2UNION ALL效率比效率比UNION高很多高很多 DEV2 5秒 SELECT BQC ORDER ordh order number FROM apps oe order headers all ordh WHERE ordh org id 82 UNION SELECT WGQ ORDER ordh order number FROM apps oe order headers all ordh WHERE ordh org id 93 DEV2 0 5秒 SELECT BQC ORDER ordh order number FROM apps oe order headers all ordh WHERE ordh org id 82 UNION ALL SELECT WGQ ORDER ordh order number FROM apps oe order headers all ordh WHERE ordh org id 93 3 3一些很耗资源的一些很耗资源的SQL操作 在不必要的情况下不要使用操作 在不必要的情况下不要使用 Select Order by Group by Distinct UNION MINUS INTERSECT 操作是相当耗时的 在View中能不使用就不 要使用 Eg 1 order by DEV2 0 4秒 select from apps oe order lines all ordl DEV2 23秒 select from apps oe order lines all ordl order by ordl creation date Eg 2 group by DEV2 9秒 学习报告学习报告 SELECT SUBSTR ordh order number 1 50 AS order number NVL SUM ordl ordered quantity ordl unit selling price 0 NVL SUM ordl tax value 0 AS amount FROM apps oe order headers all ordh apps oe order lines all ordl WHERE ordh header id ordl header id GROUP BY SUBSTR ordh order number 1 50 DEV2 0 5秒 SELECT SUBSTR ordh order number 1 50 AS order number SELECT NVL SUM ordl ordered quantity ordl unit selling price 0 NVL SUM ordl tax value 0 FROM apps oe order lines all ordl WHERE header id ordh header id as amount FROM apps oe order headers all ordh Eg 3 distinct DEV2 50秒 SELECT DISTINCT rctl customer trx line id rctl inventory item id rctl description item desc rctl extended amount extended amount itm attribute1 AS item type FROM apps ra customer trx lines all rctl apps mtl system items b itm WHERE rctl inventory item id itm inventory item id AND rctl line type LINE DEV2 0 1秒 SELECT rctl customer trx line id rctl inventory item id rctl description item desc rctl extended amount extended amount SELECT itm attribute1 FROM apps mtl system items b itm WHERE rctl inventory item id itm inventory item id AND ROWNUM 1 item type FROM apps ra customer trx lines all rctl apps ra customer trx all rcta WHERE rctl customer trx id rcta customer trx id AND rctl line type LINE 通常 带有UNION MINUS INTERSECT的SQL语句都可以用其它方式重写 如果你的数据库的SORT AREA SIZE调配得 好 使用UNION MINUS INTERSECT也是可以考虑的 毕竟它们的可读性很强 3 4通常联接查询比子查询的效率要高很多通常联接查询比子查询的效率要高很多 尽量在 SELECT 子句里面用联接查询 少子查询 因为Subquery所得到的子Table的数据量等讯息是Oracle无法事前统计 出来的 所以优化器也很难得出一个优化过的执行计划 子查询在以下情况比较适合 1 适合用于小 table 也就是说 以大tables为base做join 小table 做 subquery 大 tables 之间最好用直接 join 2 不想该 table 成为限制条件时也可以勉强使用 3 需要取subquery 中summary 的值时可以考虑 因为 Group by 和 distinct 也是 SQL performance 的一大挑 战 3 5用用table 索引 索引 index 栏位去做 栏位去做Table间的关联间的关联 可避免费时的全表扫描 可避免费时的全表扫描 如果 Table 上有索引 则系统访问带索引的 Field 时 可通过访问索引中的栏位来快速获得相对应记录的 ROWID 而通常情 况下 使用索引比全表扫描要块几倍乃至几千倍 Oracle ERP 中几乎所有的 Table 都设有 Index 尽量以 Index 中的栏位做 join 避免用我们认为值是唯一的栏位去串 例 如 sales order number transaction number inventory org code 等 eg 使用OM Sale Order的主档表oe order headers all 和fnd lookup values 串一个简单的sql 这两张table的数据 量都会很大 fnd lookup values的index有两个 学习报告学习报告 INDEX applsys fnd lookup values u1 ON applsys fnd lookup values lookup type ASC view application id ASC lookup code ASC security group id ASC language ASC INDEX applsys fnd lookup values u2 ON applsys fnd lookup values lookup type ASC view application id ASC meaning ASC security group id ASC language ASC SQL1 SELECT order1 order number flv meaning FROM apps fnd lookup values flv APPS oe order headers all order1 WHERE order1 shipping method code flv lookup code 使用时间 1 67sec 数据 22819笔 BQC环境 这里只关联了一个条件lookup code lookup code的确是fnd lookup values Index fnd lookup values u1中的一 个栏位 但观察执行计划 它没有去用Index 为什么 因为Oracle中 用作Index的多个COLUMN是有顺序的 就刚才那个例子用lookup code做关联 它是在Index中第三位 所以 Oracle优化器会去分析 如果使用INDEX的话 就要先FULL SCAN lookup type 接着是view application id 再找到lookup code 分析结果后认为这样反而比FULL SCAN TABLE还要慢 所以执行计划没有使用INDEX 直接采用 全表扫描 有些文档直接说 如果索引是建立在多个列上 只有在它的第一个列 leading column 被where子句引用时 优化器才会选择使用该索引 所以为了让使用fnd lookup values的fnd lookup values u1 index 用一下SQL SQL2 SELECT order1 order number flv meaning FROM apps fnd lookup values flv APPS oe order headers all order1 WHERE order1 shipping method code flv lookup code and flv LOOKUP TYPE SHIP METHOD 时间 0 00sec BQC环境 学习报告学习报告 这个SQL中使用了LOOKUP TYPE SHIP METHOD 这个正好是INDEX的第一位 Oracle优化器分析 执行计划如果 使用INDEX 就马上找到LOOKUP TYPE SHIP METHOD 的INDEX记录 COST最小 分析的结果就是使用了 INDEX performance得到提升 3 6在在View中尽量不要使用中尽量不要使用 Package function 来得到栏位值 来得到栏位值 在view中尽量不要引用function 否则会增加一定的通讯开销 简单的判断尽量用decode nvl case when等实现 3 7通过通过ROWID访问表访问表 ORACLE 采用两种访问表中记录的方式 a 全表扫描 全表扫描就是顺序地访问表中每条记录 ORACLE采用一次读入多个数据块 database block 的方式优化全表扫描 b 通过ROWID访问表 如果可以 强烈采用基于ROWID的访问方式情况以提高访问表的效率 ROWID包含了表中记录的物理位置信息 ORACLE 采用索引 INDEX 实现了数据和存放数据的物理位置 ROWID 之间的联系 通常索引提供了快速访问ROWID的方法 因此 那些基于索引列的查询就可以得到性能上的提高 3 8必要时 可在必要时 可在Oracle Standard Table上加索引上加索引 eg BQC 收单用到如下一个简单的 SQL 耗时要 1 秒左右 测试后发现只要用到 flow status code 栏位 速度就很慢 SELECT ordl ordered item AS inventory item ordl subinventory NVL ordl ordered quantity 0 AS qty FROM apps oe order lines all ordl WHERE ordl flow status code ENTERED 于是手工在 apps oe order lines all 加上索引 CREATE INDEX ont oe order lines q1 ON ont oe order lines all flow status code ASC 再次运行此 SQL 耗时基本为 0 秒 学习报告学习报告 3 9合理排列合理排列WHERE子句中的连接顺序 子句中的连接顺序 ORACLE 采用自下而上的顺序解析 WHERE 子句 根据这个原理 那些可以过滤掉最大数量记录的条件最好写在 WHERE 子 句的末尾 虽然对简单 SQL Oracle 优化器自动会去调整顺序 但还是建议将能过滤掉最多记录的 Where 条件放在最后 3 10 用用Where子句替换子句替换HAVING子句子句 避免使用 HAVING 子句 HAVING 只会在检索出所有记录之后才对结果集进行过滤 这个处理需要排序 总计等操作 如 果能通过 WHERE 子句限制记录的数目 那就能减少这方面的开销 例如 低效 SELECT ordl ordered item sum ordl ordered quantity ordl unit selling price FROM apps oe order lines all ordl group by ordl ordered item having ordl ordered item like 00 高效 SELECT ordl ordered item sum ordl ordered quantity ordl unit selling price FROM apps oe order lines all ordl where ordl ordered item like 00 group by ordl ordered item 3 11关于使用索引 关于使用索引 Index 的一些注意点 的一些注意点 而通常情况下 使用索引比全表扫描要块几倍至几千倍 所以对索引要有比较深入的了解 某些情况下 SELECT 语句中的 WHERE 子句用到索引列 但生成的执行计划却不不使用索引 这里有一些例子 3 11 1 NOT操作将不使用索引操作将不使用索引 记住 索引只能告诉你什么存在于表中 而不能告诉你什么不存在于表中 不使用索引 SELECT ACCOUNT NAME FROM TRANSACTION WHERE AMOUNT 0 3 11 2 是字符连接函数是字符连接函数 就象其它函数那样就象其它函数那样 停用了索引停用了索引 不使用索引 SELECT ACCOUNT NAME AMOUNT FROM TRANSACTION 学习报告学习报告 WHERE ACCOUNT NAME ACCOUNT TYPE AMEXA 使用索引 SELECT ACCOUNT NAME AMOUNT FROM TRANSACTION WHERE ACCOUNT NAME AMEX AND ACCOUNT TYPE A 3 11 3相同的索引列不能互相比较相同的索引列不能互相比较 这将会启用全表扫描这将会启用全表扫描 不使用索引 SELECT ACCOUNT NAME AMOUNT FROM TRANSACTION WHERE