ORACLESQL性能调优.doc

《ORACLESQL性能调优.doc》由会员分享，可在线阅读，更多相关《ORACLESQL性能调优.doc（13页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、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跟踪 S

2、QL 实际运行的 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必要时，可在 ORA

3、CLE 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识别 “低效运行”

4、的 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 Perf

5、ormance 的关键因素和配置：的关键因素和配置： 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的写法对效率的影 响很大，首先

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

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

8、优化器： 2.2.1ORACLE优化器的优化方式优化器的优化方式 .- ORACLE优化器的优化方式有两大类，即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方 式(Cost-Based Optimization,简称为CBO)。 A、 RBO方式：优化器在分析SQL语句时，更据数据库中表和索引等定义信息，遵循的是Oracle内部预定的一些规则。比如 我们常见的：当一个where子句中的一列有索引时去走索引而不走全表扫描。 B、 CBO方式：依词义可知,它是看语句的代价(Cost)了。基于代价的查询，数据库根据搜集的表和索引的数据的统计信息

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

10、本,Oracle强列推荐用CBO的方式。 2.2.2优化器的优化模式优化器的优化模式(Optermizer Mode) 优化模式包括Rule，Choose，First rows，All rows这四种方式，先解释一下： 1.Rule：即走基于规则的方式。 2.First_Rows：基于成本的方式。指执行计划采用最少资源尽快的返回部分结果给客户端，它将是以最快的方式返回查询 的最先的几行,从总体上减少了响应时间，对于排序分页页显示这种查询尤其适用。 3.All_Rows：基于成本的方式。当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐 量。没有统计信息则走基于规则的

11、方式。 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 级别：我们可以通过

12、在.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查看对象统计信息查看对象统计信息(obj

13、ect 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

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

15、去重新统计 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优化模式优化模式

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

17、irst_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 #

18、 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_ST

19、ATS。 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()

20、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_s

21、chema_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的

22、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 用

23、的比较少的, 就会比较好! .- 2.5小结小结 更据以上一些理论和我们 ERP 上的实际状况，我们可以得到一些建议： （1）因为在 Instance Level 我们的 optimizer_mode = choose ，所以定期运行 ANALYZE 或 dbms_stats 非常重要， 尤其是当上次统计后，数据量已发生较大变化之后。注意：统计操作是很耗资源的动作，要在系统 Loading 小的时候进行。 （2）因为 optimizer_mode 优化模式可以设定 Sessions 级别和语句级别，所以必要时可以通过改 optimizer_mode 的方 式让提高 Performance。 例

24、如，某报表的 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 F

25、ROM 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 相比

26、，效率有高有低，没有明显的差别。 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_nu

27、mber 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中能不使用就

28、不 要使用， 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_se

29、lling_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 (

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

31、.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.inv

32、entory_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.

33、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无法事前统计 出来的，所以优

34、化器也很难得出一个优化过的执行计划。 子查询在以下情况比较适合： 1.适合用于小 table。也就是说, 以大tables为base做join, 小table 做 subquery. (大 tables 之间最好用直接 join) 2.不想该 table 成为限制条件时也可以勉强使用。 3.需要取subquery 中summary 的值时可以考虑 - 因为 Group by (和 distinct)也是 SQL performance 的一大挑 战。 3.5用用table 索引（索引（index）栏位去做）栏位去做Table间的关联间的关联，可避免费时的全表扫描，可避免费时的全表扫描 如果 T

35、able 上有索引，则系统访问带索引的 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_gro