一次效能問題原因查詢
原創 轉載請註明出處
（我的故障處理）
我對今天POMS資料庫的故障進行了分析，原因如下：
我取出了程式中的一個重要的片段，而整個語句基本都是由這個語句拼湊起來我先叫它主SQL，所以如果這個片段能夠最佳化效果會很好，片段如下
select decode(sum(pf.period_prem),
null,
0,
sum(pf.period_prem))--sum(pf.period_prem)
from t_product_fee pf,
t_contract_master cm,
t_product_life pl
where pf.policy_id = cm.policy_id
and cm.validate_date <
ADD_MONTHS(to_date('200911', 'yyyymm'), -13)--13
and cm.validate_date >=
ADD_MONTHS(to_date('200911', 'yyyymm'), -(13+12))--14
and pf.charge_type = 1 --不為躉交,小繳別暫不考慮and pf.charge_type <> 5
and pf.product_id = pl.product_id
and pkg_life_calc_basic_comm.f_is_long_product(pl.product_id) = 0
and pf.fee_type in (41, 47)
and pf.policy_year = 1
and pkg_life_calc_basic_comm.f_is_effect(pf.list_id) = '0'
and (('3' = '1' and pf.benefit_type <> 41) or
('3' = '2' and pf.benefit_type = 41) or
('3' = '3'))
and (('0' ='7' and cm.nb_agency_id in (select agency_id from t_agency start with agency_id=1 connect by parent_id = prior agency_id) or
'0' <>'7'))
and cm.organ_id in
(select organ_id
from t_company_organ
start with organ_id = 1
connect by parent_id = prior organ_id)
and cm.sell_way = 0
and cm.policy_type = 1;
我檢視了這個語句的執行計劃，並未發現明顯的問題，然後對這個語句進行了SQL_TRACE，然後打出了報告，發現問題的實質在於這個語句
pkg_life_calc_basic_comm.f_is_effect，pkg_life_calc_basic_comm.f_is_long_product進行呼叫的PL/SQL，TRACE報告中給出了4個引起效能
問題根源的片段如下：
SELECT COUNT(CM.POLICY_ID)
FROM
T_PRODUCT_FEE PF, T_CONTRACT_MASTER CM WHERE CM.PREM_STATUS IN (2, 3, 4) AND
PF.POLICY_ID = CM.POLICY_ID AND PF.LIST_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 43136 1.77 1.80 0 0 0 0
Fetch 43136 2.31 2.16 0 258816 0 43136
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 86273 4.08 3.97 0 258816 0 43136
SELECT COUNT(CP.ITEM_ID)
FROM
T_PRODUCT_FEE PF, T_CONTRACT_PRODUCT CP WHERE CP.LIABILITY_STATE = 3 AND
CP.END_CAUSE IN (1, 2, 8, 10, 11,13,14) AND PF.PRODUCT_NUM = CP.PRODUCT_NUM
AND PF.INSURED_1 = CP.INSURED_1 AND PF.POLICY_ID = CP.POLICY_ID AND
PF.LIST_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 43106 1.51 1.60 0 0 0 0
Fetch 43106 2.29 2.17 0 258604 0 43106
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 86213 3.80 3.78 0 258604 0 43106
SELECT COUNT(CPL.CHANGE_ID)
FROM
T_PRODUCT_FEE PF,T_POLICY_CHANGE PC, T_CONTRACT_PRODUCT_LOG CPL WHERE
PF.POLICY_ID = PC.POLICY_ID AND PC.SERVICE_ID=213 AND PC.CHANGE_STATUS=3
AND PC.CHANGE_ID=CPL.CHANGE_ID AND PF.PRODUCT_NUM = CPL.PRODUCT_NUM AND
PF.PRODUCT_ID = CPL.PRODUCT_ID AND PF.POLICY_ID = CPL.POLICY_ID AND
PF.LIST_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 43024 1.56 1.60 0 0 0 0
Fetch 43024 11.96 11.69 0 1518816 0 43024
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 86049 13.52 13.30 0 1518816 0 43024
SELECT COUNT(PF2.LIST_ID)
FROM
T_PRODUCT_FEE PF, T_PRODUCT_FEE PF2,T_POLICY_CHANGE PC WHERE PF.PRODUCT_NUM =
PF2.PRODUCT_NUM AND PF.INSURED_1 = PF2.INSURED_1 AND PF.POLICY_ID =
PF2.POLICY_ID AND PF2.FEE_TYPE = 42 AND PF2.CHANGE_ID=PC.CHANGE_ID AND
PC.SERVICE_ID=201 AND PC.CHANGE_STATUS=3 AND PF.LIST_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 42962 2.26 2.02 0 0 0 0
Fetch 42962 3.03 2.88 0 307000 0 42962
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 85925 5.29 4.90 0 307000 0 42962
可以看出每執行一次主語句，這4個語句都會執行40000多次，如果整個SQL語句由5個主SQL的拼湊起來，那查詢1個代理人
這4個語句都會執行200000次，如果代理人為1000個那這4個語句就會執行200000000次，但是單重這4個語句的執行計劃來看
都沒有大的問題都是透過索引進行查詢然後透過ROWID進行關聯到行資料未出現FULL TABLE SCAN，所以我初步覺得對單個SQL
的調整視乎得不到好的效果。
同時在AWRRPT中也說明了這一點如下：
SQL ordered by Executions
- Total Executions: 31,614,801
- Captured SQL account for 100.0% of Total
|
Executions |
Rows Processed |
Rows per Exec |
CPU per Exec (s) |
Elap per Exec (s) |
SQL Id |
SQL Module |
SQL Text |
|
6,935,338 |
6,934,462 |
1.00 |
0.00 |
0.00 |
|
|
SELECT COUNT(CM.POLICY_ID) FRO... |
|
6,927,506 |
6,925,527 |
1.00 |
0.00 |
0.00 |
|
SELECT COUNT(CP.ITEM_ID) FROM ... | |
|
6,911,058 |
6,909,436 |
1.00 |
0.00 |
0.00 |
|
|
SELECT COUNT(CPL.CHANGE_ID) FR... |
|
6,902,051 |
6,901,181 |
1.00 |
0.00 |
0.00 |
|
|
SELECT COUNT(PF2.LIST_ID) FROM... |
|
1,526,231 |
1,526,182 |
1.00 |
0.00 |
0.00 |
|
|
SELECT PF.POLICY_ID, PF.PRODU... |
|
1,454,164 |
1,454,138 |
1.00 |
0.00 |
0.00 |
|
|
SELECT COUNT(1) FROM T_PRODUCT... |
|
855,607 |
855,584 |
1.00 |
0.00 |
0.00 |
|
|
SELECT MAX(PF.LIST_ID) FROM T_... |
|
40,836 |
40,836 |
1.00 |
0.00 |
0.00 |
|
|
SELECT PC.CHANGE_STATUS FROM T... |
|
18,887 |
18,887 |
1.00 |
0.00 |
0.00 |
|
|
SELECT COUNT(DISTINCT PP.PRODU... |
|
8,640 |
9,854 |
1.14 |
0.00 |
0.00 |
|
|
select intcol#, nvl(pos#, 0), ... |
在出問題的一段時間內TOP 4 EXECUTIONS 語句就是我剛才給出的4個片段，請注意上圖中的前4位。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-623403/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一次奇怪的StackOverflowError問題查詢之旅Error
- 一次慢查詢暴露的隱蔽問題
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- 【恩墨學院】一次由查詢轉換引起的效能問題的分析
- Oracle多層級查詢相容的效能問題Oracle
- BIEB:關於CRM系統查詢效能問題
- 查詢鎖的原因
- 記一次資料庫查詢超時優化問題資料庫優化
- sql 模糊查詢問題SQL
- Xilinx問題查詢
- 斷號查詢問題
- 一次快取效能問題排查快取
- Laravel5.7 查詢問題Laravel
- sphinx查詢過濾問題
- 批次分頁查詢問題?
- hibernate批量查詢問題
- sql 查詢條件問題SQL
- 一個使用JDBC按Date查詢查詢的問題JDBC
- 一次容器MySQL的效能問題排查MySql
- 【案例】常駐查詢引發的thread pool 效能問題之二thread
- 【案例】常駐查詢引發的thread pool 效能問題之一thread
- SQLAlchemy in 查詢空列表問題分析SQL
- 分頁查詢的排序問題排序
- 模板中的名字查詢問題
- Hibernate的Criteria查詢問題。
- 批次查詢的翻頁問題
- 求助：DetachedCriteria關聯查詢問題~~
- HBase一次慢查詢請求的問題排查與解決過程
- leetcode題解(查詢表問題)LeetCode
- 記一次Prometheus代理效能優化問題Prometheus優化
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- 查詢oracle效能SQLOracleSQL
- 提高SQL查詢效能SQL
- SQL查詢效能分析SQL
- with as 查詢效能記載
- 資料庫查詢慢的原因資料庫
- 查詢Library Cache Pin等待原因
- MySQL鎖表相關問題查詢思路MySql