检查16号上午8:00-12:00的数据库报告,发现一条SQL语句执行时间为1977s,逻辑读为103,491,288。
Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|
21,756 | 16,342 | 11 | 1977.81 | 71.70 | bw2py1pg93nrd | SELECT * FROM GG_TASK_WORK_S... | |
337 | 313 | 5,306 | 0.06 | 1.11 | 1tgdyrsn703jk | SELECT PO.* FROM OPERATION... |
SQL语句为:
SELECT *
FROM GG_WORK_SHEET A
WHERE (A.IS_COMPLETE = 0 OR A.IS_COMPLETE = 1)
AND (A.WORK_MASTER_ID LIKE '%00000487%' OR
A.WORK_MEMBER_ID LIKE '%00000487%')
AND WORK_SHEET_ID IN
(SELECT OBJECT_ID FROM GG_FORM_RELATIONTEMPLAT)
and (EXISTS (select null
from GG_form_exeGGrecord ptfe,
GG_form_relationtemplat ptfr
where ptfe.rel_temp_id = ptfr.rel_temp_id
and ptfe.mobile_type != 1
and ptfe.state <> 7
and ptfr.object_id = A.WORK_SHEET_ID));
优化第一步: 先找现场实施拿回执行计划
select v.HASH_VALUE,v.CHILD_NUMBER,v.SQL_TEXT from v$sql v where v.SQL_ID='bw2py1pg93nrd';
select * from table(dbms_xplan.display_cursor(HASH_VALUE,CHILD_NUMBER,'advanced'));
优化第二步:GG_FORM_RELATIONTEMPLAT有8万的数据量,GG_FORM_RELATIONTEMPLAT和GG_FORM_EXEGGRECORD大概有6万。发现执行计划中MERGE JOIN SEMI,大量数据下是很耗资源的。
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1207 (100)| |
| 1 | MERGE JOIN SEMI | | 1 | 282 | 734 (1)| 00:00:09 |
|* 2 | TABLE ACCESS BY INDEX ROWID| GG_WORK_SHEET | 115 | 31625 | 453 (1)| 00:00:06 |
|* 3 | INDEX FULL SCAN | PK_GG_WORK_SHEET | 3535 | | 36 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 85 | 473 (1)| 00:00:06 |
|* 5 | TABLE ACCESS FULL | GG_FORM_RELATIONTEMPLAT | 1 | 42 | 278 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL | GG_FORM_EXEGGRECORD | 7760 | 325K| 194 (1)| 00:00:03 |
|* 7 | SORT UNIQUE | | 48606 | 332K| 281 (2)| 00:00:04 |
| 8 | TABLE ACCESS FULL | GG_FORM_RELATIONTEMPLAT | 48606 | 332K| 278 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------------
2 - filter((("A"."WORK_MASTER_ID" LIKE '%00000350%' OR "A"."WORK_MEMBER_ID" LIKE '%00000350%') AND
(TO_NUMBER("A"."IS_COMPLETE")=0 OR TO_NUMBER("A"."IS_COMPLETE")=1)))
3 - filter( IS NOT NULL)
4 - access("PTFE"."REL_TEMP_ID"="PTFR"."REL_TEMP_ID")
5 - filter(TO_NUMBER("PTFR"."OBJECT_ID")=:B1)
6 - filter(("PTFE"."MOBILE_TYPE"<>1 AND "PTFE"."STATE"<>7))
7 - access("WORK_SHEET_ID"=TO_NUMBER("OBJECT_ID"))
优化第三步:找到优化点
1. WORK_SHEET_ID IN (SELECT OBJECT_ID FROM GG_FORM_RELATIONTEMPLAT)可以删除,因为这个条件在exists中有,这个是逻辑错误。这个是优化的最重点,去掉这个,可以消除MERGE JOIN SEMI。
2. IS_COMPLETE = 0改为IS_COMPLETE = '0'
3. gg_task_form_relationtemplat.object_id = GG_TASK_WORK_SHEET.WORK_SHEET_ID由于两个字段的类型不一致和object_id没有建索引,导致不能走索引。即使优化,只能有一张表走索引。找现场查了一下数据量:
GG_TASK_WORK_SHEET(80994),
gg_task_form_relationtemplat(59053)
让object_id做一个转换 使得WORK_SHEET_ID 能够走索引,to_number(ptfr.object_id) = A.WORK_SHEET_ID
最终优化结果:
SELECT * FROM GG_TASK_WORK_SHEET A
WHERE (A.IS_COMPLETE = '0' OR A.IS_COMPLETE = '1')
AND (A.WORK_MASTER_ID LIKE '%00000709%' OR
A.WORK_MEMBER_ID LIKE '%00000709%')
and (EXISTS (select 1
from GG_task_form_exetaskrecord ptfe,
GG_task_form_relationtemplat ptfr
where ptfe.rel_temp_id = ptfr.rel_temp_id
and ptfe.mobile_type != 1
and ptfe.state <> 7
and to_number(ptfr.object_id) = A.WORK_SHEET_ID));
已用时间: 00: 00: 00.28
执行计划
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |288 | 475 (2)|
| 1 | NESTED LOOPS | | 1 |288 | 475 (2)|
| 2 | VIEW | VW_SQ_1 | 7842 |99K| 473 (1)|
| 3 | HASH UNIQUE | | 7842 |650K| |
| 4 | HASH JOIN | | 7842 |650K| 473 (1)|
| 5 | TABLE ACCESS FULL | PROD_TASK_FORM_EXETASKRECORD | 7760 |325K| 194 (1)|
| 6 | TABLE ACCESS FULL | PROD_TASK_FORM_RELATIONTEMPLAT | 48606 |1993K| 278 (1)|
| 7 | TABLE ACCESS BY INDEX ROWID| PROD_TASK_WORK_SHEET | 1 |275 | 1 (0)|
| 8 | INDEX UNIQUE SCAN | PK_PROD_TASK_WORK_SHEET | 1 || 1 (0)|
----------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
13898 consistent gets
0 physical reads
0 redo size
1944 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed