前些天优化了一些耗费buffers较多的SQL，但系统CPU降低的效果不明显，于是又拉了awr报告，查看了SQL ordered by Gets排名前列的SQL。
SQL代码：
select distinct pro5.value as CUSTOMERCODE, to_date('19000101000000', 'yyyymmddhh24miss') as LAST_UPDATE_TIME, pro2.value as NAME, nvl(pro3.value, '$$400006000004') as GENDER, decode(pro4.value, '$$400003000001', decode(length(trim(pro5.value)), 18, substr(trim(pro5.value), 7, 8)), null) as BIRTHDAY, decode(pro4.value, '$$400003000001', pro5.value, null) as CERT_NO, pro4.value as CERTIFICATE_TYPE, pro5.value as CERTIFICATE_NO, null as NATION, null as EFFECTIVE_DATE, null as EXPIRE_DATE from policy p inner join role r on p.topactualid = r.topactualid and r.kind = 'INSURANCECERTIFICATELIST' inner join property pro2 on r.topactualid = pro2.topactualid and r.actualid = pro2.parentactualid and r.parentagreementid = pro2.parentagreementid and r.topagreementid = pro2.topagreementid and pro2.kind = 'CUSTOMERNAME' inner join property pro3 on r.topactualid = pro3.topactualid and r.actualid = pro3.parentactualid and r.parentagreementid = pro3.parentagreementid and r.topagreementid = pro3.topagreementid and pro3.kind = 'PERSONSEX' inner join property pro4 on r.topactualid = pro4.topactualid and r.actualid = pro4.parentactualid and r.parentagreementid = pro4.parentagreementid and r.topagreementid = pro4.topagreementid and pro4.kind = 'CERTIFICATETYPE' inner join property pro5 on r.topactualid = pro5.topactualid and r.actualid = pro5.parentactualid and r.parentagreementid = pro5.parentagreementid and r.topagreementid = pro5.topagreementid and pro5.kind = 'CERTIFICATECODE' and pro5.value is not null left join endorsement e on p.endorsementid = e.endorsementid where p.productcode = '00070002' and p.currentflag = 'Y' and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and ((p.policystatus = '$$900001103001') or (e.endorsementstatus = '$$900002106001' and e.ISSUEDATE > to_date('20160411', 'YYYYMMDD')))执行计划：
Plan hash value: 3936231819 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 336 | 124K (1)| 00:24:55 | | 1 | HASH UNIQUE | | 1 | 336 | 124K (1)| 00:24:55 | | 2 | NESTED LOOPS | | 1 | 336 | 124K (1)| 00:24:55 | | 3 | NESTED LOOPS | | 1 | 284 | 124K (1)| 00:24:55 | | 4 | NESTED LOOPS | | 1 | 232 | 124K (1)| 00:24:55 | | 5 | NESTED LOOPS | | 51 | 9180 | 124K (1)| 00:24:50 | | 6 | NESTED LOOPS | | 14950 | 1868K| 11388 (1)| 00:02:17 | |* 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 701 | 58183 | 6559 (1)| 00:01:19 | |* 9 | TABLE ACCESS FULL | POLICY | 2661 | 129K| 5834 (1)| 00:01:11 | | 10 | TABLE ACCESS BY INDEX ROWID | ENDORSEMENT | 1 | 33 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | ENDORSEMENT_PK | 1 | | 0 (0)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID | ROLE | 21 | 945 | 25 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | TC_ROLE66 | 453 | | 4 (0)| 00:00:01 | |* 14 | TABLE ACCESS BY INDEX ROWID | PROPERTY | 1 | 52 | 124K (1)| 00:24:50 | | 15 | BITMAP CONVERSION TO ROWIDS | | | | | | | 16 | BITMAP AND | | | | | | | 17 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 18 | INDEX RANGE SCAN | TC_PROPERTY_PARENT | 12 | | 3 (0)| 00:00:01 | | 19 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 20 | INDEX RANGE SCAN | TC_PROPERTY24 | 12 | | 3 (0)| 00:00:01 | |* 21 | TABLE ACCESS BY INDEX ROWID | PROPERTY | 1 | 52 | 13 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | TC_PROPERTY_PARENT | 12 | | 3 (0)| 00:00:01 | |* 23 | TABLE ACCESS BY INDEX ROWID | PROPERTY | 1 | 52 | 3 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | TC_PROPERTY_PARENT | 12 | | 3 (0)| 00:00:01 | |* 25 | TABLE ACCESS BY INDEX ROWID | PROPERTY | 1 | 52 | 3 (0)| 00:00:01 | |* 26 | INDEX RANGE SCAN | TC_PROPERTY_PARENT | 12 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter("P"."POLICYSTATUS"='$$900001103001' OR "E"."ENDORSEMENTSTATUS"='$$900002106001' AND "E"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00') 9 - filter("P"."PRODUCTCODE"='00070002' AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."CURRENTFLAG"='Y') 11 - access("P"."ENDORSEMENTID"="E"."ENDORSEMENTID"(+)) 12 - filter("R"."KIND"='INSURANCECERTIFICATELIST') 13 - access("P"."TOPACTUALID"="R"."TOPACTUALID") 14 - filter("PRO5"."VALUE" IS NOT NULL AND "PRO5"."KIND"='CERTIFICATECODE' AND "R"."PARENTAGREEMENTID"="PRO5"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO5"."TOPAGREEMENTID") 18 - access("R"."ACTUALID"="PRO5"."PARENTACTUALID") 20 - access("R"."TOPACTUALID"="PRO5"."TOPACTUALID") 21 - filter("PRO2"."KIND"='CUSTOMERNAME' AND "R"."TOPACTUALID"="PRO2"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO2"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO2"."TOPAGREEMENTID") 22 - access("R"."ACTUALID"="PRO2"."PARENTACTUALID") 23 - filter("PRO4"."KIND"='CERTIFICATETYPE' AND "R"."TOPACTUALID"="PRO4"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO4"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO4"."TOPAGREEMENTID") 24 - access("R"."ACTUALID"="PRO4"."PARENTACTUALID") 25 - filter("PRO3"."KIND"='PERSONSEX' AND "R"."TOPACTUALID"="PRO3"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO3"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO3"."TOPAGREEMENTID") 26 - access("R"."ACTUALID"="PRO3"."PARENTACTUALID")分析：
1）执行计划中 id = 15 关键字为BITMAP CONVERSION TO ROWIDS，此关键字在此之前都未遇见过，于是谷歌下，有了以下解释：
1)出现这样的情况，是因为表中存在不适当的索引，这些索引列的唯一度不高，oracle就有可能选择两个这样的索引转为bitmap来执行
2)根据这两个索引的值再确认共同有的ROWID，最后再通过ROWID回表提取符合条件的数据。
2) 可以使用/*+ opt_param('_b_tree_bitmap_plans','false') */hint 在sql级消除bitmap
3） 也可以删除选择率低的索引，建立复合索引进行改善
4） 根据执行计划的谓词信息，我建立了如下索引：
create index IDX_POLICY_01 on POLICY (PRODUCTCODE, CURRENTFLAG, UNIQUECODE); create index IDX_PROPERTY_TEST02 on PROPERTY (KIND, PARENTACTUALID, TOPACTUALID, PARENTAGREEMENTID, TOPAGREEMENTID, VALUE) nologging;5）建立索引后的执行计划：
Plan hash value: 2001935116 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 336 | 10938 (1)| 00:02:12 | | 1 | HASH UNIQUE | | 1 | 336 | 10938 (1)| 00:02:12 | | 2 | NESTED LOOPS | | 1 | 336 | 10937 (1)| 00:02:12 | | 3 | NESTED LOOPS | | 1 | 284 | 10934 (1)| 00:02:12 | | 4 | NESTED LOOPS | | 1 | 232 | 10931 (1)| 00:02:12 | | 5 | NESTED LOOPS | | 1 | 180 | 10928 (1)| 00:02:12 | | 6 | NESTED LOOPS | | 875 | 109K| 8301 (1)| 00:01:40 | |* 7 | FILTER | | | | | | | 8 | NESTED LOOPS OUTER | | 300 | 24900 | 6500 (1)| 00:01:19 | |* 9 | TABLE ACCESS FULL | POLICY | 2776 | 135K| 5751 (1)| 00:01:10 | | 10 | TABLE ACCESS BY INDEX ROWID| ENDORSEMENT | 1 | 33 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | ENDORSEMENT_PK | 1 | | 0 (0)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID | ROLE | 3 | 135 | 6 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | TC_ROLE66 | 62 | | 3 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | IDX_PROPERTY_TEST02 | 1 | 52 | 3 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | IDX_PROPERTY_TEST02 | 1 | 52 | 3 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | IDX_PROPERTY_TEST02 | 1 | 52 | 3 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | IDX_PROPERTY_TEST02 | 1 | 52 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter("P"."POLICYSTATUS"='$$900001103001' OR "E"."ENDORSEMENTSTATUS"='$$900002106001' AND "E"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00') 9 - filter("P"."PRODUCTCODE"='00070002' AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."CURRENTFLAG"='Y') 11 - access("P"."ENDORSEMENTID"="E"."ENDORSEMENTID"(+)) 12 - filter("R"."KIND"='INSURANCECERTIFICATELIST') 13 - access("P"."TOPACTUALID"="R"."TOPACTUALID") 14 - access("R"."ACTUALID"="PRO4"."PARENTACTUALID" AND "R"."TOPACTUALID"="PRO4"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO4"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO4"."TOPAGREEMENTID" AND "PRO4"."KIND"='CERTIFICATETYPE') 15 - access("R"."ACTUALID"="PRO3"."PARENTACTUALID" AND "R"."TOPACTUALID"="PRO3"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO3"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO3"."TOPAGREEMENTID" AND "PRO3"."KIND"='PERSONSEX') 16 - access("R"."ACTUALID"="PRO2"."PARENTACTUALID" AND "R"."TOPACTUALID"="PRO2"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO2"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO2"."TOPAGREEMENTID" AND "PRO2"."KIND"='CUSTOMERNAME') 17 - access("R"."ACTUALID"="PRO5"."PARENTACTUALID" AND "R"."TOPACTUALID"="PRO5"."TOPACTUALID" AND "R"."PARENTAGREEMENTID"="PRO5"."PARENTAGREEMENTID" AND "R"."TOPAGREEMENTID"="PRO5"."TOPAGREEMENTID" AND "PRO5"."KIND"='CERTIFICATECODE') filter("PRO5"."VALUE" IS NOT NULL)建立索引后，sql从原来的450s降低到20s，buffers 消耗也显著降低。
转载于:https://www.cnblogs.com/wanbin/p/9514680.html
相关资源：JAVA上百实例源码以及开源项目