Oracle优化案例表连接优化.docx
《Oracle优化案例表连接优化.docx》由会员分享，可在线阅读，更多相关《Oracle优化案例表连接优化.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle优化案例表连接优化
Oracle表连接的优化分享
这三个例子来源于近期一个月内我们部门遇到的经典SQL问题，都来自身边，也许都是大家非常熟悉的SQL，但是他们都有一个共同的特点，就是表连接的优化，现总结如下：
1.子查询与表连接优化（来自集团ITSM应用）
我发现身边的开发人员，很多人喜欢使用复杂子查询写各类SQL，其实很多时候，要是不注意默写细节，就会出现很多问题，如性能问题甚至结果值不对。
举个身边例子,具体如下：
-----略去
selectta.tch_id,ta.flow_id
fromtachepr,
（selectTCH_ID,c.flow_id
fromevent_qa,staff_eventb,tachec
where（a.event_type='2'ORa.event_type='1'）
anda.event_id=b.event_id
and（a.flagisnullora.flag='1'）
andb.staff_id=1
anda.content_id=c.tch_id）ta
whereta.flow_id=pr.sub_flow_id（+）
andpr.flow_idisnull
-------略去
这条SQL本身很复杂，我把其他部分略去，只显示典型的子查询写法的这一小部分，另外主要也是这一小部分慢，如果把这一小部分注释掉，执行速度在1秒内完成，否则需要30多秒才可以完成。
我们单看这一小部分的执行计划，截取如下：
我们可以很清楚的判断出来，该子查询的连接顺序是什么？
首先是外面的tache表先和ta结果集的tache表连接，然后再和STAFF_EVENT连接，最后和EVENT_Q完成连接！
由于staff_event这个表和TACHE表没有连接条件，就是没有体现staff_event的某某字段和TACHE表的某某字段关联的地方，所以这里产生了笛卡尔乘积！
因此本次查询非常的慢。
该如何处理呢？
其实很简单，我们发现，整个语句最终只返回42条
说明ta这个结果集本身返回的记录数并不多，虽然tache是一张大表，但是这段语句如果没有笛卡尔乘积，应该非常快！
简单研究发现，只要ta结果集内部先完成表连接，再和外部连接，就不至于产生笛卡尔乘积了，因为在ta结果集里，我们可以STAFF_EVENTb和EVENT_Qa先完成连接，他们是有连接条件的，条件是
a.event_id=b.event_id
接下来这两个连接再和ta结果集中的STAFFc表连接，又有连接条件，条件是：
a.content_id=c.tch_id
最好再和外面的tachepr表完成连接，连接条件是：
ta.flow_id=pr.sub_flow_id（+）
如此看来，不可能有笛卡尔！
而如何让ORACLE的子查询不要先自行逛街出去和外面表先连接，再会过来连接内部剩下的部分呢？
很简单，只要修改为如下即可,增加rownum部分
selectta.tch_id,ta.flow_id
fromtachepr,
（selectTCH_ID,c.flow_id,rownum
fromevent_qa,staff_eventb,tachec
where（a.event_type='2'ORa.event_type='1'）
anda.event_id=b.event_id
and（a.flagisnullora.flag='1'）
andb.staff_id=1
anda.content_id=c.tch_id）ta
whereta.flow_id=pr.sub_flow_id（+）
andpr.flow_idisnull
为了保证rownum的结果集是对的，oracle不可能先出去和结果集外的表关联一部分再回到结果集内，只可能内部先关联了，因此，我们成功了！
最终执行计划如下，笛卡尔乘积消失，总的SQL语句执行时间从30秒变为1秒
2.NL连接与表连接优化
在我们电信行业，80%的表连接都是NL连接，因为这是由oltp系统的特性决定的：
“虽然数据量很大，查询更新很频繁，但是最终需要查询返回及更新的数据量却很少。
”
但是NL连接应用不当，却是有致命的问题，因为NL最大的特点是驱动表返回多少条，被驱动表访问多少次，所以NL连接在驱动表返回极少的时候，性能不低，但是驱动表返回很多的时候，性能却很低下，万万不可使用！
下面再举一个身边的例子，来自安徽拨测应用的一条SQL，具体如下：
--略去
from（selecta.alarm_title,
--略去其他字段
fromne_alarm_lista
wherea.alarm_type=20
anda.alarm_Levelin（'1','2'）
anda.alarm_statein（0）
anda.last_send_time>=TO_DATE（'&P_DATE_BEGIN'||'00:
00:
00','YYYY-MM-DDhh24:
mi:
ss'）
anda.last_send_time<=TO_DATE（'&P_DATE_END'||'23:
59:
59','YYYY-MM-DDhh24:
mi:
ss'）
）a1,
manage_regionb,
net_elementd,
（select*
fromtp_domain_listvalues
wheredomain_codeLIKE'DOMAIN_ALARM_STATE%'）f,
kpi_code_listg,
ne_alarm_msg_source_relah,
net_elementk,
manage_regionl
wherea1.alarm_region_origin=b.region_id（+）
anda1.ne_id=d.ne_id
--andinstr（d.path,:
P_NE_ID,1,1）>0
anda1.alarm_state=f.list_value
anda1.kpi_id=g.kpi_id
anda1.ne_alarm_list_id=h.ne_alarm_msg_id
andh.source_type='19'
andh.source_id=k.ne_id
andk.region_id=l.region_id
andl.region_idin（'&PSOURCE_REGION'）
orderbya1.alarm_state,a1.create_timedesc
截取部分执行计划分析，我们惊奇的发现，ORACLE预估NE_ALARM_MSG_SOURCE_RELA表source_type=’19’的条件是返回1条，实际返回了477K条，预估的严重不准确！
怪不得会使用NL连接。
结果NL连接导致后续的被驱动表被访问了477K次，真是惊人的数字！
那该如何优化呢？
优化招式没有定式，有的时候要改写SQL，有的时候需要增加索引，而有的时候，却只要重新收集一下表的统计信息或者直方图即可。
而此时，我们非常清楚的判断出来，ORACLE对NE_ALARM_MSG_SOURCE_RELA表的SOURCE_TYPE列的数据分别严重错误了。
实际这张表记录有快200万，而这个表的SOURCE_TYPE的列只有3个取值，都是大量的返回。
因此我们要做的事情非常简单，重新收集这个表的列的直方图即可，如下：
execdbms_stats.gather_table_stats（ownname=>'BOSSWG',tabname=>'NE_ALARM_MSG_SOURCE_RELA',estimate_percent=>10,method_opt=>'forallcolumnssize254',cascade=>TRUE）;
新的执行计划如下从NL更新为部分HASH了。
执行时间从原先的20秒缩减为0.2秒
3.NOTIN与表连接优化
很多人说不要用NOTIN,要考虑用NOTEXISTS，其实很多时候NOTIN是可以很快的，而且很多场合NOTIN也和NOTEXISTS不完全等价，在列明确为非空的时候，NOTIN在可以用到ANTI算法的时候，是可以很快的，我们可以看看SQL优化
SELECTT.FLOW_ID
FROMFLOW_HIST
WHERET.FLOW_MOD=11263
ANDT.FLOW_IDNOTIN（SELECTZJ.FLOW_IDfromZJ_EVENT_HISZJ）
执行计划如下：
该SQL是NOTIN写法的SQL，理论上应该是anti算法的执行计划最高效，但是却是走filter，因此效率极低，在确定ZJ_EVENT_HIS表的FLOW_ID字段不会为空后，将该语句增加whereflow_idisnotnull，或者将该列的属性修改为NOTNULL属性。
修改为如下，效率可以提升很多，从1万多秒缩减为1秒，具体修改如下：
SELECTT.FLOW_ID
FROMFLOW_HIST
WHERET.FLOW_MOD=11263
ANDT.FLOW_IDNOTIN
（SELECTZJ.FLOW_IDFROMZJ_EVENT_HISZJwhereflow_idisnotnull）
如果这样修改，执行计划将会边为如下
/*
结论：
NL连接中，驱动表被访问0或者1次，被驱动表被访问0次或者N次，N由驱动表返回的结果集的条数来定）
*/
--环境构造
--研究NestedLoopsJoin的表访问次数前准备工作
DROPTABLEt1CASCADECONSTRAINTSPURGE;
DROPTABLEt2CASCADECONSTRAINTSPURGE;
CREATETABLEt1（
idNUMBERNOTNULL,
nNUMBER,
contentsVARCHAR2（4000）
）
;
CREATETABLEt2（
idNUMBERNOTNULL,
t1_idNUMBERNOTNULL,
nNUMBER,
contentsVARCHAR2（4000）
）
;
executedbms_random.seed（0）;
INSERTINTOt1
SELECTrownum,rownum,dbms_random.string（'a',50）
FROMdual
CONNECTBYlevel<=100
ORDERBYdbms_random.random;
INSERTINTOt2SELECTrownum,rownum,rownum,dbms_random.string（'b',50）FROMdualCONNECTBYlevel<=100000
ORDERBYdbms_random.random;
COMMIT;
selectcount（*）fromt1;
selectcount（*）fromt2;
--我们用设置statistics_level=all的方式来观察如下表连接语句的执行计划：
--T2表被访问100次（驱动表访问1次，被驱动表访问100次）
--这个setlinesize1000对dbms_xplan.display_cursor还是有影响的，如果没有设置，默认情况下的输出，将会少了很多列，如BUFFERS等
Setlinesize1000
altersessionsetstatistics_level=all;
SELECT/*+leading（t1）use_nl（t2）*/*
FROMt1,t2
WHEREt1.id=t2.t1_id;
--略去记录结果
select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
-------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|
-------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1||100|00:
00:
00.94|100K|
|1|NESTEDLOOPS||1|100|100|00:
00:
00.94|100K|
|2|TABLEACCESSFULL|T1|1|100|100|00:
00:
00.01|14|
|*3|TABLEACCESSFULL|T2|100|1|100|00:
00:
00.94|100K|
-------------------------------------------------------------------------------------
3-filter（"T1"."ID"="T2"."T1_ID"）
---换个语句，这次T2表被访问2次（驱动表访问1次，被驱动表访问2次）
Setlinesize1000
altersessionsetstatistics_level=all;
SELECT/*+leading（t1）use_nl（t2）*/*
FROMt1,t2
WHEREt1.id=t2.t1_id
ANDt1.nin（17,19）;
select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
-------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|
-------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1||2|00:
00:
00.02|2019|
|1|NESTEDLOOPS||1|2|2|00:
00:
00.02|2019|
|*2|TABLEACCESSFULL|T1|1|2|2|00:
00:
00.01|8|
|*3|TABLEACCESSFULL|T2|2|1|2|00:
00:
00.02|2011|
-------------------------------------------------------------------------------------
2-filter（（"T1"."N"=17OR"T1"."N"=19））
3-filter（"T1"."ID"="T2"."T1_ID"）
--继续换个语句，这次T2表被访问1次（驱动表访问1次，被驱动表访问1次）
Setlinesize1000
altersessionsetstatistics_level=all;
SELECT/*+leading（t1）use_nl（t2）*/*
FROMt1,t2
WHEREt1.id=t2.t1_id
ANDt1.n=19;
select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
SQL>select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
-------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|
-------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1||1|00:
00:
00.01|1014|
|1|NESTEDLOOPS||1|1|1|00:
00:
00.01|1014|
|*2|TABLEACCESSFULL|T1|1|1|1|00:
00:
00.01|8|
|*3|TABLEACCESSFULL|T2|1|1|1|00:
00:
00.01|1006|
-------------------------------------------------------------------------------------
2-filter（"T1"."N"=19）
3-filter（"T1"."ID"="T2"."T1_ID"）
---接下来，T2表居然被访问0次（驱动表访问1次，被驱动表访问0次）
SELECT/*+leading（t1）use_nl（t2）*/*
FROMt1,t2
WHEREt1.id=t2.t1_id
ANDt1.n=999999999;
select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
-------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|
-------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1||0|00:
00:
00.01|7|
|1|NESTEDLOOPS||1|1|0|00:
00:
00.01|7|
|*2|TABLEACCESSFULL|T1|1|1|0|00:
00:
00.01|7|
|*3|TABLEACCESSFULL|T2|0|1|0|00:
00:
00.01|0|
-------------------------------------------------------------------------------------
2-filter（"T1"."N"=999999999）
3-filter（"T1"."ID"="T2"."T1_ID"）
---到最后，不只是T2表被访问0次，连T1表也访问0次
SELECT/*+leading（t1）use_nl（t2）*/*
FROMt1,t2
WHEREt1.id=t2.t1_id
AND1=2;
select*fromtable（dbms_xplan.display_cursor（null,null,'allstatslast'））;
----------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||1||0|00:
00:
00.01|
|*1|FILTER||1||0|00:
00:
00.01|
|2|NESTEDLOOPS||0|100|0|00:
00:
00.01|
|3|TABLEACCESSFULL|T1|0|100|0|00:
00:
00.01|
|*4|TABLEACCESSFULL|T2|0|1|0|00:
00:
00.01|
----------------------------------------------------------------------------
1-filter（NULLISNOTNULL）
4-filter（"T1"."ID"="T2"."T1_ID"）
--分析T2表被访问次数不同的原因
---解释T2表为啥被访问100次
selectcount（*）fromt1;
COUNT（*）
----------
100
---解释T2表为啥被访问2次
selectcount（*）fromt1wheret1.nin（17,19）;
COUNT（*）
----------
2
---解释T2表为啥被访问1次
selectcount（*）fromt1wheret1.n=19;
COUNT（*）
----------
1
---解释T2表为啥被访问0次
selectcount（*）fromt1wheret1.n=999999999;
C
升级会员 