前段時間，優化了一些耗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使用率也随之下降。