ORACLE SQL性能调优(11页).doc
《ORACLE SQL性能调优(11页).doc》由会员分享,可在线阅读,更多相关《ORACLE SQL性能调优(11页).doc(11页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、-ORACLE SQL性能调优-第 11 页ORACLE SQL 性能调优1序言22影响SQL PERFORMANCE的关键因素和配置:2关于执行计划2ORACLE优化器2ORACLE优化器的优化方式2优化器的优化模式(Optermizer Mode)3Optimizer mode优化模式级别的设定:3查看对象统计信息(object statistics)3结合BenQ Oracle ERP4优化模式(Optermizer Mode)4关于 Gather5跟踪 SQL实际运行的Cost5小结53SQL语句的TUNING经验分享:6绝大多数情况下not exists比not in 效率高6UNI
2、ON ALL效率比UNION高很多6一些很耗资源的SQL操作,在不必要的情况下不要使用6通常联接查询比子查询的效率要高很多7用table 索引(index)栏位去做Table间的关联,可避免费时的全表扫描7在View中尽量不要使用 Package/function 来得到栏位值,8通过ROWID访问表9必要时,可在Oracle Standard Table上加索引9合理排列WHERE子句中的连接顺序9用Where子句替换HAVING子句9关于使用索引(Index)的一些注意点10!=,NOT操作将不使用索引.10|是字符连接函数. 就象其它函数那样, 停用了索引.10相同的索引列不能互相比较,
3、这将会启用全表扫描.10避免在索引列上使用计算11基于成本的优化器(CBO)会对索引的选择性进行判断,来决定是否使用索引11Index信息的重新统计11识别 “低效运行”的SQL语句111 序言BenQ Sale Office自Oracle ERP上线后,随着资料量的日益加大,目前BQE,BQC,BQP这几个Site的Performance的问题都表现得越来越明显,Tunning得工作量也明显增加。考虑到影响Oracle SQL Performance的有很多方面,我们这次研究的Tunning先包括两个大方向:Oracle DB Configuration和SQL Statement Tuni
4、ng。这份文档主要将影响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执行效率的一些关键因素和设置上。
5、2.1 关于执行计划我们在平时工作中用到大量的View,View中SQL的写法对效率的影响很大,首先有必要了解一条SQL语句是如何被执行的。当SQL语句进入Oracle的缓存后,在该语句准备执行之前,DBMS将执行下列步骤:1. SQL语法检查:检查SQL语句拼写是否正确和词序。2. SQL语义分析:核实所有的与数据字典不一致的表和列的名字。3. 生成执行计划:使用优化规则和数据字典中的统计表来决定最佳执行计划。4. 建立可执行的二进制代码:基于执行计划,Oracle生成二进制执行代码。5. 抓取并返回需要的数据。其中第三步生成执行计划非常关键,所谓执行计划,就是对一个查询任务,做出一份怎样去
6、完成任务的详细方案。对于查询而言,我们提交的SQL仅仅是描述出了我们的目的,但Oracle内部怎么去得到这些数据,是由数据库DBMS来决定的。所以执行计划产生的好坏直接影响SQL 运行的Performance。我们平时对SQL做一些Tuning,为了得到相同的数据而去尝试用不同的SQL写法,目的就是能让Oracle更据你的语句产生一个更好的执行计划,从而得到更好的效率。2.2 ORACLE优化器在不同的情况下,同一条SQL可能有多种执行计划。但理论上在某一时点,一定只有一种执行计划是最优的、花费时间是最少的。执行计划的工作是由优化器(Optimizer)来完成的, 那优化器是依据什么讯息去创建
7、出最合理的执行计划?回答这个问题前先要了解一下ORACLE的优化器:2.2.1 ORACLE优化器的优化方式ORACLE优化器的优化方式有两大类,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。A、 RBO方式:优化器在分析SQL语句时,更据数据库中表和索引等定义信息,遵循的是Oracle内部预定的一些规则。比如我们常见的:当一个where子句中的一列有索引时去走索引而不走全表扫描。 B、 CBO方式:依词义可知,它是看语句的代价(Cost)了。基于代价的查询,数据库根据搜
8、集的表和索引的数据的统计信息(统计信息通过analyze 命令或者使用dbms_stats包来搜集)综合来决定选取一个数据库认为最优的执行计划(实际上不一定最优) 。统计信息给出表的大小 、有多少行、每行的长度等信息。注意:这些统计信息起初在库内是没有的,是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的,所以很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。在Ora
9、cle8及以后的版本,Oracle强列推荐用CBO的方式。2.2.2 优化器的优化模式(Optermizer Mode)优化模式包括Rule,Choose,First rows,All rows这四种方式,先解释一下:1. Rule:即走基于规则的方式。2. First_Rows:基于成本的方式。指执行计划采用最少资源尽快的返回部分结果给客户端,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间,对于排序分页页显示这种查询尤其适用。3. All_Rows:基于成本的方式。当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式
10、。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级别:我们可以通过在.ora文件中设定OPTIMIZER_MODE=去选用。B、Ses
11、sions级别:通过SQL ALTER SESSION SET OPTIMIZER_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
12、 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_ALLTABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL534450550044
13、1可以看到数据字典中统计到的该表有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
14、;再次Query数据字典:TABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL1651815301035865257643发现此时的信息已是最新的了。有了比较正确的统计信息,optimizer才能给出高效的执行计划。2.3 结合BenQ Oracle ERP2.3.1 优化模式(Optermizer Mode)我们再来看一下我们ERP的DB的设置情况:以下是从我们ERP中BQE Production 环境取到的一些设置:# optimizer_mode# Prior to 11i, optimi
15、zer_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 dict
16、ionary 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
17、 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 Stat
18、istics-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,以便能让Ora
19、cle统计到最新的数据库状况: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不仅要看“执行计划”,有时还必须结合tra
20、ce的Log去分析。For example,若我要查某段程序运行过程的所有SQL的Performance情况;可以程序逻辑开始前加上sql_trace=true,结束前结束sql_trace=false即可:alter session set sql_trace=true;程序逻辑herealter session set sql_trace=false;然后去OS上去找出这个trace file,用tkprof 去转换,然后再看Log的详细内容。DEV2附档是转出来的例子, 记得 , CPU + DISK 用的比较少的, 就会比较好!2.5 小结更据以上一些理论和我们ERP上的实际状况,我们
21、可以得到一些建议:(1)因为在Instance Level我们的optimizer_mode = choose ,所以定期运行ANALYZE 或dbms_stats非常重要,尤其是当上次统计后,数据量已发生较大变化之后。注意:统计操作是很耗资源的动作,要在系统Loading小的时候进行。(2)因为optimizer_mode优化模式可以设定Sessions级别和语句级别,所以必要时可以通过改optimizer_mode的方式让提高Performance。例如,某报表的View是EIS类型的,需要一次抓得所有资料,则可以使用Hint的方式使该SQL的optimizer_mode= ALL_ROW
22、S,让Oracle优化器产生更好的执行计划。3 SQL语句的Tuning经验分享:3.1 绝大多数情况下not exists比not in 效率高低效:(DEV2: 5秒) 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秒) FROM apps.oe_order_lines_all ordl WHERE not E
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE SQL性能调优11页 SQL 性能 11
限制150内