ORACLE SQL性能调优

1、ORACLE SQL 性能调优性能调优1序言序言.22影响影响 SQL PERFORMANCE 的关键因素和配置：的关键因素和配置：.22.1关于执行计划.22.2ORACLE 优化器.22.2.1ORACLE优化器的优化方式.22.2.2优化器的优化模式(Optermizer Mode) .32.2.3Optimizer mode优化模式级别的设定：.32.2.4查看对象统计信息(object statistics).32.3结合 BENQ ORACLE ERP.42.3.1优化模式(Optermizer Mode) .42.3.2关于 Gather.52.4跟踪 SQL 实际运行的 COS

2、T.52.5小结.53SQL 语句的语句的 TUNING 经验分享：经验分享：.63.1绝大多数情况下NOT EXISTS比NOT IN 效率高.63.2UNION ALL 效率比 UNION 高很多.63.3一些很耗资源的 SQL 操作，在不必要的情况下不要使用 .63.4通常联接查询比子查询的效率要高很多.73.5用TABLE 索引（INDEX）栏位去做 TABLE间的关联，可避免费时的全表扫描 .73.6在 VIEW中尽量不要使用 PACKAGE/FUNCTION 来得到栏位值，.83.7通过 ROWID 访问表 .93.8必要时，可在 ORACLE STANDARD TABLE上加索引

3、 .93.9合理排列 WHERE 子句中的连接顺序.93.10用 WHERE子句替换 HAVING 子句.93.11关于使用索引（INDEX）的一些注意点.103.11.1!=,NOT操作将不使用索引.103.11.2|是字符连接函数. 就象其它函数那样, 停用了索引.103.11.3相同的索引列不能互相比较,这将会启用全表扫描. .103.11.4避免在索引列上使用计算.113.11.5基于成本的优化器(CBO)会对索引的选择性进行判断，来决定是否使用索引.113.11.6Index信息的重新统计.113.12识别 “低效运行”的 SQL 语句.11学习报告学习报告1序言序言BenQ Sal

4、e 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

5、 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的缓存后

6、，在该语句准备执行之前，DBMS将执行下列步骤：1.SQL 语法检查：检查 SQL 语句拼写是否正确和词序。2.SQL 语义分析：核实所有的与数据字典不一致的表和列的名字。3.生成执行计划：使用优化规则和数据字典中的统计表来决定最佳执行计划。4.建立可执行的二进制代码：基于执行计划，Oracle 生成二进制执行代码。5.抓取并返回需要的数据。其中第三步生成执行计划非常关键，所谓执行计划，就是对一个查询任务，做出一份怎样去完成任务的详细方案。对于查询而言，我们提交的SQL仅仅是描述出了我们的目的，但Oracle内部怎么去得到这些数据，是由数据库DBMS来决定的。所以执行计划产生的好坏直接影响SQ

7、L 运行的Performance。我们平时对SQL做一些Tuning，为了得到相同的数据而去尝试用不同的SQL写法，目的就是能让Oracle更据你的语句产生一个更好的执行计划，从而得到更好的效率。2.2ORACLE优化器优化器在不同的情况下，同一条SQL可能有多种执行计划。但理论上在某一时点，一定只有一种执行计划是最优的、花费时间是最少的。执行计划的工作是由优化器(Optimizer)来完成的， 那优化器是依据什么讯息去创建出最合理的执行计划？回答这个问题前先要了解一下ORACLE的优化器：2.2.1ORACLE优化器的优化方式优化器的优化方式学习报告学习报告ORACLE优化器的优化方式有两大

8、类，即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。A、 RBO方式：优化器在分析SQL语句时，更据数据库中表和索引等定义信息，遵循的是Oracle内部预定的一些规则。比如我们常见的：当一个where子句中的一列有索引时去走索引而不走全表扫描。 B、 CBO方式：依词义可知,它是看语句的代价(Cost)了。基于代价的查询，数据库根据搜集的表和索引的数据的统计信息（统计信息通过analyze 命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划

9、（实际上不一定最优） 。统计信息给出表的大小 、有多少行、每行的长度等信息。注意：这些统计信息起初在库内是没有的，是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的，所以很多的时侯过期统计信息会令优化器做出一个错误的执行计划，因些我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。2.2.2优化器的优化模式优化器的优化模式(Optermizer Mode)优化模式

10、包括Rule，Choose，First rows，All rows这四种方式，先解释一下：1.Rule：即走基于规则的方式。2.First_Rows：基于成本的方式。指执行计划采用最少资源尽快的返回部分结果给客户端，它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间，对于排序分页页显示这种查询尤其适用。3.All_Rows：基于成本的方式。当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。4.Choose：这是我们应关注的，默认的情况下 Oracle 用的便是这种方式。指的是当一个表或索引有统计信息（指运行过analy

11、ze 命令或者使用过 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 OPTI

12、MIZER_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 中关于表的

13、统计信息是在数据字学习报告学习报告典中的，可以下 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_ALLTABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL5344505500441可以看到数据字典中统计到的

14、该表有 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 STAT

15、ISTICS;再次 Query 数据字典：TABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL1651815301035865257643发现此时的信息已是最新的了。有了比较正确的统计信息，optimizer 才能给出高效的执行计划。2.3结合结合BenQ Oracle ERP2.3.1优化模式优化模式(Optermizer Mode)我们再来看一下我们 ERP 的 DB 的设置情况：以下是从我们 ERP 中 BQE Production 环境取到的一些设置：/disk/BQE/bqedb/9.

16、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

17、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 statistic

18、s,# 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 有关的标准 Req

19、uest：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 的 Packa

20、ge 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 更据一些统计信息

21、去“估计”出各个步骤所耗的 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;程序逻辑herealter session set sql_trace

22、=false;然后去OS上去找出这个trace file，用tkprof 去转换，然后再看Log的详细内容。DEV2: /disk/DEV2/dev2db/9.2.0/admin/DEV2/udumptkprof dev2_ora_13148.trc log.txt附档是转出来的例子, 记得 , CPU + DISK 用的比较少的, 就会比较好!学习报告学习报告2.5小结小结更据以上一些理论和我们 ERP 上的实际状况，我们可以得到一些建议：（1）因为在 Instance Level 我们的 optimizer_mode = choose ，所以定期运行 ANALYZE 或 dbms_stat

23、s 非常重要，尤其是当上次统计后，数据量已发生较大变化之后。注意：统计操作是很耗资源的动作，要在系统 Loading 小的时候进行。（2）因为 optimizer_mode 优化模式可以设定 Sessions 级别和语句级别，所以必要时可以通过改 optimizer_mode 的方式让提高 Performance。例如，某报表的 View 是 EIS 类型的，需要一次抓得所有资料，则可以使用 Hint 的方式使该 SQL 的 optimizer_mode= ALL_ROWS，让 Oracle 优化器产生更好的执行计划。学习报告学习报告3SQL 语句的语句的 Tuning 经验分享：经验分享：3

24、.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

25、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 _i

26、d = 82UNION SELECT WGQ_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 93-（DEV2: 0.5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 82UNION ALLSELECT WGQ_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE

27、_id = 933.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_dat

28、e; -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.heade

29、r_idGROUP 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 amou

30、nt 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 WHER

31、E 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

32、= 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也是

33、可以考虑的, 毕竟它们的可读性很强。3.4通常联接查询比子查询的效率要高很多通常联接查询比子查询的效率要高很多尽量在 SELECT 子句里面用联接查询，少子查询。因为Subquery所得到的子Table的数据量等讯息是Oracle无法事前统计出来的，所以优化器也很难得出一个优化过的执行计划。子查询在以下情况比较适合：1.适合用于小 table。也就是说, 以大tables为base做join, 小table 做 subquery. (大 tables 之间最好用直接 join)2.不想该 table 成为限制条件时也可以勉强使用。3.需要取subquery 中summary 的值时可以考虑 -

34、 因为 Group by (和 distinct)也是 SQL performance 的一大挑战。3.5用用table 索引（索引（index）栏位去做）栏位去做Table间的关联间的关联，可避免费时的全表扫描，可避免费时的全表扫描如果 Table 上有索引，则系统访问带索引的 Field 时，可通过访问索引中的栏位来快速获得相对应记录的 ROWID，而通常情况下，使用索引比全表扫描要块几倍乃至几千倍。Oracle ERP 中几乎所有的 Table 都设有 Index，尽量以 Index 中的栏位做 join，避免用我们认为值是唯一的栏位去串: 例如 sales order number;

35、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_gr

36、oup_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 order1WHERE or

37、der1.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_

38、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.meaningFROM apps.fnd_lookup_valu

39、es flv,APPS.oe_order_headers_all order1WHERE order1.shipping_method_code = flv.lookup_codeand 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

40、.6在在View中尽量不要使用中尽量不要使用 Package/function 来得到栏位值，来得到栏位值，在view中尽量不要引用function，否则会增加一定的通讯开销。简单的判断尽量用decode，nvl，case when等实现。3.7通过通过ROWID访问表访问表 ORACLE 采用两种访问表中记录的方式: a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描. b. 通过ROWID访问表 如果可以，强烈采用基于ROWID的访问方式情况以提高访问表的效率。ROWID包含了表中记录的物理位置信息，O

41、RACLE采用索引(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

42、 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 子句，根据这个原理，那些可以过滤掉最大数量

43、记录的条件最好写在 WHERE 子句的末尾。虽然对简单 SQL，Oracle 优化器自动会去调整顺序，但还是建议将能过滤掉最多记录的 Where 条件放在最后。3.10 用用Where子句替换子句替换HAVING子句子句 避免使用 HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。 如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。 例如: 低效：SELECT ordl.ordered_item, sum(ordl.ordered_quantity*ordl.unit_selling_price) FROM app

44、s.oe_order_lines_all ordlgroup by ordl.ordered_itemhaving 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）的一些注意点）的一些注意点而通常情

45、况下，使用索引比全表扫描要块几倍至几千倍，所以对索引要有比较深入的了解。某些情况下 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 ACCOUNT_NAME = NVL(:A