前段时间，优化了一些耗buffer比较多的sql，但是CPU使用率还是没下来 。
查看操作系统CPU使用率
查看awr，发现又有一条超级耗性能的sql冒出来了。
该SQL每次执行耗费3e多个buffer，结果就是导致内存消耗高，cpu消耗也高。。。
利用工具PLSQL Developer，查询执行该SQL的session
数据库的等待事件为latch: cache buffers chains
SQL代码：
select distinct to_char(t5.summaryno) settlementNo,
to_char(t5.batchtype) settlementType,
to_char(s.writeouttype) certitype,
to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
to_char(t5.commissionpayno) CommissionPayNo,
to_char(t2.amount) payAmount,
to_char(t2.currencycode) payCurrency,
(case
when t5.unitcode = '012100' then
(select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
from mm_dailyreport_detail_td d
where d.seqreportno = t1.seqreportno)
else
to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
end) payDate,
to_char(s.batch_id) batchid,
t2.coreason payErrorMessage,
'' payedExchangedRate,
(select distinct o.voucherno
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno) accountingDocuments,
(case
when t2.amount > 0 then
'02'
when t2.amount < 0 then
'03'
when t2.amount = 0 then
'01'
end) payStatus,
to_char(t2.inpaymentid) ebankSequenceNo
from mm_writeout_to t1,
mm_inpayment_td t2,
mm_paymentin_events_td t3,
mm_payablemoney_td t4,
mm_batchinfo_td t5,
mm_batchinfo_ti t6,
mm_writeoutstatus_to s
where t2.inpaymentid = t1.businessno
and t1.id = s.id
and t3.newno = t2.inpaymentid
and ((t3.oldno = t4.payableno) or exists
(select 1
from mm_paymentin_events_td p
where p.listno = t3.fatherno
and t4.payableno = p.oldno))
and t6.id = t5.seqbatch
and t6.status = '2'
and t5.opstatus = '0'
and t5.serialno = t4.custseq
and s.batch_id = :1
and exists (select 1
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno)
and s.status in ('00', 'XX')该sql中有个变量值 s.batch_id = :1，利用系统视图dba_hist_sqlbind找出绑定变量值，在测试环境测试，发现每次执行都只要334个buffer
执行计划没错？只消耗334个buffer，而且1s内出结果，完全不像awr中记录的。
dba_hist_sqlbind：查询历史绑定变量信息, dba_hist_sqlbind的信息是从v$sql_bind_capture里面采集的。
v$sql_bind_capture view：只保存最后一次捕获SQL的变量信息，两次捕获之间的间隔为900s，受隐藏参数控制
再次利用视图v$sql_bind_capture抓取最新的值，代入sql中执行，发现sql卡住了。。
查看sql特殊执行计划
Plan hash value: 534203852
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | MM_DAILYREPORT_DETAIL_TD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_MM_DAILYREPORT_DETAIL_TD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 3 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.01 | 8 | 1036K| 1036K| 318K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | MM_SAP_VOUCHER_DETAIL_TO | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
|* 5 | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 6 | HASH UNIQUE | | 1 | 1 | 1 |00:10:57.87 | 335M| 751K| 751K| 359K (0)|
|* 7 | FILTER | | 1 | | 134 |00:27:07.96 | 335M| | | |
| 8 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 1 | 1 | 21M|00:26:00.03 | 335M| | | |
| 9 | NESTED LOOPS | | 1 | 1 | 86M|00:23:12.83 | 322M| | | |
| 10 | NESTED LOOPS | | 1 | 1 | 64M|00:13:59.99 | 193M| | | |
| 11 | MERGE JOIN CARTESIAN | | 1 | 1 | 64M|00:01:05.38 | 22421 | | | |
| 12 | NESTED LOOPS SEMI | | 1 | 1 | 134 |00:00:00.01 | 72 | | | |
| 13 | NESTED LOOPS | | 1 | 1 | 134 |00:00:00.01 | 65 | | | |
| 14 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 14 | | | |
| 15 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | | | |
| 16 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 7 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO | 2 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 18 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | 2 | 1 | 1 |00:00:00.01 | 6 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 2 | 134 |00:00:00.01 | 51 | | | |
|* 24 | INDEX RANGE SCAN | IDX_PAYMENTINE_07 | 1 | 2 | 134 |00:00:00.01 | 4 | | | |
|* 25 | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | 1 | 4538 | 1 |00:00:00.01 | 7 | | | |
| 26 | BUFFER SORT | | 134 | 472K| 64M|00:00:00.83 | 22349 | 56M| 2617K| 49M (0)|
|* 27 | TABLE ACCESS FULL | MM_BATCHINFO_TD | 1 | 472K| 481K|00:00:00.01 | 22349 | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TI | 64M| 1 | 64M|00:11:32.52 | 193M| | | |
|* 29 | INDEX UNIQUE SCAN | PK_BATCHINFO_TI | 64M| 1 | 64M|00:06:50.50 | 129M| | | |
|* 30 | INDEX RANGE SCAN | IDX_PAYABLEMONEY_09 | 64M| 1 | 21M|00:08:45.52 | 129M| | | |
|* 31 | INDEX RANGE SCAN | IDX_PAYMENTINE_TEST | 21M| 1 | 0 |00:00:22.85 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."SEQREPORTNO"=TO_NUMBER(:B1))
5 - access("O"."DAILYAUDITNO"=:B1)
filter(TO_NUMBER("O"."BUSINESSNO")=:B1)
7 - filter(("T3"."OLDNO"="T4"."PAYABLENO" OR IS NOT NULL))
18 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219626143')
20 - access("T1"."ID"="S"."ID")
22 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
24 - access("T3"."NEWNO"="T2"."INPAYMENTID")
25 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
27 - filter("T5"."OPSTATUS"='0')
28 - filter("T6"."STATUS"='2')
29 - access("T6"."ID"="T5"."SEQBATCH")
30 - access("T5"."SERIALNO"="T4"."CUSTSEQ")
31 - access("P"."LISTNO"=:B1 AND "P"."OLDNO"=:B2)
67 rows selected.id=11处 ，a-rows并不是0条，而是有64000000条记录。他的父级 的连接方式是 NEST LOOP，
也就是说被驱动表MM_BATCHINFO_TI表要被扫描64000000次。。。
定位到问题点，现在就开始优化吧。
尝试优化1：
利用hint（/+ OPT_PARAM(‘_optimizer_mjc_enabled’,’false’) /）禁用笛卡尔积
不见效果，此优化方法失败。
尝试优化2：
利用hint 走hash的连接方式
不见效果，此优化方法也失败。
尝试优化3：
从sql代码中看出，该SQLwhere条件又or子查询，尝试利用union改写or
select distinct to_char(t5.summaryno) settlementNo,
to_char(t5.batchtype) settlementType,
to_char(s.writeouttype) certitype,
to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
to_char(t5.commissionpayno) CommissionPayNo,
to_char(t2.amount) payAmount,
to_char(t2.currencycode) payCurrency,
(case
when t5.unitcode = '012100' then
(select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
from mm_dailyreport_detail_td d
where d.seqreportno = t1.seqreportno)
else
to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
end) payDate,
to_char(s.batch_id) batchid,
t2.coreason payErrorMessage,
'' payedExchangedRate,
(select distinct o.voucherno
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno) accountingDocuments,
(case
when t2.amount > 0 then
'02'
when t2.amount < 0 then
'03'
when t2.amount = 0 then
'01'
end) payStatus,
to_char(t2.inpaymentid) ebankSequenceNo
from mm_writeout_to t1,
mm_inpayment_td t2,
mm_paymentin_events_td t3,
mm_payablemoney_td t4,
mm_batchinfo_td t5,
mm_batchinfo_ti t6,
mm_writeoutstatus_to s
where t2.inpaymentid = t1.businessno
and t1.id = s.id
and t3.newno = t2.inpaymentid
and t3.oldno = t4.payableno
and t6.id = t5.seqbatch
and t6.status = '2'
and t5.opstatus = '0'
and t5.serialno = t4.custseq
and s.batch_id = '1219639828'
and exists (select 1
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno)
and s.status in ('00', 'XX')
union
select distinct to_char(t5.summaryno) settlementNo,
to_char(t5.batchtype) settlementType,
to_char(s.writeouttype) certitype,
to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
to_char(t5.commissionpayno) CommissionPayNo,
to_char(t2.amount) payAmount,
to_char(t2.currencycode) payCurrency,
(case
when t5.unitcode = '012100' then
(select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
from mm_dailyreport_detail_td d
where d.seqreportno = t1.seqreportno)
else
to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
end) payDate,
to_char(s.batch_id) batchid,
t2.coreason payErrorMessage,
'' payedExchangedRate,
(select distinct o.voucherno
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno) accountingDocuments,
(case
when t2.amount > 0 then
'02'
when t2.amount < 0 then
'03'
when t2.amount = 0 then
'01'
end) payStatus,
to_char(t2.inpaymentid) ebankSequenceNo
from mm_writeout_to t1,
mm_inpayment_td t2,
mm_paymentin_events_td t3,
mm_payablemoney_td t4,
mm_batchinfo_td t5,
mm_batchinfo_ti t6,
mm_writeoutstatus_to s
where t2.inpaymentid = t1.businessno
and t1.id = s.id
and t3.newno = t2.inpaymentid
and exists
(select 1
from mm_paymentin_events_td p
where p.listno = t3.fatherno
and t4.payableno = p.oldno)
and t6.id = t5.seqbatch
and t6.status = '2'
and t5.opstatus = '0'
and t5.serialno = t4.custseq
and s.batch_id = '1219639828'
and exists (select 1
from mm_sap_voucher_detail_to o
where o.businessno = t2.inpaymentid
and o.dailyauditno = t3.dailyauditno)
and s.status in ('00', 'XX')
--执行计划：
Plan hash value: 1779884842
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT UNIQUE | | 1 | 2 | 1 |00:00:00.01 | 1212 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | 134 |00:00:00.01 | 1212 | | | |
| 3 | NESTED LOOPS SEMI | | 1 | 1 | 134 |00:00:00.01 | 1145 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 134 |00:00:00.01 | 1138 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 134 |00:00:00.01 | 867 | | | |
| 6 | NESTED LOOPS | | 1 | 1 | 134 |00:00:00.01 | 463 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 134 |00:00:00.01 | 59 | | | |
| 8 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 14 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | | | |
| 10 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 7 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO | 2 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 12 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | 2 | 1 | 1 |00:00:00.01 | 6 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 16 | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 13 | 134 |00:00:00.01 | 45 | | | |
|* 18 | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | 1 | 13 | 134 |00:00:00.01 | 4 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 134 | 1 | 134 |00:00:00.01 | 404 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_MM_PAYABLEMONEY_TD | 134 | 1 | 134 |00:00:00.01 | 270 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TD | 134 | 1 | 134 |00:00:00.01 | 404 | | | |
|* 22 | INDEX RANGE SCAN | IDX_BATCH_TD_SERIALNO | 134 | 1 | 134 |00:00:00.01 | 270 | | | |
|* 23 | INDEX RANGE SCAN | IDX_BATCHINFO_TI_01 | 134 | 1 | 134 |00:00:00.01 | 271 | | | |
|* 24 | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | 1 | 2732 | 1 |00:00:00.01 | 7 | | | |
| 25 | NESTED LOOPS SEMI | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 26 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 27 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 28 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 29 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 30 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 59 | | | |
| 31 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 14 | | | |
| 32 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | | | |
| 33 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 7 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO | 2 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 35 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | 2 | 1 | 1 |00:00:00.01 | 6 | | | |
| 36 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 37 | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 39 | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 2 | 0 |00:00:00.01 | 45 | | | |
|* 41 | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | 1 | 13 | 134 |00:00:00.01 | 4 | | | |
| 42 | SORT UNIQUE | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 43 | INDEX RANGE SCAN | IDX_PAYMENTINE_TEST | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 44 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 45 | INDEX UNIQUE SCAN | PK_MM_PAYABLEMONEY_TD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 46 | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 47 | INDEX RANGE SCAN | IDX_BATCH_TD_SERIALNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 48 | INDEX RANGE SCAN | IDX_BATCHINFO_TI_01 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 49 | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | 0 | 2963 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
14 - access("T1"."ID"="S"."ID")
16 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
18 - access("T3"."NEWNO"="T2"."INPAYMENTID")
20 - access("T3"."OLDNO"="T4"."PAYABLENO")
22 - access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
23 - access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
24 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
35 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
37 - access("T1"."ID"="S"."ID")
39 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
40 - filter("T3"."FATHERNO" IS NOT NULL)
41 - access("T3"."NEWNO"="T2"."INPAYMENTID")
43 - access("P"."LISTNO"="T3"."FATHERNO")
45 - access("T4"."PAYABLENO"="P"."OLDNO")
47 - access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
48 - access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
49 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
代入最新值，秒出结果。
后经开发检验，该sql大大改善了系统性能，cpu利用率也随之下降。