最近发现报表系统上有一存储过程越来越慢，在数据库中查询后，发现有以下条SQL
--优化前：耗时>1h
select c.policyno,
c.endorseno,
r.item_code,
sum(r.outstanding_amount - r.settled_amount - r.offset_amount) OS_amount
from c_reserve_list@aclaim r
left join c_claim@aclaim c
on c.claimno = r.claim_no
where r.busi_phase in
(select max(busi_phase)
from rpt_st.c_reserve_list
where claim_no = r.claim_no
and last_modify_date =
(select max(last_modify_date)
from rpt_st.c_reserve_list
where claim_no = r.claim_no
and last_modify_date < to_date(V_ENDDATE, 'yyyymmdd')))
and r.count =
(select max(count)
from rpt_st.c_reserve_list
where claim_no = r.claim_no
and busi_phase = r.busi_phase
and count in
(select
count
from rpt_st.c_reserve_list
where claim_no = r.claim_no
and busi_phase = r.busi_phase
and last_modify_date < to_date(V_ENDDATE, 'yyyymmdd')))
group by c.policyno, c.endorseno, r.item_code;
--执行计划
Plan hash value: 3027187983
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22383 | 6251K| | 6751M (1)|999:59:59 | | |
| 1 | HASH GROUP BY | | 22383 | 6251K| 408G| 6751M (1)|999:59:59 | | |
|* 2 | FILTER | | | | | | | | |
|* 3 | HASH JOIN | | 1417M| 377G| 23M| 97M (1)|324:16:14 | | |
|* 4 | HASH JOIN RIGHT OUTER| | 144K| 22M| | 605 (2)| 00:00:08 | | |
| 5 | REMOTE | C_CLAIM | 5527 | 356K| | 153 (1)| 00:00:02 | AUTOC~ | R->S |
| 6 | REMOTE | C_RESERVE_LIST | 144K| 12M| | 450 (2)| 00:00:06 | AUTOC~ | R->S |
| 7 | VIEW | VW_SQ_1 | 1417M| 166G| | 88M (1)|293:20:25 | | |
| 8 | HASH GROUP BY | | 1417M| 248G| 601G| 88M (1)|293:20:25 | | |
|* 9 | HASH JOIN | | 1735M| 303G| 14M| 19266 (88)| 00:03:52 | | |
| 10 | REMOTE | C_RESERVE_LIST | 144K| 12M| | 450 (2)| 00:00:06 | NEWAU~ | R->S |
| 11 | REMOTE | C_RESERVE_LIST | 144K| 12M| | 450 (2)| 00:00:06 | NEWAU~ | R->S |
| 12 | SORT AGGREGATE | | 1 | 61 | | | | | |
|* 13 | FILTER | | | | | | | | |
| 14 | REMOTE | C_RESERVE_LIST | 1 | 61 | | 3 (0)| 00:00:01 | NEWAU~ | R->S |
| 15 | SORT AGGREGATE | | 1 | 51 | | | | | |
| 16 | REMOTE | C_RESERVE_LIST | 32 | 1632 | | 3 (0)| 00:00:01 | NEWAU~ | R->S |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("R"."BUSI_PHASE"= (SELECT MAX("BUSI_PHASE") FROM "A1" WHERE "LAST_MODIFY_DATE"= (SELECT
MAX("LAST_MODIFY_DATE") FROM "A1" WHERE "CLAIM_NO"=:B1 AND "LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26
00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "CLAIM_NO"=:B2))
3 - access("R"."COUNT"="VW_COL_1" AND "CLAIM_NO"="R"."CLAIM_NO" AND "BUSI_PHASE"="R"."BUSI_PHASE" AND
"CLAIM_NO"="R"."CLAIM_NO" AND "BUSI_PHASE"="R"."BUSI_PHASE")
4 - access("C"."CLAIMNO"(+)="R"."CLAIM_NO")
9 - access("COUNT"="COUNT")
13 - filter("LAST_MODIFY_DATE"= (SELECT MAX("LAST_MODIFY_DATE") FROM "A1" WHERE "CLAIM_NO"=:B1 AND
"LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT "CLAIMNO","POLICYNO","ENDORSENO" FROM "C_CLAIM" "C" (accessing 'aclaim' )
6 - SELECT "CLAIM_NO","ITEM_CODE","OUTSTANDING_AMOUNT","SETTLED_AMOUNT","OFFSET_AMOUNT","BUSI_PHASE","CO
UNT" FROM "C_RESERVE_LIST" "SYS_ALIAS_2" (accessing 'aclaim' )
10 - SELECT "CLAIM_NO","LAST_MODIFY_DATE","BUSI_PHASE","COUNT" FROM "aclaim"."C_RESERVE_LIST" "A2"
WHERE "LAST_MODIFY_DATE"<:1 (accessing 'NEWaclaim' )
11 - SELECT "CLAIM_NO","BUSI_PHASE","COUNT" FROM "aclaim"."C_RESERVE_LIST" "A1" (accessing
'NEWaclaim' )
14 - SELECT "CLAIM_NO","LAST_MODIFY_DATE","BUSI_PHASE" FROM "aclaim"."C_RESERVE_LIST" "A1" WHERE
"CLAIM_NO"=:1 (accessing 'NEWaclaim' )
16 - SELECT "CLAIM_NO","LAST_MODIFY_DATE" FROM "aclaim"."C_RESERVE_LIST" "A1" WHERE "CLAIM_NO"=:1 AND
"LAST_MODIFY_DATE"<:2 (accessing 'NEWaclaim' )
分析
--在以上执行计划中可以看出该SQL，查询的都是远程表。所以可以改写成以下形式,然后在远程库直接执行。
select c.policyno,
c.endorseno,
r.item_code,
sum(r.outstanding_amount - r.settled_amount - r.offset_amount) OS_amount
from aclaim.c_reserve_list r
left join aclaim.c_claim c
on c.claimno = r.claim_no
where r.busi_phase in
(select max(busi_phase)
from aclaim.c_reserve_list
where claim_no = r.claim_no
and last_modify_date =
(select max(last_modify_date)
from aclaim.c_reserve_list
where claim_no = r.claim_no
and last_modify_date < to_date(20170626, 'yyyymmdd')))
and r.count =
(select max(count)
from aclaim.c_reserve_list
where claim_no = r.claim_no
and busi_phase = r.busi_phase
and count in
(select
count
from aclaim.c_reserve_list
where claim_no = r.claim_no
and busi_phase = r.busi_phase
and last_modify_date < to_date(20170626, 'yyyymmdd')))
group by c.policyno, c.endorseno, r.item_code;
--执行计划如下：
Plan hash value: 897376705
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22383 | 1508K| | 15M (2)| 52:55:06 |
| 1 | HASH GROUP BY | | 22383 | 1508K| 22M| 15M (2)| 52:55:06 |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 144K| 9723K| | 536 (3)| 00:00:07 |
| 4 | VIEW | index$_join$_002 | 5527 | 161K| | 83 (0)| 00:00:02 |
|* 5 | HASH JOIN | | | | | | |
| 6 | INDEX FAST FULL SCAN| IDX_C_CLAIM_03 | 5527 | 161K| | 46 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN| PK_C_CLAIM | 5527 | 161K| | 36 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | C_RESERVE_LIST | 144K| 5496K| | 450 (2)| 00:00:06 |
| 9 | SORT AGGREGATE | | 1 | 24 | | | |
|* 10 | FILTER | | | | | | |
|* 11 | TABLE ACCESS FULL | C_RESERVE_LIST | 32 | 768 | | 449 (2)| 00:00:06 |
| 12 | SORT AGGREGATE | | 1 | 21 | | | |
|* 13 | TABLE ACCESS FULL | C_RESERVE_LIST | 32 | 672 | | 449 (2)| 00:00:06 |
| 14 | SORT AGGREGATE | | 1 | 46 | | | |
|* 15 | HASH JOIN | | 3 | 138 | | 901 (3)| 00:00:11 |
|* 16 | TABLE ACCESS FULL| C_RESERVE_LIST | 2 | 54 | | 450 (2)| 00:00:06 |
|* 17 | TABLE ACCESS FULL| C_RESERVE_LIST | 2 | 38 | | 450 (2)| 00:00:06 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("R"."BUSI_PHASE"= (SELECT MAX("BUSI_PHASE") FROM "aclaim"."C_RESERVE_LIST"
"C_RESERVE_LIST" WHERE "LAST_MODIFY_DATE"= (SELECT MAX("LAST_MODIFY_DATE") FROM
"aclaim"."C_RESERVE_LIST" "C_RESERVE_LIST" WHERE "CLAIM_NO"=:B1 AND
"LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND
"CLAIM_NO"=:B2) AND "R"."COUNT"= (SELECT MAX("COUNT") FROM "aclaim"."C_RESERVE_LIST"
"C_RESERVE_LIST","aclaim"."C_RESERVE_LIST" "C_RESERVE_LIST" WHERE "CLAIM_NO"=:B3 AND
"BUSI_PHASE"=:B4 AND "LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "COUNT"="COUNT" AND "CLAIM_NO"=:B5 AND "BUSI_PHASE"=:B6))
3 - access("C"."CLAIMNO"(+)="R"."CLAIM_NO")
5 - access(ROWID=ROWID)
10 - filter("LAST_MODIFY_DATE"= (SELECT MAX("LAST_MODIFY_DATE") FROM
"aclaim"."C_RESERVE_LIST" "C_RESERVE_LIST" WHERE "CLAIM_NO"=:B1 AND
"LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
11 - filter("CLAIM_NO"=:B1)
13 - filter("CLAIM_NO"=:B1 AND "LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
15 - access("COUNT"="COUNT")
16 - filter("CLAIM_NO"=:B1 AND "BUSI_PHASE"=:B2 AND "LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
17 - filter("CLAIM_NO"=:B1 AND "BUSI_PHASE"=:B2)
--执行计划中有filter关键字且有两个子级，一般来说是很耗费性能。
--如果不进行改写，根据自己的经验，最有效的方式就是在执行计划中谓词信息里找出有绑定变量的字段并建立索引。
--所以建立如下索引：
create index IDX_C_RESERVE_LIST_TEST on C_RESERVE_LIST(CLAIM_NO,BUSI_PHASE,LAST_MODIFY_DATE);
执行SQL，10s内能返回全部结果。
但是，在报表库上运行时，发现SQL的性能还是没改善。
再次分析执行计划：
发现此sql运行时，采用多个dblink访问远程库。查看dblink的元数据：
CREATE OR REPLACE SYNONYM "rpt_st"."C_RESERVE_LIST" FOR "aclaim"."C_RESERVE_LIST"@"NEWaclaim";
create public database link aclaim connect to aclaim using 'DOICLC';
create public database link NEWaclaim connect to aclaim using 'DOICLC';
发现两个DBLINK都是访问AUTOCLAIM用户下的表。
这种方式很容易造成sql不能走正确执行计划，所以可以把sql中的dblink改写成同一个。
再次执行，10s内能返回全部结果。
SELECT C.POLICYNO,
C.ENDORSENO,
R.ITEM_CODE,
SUM(R.OUTSTANDING_AMOUNT - R.SETTLED_AMOUNT - R.OFFSET_AMOUNT) OS_AMOUNT
FROM rpt_st.C_RESERVE_LIST R
LEFT JOIN rpt_st.C_CLAIM C
ON C.CLAIMNO = R.CLAIM_NO
WHERE R.BUSI_PHASE IN
(SELECT MAX(BUSI_PHASE)
FROM rpt_st.C_RESERVE_LIST
WHERE CLAIM_NO = R.CLAIM_NO
AND LAST_MODIFY_DATE =
(SELECT MAX(LAST_MODIFY_DATE)
FROM rpt_st.C_RESERVE_LIST
WHERE CLAIM_NO = R.CLAIM_NO
AND LAST_MODIFY_DATE < TO_DATE('20170626', 'yyyymmdd')))
AND R.COUNT =
(SELECT MAX(COUNT)
FROM rpt_st.C_RESERVE_LIST
WHERE CLAIM_NO = R.CLAIM_NO
AND BUSI_PHASE = R.BUSI_PHASE
AND COUNT IN
(SELECT COUNT
FROM rpt_st.C_RESERVE_LIST
WHERE CLAIM_NO = R.CLAIM_NO
AND BUSI_PHASE = R.BUSI_PHASE
AND LAST_MODIFY_DATE < TO_DATE('20170626', 'yyyymmdd')))
GROUP BY C.POLICYNO, C.ENDORSENO, R.ITEM_CODE
Plan hash value: 2524269579
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 22383 | 1508K| | 109K (1)| 00:21:55 | |
| 1 | HASH GROUP BY | | 22383 | 1508K| 22M| 109K (1)| 00:21:55 | |
|* 2 | FILTER | | | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 144K| 9723K| | 536 (3)| 00:00:07 | |
| 4 | VIEW | index$_join$_002 | 5527 | 161K| | 83 (0)| 00:00:02 | DOICLC |
|* 5 | HASH JOIN | | | | | | | |
| 6 | INDEX FAST FULL SCAN | IDX_C_CLAIM_03 | 5527 | 161K| | 46 (0)| 00:00:01 | DOICLC |
| 7 | INDEX FAST FULL SCAN | PK_C_CLAIM | 5527 | 161K| | 36 (0)| 00:00:01 | DOICLC |
| 8 | TABLE ACCESS FULL | C_RESERVE_LIST | 144K| 5496K| | 450 (2)| 00:00:06 | DOICLC |
| 9 | SORT AGGREGATE | | 1 | 24 | | | | |
| 10 | FIRST ROW | | 1 | 24 | | 3 (0)| 00:00:01 | |
|* 11 | INDEX RANGE SCAN (MIN/MAX) | IDX_C_RESERVE_LIST_TEST | 1 | 24 | | 3 (0)| 00:00:01 | DOICLC |
| 12 | SORT AGGREGATE | | 1 | 21 | | | | |
|* 13 | INDEX RANGE SCAN | IDX_C_RESERVE_LIST_TEST | 32 | 672 | | 3 (0)| 00:00:01 | DOICLC |
| 14 | SORT AGGREGATE | | 1 | 46 | | | | |
|* 15 | HASH JOIN | | 3 | 138 | | 9 (12)| 00:00:01 | |
| 16 | TABLE ACCESS BY INDEX ROWID| C_RESERVE_LIST | 2 | 54 | | 4 (0)| 00:00:01 | DOICLC |
|* 17 | INDEX RANGE SCAN | IDX_C_RESERVE_LIST_TEST | 2 | | | 3 (0)| 00:00:01 | DOICLC |
| 18 | TABLE ACCESS BY INDEX ROWID| C_RESERVE_LIST | 2 | 38 | | 4 (0)| 00:00:01 | DOICLC |
|* 19 | INDEX RANGE SCAN | IDX_C_RESERVE_LIST_TEST | 2 | | | 3 (0)| 00:00:01 | DOICLC |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A2"."BUSI_PHASE"= (SELECT MAX("A4"."BUSI_PHASE") FROM "aclaim"."C_RESERVE_LIST" "A4" WHERE
"A4"."CLAIM_NO"=:B1 AND "A4"."LAST_MODIFY_DATE"= (SELECT MAX("A5"."LAST_MODIFY_DATE") FROM
"aclaim"."C_RESERVE_LIST" "A5" WHERE "A5"."CLAIM_NO"=:B2 AND "A5"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND "A2"."COUNT"= (SELECT MAX("A3"."COUNT") FROM "aclaim"."C_RESERVE_LIST"
"A3","aclaim"."C_RESERVE_LIST" "A6" WHERE "A6"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A6"."BUSI_PHASE"=:B3 AND "A6"."CLAIM_NO"=:B4 AND "A3"."BUSI_PHASE"=:B5 AND "A3"."CLAIM_NO"=:B6 AND
"A3"."COUNT"="A6"."COUNT"))
3 - access("A1"."CLAIMNO"(+)="A2"."CLAIM_NO")
5 - access(ROWID=ROWID)
11 - access("A4"."CLAIM_NO"=:B1)
filter("A4"."LAST_MODIFY_DATE"= (SELECT MAX("A5"."LAST_MODIFY_DATE") FROM "aclaim"."C_RESERVE_LIST" "A5"
WHERE "A5"."CLAIM_NO"=:B1 AND "A5"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
13 - access("A5"."CLAIM_NO"=:B1 AND "A5"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("A5"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 - access("A3"."COUNT"="A6"."COUNT")
17 - access("A6"."CLAIM_NO"=:B1 AND "A6"."BUSI_PHASE"=:B2 AND "A6"."LAST_MODIFY_DATE"<TO_DATE(' 2017-06-26
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - access("A3"."CLAIM_NO"=:B1 AND "A3"."BUSI_PHASE"=:B2)
Note
-----
- fully remote statement
![多表优化及避免索引失效原则 及 一些其他优化方法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)