Oracle数据库的配置和SQL语句的优化 /*+ rule */ & INSERT/*+append*/INTO

Oracle数据库的配置和SQL语句的优化 。

  INSERT/*+append*/INTO t_servicexx(serviceid,clientid,prod_id,serviceno,addrid,
                                      connectno,fgsid,gl_serviceid,up_serviceid,servlev,
                                      dialacctname,ibss_id,gl_serviceid_num,phone_nbr,orgid)

2         Oracle数据库的配置

2.1 影响SQL效率的关键因素和配置：

       Oracle数据库上的设置对其性能的影响很大，如Shared Pool Size、Buffer Cache Size、SGA Structures、Database I/O Configuration、Rollback Segments等等，这些是DBA(数据库管理员)要根据实际状况需性能调整的部分。

 

2.2       执行计划

       我们在平时工作中用到大量的View，View中SQL的写法对效率的影响很大，首先有必要了解一条SQL语句是如何被执行的。当SQL语句进入Oracle的缓存后，在该语句准备执行之前，DBMS将执行下列步骤：

      ⑴  SQL语法检查：检查SQL语句拼写是否正确和词序。

      ⑵ SQL语义分析：核实所有的与数据字典不一致的表和列的名字。

      ⑶ 生成执行计划：使用优化规则和数据字典中的统计表来决定最佳执行计划。

      ⑷ 建立可执行的二进制代码：基于执行计划，Oracle生成二进制执行代码。

      ⑸ 抓取并返回需要的数据。

      其中第三步生成执行计划非常关键，所谓执行计划，就是对一个查询任务，做出一份怎样去完成任务的详细方案。对于查询而言，我们提交的SQL仅仅是描述出了我们的目的，但Oracle内部怎么去得到这些数据，是由数据库DBMS来决定的。

所以执行计划产生的好坏直接影响SQL运行的效能。

 

2.3       ORACLE优化器

      在不同的情况下，同一条SQL可能有多种执行计划。但理论上在某一时点，一定只有一种执行计划是最优的、花费时间是最少的。执行计划的工作是由优化器(Optimizer)来完成的，我们先要了解一下Oracle的优化器：

 

2.3.1                   ORACLE优化器的优化方式

      ORACLE优化器的优化方式有两大类，即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

      ⑴ RBO方式：优化器在分析SQL语句时，根据数据库中表和索引等定义信息，遵循的是Oracle内部预定的一些规则。比如我们常见的：当一个where子句中的一列有索引时去走索引而不走全表扫描。

      ⑵ CBO方式：依词义可知,它是看语句的代价(Cost)了。基于代价的查询，数据库根据搜集的表和索引的数据的统计信息（统计信息通过analyze 命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划。统计信息给出表的大小 、有多少行、每行的长度等信息。
      注意：这些统计信息起初在库内是没有的，是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的，所以很多的时候过期统计信息会令优化器做出一个错误的执行计划，因此我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。
在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。

 

2.3.2                   优化器的优化模式(Optimizer Mode)

       优化模式包括Rule，Choose，First rows，All rows这四种方式，先解释一下：

       ⑴ Rule：即走基于规则的方式。

       ⑵ First_Rows：基于成本的方式。指执行计划采用最少资源尽快的返回部分结果给客户端，它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间，对于排序分页页显示这种查询尤其适用。

       ⑶ All_Rows：基于成本的方式。当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

       ⑷ Choose：这是我们应关注的，默认的情况下Oracle用的便是这种方式。指的是当一个表或索引有统计信息（指运行过analyze 命令或者使用过dbms_stats包来搜集），则走CBO的方式 （在CHOOSE模式下ORACLE采用的是 FIRST_ROWS）；如果表或索引没统计信息，那么走RBO的方式。

       注：Oracle ERP 11i之前的版本，默认用RULE；Oracle ERP 11i之后的版本，默认用CHOOSE。

 

2.3.3                   Optimizer mode优化模式级别的设定：

       ⑴ Instance级别：我们可以通过在<init>.ora文件中设定OPTIMIZER_MODE=<Mode>去选用。
       ⑵ Sessions级别：通过SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;来设定。  
        ⑶ 语句级别，这些需要用到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.3.4                   查看对象统计信息

       对CBO模式，对象统计信息至关重要。我们可以用如下SQL查询到：

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	4344	505	5	0	0	441

可以看到数据字典中统计到的该表有5344笔记录

2.4      结合Oracle ERP

2.4.1                   优化模式

      Oracle ERP11i用的optimizer_mode是choose，且Oracle强烈建议要定期运行FND_STATS。

2.4.2                   关于 Gather

       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.5      跟踪 SQL实际运行的Cost

       执行计划是Oracle根据一些统计信息去“估计”出各个步骤所耗的Cost，与实际的执行过程所耗Cost不见得一样。实际执行过程耗的CPU、Disk IO等资源的数量可以通过sql_trace统计出来。所以Tuning SQL不仅要看“执行计划”，有时还必须结合trace的Log去分析。

3         SQL语句的优化：

          SQL语句的优化是需要不断尝试的，在此把自己的经验分享一二。

3.1      绝大多数情况下not exists比not in 效率高

3.2      UNION ALL效率比UNION高很多

3.3      一些很耗资源的SQL操作，在不必要的情况下不要使用

          Select *，Order by，Group by，Distinct, UNION, MINUS，INTERSECT 操作是相当耗时的，在View中能不使用就不要使用。 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其它方式重写。

3.4      通常联接查询比子查询的效率要高很多

          尽量在 SELECT 子句里面用联接查询，少用子查询。因为子查询所得到的子Table的数据量等信息是Oracle无法事前统计出来的，所以优化器也很难得出一个优化过的执行计划。

3.5      用表索引栏位去做表间的关联，可避免费时的全表扫描

          如果Table上有索引，则系统访问带索引的Field时，可通过访问索引中的栏位来快速获得相对应记录的ROWID，而通常情况下，使用索引比全表扫描要块几倍乃至几千倍。

           Oracle ERP中几乎所有的Table都设有索引，尽量以索引中的栏位做 join，避免用我们认为值是唯一的栏位去串

3.6      在View中尽量不要使用 Package/function 来得到栏位值，

在view中尽量不要引用function，否则会增加一定的通讯开销。简单的判断尽量用decode，nvl，case when等实现。

3.7      通过ROWID访问表 

  ORACLE 采用两种访问表中记录的方式:

    ⑴ 全表扫描 

        全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

    ⑵ 通过ROWID访问表

如果可以，强烈采用基于ROWID的访问方式情况以提高访问表的效率。ROWID包含了表中记录的物理位置信息，ORACLE采用索引实现了数据和存放数据的物理位置之间的联系， 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

 

3.8      必要时可在Oracle原表上加索引

3.9      合理排列WHERE子句中的连接顺序．

        ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，那些可以过滤掉最大数量记录的条件最好写在WHERE子句的末尾。虽然对简单SQL，Oracle优化器自动会去调整顺序，但还是建议将能过滤掉最多记录的Where条件放在最后。

 

3.10  用Where子句替换HAVING子句

        避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。 如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

3.11 关于使用索引（Index）的一些注意点

         而通常情况下，使用索引比全表扫描要块几倍至几千倍。某些情况下SELECT 语句中的WHERE子句用到索引列，但生成的执行计划却不使用索引。这里有一些例子.

1.          ‘!=’,NOT操作将不使用索引.
2.          ‘||’是字符连接函数. 就象其它函数那样, 停用了索引.
3.           相同的索引列不能互相比较,这将会启用全表扫描.
4.          避免在索引列上使用计算．

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;

评论

1 楼 yarkingJ 2011-08-19  

不错，谢谢分享！