前段时间，优化了一些耗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 > then
'02'
when t2.amount < then
'03'
when t2.amount = 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
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 = :
and exists (select
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:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| | TABLE ACCESS BY INDEX ROWID | MM_DAILYREPORT_DETAIL_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_DAILYREPORT_DETAIL_TD | | | |:: | | | | |
| | HASH UNIQUE | | | | |:: | | K| K| K ()|
| | TABLE ACCESS BY INDEX ROWID | MM_SAP_VOUCHER_DETAIL_TO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | | | |:: | | | | |
| | HASH UNIQUE | | | | |:: | M| K| K| K ()|
|* | FILTER | | | | |:: | M| | | |
| | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | | | M|:: | M| | | |
| | NESTED LOOPS | | | | M|:: | M| | | |
| | NESTED LOOPS | | | | M|:: | M| | | |
| | MERGE JOIN CARTESIAN | | | | M|:: | | | | |
| | NESTED LOOPS SEMI | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | INLIST ITERATOR | | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_PAYMENTINE_07 | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | | | |:: | | | | |
| | BUFFER SORT | | | K| M|:: | | M| K| M ()|
|* | TABLE ACCESS FULL | MM_BATCHINFO_TD | | K| K|:: | | | | |
|* | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TI | M| | M|:: | M| | | |
|* | INDEX UNIQUE SCAN | PK_BATCHINFO_TI | M| | M|:: | M| | | |
|* | INDEX RANGE SCAN | IDX_PAYABLEMONEY_09 | M| | M|:: | M| | | |
|* | INDEX RANGE SCAN | IDX_PAYMENTINE_TEST | M| | |:: | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("D"."SEQREPORTNO"=TO_NUMBER(:B1))
- access("O"."DAILYAUDITNO"=:B1)
filter(TO_NUMBER("O"."BUSINESSNO")=:B1)
- filter(("T3"."OLDNO"="T4"."PAYABLENO" OR IS NOT NULL))
- access((("S"."STATUS"='' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='')
- access("T1"."ID"="S"."ID")
- access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
- access("T3"."NEWNO"="T2"."INPAYMENTID")
- access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
- filter("T5"."OPSTATUS"='0')
- filter("T6"."STATUS"='')
- access("T6"."ID"="T5"."SEQBATCH")
- access("T5"."SERIALNO"="T4"."CUSTSEQ")
- access("P"."LISTNO"=:B1 AND "P"."OLDNO"=:B2)
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 > then
'02'
when t2.amount < then
'03'
when t2.amount = 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
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 > then
'02'
when t2.amount < then
'03'
when t2.amount = 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
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
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:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| | SORT UNIQUE | | | | |:: | | | | ()|
| | UNION-ALL | | | | |:: | | | | |
| | NESTED LOOPS SEMI | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | INLIST ITERATOR | | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_PAYABLEMONEY_TD | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TD | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_BATCH_TD_SERIALNO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_BATCHINFO_TI_01 | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | | | |:: | | | | |
| | NESTED LOOPS SEMI | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | NESTED LOOPS | | | | |:: | | | | |
| | INLIST ITERATOR | | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_INPAYMENT_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_INPAYMENT_TD | | | |:: | | | | |
|* | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | | | |:: | | | | |
| | SORT UNIQUE | | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_PAYMENTINE_TEST | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | | | |:: | | | | |
|* | INDEX UNIQUE SCAN | PK_MM_PAYABLEMONEY_TD | | | |:: | | | | |
| | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TD | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_BATCH_TD_SERIALNO | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_BATCHINFO_TI_01 | | | |:: | | | | |
|* | INDEX RANGE SCAN | IDX_MM_SAP_VOUCHER_DETAIL_TO1 | | | |:: | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
- access("T1"."ID"="S"."ID")
- access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
- access("T3"."NEWNO"="T2"."INPAYMENTID")
- access("T3"."OLDNO"="T4"."PAYABLENO")
- access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
- access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
- access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
- access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
- access("T1"."ID"="S"."ID")
- access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
- filter("T3"."FATHERNO" IS NOT NULL)
- access("T3"."NEWNO"="T2"."INPAYMENTID")
- access("P"."LISTNO"="T3"."FATHERNO")
- access("T4"."PAYABLENO"="P"."OLDNO")
- access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
- access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
- access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
代入最新值，秒出结果。
后经开发检验，该sql大大改善了系统性能，cpu利用率也随之下降。