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(数据库管理员)要根据实际状况需性能调整的部分。
我们在平时工作中用到大量的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的方式。
注意：这些统计信息起初在库内是没有的，是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的，所以很多的时候过期统计信息会令优化器做出一个错误的执行计划，因此我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。
在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。
优化模式包括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,比如:
⑵ 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;
对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笔记录
Oracle ERP11i用的optimizer_mode是choose，且Oracle强烈建议要定期运行FND_STATS。
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();
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语句都可以用其它方式重写。
尽量在 SELECT 子句里面用联接查询，少用子查询。因为子查询所得到的子Table的数据量等信息是Oracle无法事前统计出来的，所以优化器也很难得出一个优化过的执行计划。
如果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子句用到索引列，但生成的执行计划却不使用索引。这里有一些例子.
- ‘!=’,NOT操作将不使用索引.
- ‘||’是字符连接函数. 就象其它函数那样, 停用了索引.
- 相同的索引列不能互相比较,这将会启用全表扫描.
- 避免在索引列上使用计算．
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;
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;
扫一扫
998
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
