oraclesql语句级与策略级优化小结.docx
《oraclesql语句级与策略级优化小结.docx》由会员分享，可在线阅读，更多相关《oraclesql语句级与策略级优化小结.docx（15页珍藏版）》请在冰豆网上搜索。
oraclesql语句级与策略级优化小结
尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
用表连接替换EXISTS， 通常来说，采用表连接的方式比EXISTS更有效率，RBO中适用，因为前者需要FILTER，nestedloopssemi是nestedloop连接的变种，又叫半连接。
原理与nl相同，通常用于in,exist操作，这种操作join时候，通常查找到一条纪录就可以了，所以用semi表示。
与semi相似的有一种叫anti，反连接，一般用于notin,notexists，也有nestloopanti和hashanti两种。
不用NOTIN操作符，可以用NOTEXISTS或者外连接+替代。
Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。
而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。
NOTEXISTS比NOTIN效率稍高。
但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。
不用“<>”或者“!
=”操作符。
对不等于操作符的处理会造成全表扫描，可以用“<”or“>”代替。
Where子句中出现ISNULL或者ISNOTNULL时，Oracle会停止使用索引而执行全表扫描。
可以考虑在设计表时，对索引列设置为NOTNULL。
这样就可以用其他操作来取代判断NULL的操作。
当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。
对于有连接的列“||”，最后一个连接列索引会无效。
尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。
如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。
对数据类型不同的列进行比较时，会使索引失效。
UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。
如果不需要删除重复记录，应该使用UNIONALL。
OrderBy语句中的非索引列会降低性能，可以通过添加索引的方式处理。
严格控制在OrderBy语句中使用表达式。
不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。
多利用内部函数提高Sql效率。
当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。
这样可以减少解析时间。
用TRUNCATE替代DELETE，开发中不准使用。
当删除表中的记录时，在通常情况下，回滚段（rollbacksegments）用来存放可以被恢复的信息。
如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）。
而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。
当命令运行后，数据不能被恢复。
因此很少的资源被调用，执行时间也会很短。
SELECT子句中避免使用'*'
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用'*'是一个方便的方法。
不幸的是，这是一个非常低效的方法。
实际上，ORACLE在解析的过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
Count（*）10G中例外
用Where子句替换HAVING子句
避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。
这个处理需要排序，总计等操作。
如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
需要注意的是，随着Oracle的升级，查询优化器会自动对Sql语句进行优化，某些限制可能在新版本的Oracle下不再是问题。
尤其是采用CBO（Cost-BasedOptimization，基于代价的优化方式）时。
二．跟踪和分析系统及SESSION级的SQL：
跟踪SQL语句
SQLtrace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.
设置SQLTRACE在会话级别:
有效
ALTERSESSIONSETSQL_TRACETRUE
设置SQLTRACE在整个数据库有效仿,你必须将SQL_TRACE参数在中设为TRUE,USER_DUMP_DEST参数说明了生成跟踪文件的目录
跟踪会话和系统跟踪
跟踪自己的会话或者是别人的会话
跟踪自己的会话很简单
Altersessionsetsql_tracetrue|false
or
exec（TRUE）;
如果跟踪别人的会话，需要调用一个包
exec（sid,serial#,true|false）
或exec''）停止TRACE（sid,serial#,10046,0,''）
跟踪的信息在user_dump_dest目录下可以找到
可以通过Tkprof来解析跟踪文件，如
Tkprof原文件目标文件sys=nsort=exeela
设置整个数据库系统跟踪
其实文档上的altersystemsetsql_trace=true是不成功的
但是可以通过设置事件来完成这个工作，作用相等
altersystemsetevents
'10046tracenamecontextforever,level1';
如果关闭跟踪，可以用如下语句
altersystemsetevents
'10046tracenamecontextoff';
其中的level1与上面的8都是跟踪级别
level0:
停止
level1：
跟踪SQL语句，等于sql_trace=true
level4：
包括变量的详细信息
level8：
包括等待事件
level12：
包括绑定变量与等待事件
eg:
altersystemsetmax_dump_file_size=unlimited;
ALTERSESSIONSETEVENTS'10046tracenamecontextforever,level12';
ALTERSESSIONSETEVENTS'942tracenameerrorstacklevel10';
（对SQLTRACE的用法也不够准确,设置SQLTRACE首先要在中设定TIMED_STATISTICS,这样才能得到那些重要的时间状态.生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数.大家可以参考ORACLE手册来了解具体的配置.）
分析SQL语句
用EXPLAINPLAN分析SQL语句
EXPLAINPLAN是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句.通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表（索引扫描或全表扫描）以及使用到的索引名称.
你需要按照从里到外,从上到下的次序解读分析的结果.EXPLAINPLAN分析的结果是用缩进的格式排列的,最内部的操作将被最先解读,如果两个操作处于同一层中,带有最小操作号的将被首先执行.
NESTEDLOOP是少数不按照上述规则处理的操作,正确的执行路径是检查对NESTEDLOOP提供数据的操作,其中操作号最小的将被最先处理.
通过实践,感到还是用SQLPLUS中的SETTRACE功能比较方便.
举例:
SQL>list
1SELECT*
2FROMdept,emp
3*WHERE=
SQL>setautotracetraceonly/*traceonly可以不显示执行结果*/
SQL>/
14rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10NESTEDLOOPS
21TABLEACCESS（FULL）OF''''EMP''''
31TABLEACCESS（BYINDEXROWID）OF''''DEPT''''
43INDEX（UNIQUESCAN）OF''''PK_DEPT''''（UNIQUE）
Statistics
----------------------------------------------------------
0recursivecalls
2dbblockgets
30consistentgets
0physicalreads
0redosize
2598bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
14rowsprocessed
通过以上分析,可以得出实际的执行步骤是:
1. TABLEACCESS（FULL）OF''''EMP''''
2. INDEX（UNIQUESCAN）OF''''PK_DEPT''''（UNIQUE）
3. TABLEACCESS（BYINDEXROWID）OF''''DEPT''''
4. NESTEDLOOPS（JOINING1AND3）
注:
目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQLAnalyze都提供了极其方便的EXPLAINPLAN工具.也许喜欢图形化界面的朋友们可以选用它们.
10G：
在同一个SESSION中执行以下两句：
EXPLAINPLANFORSELECT*FROMC_CONSWHERECONS_NO=:
A
SELECTfromdual;
三．Oracle优化器
Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具。
Oracle的优化器有两种优化方式：
基于规则的（RBO）和基于代价的（CBO）。
RBO：
优化器遵循Oracle内部预定的规则。
CBO：
依据语句执行的代价，主要指对CPU和内存的占用。
优化器在判断是否使用CBO时，要参照表和索引的统计信息。
统计信息要在对表做analyze后才会有。
Oracle8及以后版本，推荐用CBO方式。
Oracle优化器的优化模式主要有四种：
Rule：
基于规则；
Choose：
默认模式。
根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。
Firstrows：
与Choose类似。
不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。
Allrows：
即完全基于Cost的模式。
当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。
没有统计信息则使用RBO方式。
设定优化模式
Instance级别：
在init.ora文件中设定OPTIMIZER_MODE；
Session级别：
通过SQL>ALTERSESSIONSETOPTIMIZER_MODE=;来设定。
语句级别：
通过SQL>SELECT/*+ALL+_ROWS*/……;来设定。
可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/等。
统计表信息
要注意的是，如果表有统计信息，则可能造成语句不走索引的结果。
可以用SQL>ANALYZETABLEtable_nameDELETESTATISTICS;删除统计信息。
对列和索引更新统计信息的SQL：
SQL>ANALYZETABLEtable_nameCOMPUTESTATISTICS;
SQL>ANALYZEINDEXindex_nameESTIMATESTATISTICS;
10g:
DBMS_STATS.
四．使用HINT
Oracle使用的hints调整机制一直很复杂，OracleTechnicalNetwork对使用hints调整OracleSQL的过程有很好的全面评述。
根据对10g数据库的介绍，可使用更多新的optimizerhints来控制优化行为。
现在让我们迅速了解一下这些强大的新hints:
Oracle使用的hints调整机制一直很复杂，OracleTechnicalNetwork对使用hints调整OracleSQL的过程有很好的全面评述。
根据对10g数据库的介绍，可使用更多新的optimizerhints来控制优化行为。
现在让我们迅速了解一下这些强大的新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）*/...
典型例子：
电费发行中的一句话。
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_amt,
FROM e_pl_amt a, e_cons_prc_amt b, a_rcvbl_flow_tmp c
WHERE = in_org_no
AND = in_ym
AND = in_org_no
AND = in_ym
AND = in_app_no
AND =
AND =
AND =
GROUP BY , , , ;
CARDINALITY
此hint定义了对由查询或查询部分返回的基数的评价。
注意如果没有定义表格，基数是由整个查询所返回的总行数。
例如:
SELECT/*+CARDINALITY（[tablespec]card）*/
典型例子：
临时表的使用：
SELECTx.*,+owe_amt
FROM（SELECTCOUNT
（1）row_count,COUNTcons_count,,
SUM-rcvbl_owe,
SUM）penalty
FROMa_rcvbl_flowa,c_consb
WHERE=
AND=
ANDIN（SELECT/*+CARDINALITY（x1）+*/
*
FROMTABLE（v_orgnolist）x
WHERErownum>=0）
ANDBETWEENin_rcvblymbgnANDin_rcvblymend
ANDIN（'01','02'）
ANDLIKEin_paymode||'%'
ANDLIKEin_amttype||'%'
ANDnvl,'00'）LIKEin_conssortcode||'%'
ANDLIKEin_electypecode||'%'
ANDnvl,'0'）LIKEin_periodnum||'%'
ANDrcvbl_amt-rcved_amt>=v_compareamtbgn
ANDrcvbl_amt-rcved_amt<=v_compareamtend
ANDLIKEin_consno||'%'
AND<=in_showtype
GROUPBY
ORDERBYrcvbl_ymDESC）x
no_use_nl
Hintno_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。
通过这一hint，只有hashjoin和sort-mergejoins会为指定表格所考虑。
例如:
SELECT/*+NO_USE_NL（employees）*/...
no_use_merge
此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。
例如:
SELECT/*+NO_USE_MERGE（employeesdept）*/...
no_use_hash
此hint使CBO通过把指定表格作为内部表格的方式，拒绝hashjoins把每个指定表格加入到另一原始行。
例如:
SELECT/*+NO_USE_HASH（employeesdept）*/...
no_index_ffs
此hint使CBO拒绝对指定表格的指定标签进行fastfull-indexscan。
Syntax:
/*+NO_INDEX_FFS（tablespecindexspec）*/
no_index_ss
此hint使CBO拒绝对指定表格的指定标签进行skipscan。
Syntax:
/*+NO_INDEX_SS（tablespecindexspec）*/
index_ss
此hint明确地为指定表格选择indexskipscan。
如果语句使用indexrangescan，Oracle将以对其索引值的升序排列来检查索引入口。
在被分割的索引中，其结果为对每个部分内部的升序排列。
Syntax:
/*+INDEX_SS（tablespecindexspec）*/
cpu_costing
此hint为SQL语句打开CPUcosting。
这是优化器的默认评估模式。
优化器评估当执行给定查询时，数据库需要运行的IO操作数、IO操作种类、以及CPU周期数。
Syntax:
/*+CPU_COSTING（tablespecindexspec）*/
no_cpu_costing
此hint为SQL语句关闭CPUcosting。
然后CBO使用IOcost模式，此模式忽略CPU花费，仅测量在single-blockreads中的所有指标。
Syntax:
/*+NO_CPU_COSTING*/
随着Oracle优化器越来越成熟，Oracle专家必须不断增加自己对调整SQL语句的工具储备。
当然，讨论所有复杂的Oracle10gSQL新hints远远超出了本文的范围，你可以从MikeAult的新书OracleDatabase10gNewFeatures中获得关于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区中语句使用的所有索引
selectobject_owner,object_name,options,count（*）
from v$sql_plan
where operation='INDEX'
and object_owner!
='SYS'
group by object_owner,object_name,operation,options
order bycount（*）desc;
所有基于共享SQL区中的信息来识别索引使用情况的方法，都可能会收集到不完整的信息。
共享SQL区是一 个动态结构，除非能对它进行足够频繁的采样，否则在有关索引使用的情况的信息被收集之前，SQL语句可能就已经（因为老化）被移出缓存了。
oracle9i提供了解决这个问题的方案，即它为alterindex提供了一个
monitoringusage子句。
当启用monitoringusage时，oralce记录简单的yes或no值，以指出在监控间隔期间某个索引是否被使用。
SQL：
selectindex_name,monitoring,used,start_monitoring,end_monitoringfromv$object_usage;
alterindextest_pkmonitoringusage;
alterindextest_pknomonitoringusage;
五．其它
共享SQL语句
Librarycache
共享的语句必须满足三个条件：
A、字符级的比较：
当前被执行
升级会员 