讲稿优化之手记1 chapter3

UNION效率比UNIONALL效率SQL的时候发现，居然使用UNIONUNIONALL的效率高UNION效率比UNIONALL效率SQL的时候发现，居然使用UNIONUNIONALL的效率高。具体SQL语句如下：SQL>select23456789fromselecta.id,c.plat_name,a.plat_id,fromcat_auth_pricea,plt_platcwherea.id((selectd12.price_idfromcat_auth_price_drug12d12,cat_drugdwhered12.drug_id=d.idand_chnlike'%')union(selectd20.price_idfromcat_auth_price_drug20d20,cat_drugdwhered20.drug_id=d.idand_chnlike'%'))anda.plat_id=and(a.plat_id='FR20T0000020000000000032'ora.plat_id=Elapsed:00:00:00.81SQL>selectcount(*)23456789(selecta.id,c.plat_name,a.plat_id,fromcat_auth_pricea,plt_platcwherea.id((selectd12.price_idfromcat_auth_price_drug12d12,cat_drugdwhered12.drug_id=d.idand_chnlike'%')selectd20.price_idfromcat_auth_price_drug20d20,cat_drugdwhered20.drug_id=d.idand_chnlike'%')(anda.plat_id=and(a.plat_id='FR20T0000020000000000032'ora.plat_id=Elapsed:00:00:00.71对比两个SQL语句唯一的区别就是一个是UNIONALL，另一个是UNION，而且按照一般的规律，UNIONALL的速度会更快一些，因为无须进行排序去重的操作。IN语句中，猜测UNION速度快是由于去掉了重复值，使得IN的结果集变小，导致SQL>explainplan23456789selectcount(*)(selecta.id,c.plat_name,a.plat_id,a.substitute_flagfromcat_auth_pricea,plt_platcwherea.idin((selectd12.price_idfromcat_auth_price_drug12d12,cat_drugdwhered12.drug_id=d.idand_chnlike'%')(selectd20.price_idfromcat_auth_price_drug20d20,cat_drugdwhered20.drug_id=d.idand_chnlike'%'))anda.plat_id=and(a.plat_id='FR20T0000020000000000032'ora.plat_idSQL>select*fromtable(dbms_xplan.display);|Id||||Cost||||||||||||||||||012345678910111213SQL>select*fromtable(dbms_xplan.display);|Id||||Cost||||||||||||||||||01234567891011121314151617SORTAGGREGATEHASH|||||||||||11||||||||||||||||||||||||||||||||||||3862386270142|2|38371637167082058887358892NESTEDLOOPSINLISTITERATORINDEXRANGESCANINLISTITERATORINDEXRANGESCANHASHJOINTABLEACCESSFULLTABLEACCESSFULLHASHJOINTABLEACCESSTABLEACCESS||||||||||||||2|||||||||149K149K246622466293917124K93917124K3785K12M2191K1204K3760K10M3760K6076K25SQL>explainplan23456789selectcount(*)(selecta.id,c.plat_name,a.plat_id,a.substitute_flagfromcat_auth_pricea,plt_platcwherea.idin((selectd12.price_idfromcat_auth_price_drug12d12,cat_drugdwhered12.drug_id=d.idand_chnlike'%')union(selectd20.price_idfromcat_auth_price_drug20d20,cat_drugdwhered20.drug_id=d.idand_chnlike'%'))anda.plat_id=and(a.plat_id='FR20T0000020000000000032'ora.plat_id=SQL>select*fromtable(dbms_xplan.display);|Id||||Bytes|TempSpc|||||||||0|SELECT||||||||||||11||100100||||||||||||||123456NESTEDLOOPSHASHTABLEACCESSFULLSORT|54800||661K354K3640K|||149K||||||||||789101112131415|||||||||789101112131415UNION-HASH||||2191K1204K3760K10M3760K6076K|25|||||||||||||||||||24662TABLEACCESSFULL|CAT_AUTH_PRICE_DRUG12|24662TABLEACCESSFULL||93917|124K|93917HASH|TABLEACCESSFULL|TABLEACCESSFULL|CAT_AUTH_PRICE_DRUG20124K|1INLISTINDEXUNIQUE||||23rows对比两个SQL看来在一个比较复杂的SQL语句中，UNIONALLUNION的区别并不是简单的是否过滤重复数据的 UNIONALL效率还高恒等查询条件对查询的影有的时候开发人员为了方便会在WHERE语句后面添加一个恒等条件1=1，这样在处理页面传入的条件时就可以不用判断当前是否是第一个条件，而直接添加AND条件了。OracleOracle的性能而已。但是今天突然发现这个恒等的查询条件居然可以影响Oracle的执行计划。SQL>CREATETABLET1ASSELECT*FROM234WHEREOWNER=ANDOBJECT_TYPENOTLIKE'%BODY'ANDOBJECT_TYPENOTLIKE'JAVA%';TableSQL>CREATETABLET2ASSELECT*FROMDBA_SEGMENTSWHEREOWNER=TableSQL>CREATETABLET3ASSELECT*FROMDBA_INDEXESWHEREOWNER=TableSQL>ALTERTABLET1ADDCONSTRAINTPK_T1PRIMARYKEYTableSQL>CREATEINDEXIND_T2_SEGNAMEONIndexSQL>CREATEINDEXIND_T3_TABNAMEONIndex100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfullycompleted.SQL>SETAUTOTTRACEEXPSQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,2345PL/SQLproceduresuccessfullycompleted.SQL>SETAUTOTTRACEEXPSQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,23456789FROMT1,WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDNOTEXISTS(SELECT1FROMWHERET3.INDEX_NAME=T1.OBJECT_NAMEANDT3.TABLE_NAME='OBJ$');Execution0123456SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=668Bytes=58784)HASHJOIN(ANTI)(Cost=12Card=668Bytes=58784)HASHJOIN(Cost=9Card=668TABLEACCESS(FULL)OF'T2'(Cost=2Card=668Bytes=21376)TABLEACCESS(FULL)OF'T1'(Cost=6Card=3806Bytes=102762)TABLEACCESS(BYINDEXROWID)OF'T3'(Cost=2Card=2INDEX(RANGESCAN)OF'IND_T3_TABNAME'(NON-UNIQUE)(Cost=1012215SQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,23456789FROMT1,WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDNOTEXISTS(SELECT1FROMT3WHERE1=1ANDT3.INDEX_NAME=T1.OBJECT_NAMEANDT3.TABLE_NAME='OBJ$');Execution01234567SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=668Bytes=50768)HASHJOIN(ANTI)(Cost=12Card=668Bytes=50768)HASHJOIN(Cost=9Card=668TABLEACCESS(FULL)OF'T2'(Cost=2Card=668Bytes=21376)TABLEACCESS(FULL)OF'T1'(Cost=6Card=3806Bytes=102762)VIEWOF'VW_SQ_1'(Cost=2Card=2TABLEACCESS(BYINDEXROWID)OF'T3'(Cost=2Card=2INDEX(RANGESCAN)OF'IND_T3_TABNAME'(NON-UNIQUE)(Cost=10122156观察上面两个查询语句就会发现两个查询语句唯一的区别就是第二个查询语句中的NOTEXISTS子查询中包含了一个恒等查询条件1=1。仅仅是这一点的区别却造成了两个SQLOrace多生成了一个临时的VIEW。也许有人认为这个执行计划没有太大的区别，基本上可以认为是等价的。其实不然，由于多生成了一个VIEW的步骤，就有可能造成性能的下降。更为关键的是：这说明Oracle认为两个SQL语句是不同的，因此处理方式也是不同的。如果一个简单的VIEWSQL>SETAUTOTTRACESQL>SELECT23456789SQL>SETAUTOTTRACESQL>SELECT23456789FROMCAT_PRODUCTA,CAT_DRUGBWHEREA.MEDICAL_ID=B.IDANDA.CHECK_FLAG=1ANDNOTEXISTSSELECTWHEREC.DATA_PRODUCT=A.IDANDC.ENABLE_FLAG='1'ANDC.PROJECT_ID=);Execution01234567SELECTSTATEMENTOptimizer=CHOOSE(Cost=483Card=1Bytes=213)NESTEDLOOPS(ANTI)(Cost=483Card=1Bytes=213)NESTEDLOOPS(Cost=481Card=1VIEWOF'index$_join$_001'(Cost=480Card=1Bytes=77)HASHJOINHASHINDEX(FASTFULLSCAN)OF'PK_CAT_PRODUCT'(UNIQUE)(Cost=33Card=1Bytes=77)012345584INDEX(FASTFULLSCAN)OF'TU_CAT_PRODUCT_MANUFACT_ID'(NON-UNIQUE)(Cost=339291TABLEACCESS(BYINDEXROWID)OF'CAT_DRUG'(Cost=1Card=1Bytes=83)INDEX(UNIQUESCAN)OF'PK_CAT_DRUG'(UNIQUE)TABLEACCESS(BYINDEXROWID)OF'PROJECT_SUPPLY_PRODUCT'(Cost=2Card=1INDEX(RANGESCAN)OF'IDX_PROJECT_SUPPLY_PRODUCT_PJ'(NON-UNIQUE)(Cost=112SQL>SELECT23456789FROMCAT_PRODUCTA,CAT_DRUGBWHEREA.MEDICAL_ID=B.IDANDA.CHECK_FLAG=1ANDNOTEXISTSSELECTWHERE1=1ANDC.DATA_PRODUCT=A.IDANDC.ENABLE_FLAG='1'ANDC.PROJECT_ID=);Execution0123456701234567SELECTSTATEMENTOptimizer=CHOOSE(Cost=484Card=1Bytes=186)HASHJOIN(ANTI)(Cost=484Card=1Bytes=186)NESTEDLOOPS(Cost=481Card=1VIEWOF'index$_join$_001'(Cost=480Card=1Bytes=77)HASHJOINHASHINDEX(FASTFULLSCAN)OF'TU_CAT_PRODUCT_MED_CHECK'(NON-UNIQUE)(Cost=330123455INDEX(FASTFULLSCAN)OF'TU_CAT_PRODUCT_MANUFACT_ID'(NON-UNIQUE)291TABLEACCESS(BYINDEXROWID)OF'CAT_DRUG'(Cost=1Card=1Bytes=83)INDEX(UNIQUESCAN)OF'PK_CAT_DRUG'(UNIQUE)VIEWOF'VW_SQ_1'(Cost=2Card=1INDEX(RANGESCAN)OF'IDX_PROJECT_SUPPLY_PRODUCT_PJ'(NON-UNIQUE)(Cost=1SQLNOTEXISTS语句中11的恒等条件，但是对于没有这个恒等语句，Oracle则选择了HASHJOINANTI。这次执行计划的改变足以使SQL一个恒等查询条件居然可以使查询计划发生变化，这是以前从来没有想到的。根据这个问题的现象，可总结出以下三点结论：不要 建立良好的编码风格，建立SQL两层GROUPBY的效率反而比一层GROUPBY一般的优化思路都是尽可能地减少嵌套的层数，从而减少不必要的操作。但是有的时候情况却并非如此。下面通过一个具体的例子来说明这个问题：SQL>CREATETABLETASSELECT*FROM表已创SQL>INSERTINTOTSELECT*FROM已创建32774SQL>INSERTINTOTSELECT*FROM已创建65548SQL>INSERTINTOTSELECT*FROM已创建131096SQL>INSERTINTOTSELECT*FROM已创建262192SQL>提交完SQL>SETAUTOTTRACESQL>SETTIMINGONSQL>COLPLAN_PLUS_EXPFORMATSQL>SETLINESSQL>SELECTOBJECT_TYPE,COUNT(DISTINCT2FROMTGROUPSQL>SETLINESSQL>SELECTOBJECT_TYPE,COUNT(DISTINCT2FROMTGROUPBY34已用时间:0000012SELECTSTATEMENTSORT(GROUPTABLEACCESS(FULL)OF001SQL>dbblockgetsphysicalreadssorts(memory)rowsprocessedOBJECT_TYPE,COUNT(CREATED)(SELECTOBJECT_TYPE,CREATEDFROMTGROUPBYOBJECT_TYPE,CREATEDGROUPBY34已用时间:000001234SELECTSTATEMENTOptimizer=CHOOSESORT(GROUPBY)SORT(GROUPTABLEACCESS(FULL)OF01232physicalreadssorts(memory)rows从统计信息看，两SQL完全一行计划只有一次GROUPBY而后者需要 这个结果让人费解，经过仔细的考虑，怀疑导致问题的主要原因是COUNT(DISTINCTCREATED)操作较费时第二个查询虽然要GROUPBY二次GROUPBY的字段OBJECT_TYPE就是第一次GROUPBYGROUPBY因此外层SQL中的GROUPBYGROUPBY就是一个计算CREATED字段的个数的操作。而对于第一个查询来说，虽然只有一层且查询只对OBJECT_TYPE进行了GROUPY，但是COUNT(DISTINCTCREATED)操作并不是简单的计数，而是要统计不同的CREATED值。虽然从统计信息没有看到排序操作，但是由于要计算DISTINCTCREATED的值，Oracle必然要保存并比较每个CREATED的值，因此根据目前的结果怀疑这个操作比GROUPBY操作的效率要低，这就是造成两个GROUPBY效率比一个GROUPBY效率更高的原因。这也说明，虽然绝大部分情况下，通过逻辑读来判断SQL执行效率都是准确的，但是这个SQLSQL的执行效率还是有可能存在很大差别的。这个例子中执行时间的差异无论从统计信息还是从执行计划中都是无法预测的。SQL>SELECTOBJECT_TYPE,COUNT(DISTINCTCREATED)CREATED,SQL的执行效率还是有可能存在很大差别的。这个例子中执行时间的差异无论从统计信息还是从执行计划中都是无法预测的。SQL>SELECTOBJECT_TYPE,COUNT(DISTINCTCREATED)CREATED,COUNT(DISTINCTSTATUS)2FROMTGROUPBY34已用时间:0000012SELECTSTATEMENTSORT(GROUPTABLEACCESS(FULL)OF0010SQL>dbblockgetssorts(memory)sorts(disk)rowsprocessedB.OBJECT_TYPE,CREATED,23456789SELECTOBJECT_TYPE,COUNT(CREATED)CREATED(SELECTOBJECT_TYPE,CREATEDFROMTGROUPBYOBJECT_TYPE,)GROUPBYSELECTOBJECT_TYPE,COUNT(STATUS)STATUS(SELECTOBJECT_TYPE,STATUSFROMTGROUPBYOBJECT_TYPE,)GROUPBY)WHEREB.OBJECT_TYPE=34已用时间:0000012SELECTSTATEMENT0MERGE134567892345678923451789SORT(GROUPBY)SORT(GROUPTABLEACCESS(FULL)OF'T'SORT(JOIN)SORT(GROUPBY)SORT(GROUPTABLEACCESS(FULL)OF0050dbblockgetssorts(memory)sorts(disk)rowsSQL是等价的，但是第二个SQL的统计信息中逻辑读的数量是第一个2倍，排序次数是第一SQL的5SQLSQL的64%增加DISTINCT后查询效率反而只要增加了DISTINCT关键字，Oracle就会对随后跟着的所有字段进行排序去重。以前也经常发现由于开发人员对SQL不是很理解，在SELECT列表的20多个字段前面添加了DISTINCT，造成查询的执行异常缓慢，基本上很难在ORA-1555错误出现之前得到查询的结果，甚至有些SQLORA-7445错误。所以在给开发人员培训的时候还着重介绍了一下DISTINCT的功能以及不正确地使用DISTINCT所带来的性能方面的负面影不过这次碰到了一个有趣的现象开发人员在测试一个比较复杂的SQL时发现如果SQL中加上了DISTINCT，则查询大概要花费4分钟左右；而如果不加DISTINCT，则查询执行了10多分钟仍然没有返回结DISTINCTDISTINCT使得第一步的结果集缩小了，从而导致查询性能的提高。但一看SQL才发现，DISTINCT居然是在查询的最外层。由于原始SQL很复杂，牵扯太多的表，很难表述清楚。因此这里模拟了一个例子，这个例子由于受到数据量和SQL复杂程度的限制，所以是否添加DISTINCT对SQL执行时间没有太大的影响，但是两个SQL逻辑读SQL>CREATETABLET1ASSELECT*FROMWHEREOWNER=ANDOBJECT_TYPENOTLIKE'%BODY'ANDOBJECT_TYPENOTLIKE'JAVA%';Tablecreated.SQL>CREATETABLET2ASSELECT*FROMDBA_SEGMENTSWHEREOWNER=TableSQL>CREATETABLET3ASSELECT*FROMDBA_INDEXESWHEREOWNER=TableSQL>CREATETABLET3ASSELECT*FROMDBA_INDEXESWHEREOWNER=TableSQL>ALTERTABLET1ADDCONSTRAINTPK_T1PRIMARYKEYTableSQL>CREATEINDEXIND_T2_SEGNAMEONIndexSQL>CREATEINDEXIND_T3_TABNAMEONIndex100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfully下面看看原SQLDISTINCT后的差SQL>SETAUTOTSQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,2FROMT1,T2WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDT1.OBJECT_NAME3(SELECTINDEX_NAMEFROMT3WHERET3.TABLESPACE_NAME=311rowsExecution012345SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=668Bytes=62124)HASHJOIN(SEMI)(Cost=12Card=668Bytes=62124)HASHJOIN(Cost=9Card=668TABLEACCESS(FULL)OF'T2'(Cost=2Card=668Bytes=21376)TABLEACCESS(FULL)OF'T1'(Cost=6Card=3806Bytes=102762)TABLEACCESS(FULL)OF'T3'(Cost=2Card=3400122100SQL>sorts(memory)sorts(disk)rowsprocessedDISTINCTT1.OBJECT_NAME,T1.OBJECT_TYPE,2FROMT1,T2WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDT1.OBJECT_NAME3(SELECTINDEX_NAMEFROMT3WHERET3.TABLESPACE_NAME=311rowsExecution0123456SELECTSTATEMENTOptimizer=CHOOSE(Cost=16Card=1Bytes=93)SORT(UNIQUE)(Cost=16Card=1Bytes=93)HASHJOIN(Cost=12Card=1HASHJOIN(Cost=5Card=668TABLEACCESS(FULL)OF'T3'(Cost=2Card=340Bytes=11560)TABLEACCESS(FULL)OF'T2'(Cost=2Card=668TABLEACCESS(FULL)OF'T1'(Cost=6Card=38060123321sorts(memory)1sorts(memory)rows从SQL执行的统计信息可以看出，添加DISTINCT后，语句的逻辑读数量反而比不加DISTINCT要低。为对于不加DISTINCT的情况：由于使用IN子查询，Oracle对第二个连接采用了HASHJOINSEMI，这种方式相对于普通的HASHJOIN来说代价要大一些。如果添加了DISTINCT：CBO清楚知道在最后一步肯定要进行排序去重的操作，因此在连接时就选择了HASHJOIN作为连接方式。这就是加上了DISTINCT后，逻辑读反而减少的原因。不过加上DISTINCT后，执行计划增加了一个排序操作；而在不加DISTINCT时是没有这个操作的。当连接的表数据量很大，但SELECT的最终结果并不是很多，且SELECT列数也不是很多的时候，加上DISTINCT后SEMIJOIN连接DISTINCT操作，查询效率最后要说明一点，举这个例子意在说明优化时没有什么东西是一成不变的，几乎任何事情都有可能发生，不要被一些所谓规则限制住。这篇文章并不是在介绍一种优化SQL的方法，严格意义上讲，加上DISTNCT和不加DSTNCT是两个完全不同的SQL语句。虽然在这个例子中二者是等价的，但这是表结构、约束条件和数据本身共同限制的结果，换成另一个环境，这两个SQL得到的结果可能会相去甚远。因此这两个SQL实增加索引改变执行计大家都对删除对象比较敏感，而对增加对象则会放松警惕。而增加索引所带来的危害有时和删除索引同样大。由于添加一个新的索引，使得以前运行正常的SQL错误地选择了新建索引，从而导致SQL运行时间成倍增长，最终使得整个数据库无法响应。这种现象已经屡见不鲜了。因此增加一个索引而改变SQL用新增的索引。SQL>CREATETABLET1ASSELECT*FROM2WHEREOWNER='SYS'ANDOBJECT_TYPENOTLIKE'%BODY'ANDOBJECT_TYPENOTLIKE'JAVA%';Tablecreated.SQL>CREATETABLET2ASSELECT*FROMDBA_SEGMENTSWHEREOWNER=TableSQL>CREATETABLET3ASSELECT*FROMDBA_INDEXESWHEREOWNER=TableSQL>ALTERTABLET1ADDCONSTRAINTPK_T1PRIMARYKEYTableSQL>CREATEINDEXIND_T2_SEGNAMEONIndexSQL>CREATEINDEXIND_T3_TABNAMEONIndexIndex100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfully100',CASCADE=>TRUE)PL/SQLproceduresuccessfullycompleted.SQL>SETAUTOTTRACEEXPSQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,FROMT1,T2WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDNOT(SELECT1FROMT3WHERET3.INDEX_NAME=T1.OBJECT_NAMEANDT3.TABLE_NAME='OBJ$');ExecutionPlan0123456SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=668Bytes=58784)HASHJOIN(ANTI)(Cost=12Card=668Bytes=58784)HASHJOIN(Cost=9Card=668TABLEACCESS(FULL)OF'T2'(Cost=2Card=668Bytes=21376)TABLEACCESS(FULL)OF'T1'(Cost=6Card=3806Bytes=102762)TABLEACCESS(BYINDEXROWID)OF'T3'(Cost=2Card=2INDEX(RANGESCAN)OF'IND_T3_TABNAME'(NON-UNIQUE)(Cost=1012215为了提高上面查询的性能，建立一个复合索引来避免表的扫但是如果建立索引时，把列的顺序搞错了SQL>CREATEINDEXIND_T3_IND_TAB_NAMEONT3(INDEX_NAME,IndexSQL>SELECTT1.OBJECT_NAME,T1.OBJECT_TYPE,FROMT1,T2WHERET1.OBJECT_NAME=T2.SEGMENT_NAMEANDNOT(SELECT1FROMT3WHERET3.INDEX_NAME=T1.OBJECT_NAMEANDT3.TABLE_NAME='OBJ$');ExecutionPlan0123456SELECTSTATEMENTOptimizer=CHOOSE(Cost=389Card=190Bytes=11210)HASHJOIN(Cost=9Card=190TABLEACCESS(FULL)OF'T1'(Cost=6Card=190Bytes=5130)TABLEACCESS(FULL)OF'T2'(Cost=2Card=668Bytes=21376)TABLEACCESS(BYINDEXROWID)OF'T3'(Cost=2Card=1INDEX(RANGESCAN)OF'IND_T3_TABNAME'(NON-UNIQUE)(Cost=1012215执行计划从原来的HASHONANTI变成了FILTEROrace却并没有使用新建立的索引。Oracle居然在没有使用新建索引的情况下，改变了现有查询的执行计划，而且改变后的执行计划效率远远低于改变之前的。这个例子说明增加索引时要更加谨慎，即使Oracle并没有使用新建索引，仍然有可能改变现有SQ