OraclePerformanceTuning入门手册V

1、ORACLE SQL Performance Tuning TOC o 1-2 h z u HYPERLINK l _Toc168817003 1序言 PAGEREF _Toc168817003 h 3 HYPERLINK l _Toc168817004 2影响SQL Performance的关键因素和配置： PAGEREF _Toc168817004 h 3 HYPERLINK l _Toc168817005 2.1关于执行计划 PAGEREF _Toc168817005 h 3 HYPERLINK l _Toc168817006 2.2ORACLE优化器 PAGEREF _Toc16881

2、7006 h 3 HYPERLINK l _Toc168817007 2.3结合BenQ Oracle ERP PAGEREF _Toc168817007 h 6 HYPERLINK l _Toc168817008 2.4跟踪 SQL实际运行的Cost PAGEREF _Toc168817008 h 7 HYPERLINK l _Toc168817009 2.5小结 PAGEREF _Toc168817009 h 7 HYPERLINK l _Toc168817010 3SQL语句的Tuning经验分享： PAGEREF _Toc168817010 h 8 HYPERLINK l _Toc16

3、8817011 3.1绝大多数情况下not exists比not in 效率高 PAGEREF _Toc168817011 h 8 HYPERLINK l _Toc168817012 3.2UNION ALL效率比UNION高很多 PAGEREF _Toc168817012 h 8 HYPERLINK l _Toc168817013 3.3一些很耗资源的SQL操作，在不必要的情况下不要使用 PAGEREF _Toc168817013 h 8 HYPERLINK l _Toc168817014 3.4通常联接查询比子查询的效率要高很多 PAGEREF _Toc168817014 h 10 HYP

4、ERLINK l _Toc168817015 3.5用table 索引（index）栏位去做Table间的关联，可避免费时的全表扫描 PAGEREF _Toc168817015 h 10 HYPERLINK l _Toc168817016 3.6在View中尽量不要使用 Package/function 来得到栏位值， PAGEREF _Toc168817016 h 11 HYPERLINK l _Toc168817017 3.7通过ROWID访问表 PAGEREF _Toc168817017 h 11 HYPERLINK l _Toc168817018 3.8必要时，可在Oracle Sta

5、ndard Table上加索引 PAGEREF _Toc168817018 h 12 HYPERLINK l _Toc168817019 3.9合理排列WHERE子句中的连接顺序 PAGEREF _Toc168817019 h 12 HYPERLINK l _Toc168817020 3.10合理排列FROM中的表名 PAGEREF _Toc168817020 h 13 HYPERLINK l _Toc168817021 3.11用WHERE子句替换HAVING子句 PAGEREF _Toc168817021 h 13 HYPERLINK l _Toc168817022 3.12关于使用索引（

6、Index）的一些注意点 PAGEREF _Toc168817022 h 13 HYPERLINK l _Toc168817023 3.13识别 “低效运行”的SQL语句 PAGEREF _Toc168817023 h 15 HYPERLINK l _Toc168817024 3.14一些特别的用法，不一定对提升Performance有效，此处仅列其使用方法，以供借鉴 PAGEREF _Toc168817024 h 15 HYPERLINK l _Toc168817025 3.15Query以外的SQL语句的Tuning PAGEREF _Toc168817025 h 18 HYPERLINK

7、 l _Toc168817026 4SQL语句的Tuning建议步骤 PAGEREF _Toc168817026 h 18 HYPERLINK l _Toc168817027 5金玉良言 PAGEREF _Toc168817027 h 19序言本文档基于版本进行了更新和补充，由PTEG(Performance Tuning Expert Group, 2006-10-5成立)小组进行重新整理，生成了该1.1版本。这份文档主要将影响SQL Performance的因素做个基础的阐述，再将大家在平时写SQL积累的经验做个总结。希望本文档可以成为一份有价值的入门级资料。影响SQL Performan

8、ce的关键因素和配置：Oracle Database上的设置对Performance的影响很大，如Shared Pool Size、Buffer Cache Size、SGA Structures、Database I/O Configuration、Rollback Segments等等，这些是DBA要更据实际状况去Tunning的部分，我们暂不详细讨论，DBA可对这块进行补充；这份文档我们将重点放在影响SQL执行效率的一些关键因素和设置上。关于执行计划我们在平时工作中用到大量的View，View中SQL的写法对performance的影响很大，首先有必要了解一条SQL语句是如何被执行的。当

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

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

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

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

13、式，先解释一下：Rule：即走基于规则的方式。First_Rows：基于成本的方式。指执行计划采用最少资源尽快的返回部分结果给客户端，它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间，对于排序分页页显示这种查询尤其适用。All_Rows：基于成本的方式。当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。Choose：这是我们应关注的，默认的情况下Oracle用的便是这种方式。指的是当一个表或索引有统计信息（指运行过analyze 命令或者使用过dbms_stats包来搜集），则走CBO的方式 （在CHOOSE模式下O

14、RACLE采用的是 FIRST_ROWS）；如果表或索引没有统计信息，那么走RBO的方式。注：Oracle ERP 11i之前的版本，默认用RULE；Oracle ERP 11i之后的版本，默认用CHOOSE。Optimizer mode优化模式级别的设定：A、Instance级别：我们可以通过在.ora文件中设定OPTIMIZER_MODE=去选用。B、Session级别：通过SQL ALTER SESSION SET OPTIMIZER_MODE=;来设定。C、语句级别，这些需要用到Hint,比如:SELECT /*+ rule */ ordh.order_number,ordl.orde

15、red_item FROM apps.oe_order_headers_all ordh, apps.oe_order_lines_all ordl WHERE ordh.header_id = ordl.header_id;查看对象统计信息(object statistics)对CBO模式，对象统计信息至关重要。如何查看对象统计信息(object statistics)？Oracle中关于表的统计信息是在数据字典中的，可以下SQL查询到，eg：SELECT table_name ,num_rows ,blocks ,empty_blocks AS empty ,avg_space ,chai

16、n_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可以看到数据字典中统计到的该表有5344笔记录，我们下SQL验证一下：SELECT COUNT (*) FROM apps.oe_order_lines_all发现返回是16518笔记录，可见这个表的统计信息是比较陈旧的，真实数据与统计到的数据有

17、较大的差别。在这种情况下，如果某个View用到此Table，且系统使用CBO的方式，则可能导致Oracle的optimizer给出效率低下的执行计划。此时可以用ANALYZE去重新统计OE_ORDER_LINES_ALL这个表，可以下SQL：ANALYZE TABLE ont.oe_order_lines_all COMPUTE STATISTICS;再次Query数据字典：TABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL1651815301035865257643发现此时的信息已是最新的了

18、。有了比较正确的统计信息，optimizer才能给出高效的执行计划。结合BenQ Oracle ERP优化模式(Optermizer Mode)我们再来看一下我们ERP的DB的设置情况：以下是从我们ERP中BQE Production 环境取到的一些设置：/disk/BQE/bqedb/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

19、 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 en

20、sure 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强烈建议

21、要定期运行FND_STATS。关于 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 Tab

22、le 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_tab

23、le_stats(); dbms_stats.gather_index_stats();跟踪 SQL实际运行的Cost执行计划是Oracle根据一些统计信息去“估计”出各个步骤所耗的Cost，与实际的执行过程所耗Cost不见得一样。实际执行过程耗的CPU、Disk IO等资源的数量可以通过sql_trace统计出来。所以Tuning SQL不仅要看“执行计划”，有时还必须结合trace的Log去分析。For example，若我要查某段程序运行过程的所有SQL的Performance情况;可以程序逻辑开始前加上sql_trace=true，结束前结束sql_trace=false即可：alte

24、r session set sql_trace=true;程序逻辑herealter session set sql_trace=false;然后去OS上去找出这个trace file，用tkprof 去转换，然后再看Log的详细内容。DEV2: /disk/DEV2/dev2db/admin/DEV2/udumptkprof dev2_ora_13148.trc log.txt附档是转出来的例子, 记得 , CPU + DISK 用的比较少的, 就会比较好!小结根据以上一些理论和我们ERP上的实际状况，我们可以得到一些建议：（1）因为在Instance Level我们的optimizer_m

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

26、情况下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 EX

27、ISTS ( SELECT 1 FROM apps.oe_order_headers_all ordh WHERE ordh.flow_status_code = CLOSED AND ordh.header_id = ordl.header_id);注：exists和 in 相比，效率有高有低，没有明显的差别。UNION ALL效率比UNION高很多（DEV2: 5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 82UNIONSELECT WGQ_ORD

28、ER, 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 _id = 93一些很耗资源的SQL操

29、作，在不必要的情况下不要使用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 ordlORDER BY ordl.creation_date;Eg 2: group by（DEV2: 9秒）SELECT SUBSTR (ordh.order_num

30、ber, 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_idGROUP BY SUBSTR (ordh.order_number, 1, 50);（DEV2: 0.5秒）SEL

31、ECT 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: 5

32、0秒）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.l

33、ine_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

34、_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也是可以考虑的, 毕竟它们的可读性很强。通常联接查询比子查询的效率要高很多尽量在 SELECT 子句里面用联接查询，少子查询。因

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

36、如果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的数据量都会很大）

37、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, mea

38、ning 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：f

39、nd_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，直接采用全表扫描。有些文档直接说：如果索引是建立在多个列上， 只有在它的第一个列(le

40、ading 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.0

41、0sec（BQC环境）这个SQL中使用了LOOKUP_TYPE=SHIP_METHOD ，这个正好是INDEX的第一位。Oracle优化器分析：执行计划如果使用INDEX，就马上找到LOOKUP_TYPE=SHIP_METHOD 的INDEX记录，COST最小，分析的结果就是使用了INDEX， performance得到提升。在View中尽量不要使用 Package/function 来得到栏位值，在view中尽量不要引用function，否则会增加一定的通讯开销。简单的判断尽量用decode，nvl，case when等实现。通过ROWID访问表 ORACLE 采用两种访问表中记录的方式:

42、a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描. b. 通过ROWID访问表 如果可以，强烈采用基于ROWID的访问方式情况以提高访问表的效率。ROWID包含了表中记录的物理位置信息，ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系， 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 必要时，可在Oracle Standard Table上加索引eg:BQC收单用到如下一个简单的SQL，耗时要1秒左右；测试后发现只要用到fl

43、ow_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_sta

44、tus_code ASC)再次运行此SQL，耗时基本为0秒。合理排列WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，那些可以过滤掉最大数量记录的条件最好写在WHERE子句的末尾。虽然对简单SQL，Oracle优化器自动会去调整顺序，但还是建议将能过滤掉最多记录的Where条件放在最后。合理排列FROM中的表名ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时

45、, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.注: 这两点可不再考虑, CBO情况下ORACLE会自动调整连接顺序用WHERE子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。 如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。 例如: 低效：SELECT ordl.ordered_item ,SUM (ordl.orde

46、red_quantity * ordl.unit_selling_price) FROM apps.oe_order_lines_all ordlGROUP 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.ord

47、ered_item;关于使用索引（Index）的一些注意点而通常情况下，使用索引比全表扫描要块几倍至几千倍，所以对索引要有比较深入的了解。某些情况下SELECT 语句中的WHERE子句用到索引列，但生成的执行计划却不不使用索引。这里有一些例子. !=,NOT操作将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中 不使用索引: SELECT account_name FROM TRANSACTION WHERE amount != 0|是字符连接函数. 就象其它函数那样, 停用了索引. 不使用索引: SELECT account_name, amount FRO

48、M TRANSACTION WHERE account_name | account_type = AMEXA使用索引: SELECT account_name, amount FROM TRANSACTION WHERE account_name = AMEX AND account_type = A相同的索引列不能互相比较,这将会启用全表扫描. 不使用索引: SELECT account_name, amount FROM TRANSACTION WHERE account_name = NVL (:acc_name, account_name)使用索引: SELECT account_n

49、ame, amount FROM TRANSACTION WHERE account_name LIKE NVL (:acc_name, %)如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好的方案. CREATE INDEX emp_i ON emp (UPPER(ename);/*建立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = BLACKSNAIL; /*将使用索引*/避免在索引列上使用计算 WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引

50、而使用全表扫描。这是一个非常实用的规则，请务必牢记。举例: 低效： SELECT FROM dept WHERE sal * 12 25000高效: SELECT FROM dept WHERE sal 25000 / 12基于成本的优化器(CBO)会对索引的选择性进行判断，来决定是否使用索引 索引的选择性: 那就是说Table中该Field的所有值中不重复的索引键值的比率。比如， 表中共有100条记录而其中有80个不重复的索引键值，这个索引的选择性就是80/100 = 0.8 。选择性越高, 通过索引键值检索出的记录就越少。如果索引的选择性很低，检索数据就需要大量的索引范围查询操作和ROWI

51、D 访问表的操作， 也许会比全表扫描的效率还低。 Index信息的重新统计有时候，陈旧的Index信息会Oracle产生的执行计划不使用Index，所以对Oracle Index 的Statistics也需要经常去做。Oracle ERP中的Analyze All Index Column Statistics- Analyze All Index Column Statistics就是在做这样的动作。分析函数Package使索引失效当你所Query的View中使用了分析函数，它会导致一段调用的Package的SQL中，Oracle对Table的统计信息失误，进行CBO分析出的执行计划走了低效

52、路径。举例：不使用索引：SELECT rcv.hawb FROM apps.xxpo_rcv_ship_h_l_v rcv WHERE rcv.sort_no = 1 AND rcv.po_no = NVL (apps.xxom_shipreport_pkg.get_dropship_po_number (7345) ,0000000 );Blocks: 1,126 Est. Rows: 17,010 of 17,010 Cost: 173使用索引：SELECT rcv.hawb FROM apps.xxpo_rcv_ship_h_l_v rcv WHERE rcv.sort_no = 1 A

53、ND rcv.po_no = NVL (, )Blocks: 1,126 Est. Rows: 1 of 17,010 Cost: 3View中有如下一列值ROW_NUMBER() OVER(PARTITION BY h.ordno ORDER BY h.rowid) AS SORT_NO访分析函数的使用导致使用这个View去查询时，若调Package，则无法走索引，它会令Oracle统计信息错误。识别 “低效运行”的SQL语句 用下列语句找出与我们客制有关的低效SQL:SELECT executions ,disk_reads ,buffer_gets ,ROUND (buffer_gets

54、 - disk_reads) / buffer_gets, 2) hit_radio ,ROUND (disk_reads / executions, 2) reads_per_run ,sql_text FROM v$sqlarea WHERE sql_text LIKE %XX% AND executions 0 AND buffer_gets 0 AND (buffer_gets - disk_reads) / buffer_gets 0.8ORDER BY 4 DESC一些特别的用法，不一定对提升Performance有效，此处仅列其使用方法，以供借鉴分析函数简介分析函数表达方法FUN

55、CTION_NAME ( )OVER ( , , )说明：1.Function Name：分析函数的名称，Oracle 有二十多个分析函数，其中一些与聚合函数同名2.OVER：分析函数的关键字，这是区分同名的分析函数与聚合函数的关键字3.Partition Clause：分区子句，作用类似于 Group By，将结果集分组，分析函数分别作用于每一组，在各组内独立计算，当切换组时，计算结果复位并重新开始计算。查询中分析函数的每个实例允许有不同的分区子句。4.Order by Clause：排序子句，排序子句指定数据在每个组（分区）内是按什么样的顺序来计算的。ORDER BY expr 5.Win

56、dowing Clause:开窗子句，指定分析函数当前可见的窗口（作用域）与 Partition Clause 的区别ROWS/RANGE UNBOUNDED/num PRECEDING-样例With Clause -样例CREATE OR REPLACE VIEW zz_test_viewAS WITH ou AS (SELECT * FROM apps.hr_operating_units) SELECT h.* FROM apps.oe_order_headers_all h ,ou WHERE _id = anization_id-样例(/*+ materiali

57、ze */）CREATE OR REPLACE VIEW zz_test_viewAS WITH ou AS (SELECT /*+ materialize */ * FROM apps.hr_operating_units) SELECT l.* FROM apps.oe_order_lines_all l ,ou WHERE _id = anization_id动态SQL-样例EXECUTE IMMEDIATE SELECT NVL( SUM( NVL( fulfilled_quantity, 0 ), 0 ) FROM apps.oe_order_lines_all

58、WHERE NVL( line_set_id, 0 ) = :p_line_set_id AND line_number = :p_line_number AND header_id = :p_header_id INTO v_shipped_quantityUSING p_line_set_id, p_line_number, p_header_id;-游标变量绑定样例(1)把游标声明成 Reference Cursor 类型，即所谓的“游标变量”，声明的时候是不需要指定 SELECT 语句的。(2)在 Open 的时候，才指定 SELECT 语句，这时候就可以套用动态 SQL 了。OPEN

59、 cur_test FOR SELECT name FROM apps.xx_test_d_sql WHERE id = :id USING v_id;Hint说明：这种方法通常不一定奏效，因为Oracle CBO执行计划生成的是不无道理的。只有特定情况下，才会强制HINT。以下举几种强制索引的方式：FULL hint 告诉ORACLE使用全表扫描的方式访问指定表.例如: SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = 7893; ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表. 通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式, 你需要知道ROIWD的值或者使用索引. 如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留在SGA中,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.例如:SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *FROM WORK; 索引hint