oraclesql语句级与策略级优化小结

1、尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS弋替。用 表连接替换EXISTS通常来说，采用表连接的方式比EXISTS更有效率，RBOfr适用，因为前者需要 FILTER, nested loops semi 是 nested loop连接的变种，又叫半连接。原理与 nl相同，通常用于in,exist 操 作，这种操作join时候，通常查找到一条纪录就可以了，所以用 semi 表示。与semi相似的有一种叫anti ,反连接，一般用于not in,not exists , 也有 nest loop anti 和 hash anti 两种。不用NOT IN操作符，可以用NOT E

2、XISTS者外连接+替代。Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再 执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第 一个匹配项。NOTEXISTS比NOT1N效率稍高。但具体在选择IN或EXIST 操作时，要根据主子表数据量大小来具体考虑 。不用“ <>”或者“ ！=”操作符。对不等于操作符的处理会造成全表扫描， 可以用“<" or、”代替。Where子句中出现IS NULL4E者IS NOTNULLM, Oracle会停止使用索引 而执行全表扫描。可以考虑在设计表时，对索引列设置为 NOT NULL这 样就可以用

3、其他操作来取代判断 NULL的操作。当通配符" %或者” "作为查询字符串的第一个字符时，索引不会被使 用。对于有连接的列“ | "，最后一个连接列索引会无效。 尽量避免连接，可 以分开连接或者使用不作用在列上的函数替代。如果索引不是基于函数的，那么当在 Where子句中对索引列使用函数时， 索引不再起作用。Where子句中避免在索引列上使用计算, 否则将导致索引失效而进行全表 扫描。对数据类型不同的列进行比较时，会使索引失效。UNION®作符会对结果进行筛选，消除重复，数据量大的情况下可能会引 起磁盘排序。如果不需要删除重复记录，应该使用 UNION

4、ALLOrder By语句中的非索引列会降低性能，可以通过添加索引的方式处理。 严格控制在Order By语句中使用表达式。不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用 SGA 共享池，防止相同的Sql语句被多次分析。多利用内部函数提高Sql效率。当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。 这样可以减少解析时间。用TRUNCATE代DELETE开发中不准使用。当删除表中的记录时，在通常情况下，回滚段(rollbacksegments )用来存放可以被恢复的信息。如果你没有COMMI由务，ORACLE将数据恢复到删除之前的状态(准确地说是恢复到执行删除命

5、 令之前的状况)。而当运用TRUNCATE,回滚段不再存放任何可被恢复的信息。 当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会 很短。SELECTF旬中避免使用'* '当你想在SELECT?句中列出所有的COLUMN,使用动态SQL 列引用'*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLEE解析的过程中， 会将'*'依次转换成所有的列名，这 个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。Count(*) 10G 中例外用Where子句替换HAVING?旬避免使用HAVING?句，HAVI

6、NG只会在检索出所有记录之后才 对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE 子旬限制记录的数目，那就能减少这方面的开销。需要注意的是，随着Oracle的升级，查询优化器会自动对 Sql语句进行优化，某些限制可能在新版本的 Oracle下不再是问题。尤其是采用 CBO(Cost-Based Optimization ,基于代价的优化方式)时。.跟踪和分析系统及SESSIO酸的SQL 跟踪SQL语句SQ山ace工具收集正在执行的SQLB性能状态数据并记录到一个跟踪文件 中.这个跟踪文件提供了许多有用的信息，例如解析次数.执行次数,CPU使用时 问等.这些数据将可以用来优化

7、你的系统.设置SQL TRACES会话级别：有效ALTER SESSION SET SQL_TRACE TRUE设置SQL TRACES整个数据库有效仿，你必须将SQL_TRACE数在中设为 TRUE, USER_DUMP_DEST说明了生成跟踪文件的目录 跟踪会话和系统跟踪跟踪自己的会话或者是别人的会话跟踪自己的会话很简单Alter session set sql_trace true|false orexec (TRUE);如果跟踪别人的会话，需要调用一个包exec (sid,serial#,true|false)或 exec '') 停止 TRACE(sid,serial

8、#,10046,0,'')跟踪的信息在user_dump_dest目录下可以找到可以通过Tkprof来解析跟踪文件，如Tkprof 原文件 目标文件 sys=n sort = exeela设置整个数据库系统跟踪其实文档上的 alter system set sql_trace=true是不成功的但是可以通过设置事件来完成这个工作，作用相等alter system set events'10046 trace name context forever,level 1'如果关闭跟踪，可以用如下语句alter system set events'10046 t

9、race name context off;其中的level 1与上面的8都是跟踪级别level 0:停止level 1 :跟踪 SQL语句,等于 sql_trace=truelevel 4 :包括变量的详细信息level 8 :包括等待事件level 12 :包括绑定变量与等待事件eg:alter system set max_dump_file_size=unlimited;ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'ALTER SESSION SET EVENTS '94

10、2 trace name errorstack level 10'（对SQL TRAC的用法也不够准确，设置SQL TRACET先要在中设定TIMED_STATISTICS,这样才能得到那些重要的时间状态.生成的trace文件是 不可读的，所以要用TKPROF具对其进行转换，TKPROFt许多执行参数.大家可 以参考ORACL手册来了解具体的配置.） 分析SQL语句用EXPLAIN PLAN分析SQL语句EXPLAIN PLAN是一个很女？的分析SQL语句的工具，它甚至可以在不执行SQL 的情况下分析语句.通过分析，我们就可以知道ORACL叫怎么样连接表，使用什 么方式扫描表（索引扫描

11、或全表扫描）以及使用到的索引名称.你需要按照从里到外，从上到下的次序解读分析的结果.EXPLAIN PLAN分析 的结果是用缩进的格式排列的，最内部的操作将被最先解读，如果两个操作处 于同一层中，带有最小操作号的将被首先执行.NESTED LOOP少数不按照上述规则处理的操作，正确的执行路径是检查对 NESTED LOOP供数据的操作，其中操作号最小的将被最先处理.通过实践，感到还是用SQLPLUSP的SET TRAC或能比较方便.举例：SQL> list1 SELECT *2 FROM dept, emp3* WHERE =SQL> set autotrace traceonly

12、 /*traceonly可以不显示执行结果 */SQL> /14 rows selected.Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF ''''EMP''''3 1 TABLE ACCESS (BY INDEX ROWID) OF ''''DEPT''''4 3 INDEX (UNIQUE SCAN) OF '&#

13、39;''PK_DEPT'''' (UNIQUE)Statistics0 recursive calls2 db block gets30 consistent gets0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通过以

14、上分析，可以得出实际的执行步骤是：1. TABLE ACCESS (FULL) OF ”“EMP”2. INDEX (UNIQUE SCAN) OF ”“PK_DEPr”'(UNIQUE)3. TABLE ACCESS (BY INDEX ROWID) OF ''''DEPT''''4. NESTED LOOPS (JOINING 1 AND 3)注：目前许多第三方的工具如 TOA前ORACL本身提供白工具如 OMS勺SQL Analyze都提供了极其方便的EXPLAINPLANT具.也许喜欢图形化界面的朋友们 可以选用它

15、们.10G在同一个SESSION中执行以下两句：EXPLAIN PLAN FOR SELECT * FROM C_CONS WHERE CONS_NO=:A SELECT from dual;三.Oracle优化器Oracle优化器(Optimizer )是Oracle在执行SQ之前分析语句的工具。Oracle的优化器有两种优化方式：基于规则的(RBO和基于彳t价的(CBO。RBO优化器遵循Oracle内部预定的规则。CBO依据语句执行的代价，主要指对 CPUffi内存的占用。优化器在判断 是否使用CBOM,要参照表和索引的统计信息。统计信息要在对表做 analyze后才会有。Oracle8及

16、以后版本，推荐用CBOTj式。Oracle优化器的优化模式主要有四种：Rule：基于规则;Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBQT式；如果没有统计信息，相应列有索引，则使用RBOT式。First rows :与Choose类似。不同的是如果表有统计信息，它将以最快 的方式返回查询的前几行，以获得最佳响应时间。All rows :即完全基于Cost的模式。当一个表有统计信息时，以最快方 式返回表所有行，以获得最大吞吐量。没有统计信息则使用 RBOT式。设定优化模式Instance 级别：在 init<SID>.ora 文件中设定 OPTIMIZE

17、R_MODEE=MSession 级另通过 SQL> ALTER SESSION SET OPTIMIZER_M语句级别：通过SQL>SELECT*+ALL+_ROWS7 ；来设定。可用的HINT 包括/*+ALL_ROWS*/ /*+FIRST_ROWS*/ /*+CHOOSE*/ /*+RULE7 等。统计表信息 要注意的是，如果表有统计信息，则可能造成语句不走索引的结果。可以用SQL>ANALYZE TABLE table_name DELETE STATISTICS;除统计信息。对列和索引更新统计信息的SQLSQL> ANALYZE TABLE table_na

18、me COMPUTE STATISTICS;SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;10g:DBMS_STATS.四.使用HINTOracle使用的hints 调整机制一直很复杂， Oracle Technical Network 对 使用hints调整Oracle SQL的过程有很好的全面评述。根据对10g数据库的介绍，可使用更多新的optimizer hints来控制优化行为。现在让我们迅速了解一 下这些强大的新hints:Oracle使用的hints调整机制一直很复杂， Oracle Technical Network 对

19、 使用hints调整Oracle SQL的过程有很好的全面评述。根据对 10g数据库的介 绍，可使用更多新的optimizer hints来控制优化行为。现在让我们迅速了解一 下这些强大的新hints:spread_min_analysis使用这一 hint ,你可以忽略一些关于如详细的关系依赖图分析等电子表格 的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格 访问结构并限制修订规则等，得到了继续使用。由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我 们减少由此产生的数以百小时计的编译时间。例如:SELECT /*+ SPREAD_MIN_ANALYSI

20、S */ .spread_no_analysis通过这一 hint ,可以使无电子表格分析成为可能。同样，使用这一 hint可 以忽略修订规则和过滤产生。如果存在一电子表格分析，编译时间可以被减少到 最低程度。例如:SELECT /*+ SPREAD_NO_ANALYSIS */ .use_nl_with_index这项hint使CBCffl过嵌套循环把特定的表格加入到另一原始行。只有在以 下情况中，它才使用特定表格作为内部表格：如果没有指定标签，CB或、须可以 使用一些标签，且这些标签至少有一个作为索引键值加入判断；反之，CBC®须 能够使用至少有一个作为索引键值加入判断的标签。例

21、如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ .典型例子：电费发行中的一句话。INSERT INTO a_rcvbl_pl_flow_tmp (rcvbl_pl_id, pl_amt, item_code, acct_no, org_no, rcvbl_ym, rcved_amt, rcvbl_amt_id)SELECT /*+use_hash(b c) index(a CONSPRC_PA_fK)*/ rcvblid, SUM pl_amt, pl_code, v_acctno acct_no, org_no, ym, 0 rcved_a

22、mt, FROM e_pl_amt a, e_cons_prc_amt b, a_rcvbl_flow_tmp c WHERE = in_org_no AND=in_ymAND=in_org_noAND=in_ymAND=in_app_noAND=AND=AND=GROUPBY ,;CARDINALITY此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义 表格，基数是由整个查询所返回的总行数。例如：SELECT /*+ CARDINALITY ( tablespec card ) */典型例子：临时表的使用：SELECR*, + owe_amt FROM SELECTCOUN

23、TI) row_count, COUNTons_count, SUM- rcvbl_owe, SUIMpenaltyFROM_rcvbl_flow a, c_cons bWHERE=AND=AND IN ( SELECT/*+ CARDINALITY(x 1) +*/ *FROMTABLEv_orgnolist) x WHERiBwnum >= 0)AND BETWEEiN_rcvblymbgn ANDn_rcvblymend AND IN ( '01' , '02') AND LIKE in_paymode | '%' AND LIKE

24、in_amttype |'%'ANDnvl, '00' ) LIKE in_conssortcode |'%'AND LIKE in_electypecode |'%'ANDnvl, '0' ) LIKE in_periodnum |'%'ANDrcvbl_amt - rcved_amt >= v_compareamtbgn ANDrcvbl_amt - rcved_amt <= v_compareamtend AND LIKE in_consno |'%'AND <

25、;= in_showtypeGROUFBYORDE用Yrcvbl_ym DESCxno_use_nlHint no_use_nl使CBO行循环嵌套，通过把指定表格作为内部表格，把 每个指定表格连接到另一原始行。 通过这一 hint ,只有hash join和sort-merge joins会为指定表格所考虑。例如:SELECT /*+ NO_USE_NL ( employees ) */.no_use_merge此hint使CBOffi过把指定表格作为内部表格的方式，拒绝sort-merge把每 个指定表格加入到另一原始行。例如:SELECT /*+ NO_USE_MERGE ( employ

26、ees dept ) */ .no_use_hash此hint使CBOffi过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行。例如:SELECT /*+ NO_USE_HASH ( employees dept ) */ .no_index_ffs此hint使CBO巨绝对指定表格白指定标签进行 fast full-index scan 。Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */no_index_ss此hint使CBO巨绝对指定表格白指定标签进行 skip scan。Syntax: /*+ NO_I

27、NDEX_SS (tablespecindexspec ) */index_ss此hint明确地为指定表格选择index skip scan。如果语句使用index range scan, Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中， 其结果为对每个部分内部的升序排列。Syntax: /*+ INDEX_SS (tablespecindexspec) */cpu_costing此hint为SQL语句才T开CPU costing。这是优化器的默认评估模式。优化 器评估当执行给定查询时，数据库需要运行的IO操作数、IO操作种类、以及CPU 周期数。Syntax: /*+

28、CPU_COSTING (tablespecindexspec) */no_cpu_costing此hint为SQL语句关闭CPU costing。然后CBO£用IO cost模式，此模 式忽略CPUS费，仅测量在single-block reads中的所有指标。Syntax: /*+ NO_CPU_COSTING */随着Oracle优化器越来越成熟，Oracle专家必须不断增加自己对调整 SQL 语句的工具储备。当然，讨论所有复杂的Oracle10g SQL新hints远远超出了本文的范围，你可以从 Mike Ault 的新书 Oracle Database 10g New Fe

29、atures 中 获得关于Oracle10g的更多信息。三.如何监控索引的使用？研究发现，oracle数据库使用的索引不会超过总数的 25%或者不易他们期 望被使用的方式使用。通过 监控数据库索引的使用，释放那些未被使用的索引， 从而节省维护索引的开销，优化性能。(用此理论基础测试各个数据库的optimizer_index_cost_adj系统参数值)oracle9i中如何确定索引的使用情况在oracle9i中，情况会简单得多，因为有一个新得字典视图V$SQL_PLAN储了实际计划，这些计划用于执行共享SQL区中得语句。V$SQL_PLAN1图很类彳以与计戈U表，但 V$SQL_PLAN用AD

30、DRESS HASH_VALUE来识另语句。下面 的SQL®示了在一个oracle9i数据库中出现在共享SQL区中语句使用的所有索 引select object_owner, object_name, options, count(*)from v$sql_planwhere operation='INDEX'and object_owner!='SYS'group by object_owner, object_name, operation, optionsorder by count（*） desc;所有基于共享 SQL 区中的信息来识别索引使用

31、情况的方法， 都可能会收集到不完整的信息。共享SQL区是一个动态结构，除非能对它进行足够频繁的采 样，否则在有关索引使用的情况的信息被收集之前，SQL语句可 能就已经（因为老化 ） 被移出缓存了。 oracle9i 提供了解决这个问题的方案，即它为 alter index 提供了一个monitoring usage 子句。当启用 monitoring usage 时， oralce 记录简单 的 yes 或 no 值，以指出在监控间隔 期间某个索引是否被使用。 SQL：select index_name,monitoring,used,start_monitoring,end_monitori

32、ng from v$object_usage;alter index test_pk monitoring usage;alter index test_pk nomonitoring usage;五其它共享SQL语句Library cache共享的语句必须满足三个条件：A、 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同 （ 连空格个数都一样） 。B、 两个语句所指的对象必须完全相同：C、 两 个 SQL 语 句 中 必 须 使 用 相 同 的 名 字 的 绑 定 变 量 （bindvariables)BIND变量对执行计划的影响执行计划分析PARTITION RANGE SINGLE/INLIST/INTE/iteration/allINDEX SCA：N快速全局扫描在后就可以使用快速全局扫描 (Fast