ORACLE SQL 性能调优
1
2
2.1
2.2
2.2.1
2.2.2
2.2.3
2.2.4
2.3
2.3.1
2.3.2
2.4
2.5
3
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
3.11 序言 .............................................................................................................................................................................. 2 影响SQL PERFORMANCE的关键因素和配置： ................................................................................................. 2 关于执行计划 ........................................................................................................................................................... 2 ORACLE优化器 ...................................................................................................................................................... 2 ORACLE优化器的优化方式................................................................................................................................ 2 优化器的优化模式(Optermizer Mode) ................................................................................................................. 3 Optimizer mode优化模式级别的设定： ............................................................................................................. 3 查看对象统计信息(object statistics) .................................................................................................................... 3 结合BENQ ORACLE ERP .......................................................................................................................................... 4 优化模式(Optermizer Mode) ................................................................................................................................. 4 关于 Gather .......................................................................................................................................................... 4 跟踪 SQL实际运行的COST ................................................................................................................................... 5 小结 ........................................................................................................................................................................... 5 SQL语句的TUNING经验分享： ............................................................................................................................ 6 绝大多数情况下NOT EXISTS比NOT IN 效率高 ...................................................................................................... 6 UNION ALL效率比UNION高很多 ...................................................................................................................... 6 一些很耗资源的SQL操作，在不必要的情况下不要使用 .................................................................................. 6 通常联接查询比子查询的效率要高很多 ............................................................................................................... 7 用TABLE 索引（INDEX）栏位去做TABLE间的关联，可避免费时的全表扫描 ................................................. 7 在VIEW中尽量不要使用 PACKAGE/FUNCTION 来得到栏位值， ......................................................................... 8 通过ROWID访问表................................................................................................................................................ 8 必要时，可在ORACLE STANDARD TABLE上加索引 ............................................................................................... 9 合理排列WHERE子句中的连接顺序． ............................................................................................................... 9 用WHERE子句替换HAVING子句 .............................................................................................................................. 9 关于使用索引（INDEX）的一些注意点 ...................................................................................................................... 10
3.11.1
3.11.2
3.11.3
3.11.4
3.11.5
3.11.6
3.12 ‘!=’,NOT操作将不使用索引. ............................................................................................................................ 10 ‘||’是字符连接函数. 就象其它函数那样, 停用了索引. ............................................................................... 10 相同的索引列不能互相比较,这将会启用全表扫描. ........................................................................................ 10 避免在索引列上使用计算． .............................................................................................................................. 10 基于成本的优化器(CBO)会对索引的选择性进行判断，来决定是否使用索引 ............................................. 11 Index信息的重新统计 ......................................................................................................................................... 11 识别 “低效运行”的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.
2.
3.
4. SQL语法检查：检查SQL语句拼写是否正确和词序。 SQL语义分析：核实所有的与数据字典不一致的表和列的名字。 生成执行计划：使用优化规则和数据字典中的统计表来决定最佳执行计划。 建立可执行的二进制代码：基于执行计划，Oracle生成二进制执行代码。
5. 抓取并返回需要的数据。
其中第三步生成执行计划非常关键，所谓执行计划，就是对一个查询任务，做出一份怎样去完成任务的详细方案。对于查询而言，我们提交的SQL仅仅是描述出了我们的目的，但Oracle内部怎么去得到这些数据，是由数据库DBMS来决定的。
所以执行计划产生的好坏直接影响SQL 运行的Performance。我们平时对SQL做一些Tuning，为了得到相同的数据而去尝试用不同的SQL写法，目的就是能让Oracle更据你的语句产生一个更好的执行计划，从而得到更好的效率。
2.2 ORACLE优化器
在不同的情况下，同一条SQL可能有多种执行计划。但理论上在某一时点，一定只有一种执行计划是最优的、花费时间是最少的。执行计划的工作是由优化器(Optimizer)来完成的， 那优化器是依据什么讯息去创建出最合理的执行计划？回答这个问题前先要了解一下ORACLE的优化器：
2.2.1 ORACLE优化器的优化方式
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.3 Optimizer mode优化模式级别的设定：
A、Instance级别：我们可以通过在<init>.ora文件中设定OPTIMIZER_MODE=<Mode>去选用。
B、Sessions级别：通过SQL> ALTER SESSION SET OPTIMIZER_MODE=<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_NAME NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
OE_ORDER_LINES_ALL 5344 505 5 0 0 441
可以看到数据字典中统计到的该表有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_NAME NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
OE_ORDER_LINES_ALL 16518 1530 1035 865 257 643
发现此时的信息已是最新的了。有了比较正确的统计信息，optimizer才能给出高效的执行计划。
2.3 结合BenQ Oracle ERP
优化模式(Optermizer Mode) 2.3.1
我们再来看一下我们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优化器产生更好的执行计划。
3 SQL语句的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.2 UNION 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 ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引:
SELECT ACCOUNT_NAME, 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’; /*将使用索引*/
3.11.4 避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。这是一个非常实用的规则，请务必牢记。举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
3.11.5 基于成本的优化器(CBO)会对索引的选择性进行判断，来决定是否使用索引
索引的选择性: 那就是说Table中该Field的所有值中不重复的索引键值的比率。比如， 表中共有100条记录而其中有80个不重复的索引键值，这个索引的选择性就是80/100 = 0.8 。选择性越高, 通过索引键值检索出的记录就越少。如果索引的选择性很低，检索数据就需要大量的索引范围查询操作和ROWID 访问表的操作， 也许会比全表扫描的效率还低。
3.11.6 Index信息的重新统计
有时候，陈旧的Index信息会Oracle产生的执行计划不使用Index，所以对Oracle Index 的Statistics也需要经常去做。Oracle ERP中的"Analyze All Index Column Statistics"-- Analyze All Index Column Statistics就是在做这样的动作。
3.12 识别 “低效运行”的SQL语句
用下列语句找出与我们客制有关的低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND((BUFFER_GETS-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.8
ORDER BY 4 DESC;
我们在测试中发现：有些大家Share出来的一些经验，我们在不同环境上测试这些SQL，得到的快慢结果并不一样，结论也不明确，原因可能涉及许多方面，如：数据字典中统计讯息的准确性，当时table中数据量的大小，测试时DB所处的状态等等，所以有些Performance问题不容易找到明确的Rule。
“执行计划”和sql_trace 非常重要的，一定要习惯通过跟踪“执行计划”和 Trace的Log来调整Performance。实践是检验真理的唯一标准，对复杂的View做多次Tuning必不可少。