ORACLE SQL Performance Tuning

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

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优化器产生更好的执行计划