ORACLE SQL语句级与策略级优化小结

一、基本的Sql编写注意事项 尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。 用表连接替换EXISTS，通常来说 ， 采用表连接的方式比EXISTS更有效率，RBO中适用，因为前者需要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 EXISTS或者外连接+替代。 Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。 不用“”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“”代替。 Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。 当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。 对于有连接的列“|”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。 如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。 Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 对数据类型不同的列进行比较时，会使索引失效。 UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。 Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。 不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。 多利用内部函数提高Sql效率。 当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。 用TRUNCATE替代DELETE，开发中不准使用。 当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。 而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。 SELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 * 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将* 依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。Count(*) 10G 中例外 用Where子句替换HAVING子句 避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。 需要注意的是，随着Oracle的升级，查询优化器会自动对Sql语句进行优化，某些限制可能在新版本的Oracle下不再是问题。尤其是采用CBO（Cost-Based Optimization，基于代价的优化方式）时。二跟踪和分析系统及SESSION级的SQL：跟踪SQL语句SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.设置SQL TRACE在会话级别: 有效 ALTER SESSION SET SQL_TRACE TRUE设置SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录跟踪会话和系统跟踪 跟踪自己的会话或者是别人的会话 跟踪自己的会话很简单 Alter session set sql_trace true|false or exec dbms_session.set_sql_trace(TRUE); 如果跟踪别人的会话，需要调用一个包 exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)或exec sys.dbms_system.set_ev(sid,serial#,10046,12,)停止TRACE(sid,serial#,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 trace name context off; 其中的level 1与上面的8都是跟踪级别 level 0: 停止level 1：跟踪SQL语句，等于sql_trace=true level 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 942 trace name errorstack level 10;(对SQL TRACE的用法也不够准确, 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 大家可以参考ORACLE手册来了解具体的配置. )分析SQL语句 用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便.举例:SQL list 1 SELECT * 2 FROM dept, emp 3* WHERE emp.deptno = dept.deptnoSQL set autotrace traceonly /*traceonly 可以不显示执行结果*/SQL /14 rows selected.Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)Statistics- 0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed通过以上分析,可以得出实际的执行步骤是:1. TABLE ACCESS (FULL) OF EMP 2. INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)3. TABLE ACCESS (BY INDEX ROWID) OF DEPT4. NESTED LOOPS (JOINING 1 AND 3) 注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.10G：在同一个SESSION 中执行以下两句：EXPLAIN PLAN FOR SELECT * FROM C_CONS WHERE CONS_NO=:ASELECT dbms_xplan.display from dual;三Oracle优化器Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具。Oracle的优化器有两种优化方式：基于规则的（RBO）和基于代价的（CBO）。 RBO：优化器遵循Oracle内部预定的规则。 CBO：依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。Oracle8及以后版本，推荐用CBO方式。 Oracle优化器的优化模式主要有四种： Rule：基于规则； Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。 First rows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。 All rows：即完全基于Cost的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。 设定优化模式 Instance级别：在init.ora文件中设定OPTIMIZER_MODE； Session级别：通过SQL ALTER SESSION SET OPTIMIZER_MODE=;来设定。 语句级别：通过SQL SELECT /*+ALL+_ROWS*/ ;来设定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等。统计表信息 要注意的是，如果表有统计信息，则可能造成语句不走索引的结果。可以用SQLANALYZE TABLE table_name DELETE STATISTICS; 删除统计信息。对列和索引更新统计信息的SQL：SQL ANALYZE TABLE table_name 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对使用hints调整Oracle SQL的过程有很好的全面评述。根据对10g数据库的介绍，可使用更多新的optimizer hints来控制优化行为。现在让我们迅速了解一下这些强大的新hints:spread_min_analysis使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。例如:SELECT /*+ SPREAD_MIN_ANALYSIS */ .spread_no_analysis通过这一hint，可以使无电子表格分析成为可能。同样，使用这一hint可以忽略修订规则和过滤产生。如果存在一电子表格分析，编译时间可以被减少到最低程度。例如:SELECT /*+ SPREAD_NO_ANALYSIS */ .use_nl_with_index这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格:如果没有指定标签，CBO必须可以使用一些标签，且这些标签至少有一个作为索引键值加入判断;反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。例如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ .典型例子：电费发行中的一句话。INSERTINTOa_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(bc)index(aCONSPRC_PA_FK)*/pkg_sp_seq.f_a_rcvbl_pl_flow_rcvblidrcvblid,SUM(a.pl_amt)pl_amt,a.pl_codepl_code,v_acctnoacct_no,_noorg_no,b.ymym,0rcved_amt,c.rcvbl_amt_idFROMe_pl_amta,e_cons_prc_amtb,a_rcvbl_flow_tmpcWHERE_no=in_org_noANDa.ym=in_ymAND_no=in_org_noANDb.ym=in_ymANDb.app_code=in_app_noANDa.prc_amt_id=b.prc_amt_idAND_no=_noANDc.calc_id=b.calc_idGROUPBYa.pl_code,_no,b.ym,c.rcvbl_amt_id;CARDINALITY此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格，基数是由整个查询所返回的总行数。例如:SELECT /*+ CARDINALITY ( tablespec card ) */典型例子：临时表的使用：SELECT x.*, x.rcvbl_owe + x.penalty owe_amt FROM (SELECT COUNT(1) row_count, COUNT(a.cons_no) cons_count, a.rcvbl_ym, SUM(a.rcvbl_amt - a.rcved_amt) rcvbl_owe, SUM(pkg_ca_common.f_calcpenalty(a.rcvbl_amt_id) penalty FROM a_rcvbl_flow a, c_cons b WHERE a.cons_no = b.cons_no AND _no = _no AND _no IN (SELECT /*+ CARDINALITY(x 1) +*/ * FROM TABLE(v_orgnolist) x WHERE rownum = 0) AND a.rcvbl_ym BETWEEN in_rcvblymbgn AND in_rcvblymend AND a.settle_flag IN (01, 02) AND a.pay_mode LIKE in_paymode | % AND a.amt_type LIKE in_amttype | % AND nvl(b.cons_sort_code, 00) LIKE in_conssortcode | % AND b.elec_type_code LIKE in_electypecode | % AND nvl(a.period_num, 0) LIKE in_periodnum | % AND rcvbl_amt - rcved_amt = v_compareamtbgn AND rcvbl_amt - rcved_amt = v_compareamtend AND a.cons_no LIKE in_consno | % AND a.status_code = in_showtype GROUP BY a.rcvbl_ym ORDER BY rcvbl_ym DESC) xno_use_nlHint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。例如:SELECT /*+ NO_USE_NL ( employees ) */ .no_use_merge此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。例如:SELECT /*+ NO_USE_MERGE ( employees dept ) */ .no_use_hash此hint使CBO通过把指定表格作为内部表格的方式，拒绝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_INDEX_SS (tablespecindexspec ) */index_ss此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。Syntax: /*+ INDEX_SS (tablespecindexspec) */cpu_costing此hint为SQL语句打开CPU costing。这是优化器的默认评估模式。优化器评估当执行给定查询时，数据库需要运行的IO操作数、IO操作种类、以及CPU周期数。Syntax: /*+ CPU_COSTING (tablespecindexspec) */no_cpu_costing此hint为SQL语句关闭CPU costing。然后CBO使用IO cost模式，此模式忽略CPU花费，仅测量在single-block reads中的所有指标。Syntax: /*+ NO_CPU_COSTING */随着Oracle优化器越来越成熟，Oracle专家必须不断增加自己对调整SQL语句的工具储备。当然，讨论所有复杂的Oracle10g SQL新hints远远超出了本文的范围，你可以从Mike Ault的新书Oracle Database 10g New Features中获得关于Oracle10g的更多信息。三如何监控索引的使用？研究发现，oracle数据库使用的索引不会超过总数的25%，或者不易他们期望被使用的方式使用。通过 监控数据库索引的使用，释放那些未被使用的索引，从而节省维护索引的开销，优化性能。(用此理论基础测试各个数据库的optimizer_index_cost_adj系统参数值)oracle9i中如何确定索引的使用情况在oracle9i中，情况会简单得多，因为有一个新得字典视图V$SQL_PLAN存储了实际计划，这些计划用于执行共享SQL区中得语句。V$SQL_PLAN视图很类似与计划表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 来识别语句。下面的SQL显示了在一个oracle9i数据库中出现在共享SQL区中语句使用的所有索引select object_owner, object_name, options, count(*)from v$sql_planwhereoperation=INDEXandobject_owner!=SYSgroupbyobject_owner, object_name, operation, optionsorderby count(*) desc;所有基于共享SQL区中的信息来识别索引使用情况的方法， 都可能会收集到不完整的信息。共享SQL区是一个动态结构，除非能对它进行足够频繁的采样， 否则在有关索引使用的情况的信息被收集之前，SQL语句可 能就已经(因为老化)被移出缓存了。oracle9i提供了解决这个问题的方案，即它为alter index提供了一个monitoring usage子句。当启用monitoring usage 时，oralce记录简单的yes或no值，以指出在监控间隔 期间某个索引是否被使用。SQL：select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;alter index test_pk monitoring usage;alter index test_pk nomonitoring usage;五其它共享SQL语句 Library cache 共享的语句必须满足三个条件： A、 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同(连空格个数都一样)。 B、 两个语句所指的对象必须完全相同： C、 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。BIND变量对执行计划的影响执行计划分析PARTITION RANGE SINGLE/INLIST/INTE/iteration/allINDEX SCAN：快速全局扫描在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。跳跃式扫描从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有